default date in sql2k

Having a table with two datetime columns(date_ & time_. One to store the date and the other to store the time (my client's design).

When I insert a date alone to the date_ column the time defaults to 12:00:00 AM (as expected).
But I have a problem when inserting / updating the time in the time_ column. When i insert the time from my asp application / query analyzer the date defaults to 1900-1-1(expected). When i insert the time from enterprise manager the date defaults to 1899-12-30.

Can anybody explain me why the date defaults to 1899-12-30 in enterprise manager

thanksThe following article explains this in detail:

article (http://www.databasejournal.com/features/mssql/article.php/1494281)

If you need further discussion, let me know and I will give you my 2 cents as to what is occurring.sql

Decrease Costs in INSERTS/UPDATES Queries.

Hi, :)

I wanna know how can I do to reduce the Costs of INSERT/UPDATE Queries that I have, but in some Traces of SQL Profiler, I see that this queries taken most performance of my server.

Remember that all tables that I have are Indexed Tables with the default value of Fill Factor.

Tanks,The more indexes you have the longer an insert will take.
On heavy insert systems (telecoms for instance) I only ever have a single clustered index on the identity column of the call table.

For updates try not to update indexed fields, also do not extend the length of any fields then you should get a direct update in place (as long as you don't have triggers or replication enabled on the table) which is the fastest type (hope that statement is still valid).

Otherwise make sure the transaction log is on a different device to the data and that you are not getting contention on the table.

Decoding Decimal Form of HRESULT from ErrorCode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to another OLE-DB Command transformation that logs the error on that row to a "failed rows" table. In this table I log the ErrorCode and ErrorColumn values that come with the error path from the first OLE-DB Command.

OK, that's all working great. However, here's the kicker: there's no error description value. The ErrorCode value, naturally, is the decimal form of an HRESULT--for example, -1071607696. Without some further information, however, this code is not useful for troubleshooting.

Has anyone figured out a trick here? I'm not even certain that this is an SSIS HRESULT, since it could for all I know be from the OLE-DB layer, the database layer, or somewhere else.

Thanks,
Dan

http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0209070.html

The DescribeRedirectedErrorCode method may also be of some use here. Not sure what you actually expect to do, but normally I would log the full error via another means, such as the built in SSIS logging. Use that for the text description. The code would alllow you to automate handling of different error scenarios.

|||Hi Darren,

Thanks for the link to that error info (I had actually found that subsequent to my original post with some additional searching), and for the pointer to DescribeRedirectedErrorCode. I did not know about the existence of this method. It's also interesting to find out that this is an SSIS HRESULT even though the error pertains to a foreign key constraint violation in the database layer--is SSIS re-interpreting the original SQL Server exception? I wonder whether an ErrorCode will always be a native SSIS error code...?

You refer to logging the full error via another means. I get the feeling that I'm missing an opportunity here to be logging a row-level error in a data flow in a different way than I am now. I'd obviously prefer to log the full error info instead of just ErrorCode and ErrorColumn. However, I don't see how this would work.

Do you redirect the row first through a script component so that you can programatically use the ErrorCode to call DescribeRedirectedErrorCode for additional info for the subsequent logging?

Or are you catching the error in the control of flow? How does that work exactly? Does a row-level exception in a data flow fire an error event at the control of flow level? I guess I was specifically trying to prevent that by using Redirect Row from my OLE-DB Command transform--I just want to log the problem with that row and keep moving through the rest of the rows...

Thanks,
Dan

|||

You may also be interested in taking a look at "Enhancing an Error Output with the Script Component," which was new in the December drop of BOL.

Also, be aware of the "Integration Services Error and Message Reference" list which includes the HRESULT in hex. As for converting (in code), although I don't have the code that I used to create the list in front of me right now, I believe there are format specifiers that you can use with .ToString() to convert quite simply between decimal and hexadecimal representations.

-Doug

|||That was exactly what I needed, Douglas, thank you. I am going to use that trick on future error pathways. Hopefully a future release of SSIS will make this unnecessary by adding an intrinsic ErrorDescription column to go along with ErrorCode and ErrorColumn.

Here is the link for those who'd like to read the article:

http://msdn2.microsoft.com/en-us/library/ms345163.aspx

Has anyone else noticed that Google's URLs pointing to MSDN articles have a "(d=robot)" in them, so that when you click from Google to MSDN the article shows up with no styling or sidebar navigation. Example:

http://msdn2.microsoft.com/en-us/library(d=robot)/ms345163.aspx

I've noticed it doing this the last couple days.

Thanks again,
Dan

|||

Doug,

What is the difference between GetErrorDescription and DescribeRedirectedErrorCode, they both seem remarkably similar, apart from the hosting class. Context maybe?

How does using GetErrorDescription like this know about the upstream component that raised the error? Surely it needs to know, since if as a component author I generate my own error codes, I would then override DescribeRedirectedErrorCode to give you the description, but how do you call my implementation?

|||

Darren,

A complete answer will need to come from the dev team. The methods seem to do the same thing, as you observed - get a description from an error code. I suspect that this works only with Integration Services errors and messages, and that it is made possible (or easier) by the fact that all of these are consolidated in the managed Microsoft.SqlServer.Dts.Runtime.HResults class. I'll see what I can find out.

-Doug

|||

You can also use my enhanced error component to add the column name of the column that failed to the error output.

I guess I should add the error description as well

Decimal being rounded in Insert statement

Hi - when I insert a decimal amount into my SQL Server 2000 database, it
rounds it up.
My table has a column:
customer_amount decimal 9
..and has Precision set at 18, and Scale set at 2.
I am grabbing the decimal value from a textbox on my form, and passing
it as a parameter in ASP.Net:
cmd.Parameters.Add(New SqlParameter("@.customer_amount",
CType(tbConfAmount.Text, Decimal)))
My SP is:
CREATE Procedure addCustAmount
(
@.customer_amount decimal
)
AS INSERT INTO tblCustomerAmount (customer_amount)
VALUES
(@.customer_amount)
If my text box says: 23.79, the value that ends up in my database is 24.
Can anyone please advise where I'm going wrong?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark
select cast (23.79 as decimal)
select cast (23.79 as decimal(5,2))
"Mark" <anonymous@.devdex.com> wrote in message
news:%23mCgVUpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi - when I insert a decimal amount into my SQL Server 2000 database, it
> rounds it up.
> My table has a column:
> customer_amount decimal 9
> ..and has Precision set at 18, and Scale set at 2.
> I am grabbing the decimal value from a textbox on my form, and passing
> it as a parameter in ASP.Net:
> cmd.Parameters.Add(New SqlParameter("@.customer_amount",
> CType(tbConfAmount.Text, Decimal)))
> My SP is:
> CREATE Procedure addCustAmount
> (
> @.customer_amount decimal
> )
> AS INSERT INTO tblCustomerAmount (customer_amount)
> VALUES
> (@.customer_amount)
> If my text box says: 23.79, the value that ends up in my database is 24.
> Can anyone please advise where I'm going wrong?
> Thanks, Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You don't specify a Scale for the parameter for your stored procedure, so
that defaults to 0. Decimal without Precision or Scale is decimal(18,0) by
default. Just change the datatype of the parameter to decimal(18,2). You
might have to do that in the parameter definition in ASP.Net as well btw,
but my ADO.Net is a bit rusty.
Jacco Schalkwijk
SQL Server MVP
"Mark" <anonymous@.devdex.com> wrote in message
news:%23mCgVUpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi - when I insert a decimal amount into my SQL Server 2000 database, it
> rounds it up.
> My table has a column:
> customer_amount decimal 9
> ..and has Precision set at 18, and Scale set at 2.
> I am grabbing the decimal value from a textbox on my form, and passing
> it as a parameter in ASP.Net:
> cmd.Parameters.Add(New SqlParameter("@.customer_amount",
> CType(tbConfAmount.Text, Decimal)))
> My SP is:
> CREATE Procedure addCustAmount
> (
> @.customer_amount decimal
> )
> AS INSERT INTO tblCustomerAmount (customer_amount)
> VALUES
> (@.customer_amount)
> If my text box says: 23.79, the value that ends up in my database is 24.
> Can anyone please advise where I'm going wrong?
> Thanks, Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were managed
and a response could be expected in 48 hours.
"BrianInHouston" wrote:
> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoint
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though the
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/common/survey.aspx?scid=sw;en;1296&showpage=1&ws=msdn&sd=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL
>> Server
>> 2005. I setup a stored procedure to insert to the view, then (in Visual
>> Studio) put a stop in the stored procedure. This works fine, until I try
>> to
>> step into the trigger. Then it tells me that no code is available.
>> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it
>> won't
>> allow me, saying "A breakpoint could not be inserted at this location".
>> This only happens for any trigger on the view. If I try to set a
>> breakpoint
>> on a trigger on a table, it works fine.
>> Does anyone know a way around this? I really would like to step though
>> the
>> trigger on the view.

Debugging triggers on views in SQL Server 2005?

I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Server
2005. I setup a stored procedure to insert to the view, then (in Visual
Studio) put a stop in the stored procedure. This works fine, until I try to
step into the trigger. Then it tells me that no code is available.
If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won't
allow me, saying "A breakpoint could not be inserted at this location".
This only happens for any trigger on the view. If I try to set a breakpoint
on a trigger on a table, it works fine.
Does anyone know a way around this? I really would like to step though the
trigger on the view.Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>|||Is anyone going to respond to this? I thought these news groups were manage
d
and a response could be expected in 48 hours.
"BrianInHouston" wrote:

> I am trying to debug an "INSTEAD OF INSERT" trigger on a view in SQL Serve
r
> 2005. I setup a stored procedure to insert to the view, then (in Visual
> Studio) put a stop in the stored procedure. This works fine, until I try
to
> step into the trigger. Then it tells me that no code is available.
> If I try in Visual Studio 2005 to set a breakpoint on this trigger, it won
't
> allow me, saying "A breakpoint could not be inserted at this location".
> This only happens for any trigger on the view. If I try to set a breakpoi
nt
> on a trigger on a table, it works fine.
> Does anyone know a way around this? I really would like to step though th
e
> trigger on the view.|||I don't know the answer to the debugging question, but yes, if you're an
MSDN subscriber and posted this using the account you registered when you
signed up for managed newsgroups, you should get a response within 2
business days. If you don't, the FAQ page points you at
https://support.microsoft.com/commo...d=msdn&pa=msdnw
to contact the folks at MSDN about it.
The first thing to do, however, is to ensure that the alias that you're
posting with is registered properly. To do that, go to
http://msdn.microsoft.com/subscriptions/, sign in, and click the Managed
Newsgroups link.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"BrianInHouston" <BrianInHouston@.discussions.microsoft.com> wrote in message
news:66D6AC1C-BB18-4FC4-B30C-F94D33A441E2@.microsoft.com...[vbcol=seagreen]
> Is anyone going to respond to this? I thought these news groups were
> managed
> and a response could be expected in 48 hours.
> "BrianInHouston" wrote:
>

debugging the ole db destination?

I have an OLE DB destination which should insert data into a table named in an SSIS variable. When I run the package, I don't get any errors and I have a data viewer which shows that the data is reaching the OLE DB destination. However, the data isn't being inserted into the destination table.

Can someone suggest how I should go about debugging this?

Thanks in advance.

Hi Duane,

you might try SQL Server Profiler and monitor OLEDB and T-SQL events

--
SvenC

|||

SvenC wrote:

Hi Duane,

you might try SQL Server Profiler and monitor OLEDB and T-SQL events

--
SvenC

Thanks for your reply. I figured out what I was doing incorrectly. I was using the refresh function in SQL Server Management Studio. However, it wasn't working. I'm not sure if it's a bug or a problem with my installation.

Debugging stored procedures

I have complete error handling and printing the error after every insert or update statements or after calling another procedure.

But somehow when executing the proc it is not printing the error.

The query analyzer shows a general message 'Query batch completed with errors'

All the logic seems to be working properly, but this message is bothering me. Why is this message displayed if everything is run correctly [or] is something wrong ?

Example:

[code]

/*************************************************************
** Error Handling
**************************************************************/

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

[/code]

IF (@.@.error <> 0) --this should be @.@.error @.error from previous statement is unreliable
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

|||

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.@.error

if (@.error <> 0)

begin

end

|||

QUOTED:

Not quite getting it.

Does this mean @.@.error may not return anything. My understanding is it will be '0' if success and any other number if its an error

In other words, the following doesn't work ?

declare @.error int

select @.error = @.error + (other select clause ) --<-- what if the error lies in here

if (@.error <> 0)

begin

end

|||

thanks joeydj,

ok i see...

@.@.error is for select statements too ?

i can check the selects, but its a standard select as shown above and there seems to be no error there.

|||

thats a wild guess anyway.

|||

can you please check if this line is valid

,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)

|||

still not getting it try this. this one should do it.

hahaha

declare @.error int

select @.error=0

SELECT @.rowcount = @.@.rowcount
,@.error = @.@.error
,@.short_msg = 'Error Creating MCTM. - AG_SP_FAC_MCTN_INSERT'
,@.long_msg = 'Error in executing proc AG_SP_FAC_MCTN_INSERT'
,@.resolution_msg = 'Stored procedure error. Contact Technical Support for fix'
,@.log_cd = 'AG.CONV.ERR' + convert(char,@.exec_seq_no)
,@.log_level = 'O'
,@.log_severity = 3

IF (@.error <> 0)
BEGIN

EXEC amgrp_conv..AG_SP_LOG
@.LOG_CD = @.log_cd
,@.LOG_DTM = @.log_dtm
,@.LOG_LEVEL = @.log_level
,@.LOG_SEVERITY = @.log_severity
,@.APP_NAME = @.app_name
,@.SHORT_MSG = @.short_msg
,@.LONG_MSG = @.long_msg
,@.RESOLUTION_MSG = @.resolution_msg
,@.MAIN_STORED_PROC_NAME = @.main_stored_proc_name
,@.STEP_STORED_PROC_NAME = @.step_stored_proc_name
,@.SYBASE_CD = @.error

PRINT 'ERROR=' + convert(varchar(255),@.error)

ROLLBACK TRANSACTION TRAN_PRAC_PAR
CLOSE prac_par_cursor
DEALLOCATE prac_par_cursor
RETURN @.failure

END

Debugging SPs and triggers in VS2005

I have an AFTER INSERT trigger on table A than calls a stored procedure
which performs a DISABLE TRIGGER ALL on table A, does some actions then
finally does an ENABLE TRIGGER ALL.
This works fine normally except, when I'm stepping through the SP code, the
entire IDE hangs when executing the DISABLE TRIGGER. Presumably this is
because the debugging process is preventing the trigger from disabling
because it is still technically executing. I guess that is understandable!
Is this expected behaviour? Likely to ever change?
Thanks in advance.
regards,
Paul Ritchie.
New Zealand.Why would you need to disable a trigger during its execution? Do you also pu
t
the database in single-user mode at the same time?
ML|||So I can insert another record in the same table.
Thanks for the sarcasm - much appreciated. If you don't know the answer
then why bother?
Paul.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FD1BB005-A3DD-42F7-A42D-9435424D9DE3@.microsoft.com...
> Why would you need to disable a trigger during its execution? Do you also
> put
> the database in single-user mode at the same time?
>
> ML|||If recursion is the problem turn that off. I'm not being sarcastic. A
disabled trigger will allow other users to insert data into the table while
by-passing your procedure.
ML

Debugging Parameterized Queries

How would I debug such a query.

I have a sqlCommand to which I add several parameters for an insert statement.

if the statement fails, for some reason, I would like to copy the final sql with all values inserted as text and use this in e.g. TOAD to see where the error is coming from. Is this possible?

I have also been looking for this, but there does not seem to be a public property of the Command object that exposes this. I don't think it is actually stored in the object anywhere. I think it is created on the fly, when sent to the database.

What you can do however, if you don't have too many parameters, is to just copy/paste the SQL command into your TOAD. If you prefix your OracleParameters with : (instead of @.) then TOAD will ask you for the value of each parameter as you run your query.

Another option (although a bit cumbersome) is to write a function that actually parses the CommandText property and inserts the current values of the parameters, with respect to their datatype... But it would take some work to get it right ;-)

Debug stored procedure that uses comma delimited list to insert multiple records

I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:

exec proc_Add_Product_Files @.FileName = N'This is just a test.doc', @.FileDescription = N'test', @.FileSize = 24064, @.LanguageID = NULL, @.DocumentCategoryID = 1, @.ComplianceID = NULL, @.SubmittedBy = N'Kevin McPhail', @.SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @.ProductID = N'10,11,8'

Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)

CREATE PROCEDURE proc_Add_Product_Files_v2
/*
Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a row
into productfiles for each product that the file being uploaded pertains to.
*/
@.FileName varchar(150),
@.FileDescription varchar(150),
@.FileSize int,
@.LanguageID int,
@.DocumentCategoryID int,
@.ComplianceID int,
@.SubmittedBy varchar(50),
@.SubmittedDate datetime,
@.ProductID varchar(150)

AS
BEGIN

DECLARE @.FileID INT

SET NOCOUNT ON

/*
Insert into the files table and retrieve the primary key of the new record using @.@.identity
*/
INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate)
Values
(@.FileName, @.FileDescription, @.FileSize, @.LanguageID, @.DocumentCategoryID, @.ComplianceID, @.SubmittedBy, @.SubmittedDate)

Select @.FileID=@.@.Identity

/*
Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.
*/
DECLARE @.ProductFilesInsert varchar(2000)

SET @.ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@.FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @.ProductID + ')'

exec(@.ProductFilesInsert)

End
GO

I created your stored procedure locally, and did a PRINT of @.ProductFilesInsert and all looks good to me. Setting @.FileID = 0 instead of selecting its value to be @.@.Identity, this is what @.ProductFilesInsert contains, and that is syntactically correct:

INSERT INTO ProductFiles (FileID, ProductID) SELECT 0, Product1ID FROM Products WHERE Product1ID IN (10,11,8)

Your stored procedure is named proc_Add_Product_Files_v2, yet you are executing proc_Add_Product_Files. Is the problem simply that your are executing an old version of your stored procedure?|||

Terri:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

Thanks again,

Kevin

|||

Kevin.McPhail wrote:

Thanks! Sometimes it is so obvious. I am a little embarrassed that i did not catch that. :)

It wasn't obvious to me. The only reason I noticed was that exec proc_Add_Product_Files failed failed for me because I didn't have the original in place :-) I can't tell you how many times I've been burned by the very same thing.

For what it's worth, I am not a big fan of dynamic SQL, especially when an alternate methodology is possible. You could use this approach instead:

INSERT INTO
ProductFiles
(
FileID,
ProductID
)
SELECT
@.FileID,
Product1ID
FROM
Products
INNER JOIN
dbo.Split(@.ProductID,',') AS A ON Products.Product1ID = A.Element

There are many variations of a "split" function. Here's one that Dinakar provided in this thread:http://forums.asp.net/989365/ShowPost.aspx:

CREATE FUNCTION [dbo].[Split] ( @.vcDelimitedString nVarChar(4000),
@.vcDelimiter nVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@.vcDelimitedString - The string to be split
@.vcDelimiter - String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR: Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
-- ---- ----------------
***************************************************************************/
RETURNS @.tblArray TABLE
(
ElementID smallint IDENTITY(1,1) not null primary key, --Array index
Element nVarChar(1200) null --Array element contents
)
AS
BEGIN
DECLARE
@.siIndex smallint,
@.siStart smallint,
@.siDelSize smallint
SET @.siDelSize = LEN(@.vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@.vcDelimitedString) > 0
BEGIN
SET @.siIndex = CHARINDEX(@.vcDelimiter, @.vcDelimitedString)
IF @.siIndex = 0
BEGIN
INSERT INTO @.tblArray (Element) VALUES(@.vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @.tblArray (Element) VALUES(SUBSTRING(@.vcDelimitedString, 1,@.siIndex - 1))
SET @.siStart = @.siIndex + @.siDelSize
SET @.vcDelimitedString = SUBSTRING(@.vcDelimitedString, @.siStart , LEN(@.vcDelimitedString) - @.siStart + 1)
END
END

RETURN
END|||

Thanks again Terri! I had been looking for a good understandable (not a sql guru) way to pass a delimited string or array to sql for inserts. I read through a couple articles i found that left my head spinning and decided to go with the old dynamic sql method since i at least understood what it did. Your example(and Dinakar and Karen's ) is exactly what i had been looking for.

Thanks,

Kevin

Dear Insert in to database from textbox in asp.net

I have 5 textboxs and 1 botton in web form. I want to click botton and insert all data in textbox to database that using sql server 2005. Please help meYou can use SqlCommand with parameters, as shown in this documentation:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx