Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing SQL Statement from flat file

I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.

I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.

I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.

Any help would be greatly appreicated. Consider me an SSIS novice.

Thanks

I'm not sure if this is a good idea, but how about this?

A script task which will read the entire contents of the file and assign it to an object variable.
This object variable should be of array type, if you can iterate through the array in the for loop to execute your sql statements from the array.

Thanks|||

Another thought: If the source for your keys is a database, you can use an Execute SQL Task to get a list of keys into a recordset, and the ForEach (set to ADO Recordset instead of directory) to iterate through it.

To get the data loaded, you should use a data flow task with an OLEDB Source pointed to Oracle, and an OLEDB Destination pointed to SQL Server. The source should be set to get it's SQL from a variable (which should be populated with your view select statement).

Here's a similar example (one of many, if you search around you'll find more): http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

|||

I was able to get the expected data loaded into SQL Server.

thanks for the help

Executing SP_SpaceUsed for tables in a other database with EXEC

Hi

I'm executing SP_SpaceUsed in a stored procedure like this :

Exec ('SP_SpaceUsed '+ @.table)

This works great but when i want to execute it for a table in a other
database i'm running in to troubles. Things i tried is this :

Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
syntax)

Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
(uncorrect syntax)

Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
syntax)

Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
(uncorrect syntax)

Could someone give me a clue

Thanx,

Hennie de Nooijerhdenooijer@.hotmail.com (Hennie de Nooijer) wrote in message news:<191115aa.0311040343.674519a6@.posting.google.com>...
> Hi
> I'm executing SP_SpaceUsed in a stored procedure like this :
> Exec ('SP_SpaceUsed '+ @.table)
> This works great but when i want to execute it for a table in a other
> database i'm running in to troubles. Things i tried is this :
> Exec ('USE <DB> ; SP_SpaceUsed '+ @.table) -->not working (uncorrect
> syntax)
> Exec ('USE <DB> ; Master.dbo.SP_SpaceUsed '+ @.table) -->not working
> (uncorrect syntax)
> Exec ('SP_SpaceUsed <DB>.dbo.'+ @.table) --> not working (uncorrect
> syntax)
> Exec ('Master.dbo.SP_SpaceUsed <DB>.dbo.'+ @.table) -->not working
> (uncorrect syntax)
> Could someone give me a clue
>
> Thanx,
> Hennie de Nooijer

exec('exec ' + @.database + '..sp_spaceused ' + @.table)

Simon

Executing Dynamic SQL with update

I have a temp table #Temp(id int, statement varchar, result int)
The statement column contains a prebuilt sql select statement e.g
id statement
Result
1 select count(*) from books where authorname like 'A%'.
2 select count(*) from books where authorname like 'J%'.
I want run an update statement on the table so that i can set the result
column to the result of the select statement in the statement column.
e.g. update #temp
set result = [statement result]
I want to aviod cursors. Can this be done?
Please help.This is in general, a poor approach. You can use certain undocumented
procedures ( in SQL 2000 ) to get this done, but it is complex, error prone
and rarely worth it.
Instead of having SQL statements as data values & updating #temp tables,
consider using a view. Alternatively, depending on tables & wild card
patterns involved, in some cases you might be able to resolve the problem
with a single query with CASE.
If you want a workable solution, pl. refer to www.aspfaq.com/5006 and post
the required information along with a brief explanation of your
requirements.
In case you are wondering how to get the scalar result of a SELECT statement
into a variable dynamically, refer to the procedure sp_ExecuteSQL in SQL
Server Books Online.
Anith

Executing an insert in a linked server.

Hi:
Im performing an insert in a table that has a trigger.
The propossal of this trigger is to keep update two
tables when an insert occurss in server A; it starts a
trigger to insert a row in one table in server B
The servers are linked and the user has permission to
perform insert in the remote server. Im getting the
following error:
Unable to start a nested transaction for OLE DB
provider 'SQLOLEDB'. A nested transaction was required
because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more
transactions on this session.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionLocal::StartTransaction returned 0x8004d013:
ISOLEVEL=4096].
Can anybody helpme'
Regards"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
> Hi:
> Im performing an insert in a table that has a trigger.
> The propossal of this trigger is to keep update two
> tables when an insert occurss in server A; it starts a
> trigger to insert a row in one table in server B
> The servers are linked and the user has permission to
> perform insert in the remote server. Im getting the
> following error:
> Unable to start a nested transaction for OLE DB
> provider 'SQLOLEDB'. A nested transaction was required
> because the XACT_ABORT option was set to OFF.
That is - set XACT_ABORT ON at begining of your transaction
> [OLE/DB provider returned message: Cannot start more
> transactions on this session.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionLocal::StartTransaction returned 0x8004d013:
> ISOLEVEL=4096].
>
> Can anybody helpme'
> Regards|||Regards, I appreciate your help
>--Original Message--
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:09fa01c38c59$79adb0f0$a101280a@.phx.gbl...
>> Hi:
>> Im performing an insert in a table that has a trigger.
>> The propossal of this trigger is to keep update two
>> tables when an insert occurss in server A; it starts a
>> trigger to insert a row in one table in server B
>> The servers are linked and the user has permission to
>> perform insert in the remote server. Im getting the
>> following error:
>> Unable to start a nested transaction for OLE DB
>> provider 'SQLOLEDB'. A nested transaction was required
>> because the XACT_ABORT option was set to OFF.
>That is - set XACT_ABORT ON at begining of your
transaction
>> [OLE/DB provider returned message: Cannot start more
>> transactions on this session.]
>> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> ITransactionLocal::StartTransaction returned
0x8004d013:
>> ISOLEVEL=4096].
>>
>> Can anybody helpme'
>> Regards
>
>.
>

Executing a queries contained in a column of a table IN SQL Server.

Hi,
I have a Table that contains SQL queries in one of its columns.I need to execute those query and finally want to retrive the result in another table i cannot use Cursors,Its working extremely slow, near about 1 min only for 2000 rows.Please tell me how can i minimize my time.Or any solution (without cursor) for such problem.Please help its very urgent.
e.g Say TableContainQuery(PKID,QueryField)
Now above table have 10000 of records,
I need to retrieve data from by executing queries contain in above table. I am using SQL Server.

Regards,
Dheeraj

Using SQL Server, queries that are slow are generally slow because there are not proper indexes on the tables involved.
Have you ensured that there are useful indexes on the tables involved?|||Yes,
There is a primary key in the table I suppose indexing will automatically done.
Regards,
Dheeraj Verma|||No, indexing, other than indexes you created, willnot automatically be done. If all your queries use only columns involved in the primary keys, then you are fine. However, if your query references other columns in joins or where clauses, you must add indexes yourself.

executescaler

so im trying to use this to check and see if there is an entry in the table, and if so get the ID... so i was going to do Int32 myint = (Int32) cmd.ExecuteScaler() but when it returns null i have a bit of a problem.. so i did some digging on the net and found that people just generally make the call twice.. once to see if its null and if its not call it again to get the value...

personally i dont like hitting the DB twice for the same thing.. i guess its probably cached at the point in which its called again, but it just seems like a waste.. is there some way to do this and only call it once? or is it not worth it?

Thanks

Justin

You don't need to call the db twice.

Just use

Object myobj=cmd.ExecuteScalar();
if(myobj != null) {
Int32 myint=(Int32)myobj;
// rest of your code
}

Jos

|||

Hi,

Nullable types come into play in such cases. You could use

//a is an int variable or nullint? a = command.ExecuteScalar();

I hope this helps.

|||

ohh hey... learn something new every day!

nullable types

http://msdn2.microsoft.com/en-us/library/1t3y8s4s(VS.80).aspx

had to modify what you did a little... cant convert an object to an int... so... int? a = (int?)cmd.ExcuteScalar(); worked

Thanks!

ExecuteScalar --> How To Get the OrderID(Identity) from a table to another Table ?

I am new to asp.net and studying on book.. currently i am stuck with a problem which not understand what is it !! Can anyone help me ?? I trying a shopping cart "Check Out" method, and when i am done the process.. My order_lines Table can update the OrderID which just generated !! What wrong with the statement ??

Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick
' Insert the order and order lines into the database
Dim conn As SqlConnection = Nothing
Dim trans As SqlTransaction = Nothing
Dim cmd As SqlCommand

Try
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
conn.Open()
trans = conn.BeginTransaction
cmd = New SqlCommand()
cmd.Connection = conn
cmd.Transaction = trans

' set the order details
cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total)"
cmd.Parameters.Add("@.MemberName", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.OrderDate", Data.SqlDbType.DateTime)
cmd.Parameters.Add("@.Name", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Address", Data.SqlDbType.VarChar, 255)
cmd.Parameters.Add("@.City", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.State", SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.PostCode", Data.SqlDbType.VarChar, 15)
cmd.Parameters.Add("@.Country", Data.SqlDbType.VarChar, 50)
cmd.Parameters.Add("@.Total", Data.SqlDbType.Money)

cmd.Parameters("@.MemberName").Value = User.Identity.Name
cmd.Parameters("@.OrderDate").Value = DateTime.Now()
cmd.Parameters("@.Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text
cmd.Parameters("@.Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text
cmd.Parameters("@.City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text
cmd.Parameters("@.State").Value = CType(Wizard1.FindControl("txtState"), TextBox).Text
cmd.Parameters("@.PostCode").Value = CType(Wizard1.FindControl("txtPostCode"), TextBox).Text
cmd.Parameters("@.Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text
cmd.Parameters("@.Total").Value = Profile.Basket.Total

Dim OrderID As Integer
OrderID = Convert.ToInt32(cmd.ExecuteScalar()) <-- Is it wrong or need to add wat ?
' change the query and parameters for the order lines
cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID,Quantity, Price) VALUES (@.OrderID, @.ProductID, @.Quantity, @.Price)"
cmd.Parameters.Clear()
cmd.Parameters.Add("@.OrderID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.ProductID", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Quantity", Data.SqlDbType.Int)
cmd.Parameters.Add("@.Price", Data.SqlDbType.Money)
cmd.Parameters("@.OrderID").Value = OrderID

For Each item As CartItem In Profile.Basket.Items
cmd.Parameters("@.ProductID").Value = item.ProductID
cmd.Parameters("@.Quantity").Value = item.Quantity
cmd.Parameters("@.Price").Value = item.UnitPrice
cmd.ExecuteNonQuery()
Next
' commit the transaction
trans.Commit()
Catch SqlEx As SqlException
' some form of error - rollback the transaction
' and rethrow the exception
If trans IsNot Nothing Then
trans.Rollback()
End If
' Log the exception
Throw

Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
' we will only reach here if the order has been created successfully
' so clear the cart
Profile.Basket.Items.Clear()
End Sub

Hi, there is no SELECT query in your SQL statement, so ExecuteScalar has nothing to return. Try this instead:

' set the order details
cmd.CommandText = "INSERT INTOOrders(MemberName, OrderDate, Name, Address, City, State, PostCode,Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address,@.City,@.State, @.PostCode, @.Country, @.Total; SELECT SCOPE_IDENTITY())"

|||

Thanks tmorton for your reply.. i try with ur suggested code can return an error

Incorrect syntax near ';'.
Incorrect syntax near ')'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near ';'.
Incorrect syntax near ')'.

Line 84: End If
Line 85: ' Log the exception
Line 86: Throw
Line 87:
Line 88: Finally

I am noob for this case, is it other way to retrieve the new data identity ?

|||cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"|||

Motley wrote:

cmd.CommandText = "INSERT INTO Orders(MemberName, OrderDate, Name, Address, City, State, PostCode, Country, Total) VALUES (@.MemberName, @.OrderDate, @.Name, @.Address, @.City,@.State, @.PostCode, @.Country, @.Total); SELECT SCOPE_IDENTITY()"

Thanks for fixing my keying error, Motley :-)

executeScalar - count(*)

i have Cust table with 5 columns (Name, Add, Contact, UserID, Passwd)

my sql statement is not working correctly..
"SELECT COUNT(*) FROM Cust WHERE UserID='" + textBoxEmail + "'AND Passwd='" + textBoxPW + "'"

what maybe the problem? i have 1 record and when im running it, whether the input is right or wrong, the count is always zero(0). i think the problem is in my sql statement(maybe in the where clause) because i tried counting the records by "select count(*) from cust" and it correctly says 1 record. pls help!ow... i forgot the .Text of the textboxes...
it's now solved!|||please don't write code like this unless you want criminals to steal your data.

see http://www.dbforums.com/showpost.php?p=6263508&postcount=7|||ow... tnx 4 ur concern! actually i don't know securities like that because im just doing a project in my subject and don't care about those things. but i really appreciate ur response and i will study those links. tnx again!

ExecuteNonQuery while dataReader still open

Hi all!

I basically need to get some records from a table and while looping through them i need to insert some records on other table.

I keep getting this error:

There is already an open DataReader associated with this connection which must be closed first.

The piece of code that I have is like this:

 ...
SqlCommand sqlCmd2 = new SqlCommand(sqlString2, dbConn);
sqlCmd2.Transaction = trans;
SqlDataReader dr = sqlCmd2.ExecuteReader(CommandBehavior.CloseConnection);
//loop through dr
while (dr.Read())
{
string sqlStr = "insert into prodQtyPrice (typeQtyId, prodId, typeId) values(28," + dr["prodId"] + "," + dr["typeId"] +")";
SqlCommand sqlCmd3 = new SqlCommand(sqlStr, dbConn);
//sqlCmd3.Transaction = trans;
sqlCmd3.ExecuteNonQuery();
}
 ...

Also I would like to have the insertions in the same transaction as the previous sql commands.

Thanks a million!

LAMwhy don't you just do that in a procedure? It looks like it's a simple select then insert right?

Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId )
VALUES
SELECT 28, prodID, TypeID From WhereverYouGetThisFrom

GO|||First of all, thanks for answering so fast!

The main reason that is not inside a storedProcedure is requirements. The entire app is not using SP and this has to be done the same way.

LAM|||>>The entire app is not using SP

looks like you have bigger prbs to worry about.
anyway, to answer your q, since the connection is already open with the datareader, you need to open another connection to do the insert.

hth|||you all are making me cross.

command.CommandText = "Insert Into ProdQtyPrice ( typeQtyId, prodId, typeId ) VALUES SELECT 28, prodID, TypeID From WhereverYouGetThisFrom";

ExecuteNonQuery requires an open and available Connection

Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
message when trying to UPDATE a very large table:
"ExecuteNonQuery requires an open and available Connection. The connection's
current state is closed."
The UPDATE command affects all the rows in the column. The confusing part
is - it only occurs on a very large table (over 1M rows). It does not orrur
on smaller tables. So I don't think it actually has anything to do with the
connection.
Any ideas would be greatly appreciated.Just a guess, the connection might have timed out or broken. Try increasing
the timeout
"Neil W." wrote:

> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The connection
's
> current state is closed."
> The UPDATE command affects all the rows in the column. The confusing part
> is - it only occurs on a very large table (over 1M rows). It does not orr
ur
> on smaller tables. So I don't think it actually has anything to do with t
he
> connection.
> Any ideas would be greatly appreciated.
>
>
>|||Neil W. (neilw@.NOSPAMTHANKYOUnetlib.com) writes:
> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The
> connection's current state is closed."
> The UPDATE command affects all the rows in the column. The confusing
> part is - it only occurs on a very large table (over 1M rows). It does
> not orrur on smaller tables. So I don't think it actually has anything
> to do with the connection.
Might be so, but I would put my bets on the ADO .Net code. How does
the relevant bits look like.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ExecuteNonQuery Fails catastrophically

I was trying an RDA Demo project. However, everytime I pull the data from Sql Server 2005 and then move on to add a row to the table using SqlCeCommand.ExecuteNonQuery(), the application fails catastrophically without giving any exception and simply exits!

When I have pulled the data once and this application has shut down (exited) I can open it again and then continue normally but I can't expect the application to PULL the data and then even add or update a row to that table. I can do select queries fine. Why would this happen?

I am disposing all the objects that I use in both the scenarios, or atleast I believe so.
Does any one have any idea?For anyone else having the same problem -
The solution is to refrain from deleting the database manually through code and also to refrain from creating the database through code. Somehow it interferes with the ExecuteNonQuery later on. From what I have made out from my research it happens mostly with Emulators. Shouldn't be an issue with real device.

Execute Stored Procedure into a temporary table problem

Hello,
I was wondering if anyone can help me with this little problem using
INSERT and EXECUTE within a stored procedure. I would be very grateful
if anyone can solve the problem.
I have a simple table (tblEmployees) to hold all the employees details
empID int -- contains the employee id
firstname varchar -- contains first name
surname varchar -- contains surname
unitID int -- contains unit id
I also have a simple stored procedure (sp_get_employeeByUnitID) that is
designed to get all the employees at a certain unit. This procedure
returns data with only the empID column.
CREATE PROCEDURE [sp_get_employeeByUnitID]
@.unitID int
AS
SELECT empID FROM tblEmployees
WHERE unitID=@.unitID
GO
In another stored procedure, what I am doing (or at least I am trying
to!) is to execute a stored procedure (sp_getEmployees) into a temporary
table so that I may perform other calculations.
CREATE PROCEDURE [sp_get_Employees]
@.unitID as int
AS
create TABLE #MATCHEDEMPLOYEES_TABLE (empID int)
insert into #MATCHEDEMPLOYEES_TABLE (empID)
EXECUTE sp_get_employeeByUnitID @.unitID
-- other calculations goes here - remove to keep it simple
Select * FROM #MATCHEDEMPLOYEES_TABLE
GO
When I execute the stored procedure (sp_get_Employees) via the "Query
Analyzer" on MS SQL server 2k, it displays the correct results.
However, if I run it via an asp file it comes up with a error:-
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name
or number of supplied values does not match table definition."
--
so, the error is on the insert command within sp_get_Employees, and I
think the error is trying to say that the result from
sp_get_employeeByUnitID contains extra columns that cannot be insert
into the #MATCHEDEMPLOYEES_TABLE. But it is - or so I though! i have
tested out sp_get_employeeByUnitID and it definitely returns a single
empID column.
The asp code is as follows:-
Function getAllEmployees(unitID)
Set cmdSP = CreateSPObject( cnDB, "sp_get_Employees" )
cmdSP.Parameters.Append cmdSP.CreateParameter ( "unitID",
adInteger, adParamInput, 8, unitID )
Set empRs = cmdSP.Execute
Set cmdSP.ActiveConnection = Nothing
Set cmdSP = Nothing
End Function
-- I just can't understand why it works okay in the query Query Analyzer
but not within an ASP page (the asp is definitely pass the correct unit
id number to the stored procedure)
Another question, it is possible that in the future, the stored
procedure (sp_get_employeeByUnitID) could return more then one column
(ie the empID and lastname). Is there any way, that I can do an insert
into #MATCHEDEMPLOYEES_TABLE from EXECUTING another stored procedure
without having to define any additional columns in the
#MATCHEDEMPLOYEES_TABLE? Ie, something like
INSERT sp_get_employeeByUnitID.empID
INTO #MATCHEDEMPLOYEES_TABLE (empID)
EXECUTE sp_get_employeeByUnitID @.unitID
Any help or suggestions would be appreciated.
Thanks
Darren Tweedale
darrentweedale@.@.hotmail.comUse SET NOCOUNT ON in every sp, to avoid this.
Example:
CREATE PROCEDURE [sp_get_employeeByUnitID]
@.unitID int
AS
set nocount on
SELECT empID FROM tblEmployees
WHERE unitID=@.unitID
GO
AMB
"Darren Tweedale" wrote:

> Hello,
> I was wondering if anyone can help me with this little problem using
> INSERT and EXECUTE within a stored procedure. I would be very grateful
> if anyone can solve the problem.
> I have a simple table (tblEmployees) to hold all the employees details
> empID int -- contains the employee id
> firstname varchar -- contains first name
> surname varchar -- contains surname
> unitID int -- contains unit id
> I also have a simple stored procedure (sp_get_employeeByUnitID) that is
> designed to get all the employees at a certain unit. This procedure
> returns data with only the empID column.
> CREATE PROCEDURE [sp_get_employeeByUnitID]
> @.unitID int
> AS
> SELECT empID FROM tblEmployees
> WHERE unitID=@.unitID
> GO
> In another stored procedure, what I am doing (or at least I am trying
> to!) is to execute a stored procedure (sp_getEmployees) into a temporary
> table so that I may perform other calculations.
> CREATE PROCEDURE [sp_get_Employees]
> @.unitID as int
> AS
> create TABLE #MATCHEDEMPLOYEES_TABLE (empID int)
> insert into #MATCHEDEMPLOYEES_TABLE (empID)
> EXECUTE sp_get_employeeByUnitID @.unitID
> -- other calculations goes here - remove to keep it simple
> Select * FROM #MATCHEDEMPLOYEES_TABLE
> GO
> When I execute the stored procedure (sp_get_Employees) via the "Query
> Analyzer" on MS SQL server 2k, it displays the correct results.
> However, if I run it via an asp file it comes up with a error:-
> --
> "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name
> or number of supplied values does not match table definition."
> --
> so, the error is on the insert command within sp_get_Employees, and I
> think the error is trying to say that the result from
> sp_get_employeeByUnitID contains extra columns that cannot be insert
> into the #MATCHEDEMPLOYEES_TABLE. But it is - or so I though! i have
> tested out sp_get_employeeByUnitID and it definitely returns a single
> empID column.
> The asp code is as follows:-
> Function getAllEmployees(unitID)
> Set cmdSP = CreateSPObject( cnDB, "sp_get_Employees" )
> cmdSP.Parameters.Append cmdSP.CreateParameter ( "unitID",
> adInteger, adParamInput, 8, unitID )
> Set empRs = cmdSP.Execute
> Set cmdSP.ActiveConnection = Nothing
> Set cmdSP = Nothing
> End Function
> -- I just can't understand why it works okay in the query Query Analyzer
> but not within an ASP page (the asp is definitely pass the correct unit
> id number to the stored procedure)
> Another question, it is possible that in the future, the stored
> procedure (sp_get_employeeByUnitID) could return more then one column
> (ie the empID and lastname). Is there any way, that I can do an insert
> into #MATCHEDEMPLOYEES_TABLE from EXECUTING another stored procedure
> without having to define any additional columns in the
> #MATCHEDEMPLOYEES_TABLE? Ie, something like
> INSERT sp_get_employeeByUnitID.empID
> INTO #MATCHEDEMPLOYEES_TABLE (empID)
> EXECUTE sp_get_employeeByUnitID @.unitID
>
> Any help or suggestions would be appreciated.
> Thanks
> Darren Tweedale
> darrentweedale@.@.hotmail.com
>|||Alejandro Mesa wrote:
> Use SET NOCOUNT ON in every sp, to avoid this.
> Example:
> CREATE PROCEDURE [sp_get_employeeByUnitID]
> @.unitID int
> AS
> set nocount on
> SELECT empID FROM tblEmployees
> WHERE unitID=@.unitID
> GO
>
> AMB
> "Darren Tweedale" wrote:
>
Thanks Alejandro Mesa for replying, that was the correct answer!
I can't believe I missed that!
regards
darren tweedale

Execute stored procedure from stored procedure

Hello,

i need to execute a stored procedure from another stored procedure.

For example:

StoredProcedure1: Returns a table with columns "year" and "value".

StoredProcedure2: Also returns a table with columns "year" and "value".

In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them.

Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?

Kind regards and thanks,

carsaw

hi,

first off all let me clarify here - you cannot return table from a stored procedure you can use output variables but not the table.

what i would suggest you from your qeustion is - sp3 is main sp for you which needs data from sp1 & sp2 right

in that case let sp3 contain a temporary table #temptable with required columns

--parent proc

alterproc p1

as

Createtable #tempu

(

pidintidentity(1,1),

namesvarchar(50)

)

begin

insert #tempu(names)

exec tempinserter

select*from #tempu

end

--2nd proc

alterproc tempinserter

as

Createtable #temp2

(

pidintidentity(1,1),

namesvarchar(50)

)

insert #temp2

values('satish')

select namesfrom #temp2

return

--similarly create 3rd one then

exec p1 --the parent proc

hope it helps

also keep in mind to drop #temp tables at end of execution

regards,

satish.

|||

like i've called tempinserter in parent proc you can call other procs in the same manner.

regards,

satish

execute stored procedure durning merge replication

Hello
I have merge replication with dynamic filters and I have to create
dynamically table just before merge replication starts. Is there any
possibility of exec stored procedure durnig merge replication or just
before it?
Thanks in advance,
marcin
Hi
My problem is more complicated.
I create dynamicly subscription from Pocket PC where I set Host_Name for
dynamic filters.
How can I add merge agent job from source code ?
Any ideas ?
Thanks,
Marcin
Paul Ibison wrote:

> Sure - just add another step to the merge agent's job and
> change the workflow accordingly. I have had issues with
> using sp_start_job in job steps - there is no guarantee
> that a step will complete fully before the next one
> starts, but in the case of a script call this should be
> ok.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Execute stored proc dynamically (stored a variable)

Hi,
Can someone please show me how to execute a stored proc which has been
assigned to a variable. I have a table of stored procedures and I want to
pick out a certain sp, based on a set of criteria, and execute it
dynamically. I'm thinking of setting up a CURSOR to loop through the selecte
d
sp, assign each one to a variable and then execute it but I don't know how
yet.
I would also like to know how to pass a variable of type TABLE to the above
stored proc. This table type variable contains a list of parameters in a for
m
of "key-value" pairs.
Example
DECLARE @.myTable TABLE(
paramName Varchar(100),
paramValue Varchar(100))
INSERT INTO @.myTable(paramName, paramValue)
VALUES ('param1', '123')
DECLARE @.myStoredProc Varchar(100)
DECLARE myCursor CURSOR FOR
SELECT StoredProcName
FROM tableOfStoredProcs
WHERE something = somethingelse
OPEN myCursor
FETCH NEXT FROM myCursor
INTO @.myStoredProc
...
EXEC @.myStoredProc(@.myTable) -- this is what i want to do but syntactically
incorrect.
I'm using SQL Server 2000.
Any suggestion is greatly appreciated.
CalvinCalvin
CREATE PROC myProc
@.parameter1 VARCHAR(...),
@.parameter2 INT
AS
CREATE TABLE #t
(
spnames SYSNAME PRIMARY KEY
)
INSERT INTO #t VALUES ('sp1')
INSERT INTO #t VALUES ('sp2')
INSERT INTO #t VALUES ('sp3')
SELECT 'EXEC '+spnames+' '''+@.parameter1 +''''+','+cast(@.parameter2 as
varchar(10)) FROM #t
"Calvin KD" <CalvinKD@.discussions.microsoft.com> wrote in message
news:93EFAF4B-C947-4A0D-A230-2C8CDE5CD418@.microsoft.com...
> Hi,
> Can someone please show me how to execute a stored proc which has been
> assigned to a variable. I have a table of stored procedures and I want to
> pick out a certain sp, based on a set of criteria, and execute it
> dynamically. I'm thinking of setting up a CURSOR to loop through the
> selected
> sp, assign each one to a variable and then execute it but I don't know how
> yet.
> I would also like to know how to pass a variable of type TABLE to the
> above
> stored proc. This table type variable contains a list of parameters in a
> form
> of "key-value" pairs.
> Example
> DECLARE @.myTable TABLE(
> paramName Varchar(100),
> paramValue Varchar(100))
> INSERT INTO @.myTable(paramName, paramValue)
> VALUES ('param1', '123')
> DECLARE @.myStoredProc Varchar(100)
> DECLARE myCursor CURSOR FOR
> SELECT StoredProcName
> FROM tableOfStoredProcs
> WHERE something = somethingelse
> OPEN myCursor
> FETCH NEXT FROM myCursor
> INTO @.myStoredProc
> ...
> EXEC @.myStoredProc(@.myTable) -- this is what i want to do but
> syntactically
> incorrect.
> I'm using SQL Server 2000.
> Any suggestion is greatly appreciated.
> Calvin|||Thanks so much for your quick response. I also like to pass the parameters t
o
the stored proc in a form of TABLE type variable, as I demo earlier. This is
because it's a lot more flexible this way. Do you know of a way to do this?
Thanks again.
Calvin
"Uri Dimant" wrote:

> Calvin
>
> --
> CREATE PROC myProc
> @.parameter1 VARCHAR(...),
> @.parameter2 INT
> AS
> CREATE TABLE #t
> (
> spnames SYSNAME PRIMARY KEY
> )
> INSERT INTO #t VALUES ('sp1')
> INSERT INTO #t VALUES ('sp2')
> INSERT INTO #t VALUES ('sp3')
> SELECT 'EXEC '+spnames+' '''+@.parameter1 +''''+','+cast(@.parameter2 as
> varchar(10)) FROM #t
>
>
>
> "Calvin KD" <CalvinKD@.discussions.microsoft.com> wrote in message
> news:93EFAF4B-C947-4A0D-A230-2C8CDE5CD418@.microsoft.com...
>
>|||Hi, Calvin
You cannot pass a table variable as a parameter. You should use a
temporary table or a permanent (normal) table instead. If you expect
that this procedure may be called simultaneously by more users, you can
use @.@.SPID to separate parameters of different processes.
For example:
CREATE TABLE Parameters (
SPID smallint,
ParamName varchar(100),
ParamValue sql_variant,
PRIMARY KEY (SPID,ParamName)
)
GO
CREATE PROCEDURE sp1 AS
SELECT ParamValue FROM Parameters
WHERE SPID=@.@.SPID AND ParamName='param1'
GO
CREATE TABLE tableOfStoredProcs (
StoredProcName sysname PRIMARY KEY
)
INSERT INTO tableOfStoredProcs VALUES ('sp1')
GO
INSERT INTO Parameters (SPID, ParamName, ParamValue)
VALUES (@.@.SPID, 'param1', 123)
DECLARE @.myStoredProc Varchar(100)
DECLARE myCursor CURSOR LOCAL READ_ONLY FOR
SELECT StoredProcName FROM tableOfStoredProcs
--WHERE ...
OPEN myCursor
WHILE 1=1 BEGIN
FETCH NEXT FROM myCursor INTO @.myStoredProc
IF @.@.FETCH_STATUS<>0 BREAK
EXEC @.myStoredProc
END
CLOSE myCursor
DEALLOCATE myCursor
DELETE Parameters WHERE SPID=@.@.SPID
GO
This usage of EXEC, without parentheses (i.e. "EXEC @.ProcedureName"),
is less vulnerable to SQL Injection attacks than using "EXEC
(@.SQLString)".
Razvan|||Calvin
Read those articles
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
"Calvin KD" <CalvinKD@.discussions.microsoft.com> wrote in message
news:F367EEC4-4B38-4F21-836D-2ADBF2910C9B@.microsoft.com...
> Thanks so much for your quick response. I also like to pass the parameters
> to
> the stored proc in a form of TABLE type variable, as I demo earlier. This
> is
> because it's a lot more flexible this way. Do you know of a way to do
> this?
> Thanks again.
> Calvin
> "Uri Dimant" wrote:
>|||Thank you Razvan for your quick response. That's pretty much what i was
after. My original idea was to pass in a "data structure" as a parameter to
the stored procs and then each stored proc picks out what it needs for
processing.
Anyway, it's a good start.
Thanks.
Calvin
"Razvan Socol" wrote:

> Hi, Calvin
> You cannot pass a table variable as a parameter. You should use a
> temporary table or a permanent (normal) table instead. If you expect
> that this procedure may be called simultaneously by more users, you can
> use @.@.SPID to separate parameters of different processes.
> For example:
> CREATE TABLE Parameters (
> SPID smallint,
> ParamName varchar(100),
> ParamValue sql_variant,
> PRIMARY KEY (SPID,ParamName)
> )
> GO
> CREATE PROCEDURE sp1 AS
> SELECT ParamValue FROM Parameters
> WHERE SPID=@.@.SPID AND ParamName='param1'
> GO
> CREATE TABLE tableOfStoredProcs (
> StoredProcName sysname PRIMARY KEY
> )
> INSERT INTO tableOfStoredProcs VALUES ('sp1')
> GO
> INSERT INTO Parameters (SPID, ParamName, ParamValue)
> VALUES (@.@.SPID, 'param1', 123)
> DECLARE @.myStoredProc Varchar(100)
> DECLARE myCursor CURSOR LOCAL READ_ONLY FOR
> SELECT StoredProcName FROM tableOfStoredProcs
> --WHERE ...
> OPEN myCursor
> WHILE 1=1 BEGIN
> FETCH NEXT FROM myCursor INTO @.myStoredProc
> IF @.@.FETCH_STATUS<>0 BREAK
> EXEC @.myStoredProc
> END
> CLOSE myCursor
> DEALLOCATE myCursor
> DELETE Parameters WHERE SPID=@.@.SPID
> GO
> This usage of EXEC, without parentheses (i.e. "EXEC @.ProcedureName"),
> is less vulnerable to SQL Injection attacks than using "EXEC
> (@.SQLString)".
> Razvan
>|||Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> Thanks so much for your quick response. I also like to pass the
> parameters to the stored proc in a form of TABLE type variable, as I
> demo earlier. This is because it's a lot more flexible this way. Do you
> know of a way to do this?
Uri's suggestion is far too complex. Just say:
EXEC @.spname @.par1, @.par2, @.par3
Uri suggested some aritcles on my web site, but not the one which appears
to be the most pertinent to your problem,
http://www.sommarskog.se/share_data.html. This article discusses techniques
to share data between stored procedures. Unforunately, table variables
cannot do that task.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your responses. I have certainly learnt a few new things.
As for my quest, I think I'll just have to pass the same number of parameter
s
to each stored proc (even though some aren't used) unless we can come up wit
h
a better solution.
The problem we're facing is that because we have a long list of stored procs
(which will expand over the years) that we want to exec., and exactly which
stored proc will be exec. depends on certain criteria for each year. Some
will be "On" in one year and may be "Off" the next.
Example:
tblStoredProcs
=========
StoredProcID
StoredProcName
Year
Active
Anyway, if anyone has an idea, please let me know. All suggestions are
greatly welcomed.
Cheers,
Calvin
"Erland Sommarskog" wrote:

> Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> Uri's suggestion is far too complex. Just say:
> EXEC @.spname @.par1, @.par2, @.par3
> Uri suggested some aritcles on my web site, but not the one which appears
> to be the most pertinent to your problem,
> http://www.sommarskog.se/share_data.html. This article discusses technique
s
> to share data between stored procedures. Unforunately, table variables
> cannot do that task.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Calvin KD (CalvinKD@.discussions.microsoft.com) writes:
> Thank you all for your responses. I have certainly learnt a few new
> things. As for my quest, I think I'll just have to pass the same number
> of parameters to each stored proc (even though some aren't used) unless
> we can come up with a better solution.
It sounds like an excellent solution to me! After all, that is as
close to the implemention of a the O-O concept of a virtual class you
can come in T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Execute SQL Task: Error

I am running a Execute SQL Task which runs a script on a table. It gives me following error:

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

Thanks,

Your query in the execute sql task is returning more than one row. Pretty much just what the error states.

If you don't need to return the results of the query to a variable, select "none" instead of "single row" in the ResultSet box. If you do in fact need to return more than one row into a variable, you'll need to select "Full Result Set." The variable you populate with single row needs to match (or be able to cast) the data type of the returned value. If you are using "Full Result Set" you'll need to use a variable of "object" data type and then enumerate through that variable using a foreach loop.|||http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Execute SQL Task with no rows affected

Hi,

I used with Execute SQL Task for update a table in Oracle DB.

I saw that when the command has no rows for updeting, the task fails.

Here is my command:

update tableName set fieldA=sysdate where fieldB is Null

and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.

I tried to play with the ResultSet with no success.

Please your advice.

Thank you in advance

Noam

What error or errors are returned when the task fails? Can you please copy and paste the error output from BIDS or from a log file into a reply?

Are you attempting to store the resultset from the query in a variable?

Can you run the query successfully (where zero rows are affected) without error using SQL*Plus or another query tool?

This information will likely be invaluable in helping track down the source of the error.

|||

Thank you for your reply.

This is the error:

"Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "update TableName set FieldA=SYSDATE where FieldB is Null" failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task."

No, I didn't attempt to store the resultset, I just executed the query.

and yes, I ran the query successfully using SQL*Plus with the result : "0 rows updated."

Please your help.

Thanks

Noam

|||

I found it.

My connection was odbc.

I changed the connection to Ado.Net provider for odbc and it works without fail.

Why?

It's another question.

Thank you anyway.

Noam

EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL