executing Insert SQLdatasource

Hey forum,

the .NET form controls make it extremely easy to manage database data, control + SQLdatasource et voilà, its there.

i have a web form, and a SQLdatasource with an insert query with parameters (works perfectly, only the Date picker Lite textbox ID as parameter wont register, but i have a thread runnin on their forums for that). when i open the Query builder and execute the query it works! woot! but now, how can i make it execute on a button press? i tried with several commands, like SQLdatasource1.execute, and several other commands i searched (can post later, not behind my test environment with my tools/webpage at the moment). so what is the command to let the SQLdatasource execute it's query? it's all i need to know to make several forms, please ghelp me! :)

gr. michael

bump|||

Hi,

According to your question, are you in trouble with executing insert operation by a button? If so, you may create the Button_Click event and useSqlDataSource.InsertCommand andSqlDataSource.Insert()to achieve your purpose.

For more details about the usage of SqlDataSource, pls refer the following link:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.aspx

Thanks.

sql

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
>
>.
>

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

ExecuteScalar returns 0 (null) but INSERT is successful.

I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:

RecordID = cmd.ExecuteScalar

I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.

No exception is thrown of any type or anything. Does anybody know what may be happening?

Can you give us a peek at the procedure?

|||

Need to check the SP, you should have the Select statement which returns the newly inserted value..

|||Yes, thanks for the quick response. I must of had a brain fart or something because I did not have the

SELECT @.@.IDENTITY

at the end of the SQL. I am not sure how it was working before, but I know I needed to add this at the end of the INSERT statement.

The only thing I can figure is that I copy and paste so much code to use as a template, that when I wrote this SQL from scratch I forgot to add it, and never really paid attention when I was copying the INSERT statements before.

Thank you!

|||

You may have a lingering problem; you probably should be using SCOPE_IDENTITY() function instead of @.@.IDENTITY.

You might want to give a look at a couple of previous posts related to SCOPE_IDENTITY() versus @.@.IDENTITY here and here.

|||

Its better to use the SCOPE_IDENTITY function..

Select Scope_Identity()

|||

Sounds good to me; I actually saw that as well in the MSDN example.

Could you tell me why it is better (performance, etc.)?

Thanks,

|||

@.@.IDENTITY hold the global value (across the scope)

Scope_Identity hold the current Scope value.

When there is a concurrency user try to insert the value on your table, the @.@.identity has the very latest data, which may not be inserted by your current scope, but the scope_identity always have the value whichever your current scope inserted.

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 error

When I try to insert a record with the ExecuteNonQuery command, I get the following error information. Any clues why? Thanks.

SSqlException was unhandled by user code
...
Message="Incorrect syntax near [output of one of my field names]."
...
[Item detail:] In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.

My code:

Private objCmdAs SqlCommand
Private strConnAsNew SqlConnection(ConfigurationManager.AppSettings("conn"))
...
objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _
"VALUES('" & strUser &"','" & strFName.Text &"','" & strLName.Text &"', '" & strLang.Text &"', '" & strCtry.Text &"', '" & strPhone.Text &"'" _
, strConn)
strConn.Open()
objCmd.ExecuteNonQuery()

hi muybn,

there's not closing bracket for values() i mean

objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _
"VALUES('" & strUser &"','" & strFName.Text &"','" & strLName.Text &"', '" & strLang.Text &"', '" & strCtry.Text &"', '" & strPhone.Text &"')" _ 'can u see please i added a bracket )
, strConn)

regards,

satish.

|||Don't concatenate UI-supplied data to SQL statements that will be executed. This is an insecure practice as it opens up your server to SQL injection attacks. Use parameters instead.|||

Thanks, but doesn't the closing parenthesis bracket go after the reference to the connection string, in this case on the last line, strConn)?

|||

Thanks, TMorton. Is this merely a security precaution or would it cause the error I'm experiencing?

I plan to incorporate parameters into my project before I take it live. Can you point me to a definitive tutorial source for forming parameters, or better yet, mock up some of the variables that I've supplied above into parameters? To be honest, I've looked at quite a few sites and they've all confused me with how to define the parameters after the SQL statement, where you set the parameters equal to the variables.

|||

What Terri is recommending is

1) considered a best practice
2) offers protection from sql injection
3) avoids issues with getting your quotes correct when concatenating the sql. (have you considered what happens if a lastname is "O'Rourke")

Dim objCmdAs SqlCommandDim strConnAs New SqlConnection(ConfigurationManager.AppSettings("conn"))'... objCmd.Parameters.Add(New SqlParameter("@.p1", strUser)) objCmd.Parameters.Add(New SqlParameter("@.p2", strFName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p3", strLName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p4", strLang.Text)) objCmd.Parameters.Add(New SqlParameter("@.p5", strCtry.Text)) objCmd.Parameters.Add(New SqlParameter("@.p6", strPhone.Text)) objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _" VALUES(@.p1,@.p2,@.p3,@.p4,@.p5,@.p6)", strConn) strConn.Open() objCmd.ExecuteNonQuery()

|||

still good option is write stored procedures wherever necessary, they are better in performance as they are precompiled. rest what mike has given as example is good one.

and for earlier post values clause has its own brackets so you need to close where i mentioned earlier.

thanks,

satish

|||Thanks, now I see. Hopefully it will work now.|||

satish_nagdev:

still good option is write stored procedures wherever necessary, they are better in performance as they are precompiled.

Performance differences between dynamic sql and stored procs is one of those things that is widely disputed. Personally i'm quite fond of dynamic sql but will still use a sproc if i see a benefit. But, rather than just debate the issue, let's test it. Here I offer the results of a very simple performance test.

IterationDynSqlSproc10.0003820.00023520.0001760.00017430.0001490.00016440.0001390.00015150.0001650.00016860.0001420.00015970.0001410.00015080.0001610.00016790.0001430.000159100.0001410.000150

Since the execution plan for dynamic sql is also cached (as is the execution plan for a sproc), the dynamic sql actually turns out to be quite performant.
Note that on iteration 1, the dynamic sql suffered a little because i ran it first. If i had run the sproc first, the result would look more like this:

IterationDynSqlSproc10.0001850.00038320.0001430.00016030.0001400.00015340.0004810.00017250.0001580.00020460.0001380.00015370.0001390.00014880.0001510.00017290.0001440.000149100.0001400.000147

Both set of results were taken after running my test code a few times to try to be more consistent with how a system in motion might perform.

Of course test results mean nothing unless you know how the test was run. I ran the test on my development system where sql 2000 was also installed on the same box.

This is the test code. Please adapt it to your own real word test to see if dynamic sql can compete with your own sprocs.

The test sproc:

CREATE PROCEDURE GetUserActivity (@.userid integer)AS-- tblTransactionLog has 1 million+ rows of data-- the userid column is indexedSELECT *FROM tblTransactionLogWHERE userid = @.userId;GO

The page code:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadDim swAs StopwatchDim drAs SqlDataReaderDim connAs SqlConnectionDim cmdDynamicAs New SqlCommandDim cmdSprocAs New SqlCommandDim dynParamAs SqlParameterDim sprocParamAs SqlParameterDim tsDynamicAs TimeSpanDim tsSprocAs TimeSpan conn =New SqlConnection("Initial Catalog=webcommon;Integrated Security=True") dynParam =New SqlParameter("@.userid", SqlDbType.Int, 4) dynParam.Value = 4347 cmdDynamic.Parameters.Add(dynParam) sprocParam =New SqlParameter("@.userid", SqlDbType.Int, 4) sprocParam.Value = 4347 cmdSproc.Parameters.Add(sprocParam) conn.Open() Using conn'prepare for sproc cmdDynamic.CommandText ="GetUserActivity" cmdDynamic.CommandType = CommandType.StoredProcedure cmdDynamic.Connection = conn'prepare for dynsql cmdSproc.CommandText ="select * from tblTransactionLog where userid = @.userid;" cmdSproc.CommandType = CommandType.Text cmdSproc.Connection = conn Response.Write("<table border=""1""><tr><th>Iteration</th><th>DynSql</th><th>Sproc</th></tr>")For indexAs Integer = 1To 10'going first incurs a small performance penalty on the very first iteration sw = Stopwatch.StartNew dr = cmdSproc.ExecuteReader() tsSproc = sw.Elapsed dr.Close() sw = Stopwatch.StartNew dr = cmdDynamic.ExecuteReader() tsDynamic = sw.Elapsed dr.Close() Response.Write(String.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>", index, tsDynamic.TotalSeconds.ToString("n6"), tsSproc.TotalSeconds.ToString("n6")))Next Response.Write("</table>")End UsingEnd Sub

|||

satish_nagdev:

still good option is write stored procedures wherever necessary, they are better in performance as they are precompiled.

This is misguided advice. There are good reasons to use stored procedures, but performance is not one of them. There are places where *not* using stored procedures is a better option. This topic (stored procedures vs. inline SQL) is the subject of a lot of heated, well-reasoned discussion in the blogosphere.

|||How awesome that you would take the time to detail all this for me! Thanks. I will test it out soon. Right now, I have to go one step at a time understanding the underlying principles and solving some other errors that are showing up.|||Mike, I'm getting this error while trying to use your suggestion on parameters: "Object reference not set to an instance of an object." This comes with each line that begins with "objCmd.Parameters." These are merely strings, as far as I can see, so I don't know why it would be asking for object instances.|||

my bad. when adapting your code i got it out of sequence...you need to create the command object before you add the parameters.

Dim objCmdAs SqlCommandDim strConnAs New SqlConnection(ConfigurationManager.AppSettings("conn")) objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _" VALUES(@.p1,@.p2,@.p3,@.p4,@.p5,@.p6)", strConn) objCmd.Parameters.Add(New SqlParameter("@.p1", strUser)) objCmd.Parameters.Add(New SqlParameter("@.p2", strFName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p3", strLName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p4", strLang.Text)) objCmd.Parameters.Add(New SqlParameter("@.p5", strCtry.Text)) objCmd.Parameters.Add(New SqlParameter("@.p6", strPhone.Text)) strConn.Open() objCmd.ExecuteNonQuery()

|||

Terri, Mike,

i wont argue on that. I agree with you guys upto a limit, but mike in my last project there were heaps of inline queries we found while re-writing the application, so using procedures added positively to scalability. so depends on from situation to situation.

mike you've done testing thats good, if you get time could you do it for simultaneous instances say 10 at a go?

thanks,

satish.

|||

Inline queries should by managed in a DAL Component. My DAL is a seperate project which keeps things nice and tidy.
I actually have very few hand typed dynamic sql statements. My dynamic sql is about 99% generated on the fly.

Anyways, I put my test page through an ACT test script and here are the results. I used only 8 simultaneous connections to avoid a resultset with http errors .

Test 1 - sproc performance:

commented out the dynamic reader code inside the loop
test duration: 1 minute
Avg Requests per second: 587
Total requests completed: 35,232

Test 2 - dynamic sql performance:

commented out the sproc reader code inside the loop
test duration: 1 minute
Avg Requests per second: 601
Total requests completed: 36,082

My test setup is a little flawed since my test script was running on the same system that was under test. But, since we're just doing a head to head comparison and since both tests were subject to the same testing flaw, i'd have to conclude that dynamic sql (in this specific test case) outperformed a stored proc.

executeBatch fails on stored proc call

I have a stored procedure that is only supposed to insert if the record
does not already exist.
ALTER PROCEDURE [CARTS].[Insert_Store_Item_Price_Data]
@.Store_Item_Price_Change_ID varchar(50),
@.Store_ID char(4),
@.Item_ID char(14),
@.Batch_Number_ID varchar(6),
@.Effective_Start_Date datetime,
@.Price_AMT decimal(8,2),
@.Promotion_Code smallint,
@.State_Name varchar(10),
@.Record_Creation_Timestamp datetime
AS BEGIN
-- insert a record if no duplicate record is found
DECLARE @.ItemCode char(14)
SELECT @.ItemCode=Item_ID
FROM [CARTS].[Store_Item_Price] WITH (NOLOCK)
WHERE Store_ID=@.Store_ID AND
Item_ID=@.Item_ID AND
Batch_Number_ID = @.Batch_Number_ID AND
Price_AMT = @.Price_AMT AND
Promotion_Code = @.Promotion_Code AND
State_NAME = @.State_NAME
IF(@.ItemCode IS NULL)
BEGIN
INSERT INTO [CARTS].[Store_Item_Price]
([Store_Item_Price_Change_ID]
,[Store_ID]
,[Item_ID]
,[Batch_Number_ID]
,[Effective_Start_Date]
,[Price_AMT]
,[Promotion_Code]
,[State_Name]
,[Record_Creation_Timestamp])
VALUES
(@.Store_Item_Price_Change_ID,
@.Store_ID,
@.Item_ID,
@.Batch_Number_ID,
@.Effective_Start_Date,
@.Price_AMT,
@.Promotion_Code,
@.State_Name,
@.Record_Creation_Timestamp);
END
END
Is there an ELSE statement I can add that will return a zero for rows
effected? That way executeBatch will work. Right now, it returns the
following exception:
java.sql.BatchUpdateException: The returned update count was -1. Either
a procedure returned a result set or not every procedure returned an
update count. The driver expects 11 update counts to be returned from
this batch.StevenMartin (stevenmartin@.us.ibm.com) writes:
> I have a stored procedure that is only supposed to insert if the record
> does not already exist.
>...

> Is there an ELSE statement I can add that will return a zero for rows
> effected? That way executeBatch will work. Right now, it returns the
> following exception:
Write the query as:
INSERT tbl (...)
SELECT ....
WHERE NOT EXISTS (SELECT *
FROM tbl
WHERE ...
Note that there is not any FROM clause in the outer SELECT.
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 without Insert

Is there a way to let an account have execute permission on a stored
procedure but not let that stored procedure run insert , delete, or
update records. Basically only let them run or create stored
procedures that do selects.[posted and mailed, please reply in news]

HD (harlan@.elementalcomponents.com) writes:
> Is there a way to let an account have execute permission on a stored
> procedure but not let that stored procedure run insert , delete, or
> update records. Basically only let them run or create stored
> procedures that do selects.

I'm not really sure what you are asking. You could have a user which have
permissions to create procedure, but only has SELECT permissions on the
tables. In such case, the procedures of that user only perform SELECTs,
no updates.

But if you are asking if you somehow can say that a user may only execute
stored procedures that performs read-only operations, there is no way
to do this by a single setting, at least not what I can think of.

But you can of course grant execute permissions selectively. And to find
out which procedures that performs updates, you can use this select:

select name
from sysobjects o
where o.type = 'P'
and exists (select *
from sysdepends d
where d.id = o.id
and d.resultobj = 1)

However, a word of caution is that this query may not return all updating
procedures. For instance, if you create a procedure first and then the
tables it refers to, there will not be any dependencies recorded.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Execute SQL Task: String or binary data would be truncated

Hi!

I try to execute SQL Task with simple statement

insert T (comment) values (@.Comment)

where comment is varchar(1000). I map package variable User::Comment (type string) to parameter @.Comment in SQL Task properties. But when length of User::Comment greater than 10 characters SSIS returns errors:

Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (@.Comment)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The similar messages from this forum suggest using the OLEDB connection. I try this method too, but get the same result.

Error: 0x0 at Execute SQL Task 1: String or binary data would be truncated.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "insert T (comment) values (?)" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What is it? Is this a bug, I made a mistake or I have SQL Server old version?

I have SQL 2005 (Developer Edition) 09.00.3054

TIA, Max.Aren't you missining the 'INTO' in the insert statement?

|||

Rafael Salas wrote:

Aren't you missining the 'INTO' in the insert statement?

That's an optional clause, technically, but it can't hurt to try it. Maybe the Execute SQL Task is a bit more rigid.

execute SQL task with in a For Each Loop

hi guys,

i'm trying to do a bulk insert through a t-sql which accepts 3 params.

i frist run a SQL statement to get a list of DB's to update through a SQL task, then i save the output to a variable object. which is been used by the For each loop.

i've done the mapping with the object fine. but i want to call a SQL task within for each loop, and parse the above results as parameters. i tried parsing it as

exec mysp3 @.[User::fundid], @.[User::dbCode], @.[User::subfund]

where fundiid, dbcode and subfund are user defined variables that gets updated by the ado recordset.

this fails with the error msg:

SSIS package "SP test.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC @.[User::fundid], @.[User::dbcode], @.[User::subfund] failed with the following error: "Incorrect syntax near 'User::fundid'.". 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

Warning: 0x80019002 at Shred the contents of the variable: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "SP test.dtsx" finished: Failure.

i also tried

exec mysp3 ?, ?, ?

then mapping the inputs still doesn't work

same thing with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC ?,?,? failed with the following error: "Syntax error, permission violation, or other nonspecific error". 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

Look here:

http://www.sqlis.com/default.aspx?81

|||

Thanks Loom,

just what i wanted. i didn't know you have to interpret your expressions differently depending on the connection type. well now i do

by the way because of these different data conection types, my variable types are different. for example i save my fundid variable as int32. when using a OLE DB connection under parameter mapping there's no int, but numeric and it craps out when i try to do the mapping. is there a way to cast this? when passing to the t-sql?

execute sql statements in text file

I am new to batch files. I have a text file that has several INSERT/UPDATE
sql statements. I would like to create a batch file to execute the sql
statements in the text file on my server. I am not having any success with
this. I thought I could create a batch file using osql that executes the sql
statements in text file but it's not working and I have no idea what I'm
doing wrong. Maybe there is a better way to do this? Any suggestions are
welcomed.
I am using the following:
osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> osql -S server -d database -E -I file pathname
Change -I to -i
Linchi
"fullertee via SQLMonster.com" wrote:
> I am new to batch files. I have a text file that has several INSERT/UPDATE
> sql statements. I would like to create a batch file to execute the sql
> statements in the text file on my server. I am not having any success with
> this. I thought I could create a batch file using osql that executes the sql
> statements in text file but it's not working and I have no idea what I'm
> doing wrong. Maybe there is a better way to do this? Any suggestions are
> welcomed.
> I am using the following:
> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
>|||Thanks Linchi
Sorry that was just a typo. It not working with the i.
osql -S server -d database -E -i file pathname
fullertee
Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>Change -I to -i
>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Error message? Can you post the exact command you execute?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fullertee via SQLMonster.com" <u21934@.uwe> wrote in message news:6066962294ae1@.uwe...
> Thanks Linchi
> Sorry that was just a typo. It not working with the i.
> osql -S server -d database -E -i file pathname
> fullertee
> Linchi Shea wrote:
>> osql -S server -d database -E -I file pathname
>>Change -I to -i
>>Linchi
>> I am new to batch files. I have a text file that has several INSERT/UPDATE
>> sql statements. I would like to create a batch file to execute the sql
>>[quoted text clipped - 5 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Thank you so much. Your question made me look at my bat file closer. I was
referencing the wrong database. Thanks! It's working now.
or Karaszi wrote:
>Error message? Can you post the exact command you execute?
>> Thanks Linchi
>> Sorry that was just a typo. It not working with the i.
>[quoted text clipped - 12 lines]
>> I am using the following:
>> osql -S server -d database -E -I file pathname
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

execute sql statements in text file

I am new to batch files. I have a text file that has several INSERT/UPDATE
sql statements. I would like to create a batch file to execute the sql
statements in the text file on my server. I am not having any success with
this. I thought I could create a batch file using osql that executes the sql
statements in text file but it's not working and I have no idea what I'm
doing wrong. Maybe there is a better way to do this? Any suggestions are
welcomed.
I am using the following:
osql -S server -d database -E -I file pathname
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1> osql -S server -d database -E -I file pathname
Change -I to -i
Linchi
"fullertee via droptable.com" wrote:

> I am new to batch files. I have a text file that has several INSERT/UPDAT
E
> sql statements. I would like to create a batch file to execute the sql
> statements in the text file on my server. I am not having any success wit
h
> this. I thought I could create a batch file using osql that executes the s
ql
> statements in text file but it's not working and I have no idea what I'm
> doing wrong. Maybe there is a better way to do this? Any suggestions are
> welcomed.
> I am using the following:
> osql -S server -d database -E -I file pathname
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1
>|||Thanks Linchi
Sorry that was just a typo. It not working with the i.
osql -S server -d database -E -i file pathname
fullertee
Linchi Shea wrote:[vbcol=seagreen]
>Change -I to -i
>Linchi
>
>[quoted text clipped - 5 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Error message? Can you post the exact command you execute?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fullertee via droptable.com" <u21934@.uwe> wrote in message news:6066962294ae1@.uwe...[vbcol
=seagreen]
> Thanks Linchi
> Sorry that was just a typo. It not working with the i.
> osql -S server -d database -E -i file pathname
> fullertee
> Linchi Shea wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1[/vbcol]|||Thank you so much. Your question made me look at my bat file closer. I was
referencing the wrong database. Thanks! It's working now.
or Karaszi wrote:[vbcol=seagreen]
>Error message? Can you post the exact command you execute?
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

Execute SQL Insert Statement from Script Task using package OLEDB Connection

Is there a way to directly do this in one step(Execute SQL Insert Statement from Script Task using package OLEDB Connection)?

Right now I'm using a script task to build a sql insert statement using package variables (to fill values) populated by certain logic in the package.

Then assigning this command string to a package variable.

Then using a sql execute task to execute this variable.

A link to an article or code would be greatly appreciated.

You can use the classes in the System.Data.OleDB namespace and the ConnectionManager.AquireConnection method to do this. It is easiest to do this if you have an ADO.NET OLEDB connection, as that returns a managed object that you can use in your script task.

See jaegd's post in this thread for a sample. It shows use of the SQL Client, and it's a script transform rather than a task, but hopefully it's enough to get you started. If not, post back and we'll help you resolve any difficulties.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1895867&SiteID=1

Execute procedure

Hello,

I have an SQL procedure as follows:

...

INSERT dbo.Levels (LevelName)
VALUES (@.LevelName)
...

LevelName is an input parameter of nvarchar type.

What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?

Thanks,

Miguel

Create a SqlConnection to the database.

Use the SqlConnection as a parameter to create a SqlCommand.

Set the command's CommandType to StoredProcedure.

Add a parameter called "@.LevelName" to the command with the value you want to insert.

Call ExecuteNonQuery on the command.

Clean up.

(The indicated SQL won't return any value.)

|||

I always use something like this (or a variation of, like ExecuteSPReturnDataTable, DataSet, etc depeding on the type of proc)

In a data access layer

1public long ExecuteStoredProcedure(string ProcedureName, Object[] Parameters)2 {3long result =new long();4 SqlCommand command;56try7 {8 Logon();910 command =new SqlCommand(ProcedureName, _conn);11 command.CommandType = CommandType.StoredProcedure;1213if (Parameters !=null){14foreach (SqlParameter paramin Parameters)15 {16 command.Parameters.Add(param);17 }18 }19 result = command.ExecuteNonQuery();2021 }22catch (Exception ex)23 {24 result = 0;25 }26finally27 {28try29 {30 Logoff();31 }32catch (Exception ex2)33 {34 }35 }3637return result;38 }

|||

Hi,

Could you please tell me what are the Logon and Logoff functions?

Could you provide me an example?

Anyway, I created a new code but it works not only with SQL database but also with other databases.

I didn't test it yet but here it is:

1' ExecuteStoredProcedure2Public Shared Function ExecuteStoredProcedure(ByVal procedureNameAs String,ByVal ParametersAs Object())As Long34' Create output5Dim outputAs New Long67' Define the connection string8Dim connectionStringAs ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnStr")910' Construct an ADO.NET provider factory11Dim dbProviderAs DbProviderFactory = DbProviderFactories.GetFactory(connectionString.ProviderName)1213' Create and define the connection14Dim connectionAs DbConnection = dbProvider.CreateConnection()15 connection.ConnectionString = connectionString.ConnectionString1617' Run command18Try1920' Open the connection21 connection.Open()2223' Create command24Dim commandAs DbCommand = dbProvider.CreateCommand()2526' Define command properties27With command28 .CommandText = procedureName29 .Connection = connection30 .CommandType = CommandType.StoredProcedure31End With3233' Add command parameters34If Not (ParametersIs Nothing)Then35 For Each parameterAs ParameterIn Parameters36 command.Parameters.Add(parameter)37Next38 End If3940' Execute command41 output = command.ExecuteNonQuery4243Catch exAs Exception4445' Define output as 046 output = 04748Finally4950' Close the connection51 connection.Close()5253End Try5455' Return output56Return output5758End Function' ExecuteStoredProcedure

What do you think?

Cheers,

Miguel

|||

My preference would be to use the Enterprise Library 2.0 Data Access Application block.

In it there are procedures for ExecuteStoredProcedure.

It is the neatest way since you not worried about the Data Access part and concentrate on the business intricacies of the project.

Let me know your thoughts.

|||

Hello,

This seems interesting.

Does it work also with Microsoft Access databases and MySQL or only with MS SQL and Oracle?

Thanks,

Miguel

|||

Hello,

Any idea how to run a stored procedure with Enterprise Library 2.0?

Any information on this anywhere?

Thanks,

Miguel

|||

Quoting from the help of the Enterprise Library

"

The application block supplements the code in ADO.NET 2.0 that allows you to use the same code with different database types. It includes classes for SQL Server and Oracle databases. These classes contain code that provides database-specific implementations for features such as parameter handling and cursors. In addition, theGenericDatabase class allows you to use the application block with any configured ADO.NET 2.0DbProviderFactoryobject. You can extend the application block by adding new database types that include database-specific features or that provide a custom implementation of an existing database. The only requirement is that an ADO.NET 2.0DbProviderFactory class exists for the target database.

"

Therefore you have to write code for MS Access and MySQL in the block to extend it.I saw the code for the block. It had classes for SQL server and Oracle only.

execute package task can't continue when the child package failed?

I use a execute package task to run a child package in which I run some sql task.

as the error handle I insert a script task and link a line from execute package task to script task

of course the line is red,

but I found when the child package failed, the execute package task turns red,it stopped

the script task can't be run, I don't konw why?

I tried it again.

I found that if the child package successed then the parent package can run in the success flow

but if child package falied then the parent package will stop at the execute package task, can't run the failure flow.

Anybody konws this....

|||

ycjj wrote:

I tried it again.

I found that if the child package successed then the parent package can run in the success flow

but if child package falied then the parent package will stop at the execute package task, can't run the failure flow.

Anybody konws this....

i'm not sure if this will work. however, try setting the FailParentOnFailure property of the child package to "false".|||

thank you for the answer.

I also think it seems strange....

I tried it. The default value of FailParentOnFailure is false.

I tried it by setting false and true

the result is same.The child package stopped when it failed.

|||

ycjj wrote:

thank you for the answer.

I also think it seems strange....

I tried it. The default value of FailParentOnFailure is false.

I tried it by setting false and true

the result is same.The child package stopped when it failed.

ok. the child package will always stop executing when it fails. my understanding was that you wanted control to return to the parent package when the child package fails. to execute a script task when the child package fails, an on failure precedence constraint from the child package should be connected to the script task.|||

>my understanding was that you wanted control to return to the parent package when the child package fails. to execute a script task when the child package fails, an on failure precedence constraint from the child package should be connected to the script task.

first thanks for your attention.

yes, I want to return to parent package after the child package failed and I hope that the parent package will continue to execute from the execute package task on the failure precedence constraint to a script task as a error handle.but now the execute package task stops if the child package fails.

maybe the failure precedence constraint is wrong......

|||

I found the prpperty "LogicalAnd" of the failure precedence constraint from execute package task to script task(error handle)

I change the value of property "LogicalAnd" from "True" to "False", it works well, but I still don't know the reason.

|||

I made some study and know the reason now .

in my parent package, some execute package tasks have a common error handle task, but I set property "LogicalAnd" of these failure precedence constraint "false"(dedault), it means all of execute package tasks fail will cause error handle task running, of course this is not right,

it must be one of of execute package tasks fails will cause error handle task running, so the value of property "LogicalAnd" is "True" is correctly.

thanks.

EXECUTE AT <Linked Server> Question

In a stored procedure, I have the following TSQL statement:

EXECUTE ('Insert Into Federation.dbo.FederationData (FederationData) Values
(?)', @.Buffer) AT [HostBox.abcd.mydomain.com\SQLServer2005,1563];

This works fine. However, I do not want to hard-code the name of the linked server like I have above. I would like to specify it at run-time (because it could be different based on certain situations). I cannot seem to get it to work using a variable to represent the linked server name, so it can be dynamic. Can it be done?

Thanks - Amos

What about something like the following:

DECLARE @.SQL1 varchar(500)

DECLARE @.STR_LINKEDSERVER varchar(80)

SET @.STR_LINKEDSERVER=N'HostBox.abcd.mydomain.com\SQLServer2005,1563'

SET @.SQL1 = N'EXECUTE (''Insert Into Federation.dbo.FederationData (FederationData) Values (?)'', @.Buffer) AT [' + @.STR_LINKEDSERVER + '];'

EXECUTE sp_executesql @.SQL1