Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-17 : 13:49:26
|
| Paul writes "Hi SQLTeam,I'm having difficulty finding resource on a problem that is occuring in my DTS package, and am hoping you may be able to help me.I've used the Execute SQL Task to perform an INSERT and want to return the Identity :INSERT INTO Export_History(BusinessID, CustomerID, AccountNumber, Module, Document,UDFLabel, UDFValue)VALUES(?, ?, ?, ?, ?, ?, ?)SELECT @@IDENTITY As IDIn Output Parameters I've assigned ID to a global variable named @ID.The package uses this ID to Update Export_History later on.When it gets to an update it's failing with the following error 'Invalid character value for cast specification'.When I view the Global Variables in package properties the value of @ID is <not displayable> yet when I run the above query in Query Analyzer with the placeholders replaced with the same values that are being input, it returns the identity no problem.Is this an issue with assigning @@IDENTITY to a global variable or am I missing something here?Many Thanks in advance for your help,Paul." |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-06-17 : 18:30:04
|
| Use SET NOCOUNT ON in your task as the rows returned message can confuse DTS. What datatype is your global variable defined as ?I would be tempted to use a string and cast the @@IDENTITY (I would use @@SCOPE_IDENTITY instead) e.g. youe SQL TaskSET NOCOUNT ONINSERT INTO Export_History (BusinessID, CustomerID, AccountNumber, Module, Document, UDFLabel, UDFValue) VALUES (?, ?, ?, ?, ?, ?, ?) SELECT ID = CAST(SCOPE_IDENTITY() as varchar(10))HTHJasper Smith |
 |
|
|
|
|
|
|
|
Assigning @@IDENTITY to a global variable in DTS