Concatanete new line

Hi,
I need to store data in XML format in the database. When the data is retrieved and printed onto a file, say, it should be automatically printed in a file format i.e. each seperate line on a new line. How can this be done? Can we append a newline character to each line before storing it as a record in the database?/If so, how can this be done?

Thanks in advanceYou can add a Carriage Return and Line Feed to a string as follows:

'your string' + CHAR(13) + CHAR(10)sqlsql

CONCAT in Execute SQL Task

I am curious to know if there is a way to suppress or temporarily store event errors until the final one, then take all those errors and write to db field?

I have a package that uses an event error to store msgs into a field. But, I end up getting several errors that will overwrite my db field each time. Only about two out of 10 are relevent to my problem...

I just need to know if there is a way to suppress the number of event errors that come over , or concat them into one event, per event that occurs? Right now when I hit an error, I end up getting 10 error messages that follow, so my OnError event gets triggered 10 times..

Jason

One way to handle this may be to count the errors using a script in your event handler. Update a variable until you reach the last error you wish to handle, then enable the behaviour you wish to execute.

Donald Farmer

Group Program Manager

SQL Server Integration Services

|||Thanks Donald, how do I determine when the final error occurs in a failure?

So, let's say I have an object that fails, I get 5 onerror event called msgs, how do I know that 5 (or whatever number) is my last error msg for the failure?|||

Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:

UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?

where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?

|||

scoobyjw wrote:

Is it possible to concat in a Execute SQL Task - T-SQL statement? I tried to do this:

UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?

where LogDetails is the field I want to concat with another parameter. However, this doesn't work! IS it supposed to, or am I missing something?

Jason,
Why not try building the SQL statement using a property expression on the SQLStatementSource property?

-Jamie|||The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:

if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?

? Sorry about the pseudo code, I am not an expert at SQL.|||

scoobyjw wrote:

The problem is that when this query runs [UPDATE ETL_Transactions SET LogDetails = LogDetails + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

and LogDetails tries to concat (LogDetails + ?), it errors out because LogDetails has a null value (as it should the first time around)... It doesn't like setting null values in the query. Is there a way to say:

if Not Null(SET LogDetails = LogDetails + ?)
else LogDetails = ?

? Sorry about the pseudo code, I am not an expert at SQL.

Yeah, try this:
[UPDATE ETL_Transactions SET LogDetails = COALESCE(LogDetails, '') + ?, TransactionStatus = 'Failed' WHERE TransactionID = ?]

I really think you should look at using a property expression tho

-Jamie

Compression

Hi everyone,
I am developing a web app and wanting to store documents (doc, xls,
pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
don't know exactly how big the docs are going to be) to compress the
files before putting them into the DB? How does this affect Full Text
Searching? On a wider note what's the attitude of people in terms of
storing documents in the DB versus on the file system. There seems to
be a lot of differing opinions. Any links to resources would be most
appreciated.
Thanks for your help in advance.
Jose
Jose,
First of all, can I assume that you are using SQL Server 2000? If so, could
you post the full output of: SELECT @.@.version -- as this is very help info
in understanding SQL FTS issues.
Secondly, what exactly do you mean by "compress[ing] the files before
putting them into the DB?" Do you mean to store them as ZIP files in columns
defined with the IMAGE datatype? or are you thinking about placing the FT
Catalog folder (SQL00060005) & files on a compressed drive. As for the
latter, I've not done any testing with compressed drive, but Hilary Cotter
has and from past postings on this subject, he's indicated that the
performance gains is mimumal. As for the former, you will need to purchase a
3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
using SQL Server 2000, you can store the zip files in an IMAGE datatype and
define a "file extension" column that will have the value of "zip" and the
ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
Daemon) processes to FT Index the contents of the zip file that contains MS
Word documents. You can purchase the ZIP IFilter from either of the
following two vendors:
http://www.4-share.com/content/products.htm
http://www.ifiltershop.com/zipfilter.html
I've personally have been using the 4-Share ZIP IFilter for over a year and
find it works very well for me.
As for "storing documents in the DB versus on the file system", this is very
much a open and very actively discussed subject, below is one past posting
I've made on this subject with links included...
"There are many issues related to storing the files on disk (faster access,
easy copy/move, etc.) vs. storing the files in the database (transaction
control, change control, audit of changes, etc) than just efficiency and
scalability, although, those are important points... There is a ASPFAQ
website that has a number of references
(http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
checked them all), but one major reason for storing the files in a SQL
Server table's column defined with an IMAGE datatype, is that in SQL Server
2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
Index the contents of supported file types, primarily MS Office, HTML and
3rd party IFilter's like Adobe's PDF files.
As this is one of those FAQ type questions that have been known to start
religious wars or at the very least a flame war, and while Sharon didn't ask
about other application issues that this question often generates as it is
often an open-ended question, i.e., one that never seems to be answered to
everyone's satisfactions as it usually "depends" upon the application or
upon how one defines the word "best"...
As for the Terra Server, checkout the "about page"
(http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
explains how MS and others did it and yes, they used the IMAGE datatype,
specifically
http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
contains the "blob" [image] field where the imagery data is stored.
Jose, depending upon how many files you have, how frequently they change,
how large they are, what the app is, etc. you may want to review the Terra
Server web site and consider the following rule of thumb that they used:
< 1 million images or big images (> 1MB) put them in the file system.
> 1 million and < 1 MB images, put them in SQL Server.
Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
For everything in between, either way will work, depending upon if you need
transactional control over your files. Additionally, if you store the files
in a TEXT or IMAGE column, you can also store related metadata about that
file in SQL Server as well for increased searching capabilities. Also, and
obviously with SQL Server you get built-in support for validating the
consistency of the database, indices, backup, restore, etc. As for loading
&/or extracting the files from SQL Server there are now many methods of
doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
to transform your files in some manner.
Hope this helps, as the primary consideration should always be what is best
for your application...
Regards,
John
"Jose Perez" <jlpv@.totalise.co.uk> wrote in message
news:3724a9d9.0404141212.480e1630@.posting.google.c om...
> Hi everyone,
> I am developing a web app and wanting to store documents (doc, xls,
> pdf, ppt...etc.) within the DB (MSSQL). Is it advisable in general (I
> don't know exactly how big the docs are going to be) to compress the
> files before putting them into the DB? How does this affect Full Text
> Searching? On a wider note what's the attitude of people in terms of
> storing documents in the DB versus on the file system. There seems to
> be a lot of differing opinions. Any links to resources would be most
> appreciated.
> Thanks for your help in advance.
> Jose
|||John,
Thanks a lot for the detailed reply which you gave - it is most
appreciated. In answer to your question, yes I am running SQL Server
2000 (Version Information - Microsoft SQL Server 2000 - 8.00.760
(Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft
Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)).
To clarify, I meant storing documents as "ZIP files in columns defined
with the IMAGE datatype". Thanks for the URLs regarding the ZIP
IFilter - I will investigate them. Your post was very helpful -
thanks.
Jose
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#qtcv8pIEHA.964@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Jose,
> First of all, can I assume that you are using SQL Server 2000? If so, could
> you post the full output of: SELECT @.@.version -- as this is very help info
> in understanding SQL FTS issues.
> Secondly, what exactly do you mean by "compress[ing] the files before
> putting them into the DB?" Do you mean to store them as ZIP files in columns
> defined with the IMAGE datatype? or are you thinking about placing the FT
> Catalog folder (SQL00060005) & files on a compressed drive. As for the
> latter, I've not done any testing with compressed drive, but Hilary Cotter
> has and from past postings on this subject, he's indicated that the
> performance gains is mimumal. As for the former, you will need to purchase a
> 3rd Party IFilter for the WinZIP ZIP file format and then assuming you are
> using SQL Server 2000, you can store the zip files in an IMAGE datatype and
> define a "file extension" column that will have the value of "zip" and the
> ZIP IFilter will be called by the MSSearch and the mssdmn (Search Filter
> Daemon) processes to FT Index the contents of the zip file that contains MS
> Word documents. You can purchase the ZIP IFilter from either of the
> following two vendors:
> http://www.4-share.com/content/products.htm
> http://www.ifiltershop.com/zipfilter.html
> I've personally have been using the 4-Share ZIP IFilter for over a year and
> find it works very well for me.
> As for "storing documents in the DB versus on the file system", this is very
> much a open and very actively discussed subject, below is one past posting
> I've made on this subject with links included...
> "There are many issues related to storing the files on disk (faster access,
> easy copy/move, etc.) vs. storing the files in the database (transaction
> control, change control, audit of changes, etc) than just efficiency and
> scalability, although, those are important points... There is a ASPFAQ
> website that has a number of references
> (http://www.aspfaq.com/show.asp?id=2149) and links to KB articles (I've not
> checked them all), but one major reason for storing the files in a SQL
> Server table's column defined with an IMAGE datatype, is that in SQL Server
> 2000 you can take advantage of the new Full-Text Search (FTS) feature to FT
> Index the contents of supported file types, primarily MS Office, HTML and
> 3rd party IFilter's like Adobe's PDF files.
> As this is one of those FAQ type questions that have been known to start
> religious wars or at the very least a flame war, and while Sharon didn't ask
> about other application issues that this question often generates as it is
> often an open-ended question, i.e., one that never seems to be answered to
> everyone's satisfactions as it usually "depends" upon the application or
> upon how one defines the word "best"...
> As for the Terra Server, checkout the "about page"
> (http://terraserver-usa.com/about.aspx?n=AboutBody) on the site that
> explains how MS and others did it and yes, they used the IMAGE datatype,
> specifically
> http://terraserver-usa.com/about.asp...utTechDbschema (The Imagery table
> contains the "blob" [image] field where the imagery data is stored.
> Jose, depending upon how many files you have, how frequently they change,
> how large they are, what the app is, etc. you may want to review the Terra
> Server web site and consider the following rule of thumb that they used:
> < 1 million images or big images (> 1MB) put them in the file system.
> Note, you can also use "Text-in-Row" if the files are >7000 bytes on avg.
> For everything in between, either way will work, depending upon if you need
> transactional control over your files. Additionally, if you store the files
> in a TEXT or IMAGE column, you can also store related metadata about that
> file in SQL Server as well for increased searching capabilities. Also, and
> obviously with SQL Server you get built-in support for validating the
> consistency of the database, indices, backup, restore, etc. As for loading
> &/or extracting the files from SQL Server there are now many methods of
> doing this via BCP, BULK INSERT as well as ADO Stream DTS too, if you need
> to transform your files in some manner.
> Hope this helps, as the primary consideration should always be what is best
> for your application...
> Regards,
> John
>
> "Jose Perez" <jlpv@.totalise.co.uk> wrote in message
> news:3724a9d9.0404141212.480e1630@.posting.google.c om...

Complex Joins

Hi,
I am unsure about the best approach to the following :
I have a database where I have to store about 60000 different Objects, and
each Object has a number of Properties attached to it (the number of
properties attached and the type of properties can vary per object).
Thus far my table structure looks like this :
Table Objects (
Id BigInt,
Descr varchar(250),
...
)
Table Properties (
Id BigInt,
Descr varchar(50)
)
Table PropertyValues (
Id BigInt,
ObjectId BigInt,
PropertyId BigInt,
PropertyValue varchar(50)
)
Now my application lets the user select specific rows from the Objects table
using selections of different combinations of propertyvalues. This leads to
rather complex queries that sometimes contain between 1 to 15 joins of the
same table (PropertyValues).
Something like this:
Select Id, Descr from Object INNER JOIN
PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER JOIN
PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER JOIN
...
PropertyValues K on K.PropertyId = 11 AND K.PropertyValue = 'AnotherValue'
I would like to know if this is the right way to create this functionality
or should I think of another table structure.
Thanks
HermanHerman
I think you designed your database well. That enforce many-many relationship
between objects and property tables.
Now regarding to queries.
I'd start to write the query with follow structure.
Select Id, Descr from Object O INNER JOIN PropertyValues PV
ON O.ObjectId=PV.ObjectId INNER JOIN Properties P
ON PV.PropertyId =P.PropertyId
WHERE PV.PropertyId IN ('DEF' .....) AND
PV.ObjectId IN (1,2....)
Note: I don't know exactly what are your business requirements ,therfore if
you something wrong try modify it for
your needs.
"Herman Lammers" <hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote in message
news:uApSPyhnDHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am unsure about the best approach to the following :
> I have a database where I have to store about 60000 different Objects, and
> each Object has a number of Properties attached to it (the number of
> properties attached and the type of properties can vary per object).
> Thus far my table structure looks like this :
> Table Objects (
> Id BigInt,
> Descr varchar(250),
> ...
> )
> Table Properties (
> Id BigInt,
> Descr varchar(50)
> )
> Table PropertyValues (
> Id BigInt,
> ObjectId BigInt,
> PropertyId BigInt,
> PropertyValue varchar(50)
> )
> Now my application lets the user select specific rows from the Objects
table
> using selections of different combinations of propertyvalues. This leads
to
> rather complex queries that sometimes contain between 1 to 15 joins of the
> same table (PropertyValues).
> Something like this:
> Select Id, Descr from Object INNER JOIN
> PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER
JOIN
> PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER
JOIN
> ...
> PropertyValues K on K.PropertyId = 11 AND K.PropertyValue = 'AnotherValue'
> I would like to know if this is the right way to create this functionality
> or should I think of another table structure.
> Thanks
> Herman
>
>|||Although it is possible to implement a generic solution for any
application with an OO design like this, don't go there. Performance
will be horrendous and it will be difficult to enforce data integrity
because all user data will be stored in the single varchar(50)
PropertyValue column. This is problematic for many reasons.
Note that I'm not saying that one should never implement this type of
design. OO design may be appropriate for infrequently accessed
configuration data, such as meta-data loaded at application startup but
should generally be avoided in a relational database. It is better to
stick with the relational model in a relational database like SQL
Server.
Consider the schema of the Northwind Orders table below and ask yourself
how you would perform the following with your schema:
- ensure OrderID is unique (primary key)
- ensure data is valid for the appropriate data type (e.g. int and
datetime)
- insert an order with a 60 character ShipAddress or unicode data
- enforce referential integrity (e.g. an order must have a customer)
- query data (e.g. print shipping labels)
- delete data for a given order
CREATE TABLE [Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
),
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [Shippers] (
[ShipperID]
)
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Herman Lammers" <hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote in
message news:uApSPyhnDHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am unsure about the best approach to the following :
> I have a database where I have to store about 60000 different Objects,
and
> each Object has a number of Properties attached to it (the number of
> properties attached and the type of properties can vary per object).
> Thus far my table structure looks like this :
> Table Objects (
> Id BigInt,
> Descr varchar(250),
> ...
> )
> Table Properties (
> Id BigInt,
> Descr varchar(50)
> )
> Table PropertyValues (
> Id BigInt,
> ObjectId BigInt,
> PropertyId BigInt,
> PropertyValue varchar(50)
> )
> Now my application lets the user select specific rows from the Objects
table
> using selections of different combinations of propertyvalues. This
leads to
> rather complex queries that sometimes contain between 1 to 15 joins of
the
> same table (PropertyValues).
> Something like this:
> Select Id, Descr from Object INNER JOIN
> PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER
JOIN
> PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER
JOIN
> ...
> PropertyValues K on K.PropertyId = 11 AND K.PropertyValue ='AnotherValue'
> I would like to know if this is the right way to create this
functionality
> or should I think of another table structure.
> Thanks
> Herman
>
>|||Hi Dan,
thanks for your reply. Creating this generic solution was not exactly what I
had in mind.
The model I described is just a (small) part of traditional relational
model.
The reason I came up with this model is that I wanted to give my customer
the possibility of
assigning extra fields (Properties) to (groups of) products (Objects). This
structure is then being used for product selection only.
My customer sells fasteners which can be divided into several product groups
(washers, nuts, bolts)
Apart from product descr, Product price etc. there are numerous (more then I
ever thought) other properties which describe the product, and these depend
on the type of product. (washers have an inner and outer diameter, and a
thickness, bolts have a product length, headtype etc). This variety brought
me to this model.
Any values critical to the rest of the application would be taken out of the
structure, and stored in the product table.
Thanks anyway
Herman
"Dan Guzman" <danguzman@.nospam-earthlink.net> schreef in bericht
news:ePCz1VinDHA.2080@.TK2MSFTNGP10.phx.gbl...
> Although it is possible to implement a generic solution for any
> application with an OO design like this, don't go there. Performance
> will be horrendous and it will be difficult to enforce data integrity
> because all user data will be stored in the single varchar(50)
> PropertyValue column. This is problematic for many reasons.
> Note that I'm not saying that one should never implement this type of
> design. OO design may be appropriate for infrequently accessed
> configuration data, such as meta-data loaded at application startup but
> should generally be avoided in a relational database. It is better to
> stick with the relational model in a relational database like SQL
> Server.
> Consider the schema of the Northwind Orders table below and ask yourself
> how you would perform the following with your schema:
> - ensure OrderID is unique (primary key)
> - ensure data is valid for the appropriate data type (e.g. int and
> datetime)
> - insert an order with a 60 character ShipAddress or unicode data
> - enforce referential integrity (e.g. an order must have a customer)
> - query data (e.g. print shipping labels)
> - delete data for a given order
>
> CREATE TABLE [Orders] (
> [OrderID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerID] [nchar] (5) NULL ,
> [EmployeeID] [int] NULL ,
> [OrderDate] [datetime] NULL ,
> [RequiredDate] [datetime] NULL ,
> [ShippedDate] [datetime] NULL ,
> [ShipVia] [int] NULL ,
> [Freight] [money] NULL CONSTRAINT [DF_Orders_Freight] DEFAULT (0),
> [ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ShipPostalCode] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
> (
> [OrderID]
> ),
> CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
> (
> [CustomerID]
> ) REFERENCES [Customers] (
> [CustomerID]
> ),
> CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
> (
> [EmployeeID]
> ) REFERENCES [Employees] (
> [EmployeeID]
> ),
> CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
> (
> [ShipVia]
> ) REFERENCES [Shippers] (
> [ShipperID]
> )
> )
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Herman Lammers" <hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote in
> message news:uApSPyhnDHA.1676@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I am unsure about the best approach to the following :
> >
> > I have a database where I have to store about 60000 different Objects,
> and
> > each Object has a number of Properties attached to it (the number of
> > properties attached and the type of properties can vary per object).
> >
> > Thus far my table structure looks like this :
> >
> > Table Objects (
> > Id BigInt,
> > Descr varchar(250),
> > ...
> > )
> >
> > Table Properties (
> > Id BigInt,
> > Descr varchar(50)
> > )
> >
> > Table PropertyValues (
> > Id BigInt,
> > ObjectId BigInt,
> > PropertyId BigInt,
> > PropertyValue varchar(50)
> > )
> >
> > Now my application lets the user select specific rows from the Objects
> table
> > using selections of different combinations of propertyvalues. This
> leads to
> > rather complex queries that sometimes contain between 1 to 15 joins of
> the
> > same table (PropertyValues).
> >
> > Something like this:
> >
> > Select Id, Descr from Object INNER JOIN
> > PropertyValues A on A.PropertyId = 1 AND A.PropertyValue = 'ABC' INNER
> JOIN
> > PropertyValues B on B.PropertyId = 2 AND B.PropertyValue = 'DEF' INNER
> JOIN
> > ...
> > PropertyValues K on K.PropertyId = 11 AND K.PropertyValue => 'AnotherValue'
> >
> > I would like to know if this is the right way to create this
> functionality
> > or should I think of another table structure.
> >
> > Thanks
> >
> > Herman
> >
> >
> >
>|||On Wed, 29 Oct 2003 16:57:44 +0100, "Herman Lammers"
<hlammers_REMOVE_THIS@.allround-ipDOTnl> wrote:
>Hi Dan,
>thanks for your reply. Creating this generic solution was not exactly what I
>had in mind.
>The model I described is just a (small) part of traditional relational
>model.
>The reason I came up with this model is that I wanted to give my customer
>the possibility of
>assigning extra fields (Properties) to (groups of) products (Objects). This
>structure is then being used for product selection only.
>My customer sells fasteners which can be divided into several product groups
>(washers, nuts, bolts)
>Apart from product descr, Product price etc. there are numerous (more then I
>ever thought) other properties which describe the product, and these depend
>on the type of product. (washers have an inner and outer diameter, and a
>thickness, bolts have a product length, headtype etc). This variety brought
>me to this model.
Well, it's the proper theoretical normalization of the table, but it
seems, to me, like you might want groupings... especially because I
think you'll end up having a hundred different records that all say
"1/4 inch diameter"
Maybe a bolt table, a washers table, etc., all with a foreign "item
number" key - foreign, because you could autonumber it on another
table that has two columns, "item number", "item type" - and each
table would have all of the necessary columns to list the necessary
properties. If you need additional properties for select items, then
you can create another linked table.
So, basically, you have
Item Listing:
"ItemNumber", "ItemType"
Washer Properties:
"Item Number" "InnerDiameter", "OuterDiameter", "Material", "Shape",
"Thickness"
I think that you want the "ItemType" to be a direct link to the
relevant table, but I don't know how to do that (or whether it *can*
be done).
This will cut down on the number of joins and make your table
structure a lot more understandable.
(Warning: I'm a DB administrator, not a developer, and your advice is
worth half of what you paid me for it.)

Complex Join

Hello,

I am blocked with a Join Request

I have 3 tables :
TaUtilisateurs store the users
TaClients store the customers
TaJoinClients_Utilisateurs store right of user on customers :

for example :

TaUtilisateurs Id_Utilisateur Login 1 Nico 2 Philo

TaClients Id_Client Entreprise 0 Company 1 4036 Company 2

TaJoinClients_Utilisateurs Id_JoinClient_Utilisateur Id_Utilisateur Id_Client Editeur Administrateur 1 1 0 True True 2 1 4036 True True 4 2 4036 True True

I wrote a stored procedure to display the rights of users on each customer :

SELECT dbo.TaUtilisateurs.Login, dbo.TaClients.Entreprise, dbo.TaJoinClients_Utilisateurs.Administrateur, dbo.TaJoinClients_Utilisateurs.Editeur,
dbo.TaUtilisateurs.Id_Utilisateur, dbo.TaClients.Id_Client
FROM dbo.TaUtilisateurs LEFT OUTER JOIN
dbo.TaJoinClients_Utilisateurs ON dbo.TaUtilisateurs.Id_Utilisateur = dbo.TaJoinClients_Utilisateurs.Id_Utilisateur RIGHT OUTER JOIN
dbo.TaClients ON dbo.TaJoinClients_Utilisateurs.Id_Client = dbo.TaClients.Id_Client
WHERE (dbo.TaUtilisateurs.Id_Utilisateur = @.Id_Utilisateur)

for example for user philo :

Login Entreprise Administrateur Editeur Id_Utilisateur Id_Client Philo Company 2 Vrai Vrai 2 4036

How can I do to have all company listed like that:

Login Entreprise Read Administrateur Editeur Id_Utilisateur Id_Client Philo Company 1 False False False 2 4036 Philo Company 2 True True True 2 4036

Read mean a row exist in TaJoinClients_Utilisateurs for User and customer

I hope you can help me ...

thanks !Here is one way:

select tu.Login,tc.Entreprise,case when tj.Id_Client=tc.Id_Client then 'True' else 'False' end [Read]
,case when tj.Id_Client=tc.Id_Client then tj.Administrateur else 'False' end [Administrateur]
,case when tj.Id_Client=tc.Id_Client then tj.Editeur else 'False' end [Editeur]
,tu.Id_Utilisateur
,tj.Id_Client
from TaUtilisateurs tu join TaJoinClients_Utilisateurs tj on tu.Id_Utilisateur=tj.Id_Utilisateur
cross join TaClients tc
where tu.Login='Philo'|||

It works fine with user philo but not with user Nico...

|||

So, it's both 'true' and 'false' you want. See if this helps:

declare @.login sysname
set @.login='Philo' --'Nico'

select *
from (
-- True
select tu.Login,tc.Entreprise,'True' [Read]
,tj.Administrateur [Administrateur]
,tj.Editeur [Editeur]
,tu.Id_Utilisateur
,tj.Id_Client
from TaUtilisateurs tu join TaJoinClients_Utilisateurs tj on tu.Id_Utilisateur=tj.Id_Utilisateur
join TaClients tc on tj.Id_Client=tc.Id_Client
union all
-- False
select tu.Login,tc.Entreprise,'False' [Read]
,'False' [Administrateur]
,'False' [Editeur]
,tu.Id_Utilisateur
,tc.Id_Client
from TaClients tc join TaUtilisateurs tu on not exists(select *
from TaJoinClients_Utilisateurs tj
where tu.Id_Utilisateur=tj.Id_Utilisateur and tj.Id_Client=tc.Id_Client)
)_v
where Login=@.login

|||Great, it is exactly what i need !! thank you very much !!!

Complex DB Search Forms (Store Proc vs. Complex Where)

I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.

Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.

I build clauses like this (i.e., 4 fields shown):

SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )

My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.You can achieve the exact same result either way you do it. It is more object oriented to pass the parameters into the SP and then build your WHERE clause. Are you good at TSQL? If so then why not put them in a SP. If you have never written an SP before then stay with what you know and write the code in your page.

I have done it both ways many many many times, it all depends on the situation. You will have to write the same code in the SP than where it is now, just different syntax.

HTH|||Ok, that makes me feel better. I know it's generally prefered to use SP, but I've never written one. If I did make one, I guess it'd be like a function with 15 parameters?

I'm doing enough learning with ASP.NET, so I'll hold off on SP's for now.

I'll have to find me a good SP book. Performance isn't an issue now, but hopefully it will be!|||You are not really giving up that much performance. My applications generally use very large databases (11 million+ records) with very complex and dynamic Where clauses. If all tables are indexed properly then the performance difference is not very evident.

Use your current constraints as your guide (time, etc.).|||If you are curious how this issue was resolved at this ASP.NET Forum (which we are all posting at now), I am posting below the stored procedure used to search the fourms. You can see that a parameter @.SearchTerms is used to hold the whole where clause. I like this way because it is easier to build dynamically the where clause in c# or vb.net and then make use of the better performance that sp provides. Furthermore this sp below gives you the ability to display only a given number of results (very convenient to use with a datagrid with paging)

CREATE PROCEDURE forums_GetSearchResults
(
@.SearchTerms nvarchar(500),
@.Page int,
@.RecsPerPage int,
@.UserName nvarchar(50)
)
AS
CREATE TABLE #tmp
(
ID int IDENTITY,
PostID int
)
DECLARE @.sql nvarchar(1000)
SET NOCOUNT ON
SELECT @.sql = 'INSERT INTO #tmp(PostID) SELECT PostID ' +
'FROM Posts P (nolock) INNER JOIN Forums F (nolock) ON F.ForumID = P.ForumID ' +
@.SearchTerms + ' ORDER BY ThreadDate DESC'
EXEC(@.sql)
-- ok, all of the rows are inserted into the table.
-- now, select the correct subset
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
DECLARE @.MoreRecords int
SELECT @.MoreRecords = COUNT(*) FROM #tmp -- WHERE ID >= @.LastRec

-- Select the data out of the temporary table
IF @.UserName IS NOT NULL
SELECT
T.PostID,
P.ParentID,
P.ThreadID,
P.PostLevel,
P.SortOrder,
P.UserName,
P.Subject,
P.PostDate,
P.ThreadDate,
P.Approved,
P.ForumID,
F.Name As ForumName,
MoreRecords = @.MoreRecords,
Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
P.Body,
P.TotalViews,
P.IsLocked,
HasRead = 0 -- not used
FROM
#tmp T
INNER JOIN Posts P (nolock) ON
P.PostID = T.PostID
INNER JOIN Forums F (nolock) ON
F.ForumID = P.ForumID
WHERE
T.ID > @.FirstRec AND ID < @.LastRec AND
(P.ForumID NOT IN (SELECT ForumID from PrivateForums) OR
P.ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @.UserName)))
ELSE
SELECT
T.PostID,
P.ParentID,
P.ThreadID,
P.PostLevel,
P.SortOrder,
P.UserName,
P.Subject,
P.PostDate,
P.ThreadDate,
P.Approved,
P.ForumID,
F.Name As ForumName,
MoreRecords = @.MoreRecords,
Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
P.Body,
P.TotalViews,
P.IsLocked,
HasRead = 0 -- not used
FROM
#tmp T
INNER JOIN Posts P (nolock) ON
P.PostID = T.PostID
INNER JOIN Forums F (nolock) ON
F.ForumID = P.ForumID
WHERE
T.ID > @.FirstRec AND ID < @.LastRec AND
P.ForumID NOT IN (SELECT ForumID from PrivateForums)

SET NOCOUNT OFF|||::You can achieve the exact same result either way you do it.

No, not EXACTLY the same result.

::It is more object oriented to pass the parameters into the SP and then build your WHERE
::clause.

Hmpf. Please dont tell me you believe this.

I mean, frankly, in how far is it more OBJECT ORIENTED to actually write a non-objectoriented METHOD (an SP is nothing more)?

More strucutre, maybe, better practice, maybe, but not more "object oriented".

Some negatives:

* Be sure to have automatic recompile switched on for this SP, as otherwise you just say goodbye to performance for your type of query.

Let see the last post:

::Furthermore this sp below gives you the ability to display only a given number of results
::(very convenient to use with a datagrid with paging)

Yea, righ. It is not exactly like you could not do it right in dynamic SQL either. Frankly, the correct way to ask for only X result is "SELECT TOP X". I somehow miss this on the SP.

* YOu dont need a temp tabl. Use query materialization to use actually use the optimizer. The use of a temp table is about the last way I would have tried to solve this.
* Frankly, the approach of selecting ALL posts into a temp table, just to then only select a subset of this sounds horrible performance wise.
* The "IF" makes not really a lot of sense - the two cases can be put into one pretty easily.|||>>No, not EXACTLY the same result.

Is the end result not a Query ?|||thona,

perhaps you are right about the IF statement. I think this stored procedure together with the ASP.NET Forums are written by a microsoft team (so it says in the agreement that comes with the free download of the forums).

Perhaps there is a better way. could you please give example of "Use query materialization to use actually use the optimizer" because I am not very familiar with this technique?

SELECT TOP ... will only work the first time you select from the database. but how would you handle displaying next result sets?
That is why the proc takes @.page and @.recsperpage parameter in order to determine from which to which result to return. If the user sees 10 results per page and he is requesting the second result set then the proc will calculate @.FirstRec and @.LastRec. But you cannot use these two in a querry against the whole database. you can apply them in a second select only against the relevant results.

If I am wrong, I will be grateful if you help me understand my mistake as efficient searching is very important indeed

Compile store procedures

I'm having a excessive amount of lock timeout and I discover that every time
when users execute store procedures, these procedures are compile before
execution. Anyone has an idea about this behaviour ?
thank you for your help...Check this link:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q243/5/86.ASP&NoWebContent=1
--
HTH,
SriSamp
Please reply to the whole group only!
"Tony" <news@.hotmail.com> wrote in message
news:%23l0Kxh8PDHA.2424@.tk2msftngp13.phx.gbl...
> I'm having a excessive amount of lock timeout and I discover that every
time
> when users execute store procedures, these procedures are compile before
> execution. Anyone has an idea about this behaviour ?
> thank you for your help...
>

compile a store proc again

Hi there!!

Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.

Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.

I am looking for something which will force the existing stored procs to compile and let me see the error if any.

Best Regards

Rahul Kumar, Software Engineer, India

SP_RECOMPILE is helpful to recompile the next time they are run.

It will remove all the chache Plan for the current object when you run the SP it will compile it.

You can do the following step to get the compile errors...

Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'

But the drawback here is you can't find the error is occured by compiler or runtime execution.

|||

This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.

|||

You can try calling DBCC FREEPROCCACHE

This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.

|||

It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.

When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.

What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)

|||

Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"

I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.

One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.

So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.

Best Regards

Rahul

|||

For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.

If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:

DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0

--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry

Object_definition is a new function in 2005 that gives you the text of the object in a nice package.

But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

|||

Thanks Louis

this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.

Can we rewrite your given code as

select object_name(id) from syscomments where text like '%=*%'

@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.

Regards

Rahul

|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||

Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.

And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.

My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)

|||

Just to site one example which made me wondering--

Here is a line of code from my stored proc

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-

Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43

Incorrect syntax near 'TSEQUAL'.

Regards

Rahul Kumar

|||

Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.

It isn't necessary, so you can change:

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

to

TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))

though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:

TmStp = @.xTmStp

compile a store proc again

Hi there!!

Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.

Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.

I am looking for something which will force the existing stored procs to compile and let me see the error if any.

Best Regards

Rahul Kumar, Software Engineer, India

SP_RECOMPILE is helpful to recompile the next time they are run.

It will remove all the chache Plan for the current object when you run the SP it will compile it.

You can do the following step to get the compile errors...

Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'

But the drawback here is you can't find the error is occured by compiler or runtime execution.

|||

This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.

|||

You can try calling DBCC FREEPROCCACHE

This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.

|||

It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.

When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.

What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)

|||

Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"

I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.

One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.

So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.

Best Regards

Rahul

|||

For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.

If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:

DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0

--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry

Object_definition is a new function in 2005 that gives you the text of the object in a nice package.

But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

|||

Thanks Louis

this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.

Can we rewrite your given code as

select object_name(id) from syscomments where text like '%=*%'

@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.

Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.

Regards

Rahul

|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||

Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.

And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.

My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)

|||

Just to site one example which made me wondering--

Here is a line of code from my stored proc

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-

Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43

Incorrect syntax near 'TSEQUAL'.

Regards

Rahul Kumar

|||

Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.

It isn't necessary, so you can change:

tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))

to

TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))

though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:

TmStp = @.xTmStp

Compilation error on store procedure

Hi all,
Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10
Syntax error converting the varchar value'The account type is already exist' to a column of data type int.
Here is my procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN @.message
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)
--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN @.message

END
ELSE
BEGIN

COMMIT TRANSACTION
END

RETURN @.@.ROWCOUNT
END
GO

--execute the procedure
DECLARE @.message VARCHAR (40);
EXEC NewAcctTypeSP 'CDs', @.message;
I am not quite sure where I got a type converting error in my code and anyone can help me solve it?
(p.s. I want to return the @.message value to my .aspx page)
Thanks.

You should probably get rid of the RETURN @.@.ROWCOUNT line. It may be getting confused that sometimes you return a varchar and other times int (the rowcount).
Marcie|||Hi Marcie,
I have get rid of the @.@.RowCount, however, I didn't get value from my @.message, it just returned 'undefined'.
do u know why, and how can i fix it?
Thanks.|||Where is it undefined, from your code or still running your test proc?
Marcie|||

when I executed my .aspx page, I input a duplicated value in a field intentionally and since it should return an error message that I have defined in my dtore procedure, however, I just got 'undefined' instead of my error message...
so, here is my .aspx code:
public function SubmitClick (sender:Object, e:EventArgs) : void
{
if (Page.IsValid)
{
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));
var acctTypeDA : SqlDataAdapter = new SqlDataAdapter ("select * from AcctTypeCatalog", myConnection);

acctTypeDA.InsertCommand = new SqlCommand("NewAcctType", myConnection);
acctTypeDA.InsertCommand.CommandType = CommandType.StoredProcedure;
acctTypeDA.InsertCommand.Parameters.Add(new SqlParameter("@.acctType", SqlDbType.VarChar, 20)).Value = accountType.Text;

var myParm : SqlParameter = acctTypeDA.InsertCommand.Parameters.Add("@.message", SqlDbType.VarChar, 40);
myParm.Direction = ParameterDirection.Output;

<%--try to get value from @.message and assign it to the variable --%>
var msg : String = acctTypeDA.InsertCommand.Parameters("@.message").Value;
msgLabel.Text = msg;

BindGrid();

}
else
{
msgLabel.Text = "The Page contains error!"
}
}
my store procedure:
ALTER PROC NewAcctTypeSP
(@.acctType VARCHAR(20), @.message VARCHAR (40) OUT)
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
SET @.message = 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
SET @.message = 'Insertion Successful!'
COMMIT TRANSACTION
END
RETURN
END
GO
I have tested my procedure, it works fine in SQL server, however, don't know why I couldn't get the value from @.message on my .aspx page.
Any idea?
Thanks

|||What language is that? You won't be able to get the value of your OUTPUT parameter until right after the command has executed, something like an ExecuteNonQuery line.
Marcie|||hi Marcie,
I used JScript to create my page with using the store procedure. (JScipt is very similar with C#)
I have reviewed the tutorial from the ASP.net and it doesn't have any ExecuteNonQuery statement on the following example:

<script language="JScript" runat="server">
publicfunction GetEmployees_Click(sender : Object, e : EventArgs) :void
{
var myConnection : SqlConnection =new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("NWString"));
var myCommand : SqlDataAdapter =new SqlDataAdapter("SalesByCategory", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.CategoryName", SqlDbType.NVarChar, 15));
myCommand.SelectCommand.Parameters("@.CategoryName").Value = SelectCategory.Value;
myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.OrdYear", SqlDbType.NVarChar, 4));
myCommand.SelectCommand.Parameters("@.OrdYear").Value = SelectYear.Value;
var ds : DataSet =new DataSet();
myCommand.Fill(ds, "Sales");
MyDataGrid.DataSource=ds.Tables("Sales").DefaultView;
MyDataGrid.DataBind();
}
</script>

So, should I use the ExecuteNonQuery statement to get my output parameter? any example you could show me for getting back the output parameter value with using store procedure(it's ok if that's written in VB or C#)?
i have tried to find some material about this problem, but can't get any of it ...
Thanks again.
|||In the example you show here, the Command gets executed in the myCommand.Fill line, your code didn't have anything like that.
Since you have your command object set up as the InsertCommand of a DataAdapter object, I believe your command would automatically fire when the .Update method is called.
Marcie|||The problem is that return parameters in T-SQL arealways integers. That is whay you got the compilation error, and why you are not getting the value you expect in your client-side code.
Even though you have @.message defined as an output parameter, the RETURN @.message is causing you to error out.|||

Thanks pjmcb,
I already got my problem fixed.