Executing Procedure (OLEDB Session Object)

I'm executing a very simple Sotred Procedure from VB6 and I'm getting this error:

"Requested operation requires an OLE DB Session object, which is not supported by the current provider."

I executed it from Query Analyzer and Worked perfectly!

I hope you can help me with this now! :(How are you trying to execute the sp in vb6 - please post your code - including connection string ... ?sql

Executing DTS Package - Error not able to find the package.

I am running Sqlserver 2005 and IS.
Here is the SP I am running.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: Name
-- Create date:
-- Description:
-- ========================================
=====
ALTER PROCEDURE [dbo].[CallRiskDataDTS]
-- Add the parameters for the stored procedure here
@.loaddate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.DTSPackageObject int
DECLARE @.HRESULT int
DECLARE @.property varchar(255)
DECLARE @.return int
DECLARE @.ErrDescrip varchar(255)
DECLARE @.ErrSource varchar(30)
DECLARE @.ErrHelpId int
DECLARE @.ErrHFile varchar(255)
DECLARE @.ErrMsg varchar(255)
Declare @.FileFilter varchar(8)
DECLARE @.sDTSPackagePath varchar(1000)
DECLARE @.sDTSSpecialUser varchar(50)
if not IsDate(@.loaddate) = 0
Begin
select @.loaddate = getdate()
End
select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(mm,@.loaddate)))),'') +
convert(varchar(2), Datepart(mm,@.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(dd,@.loaddate)))),'') +
convert(varchar(2), Datepart(dd,@.loaddate))
SELECT @.ErrMsg = 'Error running DTS package'
-- Create a DTS Package object
EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR (@.ErrMsg,11,1)
RETURN
END
EXEC @.HRESULT = sp_OAMethod
@.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolData
Upload",
Nothing)', NULL --, @.flags=256,
@.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
@.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
@.PackagePassword =''
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
OUTPUT,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrDescrip,11,1)
RETURN
END
-- Set the FailOnError property to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Set
to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
@.FileFilter --Set to true
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
-- Call the EXECute method
EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
--remove the object from memory
EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
END
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
I am getting the following message
Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
{}.{}') does not exist. I want to set this up so I can execute is from an
ASP.NET page. I am unable to find the cause of it.
In my SQL Management Studio, connect to Integration services, I see the
package in STORED PACKAGES , MSDB tree.
Any help is greatly appreciated.
ThanksFor that matter I am not able execute any package, I am getting the same
error. Thanks
Raj.
"Raj25" wrote:

> I am running Sqlserver 2005 and IS.
> Here is the SP I am running.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
>
>
> -- ========================================
=====
> -- Author: Name
> -- Create date:
> -- Description:
> -- ========================================
=====
> ALTER PROCEDURE [dbo].[CallRiskDataDTS]
> -- Add the parameters for the stored procedure here
> @.loaddate datetime
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> DECLARE @.DTSPackageObject int
> DECLARE @.HRESULT int
> DECLARE @.property varchar(255)
> DECLARE @.return int
> DECLARE @.ErrDescrip varchar(255)
> DECLARE @.ErrSource varchar(30)
> DECLARE @.ErrHelpId int
> DECLARE @.ErrHFile varchar(255)
> DECLARE @.ErrMsg varchar(255)
> Declare @.FileFilter varchar(8)
> DECLARE @.sDTSPackagePath varchar(1000)
> DECLARE @.sDTSSpecialUser varchar(50)
> if not IsDate(@.loaddate) = 0
> Begin
> select @.loaddate = getdate()
> End
> select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(mm,@.loaddate)))),'') +
> convert(varchar(2), Datepart(mm,@.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(dd,@.loaddate)))),'') +
> convert(varchar(2), Datepart(dd,@.loaddate))
> SELECT @.ErrMsg = 'Error running DTS package'
> -- Create a DTS Package object
> EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR (@.ErrMsg,11,1)
> RETURN
> END
> EXEC @.HRESULT = sp_OAMethod
> @.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolDa
taUpload",
> Nothing)', NULL --, @.flags=256,
> @.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
> @.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
> @.PackagePassword =''
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
> OUTPUT,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrDescrip,11,1)
> RETURN
> END
> -- Set the FailOnError property to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Se
t
> to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
> @.FileFilter --Set to true
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> -- Call the EXECute method
> EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> --remove the object from memory
> EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
> END
>
> SET QUOTED_IDENTIFIER OFF
> SET ANSI_NULLS ON
>
> I am getting the following message
> Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
> The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
> {}.{}') does not exist. I want to set this up so I can execute it from a
n
> ASP.NET page. I am unable to find the cause of it.
> In my SQL Management Studio, connect to Integration services, I see the
> package in STORED PACKAGES , MSDB tree.
> Any help is greatly appreciated.
> Thanks
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

executing DTS - DSN error...

Perhaps this has already been answered. If so, I apologize. I'm a
newbie at this stuff. I have a DTS package to import some Visual
Foxpro data. Yes, I said Visual Foxpro. :) The DTS package works
beautifully when I rightclick on the package and choose "execute." And
it works beautifully when I run dtsrun... at the DOS prompt. But it
doesn't work when I try to execute it with code in Query Analyzer.
I've tried exec master..xp_cmdshell 'dtsrun ...' And I've tried
scheduling the package as a Job, disabling the Job and then using
msdb..sp_start_job to run it. Neither of those work.

I get a "DSN not found and no default driver specified."

My DSN stuff looks OK to me, but perhaps some of that is wrong. What
does the DSN have to look like in order for the code in Query Analyzer
to find it? If the DSN is screwed up, why would it work OK when
executed from some places, but not in others? What am I missing?
Any help would be much appreciated. :) thanks...
-emilyAlso, I should clarify. Everything is local. Everything's on my
laptop on my coffee table. No network. SQLServer is local, Foxpro
data is local.|||OK, false alarm, I figured this out myself. Well, I didn't really
figure it out, but I did get it to work. If I set up the FoxPro DSN as
a System DSN, then it works. User DSN doesn't. I have no idea why.
I'll continue to play around with it. Next time I won't post here
until I'm absolutely sure that I've tried absolutely everything. :)|||emily (ehart624@.hotmail.com) writes:
> OK, false alarm, I figured this out myself. Well, I didn't really
> figure it out, but I did get it to work. If I set up the FoxPro DSN as
> a System DSN, then it works. User DSN doesn't. I have no idea why.
> I'll continue to play around with it. Next time I won't post here
> until I'm absolutely sure that I've tried absolutely everything. :)

I have an idea. If SQL Server runs as Local System it is not likely
to find User DSN for your user. Thus it must be a system DSN.

Recall that when you run from QA, it's SQL Server that runs the
package.

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

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

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

Executing BCP statement throws out the error

Hi

When I execute a simple BCP statement as shown below. It throws out an error message as pasted below. Any help on this is highly appreciated.

Use master
Exec xp_cmdshell 'bcp "select * from Mydb..Records" queryout "D:\Book1.xls" -U [sa] -P [pwd] -c'

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL

Thanks!

No replies to this question? We are using sp_oamethod and when calling the bcp.exe, getting the same error message.|||

can you provide some sample data and DDL?

|||There seemed to be a limit in the temp table for the bcp into a file. If the table contained 20 or less rows, it worked correctly. If the table had 21 + rows, then the bcp failed (the temp table was created and populated during the execution of a proc that then would bcp the data to a file). We ended up replacing the select statement with the temp table name and using "out" instead of "queryout". That fixed the problem. We can not explain what the reason was though.|||

I had the same problem, only instead of Excel files I was using XML files.

Anyway, it looks like it is an internal SQL Server error, and after restarting SQL Server, everything was back to normal.

sql

Executing a task with in script task

How can I execute a sql task from a script task. Both these tasks are part of the same package. The script task is actually part of the error handler. The execute sql task is part of control flow in the package.

Thanks

Why would you want to do this, as opposed to putting an Execute SQL Task in your error handler?|||Well, I dont know if I put a task specific error handler, will the package level error handler will fire or not. ( I want both error handlers to be fired). I'm trying it out now, let me see how it goes.|||Yes, the package error handler should fire as well. Events propagate up to the parent.

executing a package with dtexec.exe

Hi,
when I run the following from the command prompt I am getting an error saying that can not set the variable Status as int.

dtexec /File "C:\work\SSIS Packages\Dataflow\ControlRoom.dtsx" /SET

\Package.Variables[ChannelCode].Value;"test" /SET

\Package.Variables[Status].Value;1

both variables as you can see are in the top level.
the problem is that ChannelCode is string but Status is Integer.
if I take out the /SET \Package.Variables[Status].Value;1 part from the command line it will work fine. I think th eproblem is down to diffenrt types of variable as there are two types of String and Interger.

any ideas on what the problem migth be.
p.s. I am runnning SQL server 2005 Dev edition (without any SP)
CheersAre you sure [Status] is an integer in your package?

What happens when you run it with:
/SET \Package.Variables[Status].Value;"1"|||yes it is Integer and don't want to change it to string as I have ament that in few places in my package|||Have you tried DTexecUI to set those values and see how the command line gets generated?|||this is what get's generated

/FILE "C:\work\SSIS Packages\Dataflow\ControlRoom.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /SET "\Package.Variables[ChannelCode].Value";Test /SET "\Package.Variables[Status].Value";1
but I'm getting the same problem
back to square one|||Can you please post the exact error?|||I'm getting

Started: 4:56:31 PM
Error: 2007-03-26 16:56:33.37
Code: 0xC0017006
Source: ControlRoom
Description: The expression "@.Status == 1" must evaluate to True or False. Ch
ange the expression to evaluate to a Boolean value.
End Error
Error: 2007-03-26 16:56:33.37
Code: 0xC0014021
Source: ControlRoom

as it does a check against the Status value that I'm loading from the command prompt.
if I specify (hardcode) the variable value inside the package it will work fine, this happens only when I try to pass the value from command prompt
Thanks|||Do you have EvaluateAsExpression set to false when you need to have it set to true somewhere?

I don't think we have all of the pieces of the puzzle here, but it seems that "@.Status == 1" is being passed in as a string, not a conditional check.|||sorry, I think it's my fault as I didn't plain a clear picture.
I am passing the variable Status from command prompt and inside the package it trys to check if the passes value is 1
as the Status value is not getting passed from the command prompt and the default value of @.Status in the package is to 0, the condition fails.

now if I hardcode the default value of @.status in the package to 1 and do not try to pass the value for status from command prompt. it work perfectly!
I hope this makes it clear|||And the scope of the variable is correct? You don't have two variables of the same name in different scopes, do you? This can happen.

For instance, you could have a Status variable of package scope and have a Status variable of data flow scope in the same package.|||I thought that this might be the issue but I'm looking at all the variables across the whole package and there isn't any other one with the same name. @.Status is the only and as you can see it is at the top level so it is visible by all the sub tasks.
cheers|||

Kolf wrote:

I thought that this might be the issue but I'm looking at all the variables across the whole package and there isn't any other one with the same name. @.Status is the only and as you can see it is at the top level so it is visible by all the sub tasks.
cheers

Yep, I can see that, but just because it's visible to subtasks doesn't mean that the subtasks can't have their own variable of the same name, that's why I asked.|||

Kolf wrote:

... and inside the package it trys to check if the passes value is 1 ...

How are you doing this? Where are you doing this? Control flow, data flow, conditional split, derived column, etc...|||in the varaible windows I click on a button called show user variable and that displays all the variables define with in that package and I have just one variable called @.Status which is Int32
Thanks

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
..net framework - 1.0.3705
Thanks,
Scott
Hi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about the
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the[vbcol=seagreen]
|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOLEDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>
|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode-532459699int
StackTrace" at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManaged Interface.ExecuteToOutputStream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(S tream strm)"string
Scott Walters wrote:[vbcol=seagreen]
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fake
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>
|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote:
sql

ExecuteXmlReader error code 0x80040E21

Hi,
I can't seem to do anything with sqlxml from the .net managed classes
without getting this error. This a simple test case I've been running
from a console app...
string err;
try
{
string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
SqlXmlCommand cmd = new SqlXmlCommand(connstr);
cmd.CommandText = "select product_id, name_display from tc_products
FOR XML AUTO";
XmlReader xr = cmd.ExecuteXmlReader();
catch (SqlXmlException e)
{
e.ErrorStream.Position = 0;
StreamReader errreader = new StreamReader(e.ErrorStream);
err = errreader.ReadToEnd();
errreader.Close();
}
It enters into the catch block but the error is always blank.
I don't ever see anything in sql profiler. Both columns in the select
are char types. The sql runs ok in query analyzer.
I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
versions I have...
sqlxml (sp3) - 3.2.2917.0
vs.net - 7.0.9466
.net framework - 1.0.3705
Thanks,
ScottHi Scott,
Is there some information in e.Message? There should be details about the
error either in the exception message or in the error stream.
Thank you,
Amar
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running
> from a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products
> FOR XML AUTO";
> XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select
> are char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other
> versions I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||e.Message = "Exception from HRESULT: 0x80040E21."
The e.ErrorStream length is 0. The message is blank.
I also checked my mdac version. It's 2.8 sp1.
Scott
Amar Nalla [MS] wrote:
> Hi Scott,
> Is there some information in e.Message? There should be details about t
he
> error either in the exception message or in the error stream.
> Thank you,
> Amar
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Can you try the below:
ComException ce;
ce = (COMException) ex.InnerException();
ce.ErrorCode; //This will give you the error code.
ce.Message; //This will give you the error message
You will need to include
using System.Runtime.InteropServices
I am not sure why you don't see more details in the error stream and the
outer exception. If you see the actual error using this method then please
let me know as I would like to investigate why this error is not visible
otherwise.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:OF$SY8m7EHA.2032@.tk2msftngp13.phx.gbl...
> e.Message = "Exception from HRESULT: 0x80040E21."
> The e.ErrorStream length is 0. The message is blank.
> I also checked my mdac version. It's 2.8 sp1.
> Scott
>
> Amar Nalla [MS] wrote:
the|||Root is missing in your SQL. Try,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO, elements, root('a')";
instead of,

> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO";
Pohwan Han. Seoul. Have a nice day.
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I can't seem to do anything with sqlxml from the .net managed classes
> without getting this error. This a simple test case I've been running from
> a console app...
> string err;
> try
> {
> string connstr = "Data Source=127.0.0.1;Integrated Security=false;User
> ID=sa;Initial Catalog=WebPlus;Password=;Provider=SQLOL
EDB;";
> SqlXmlCommand cmd = new SqlXmlCommand(connstr);
> cmd.CommandText = "select product_id, name_display from tc_products FOR
> XML AUTO"; XmlReader xr = cmd.ExecuteXmlReader();
> catch (SqlXmlException e)
> {
> e.ErrorStream.Position = 0;
> StreamReader errreader = new StreamReader(e.ErrorStream);
> err = errreader.ReadToEnd();
> errreader.Close();
> }
> It enters into the catch block but the error is always blank.
> I don't ever see anything in sql profiler. Both columns in the select are
> char types. The sql runs ok in query analyzer.
> I'm running vs.net 1.0 and sql 2000 on xp sp2. Here are the other versions
> I have...
> sqlxml (sp3) - 3.2.2917.0
> vs.net - 7.0.9466
> .net framework - 1.0.3705
> Thanks,
> Scott
>|||root() in FOR XML does not work in SQL 2000 (only 2005).
However, there should be a root property on the provider or you need to fake
it using a select '<a>' select '</a>' before and after the command...
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Root is missing in your SQL. Try,
>
> instead of,
>
> --
> Pohwan Han. Seoul. Have a nice day.
> "Scott Walters" <scottw512@.hotmail.com> wrote in message
> news:%23drP7Rc7EHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||Really. By the way, that reminds me of dogs trying to bite their own tails.
IIRC, multiple selects with ExecuteXmlReader, not legacy ADO stream, doesn't
work. Tomorrow in my office I will check that again.
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eEgIJkd8EHA.2900@.TK2MSFTNGP09.phx.gbl...
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to
> fake it using a select '<a>' select '</a>' before and after the
> command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>|||Drilling down on the com exception in the debugger, I also noticed a
com+ exception code and a stack trace. I tried error lookup on the code
but it wasn't found.
_COMPlusExceptionCode -532459699 int
StackTrace " at
Microsoft.Data.SqlXml.Common.ISQLXMLCommandManagedInterface.ExecuteToOutputS
tream()
at Microsoft.Data.SqlXml.SqlXmlCommand.innerExecute(Stream strm)" string
Scott Walters wrote:
> I tried this...here are the results.
> ErrorCode -2147217887 int
> ce.Message "Exception from HRESULT: 0x80040E21." string
>
> Amar Nalla [MS] wrote:
>|||I tried setting the RootTag prop on the command object. That didn't
make any difference. I also attempted to try what you suggested but
wasn't sure I really understood it. I tried it with the sql cmds below.
Is that what you meant?
cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
tc_products FOR XML AUTO";
and...
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '<a>';";
Michael Rys [MSFT] wrote:
> root() in FOR XML does not work in SQL 2000 (only 2005).
> However, there should be a root property on the provider or you need to fa
ke
> it using a select '<a>' select '</a>' before and after the command...
> Best regards
> Michael
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:OcrHfdX8EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
>|||I meant the second one
cmd.CommandText = "select '<a>'; select product_id, name_display from
tc_products FOR XML AUTO; select '</a>';";
But as Han observed, it may be that the ExecuteXMLReader does not allow such
streamed statements (the OLEDB and ADO command streams allowed it).
If you set the RootTag prop, are you getting the correctly formatted XML
back (single root node)?
Best regards
Michael
"Scott Walters" <scottw512@.hotmail.com> wrote in message
news:%23TFNyIo8EHA.1228@.tk2msftngp13.phx.gbl...
>I tried setting the RootTag prop on the command object. That didn't make
>any difference. I also attempted to try what you suggested but wasn't sure
>I really understood it. I tried it with the sql cmds below. Is that what
>you meant?
>
> cmd.CommandText = "select '<a>', product_id, name_display, '</a>' from
> tc_products FOR XML AUTO";
> and...
>
> cmd.CommandText = "select '<a>'; select product_id, name_display from
> tc_products FOR XML AUTO; select '<a>';";
> Michael Rys [MSFT] wrote:

ExecuteReader: Connection property has not been initialized.

I have a web form that is generating an error and I can't seem to figure out why for the life of me. Below is the code:

Private Sub VerifyNoDuplicateEmail()
 Dim conn As SqlConnection
 Dim sql As String
 Dim cmd As SqlCommand
 Dim id As Guid
 sql = "Select UserID from SDCUsers where email='{0}'"
 sql = String.Format(sql, txtEmail.Text)
 cmd = New SqlCommand(sql, conn)
 conn = New SqlConnection(ConfigurationSettings.AppSettings("cnSDCADC.ConnectionString"))
 conn.Open()
 Try
 'The first this we need to do here is query the database and verify
 'that no one has registed with this particular e-mail address
 id = cmd.ExecuteScalar()
 Response.Write(id.ToString & "<BR>")
 Catch
 Response.Write(sql & "<BR>")
 Response.Write("An error has occurred: " & Err.Description)
 Finally
 If Not id.ToString Is Nothing Then
 'The e-mail address is already registered.
 Response.Write("Your e-mail address has already been registered with this site.<BR>")
 conn.Close()
 _NoDuplicates = False
 Else
 'It's safe to add the user to the database
 conn.Close()
 _NoDuplicates = True
 End If
 End Try
 End Sub
Web.Config
<appSettings>
<!-- User application and configured property settings go here.-->
<!-- Example: <add key="settingName" value="settingValue"/> -->
<add key="cnSDCADC.ConnectionString" value="workstation id=STEPHEN;packet size=4096;integrated security=SSPI;data source=SDCADC;persist security info=False;initial catalog=sdc" />
</appSettings>

Can anyone show me the error of my ways?

Thanks,
StephenPlease elaborate on "generating an error". An exact error mesage will probably go a long way in helping you correct your problem.

Also, please use parameters and not string concetenation to build your SQL commands!!

Instead of this:

sql = "Select UserID from SDCUsers where email='{0}'"
sql = String.Format(sql, txtEmail.Text)
cmd = New SqlCommand(sql, conn)

do something like this:

sql = "Select UserID from SDCUsers where email=@.email"
cmd = New SqlCommand(sql, conn)
cmd.Parameters.Add(New SqlParameter("@.email", SqlDbType.VarChar, 99)).Value = txtEmail.Text

Terri|||Terri,

The exact error is the title of the thread "ExecuteReader: Connection property has not been initialized."

Thanks,

Stephen|||Terri,

I tried the changes you suggested and still recieved the same error. After taking a little time off and coming back the to the problem I finally found the error of my ways. I was initializing the CMD object before the CONN object. Thanks for you help.

Stephen|||Oh yes, so you were. :-)

Please use parameters anyway -- that suggestion was something of an aside. And next time you post please include the error message. We could have helped you a lot quicker with that information!

Terri

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.

Execute Statement..

Dear Friens,
I am writing a SP.But storeing a query in a variable.But at the time
of execution generating error.Exam
===================
Declare @.query varchar{500)
Set @.query = 'Select * from table'

if exists (exec (@.query))
print 'Hi'
====================
But "if exists" line giving error.How do I solve this.Please help me
out.
Reagrds
Arijit ChatterjeeHi

EXISTS requires a sub-query as the test, and EXEC does not do that.

If you have to do this dynamically then you may want to look at
sp_executesql to return a count or move everything into @.query.

If you posted more precise detail it may be easier to offer advice.

John

arijitchatterjee123@.yahoo.co.in (Arijit Chatterjee) wrote in message news:<ea01504d.0310302205.2ca98658@.posting.google.com>...
> Dear Friens,
> I am writing a SP.But storeing a query in a variable.But at the time
> of execution generating error.Exam
> ===================
> Declare @.query varchar{500)
> Set @.query = 'Select * from table'
> if exists (exec (@.query))
> print 'Hi'
> ====================
> But "if exists" line giving error.How do I solve this.Please help me
> out.
> Reagrds
> Arijit Chatterjee|||Create proc sp_test
as
Declare @.query varchar{500)
Declare @.var varchar(10)
set @.var='Test'
Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
exec (@.query)--> Working fine
if exists (exec (@.query))--> Sending error
print 'Hi'
Now tell me how to solve this.
Regards
Arijit Chatterjee|||See inline

"Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> Create proc sp_test
> as
> Declare @.query varchar{500)
This will not compile

> Declare @.var varchar(10)
> set @.var='Test'
> Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
If you were doing this correctly the value in @.var would be enquoted

> exec (@.query)--> Working fine
> if exists (exec (@.query))--> Sending error
> print 'Hi'
> Now tell me how to solve this.

Reading books online would be the first place to look.

Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
justify the use of dynamic SQL.

You should then be able to work out how to return the count using
sp_executesql to get what is require.

> Regards
> Arijit Chatterjee

John|||What you need to do in this case is create a sql server temporary
table (such as create table #temp (column1 nvarchar(10),column2
nvarchar(10))) and then build your @.query string so that it inserts
the results of your dynamic select statement into the temp table. You
can then play the whole "exists" game on the results of a query
against your #temp table.

Make sure that you do not build your temp table dynamically. And
remember, you can't insert results from a dynamically built select
statement into a normal variable, but you can insert them into a temp
table.

Good Luck!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<bo2oar$89l$1@.hercules.btinternet.com>...
> See inline
> "Arijit Chatterjee" <arijitchatterjee123@.yahoo.co.in> wrote in message
> news:ea01504d.0311012028.19b506fb@.posting.google.c om...
> > Create proc sp_test
> > as
> > Declare @.query varchar{500)
> This will not compile
> > Declare @.var varchar(10)
> > set @.var='Test'
> > Set @.query = 'Select * from table' + ' Where ' + 'Colname = ' + @.var
> If you were doing this correctly the value in @.var would be enquoted
> > exec (@.query)--> Working fine
> > if exists (exec (@.query))--> Sending error
> > print 'Hi'
> > Now tell me how to solve this.
> Reading books online would be the first place to look.
> Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
> justify the use of dynamic SQL.
> You should then be able to work out how to return the count using
> sp_executesql to get what is require.
> > Regards
> > Arijit Chatterjee
> John

Execute SSIS Package using SQl Server Agent

Hi

I hv created a new Job for my SSIS Package... but when i start the job manually it gives me this error below:

"Executed as User:localhost/SYSTEM. THe package could not be loaded.the Step Failed".

i have my package deployed in Storage Packages[MSDB]...

Could you help me on this....

THanks!

Karthik

it's all to do with security:

Your localhost/SYSTEM account probably has no access to the SISS storage [MSDB].

Either you have modify the sql agent job to run as a user with sufficient rights on the SISS store or you grant this account access.

Then there is also the possibility that this user account has not access to the databases that are opened by the package.

Execute SSIS package on vb.net

Hi All,

I would like to write a small application for execute a SSIS package manually, but i got a error message as following:

Coding:
Dim oPkg As DTS.Package2
oPkg = New DTS.Package2

'Error Message Here
oPkg.LoadFromSQLServer("SQL2005", "sa", "abc", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, Nothing, Nothing, Nothing, "AccessImport")

oPkg.Execute()
oPkg.UnInitialize()

oPkg = Nothing

Error Message:
The specified DTS Package ('Name='AccessImport';ID.VersionID = {[not specified]}.{[not specified]}') does not exist.

So what is the problem here?
Thank a lot.

Laputa

You're using the DTS2000 object model.

You need the Microsoft.SqlServer.Dts.Runtime .Net namespace!

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9mref/html/N_Microsoft_SqlServer_Dts_Runtime.htm

-Jamie

|||Thx you help a lot of me !!|||I can't found the namespace Microsoft.SqlServer.Dts.Runtime

Execute SSIS Package in C# (ErrorMessage ?)

Hi all

I am executing a SSIS Package in C# (2005).
Does anybody know how to find out, in case of an error, the
error message?

I am doing the follwoing:
Microsoft.SqlServer.Dts.Runtime.Package local_Package = new Microsoft.SqlServer.Dts.Runtime.Package();
Microsoft.SqlServer.Dts.Runtime.DTSExecResult local_DTSExecResult = new Microsoft.SqlServer.Dts.Runtime.DTSExecResult();
Microsoft.SqlServer.Dts.Runtime.Application local_Application = new Microsoft.SqlServer.Dts.Runtime.Application();

local_Package = local_Application.LoadPackage("c:\temp\ssis_package.dtsx", null);
local_DTSExecResult = local_Package.Execute();

if (local_DTSExecResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
Display MessageBox of the error message ...
}

Thanks for any comment.

Best regards
Frank Uray

Try reading the SSIS system variable like

local_Package.Variables["ErrorDescription"].value.ToString()

inside the IF condition.

Thanks,
Loonysan

|||

Hi all

I have found out the solution ... :-)

if (local_DTSExecResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in local_Package.Errors)
{MessageBox.Show(local_DtsError.Description);}
}

Best regards
Frank Uray

Execute SSIS package from asp.Net

Hi,

I am a newbie to SSIS. I am trying to execute a simple package that I created from my ASP.Net application.

I get the error 'DTSER_FAILURE'. Can anyone help?

Thanks

Turn on logging, or supply an implementation of IDtsEvents interface to Execute() method to find out some information about the error.

By the way, the most common problem: security, the package is executed under ASP.NET service account, not your domain account.|||

Thanks for the reply.

I turned on the logging. However, no entires are made in the log file.

I thought that there would be some security issues,

> the package is executed under ASP.NET service account, not your domain account.

How do I address this?

|||

HoustonRocket wrote:

> the package is executed under ASP.NET service account, not your domain account.

How do I address this?

Depends on what you mean by "address". This might be quite OK in some situations, but might be not in others. Just something to be aware of.

If you want the package to be executed in different context, use other ways to execute it, rather than invoke it from object model. A common way is to create SQL Agent job and then execute it using Agent's stored procedures. Another way is to execute package using DtExec under different account (see ProcessStartInfo.UserName and ProcessStartInfo.Password).

|||

Hi,

I tried to create a job and add the package as a 'step'. That didnt work either and I got the error

'Microsoft.SqlServer.ConnectionInfo

The specified '@.subsystem' is invalid

Here's what I am trying to do:

I created a package which grabs the data from excel file and populates sql server 2005 DB - simple. This package executes if I run it from the business intelligence studio.

I want to achieve two things

1) Create a job that will schedule the package to run twice a day

2) Execute this package from an asp.net code.

|||

ok another question

I ran the dbo.sp_enum_sqlagent_subsystems and the result does not list SSIS package.

How can I include the SSIS package as a subsystem ?

|||Strange. Are you connected to SQL 2005 system? Have you installed SSIS (a checkbox during SQL install).

Try connecting to SQL using SQL Server Management Studio, can you create new jobs that use SSIS subsystem?

execute ssis and loggin with sqlserver provider cause error

Hi,

I execute my ssis from BI without problem (debug mode), but when I import into SQL server and try to run with logging on sqlserver provider always my package failure.

If option of logging is empty all works fine (No one logging method is active).

There is some permission to set for this option?

I use into SiSS ole db native client connection.

Hope some one can help me.

Alen Italy

This belove is the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "La transazione distribuita è stata
completata. Integrare questa sessione in una nuova transazione o nella
transazione Null.".

How exactly are you running the package, and what security is the connection manager used by the log provider set to use?

|||

I create the package on server log-in with the administrator user anda connect the Sql Server management with a windows autentication...so are the same level user.

With no looggin method works fine...with no one error.

I running my package from SQL server management studio...under the folder STORED PACKAGES > MSDB > MYFOLDER > MyPack and right clic on "run package".

Below the connection string:

DB1
Data Source=SQLTEST;Initial Catalog=NEW_LISTINI;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

DB2
Data Source=SQLTEST;Initial Catalog=NEW_PANGEA;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

EXCELL FILE
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\manny-slave\Fornitori\Listini\Altri\\Ready_4_Import\ePRICE.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1;MAXSCANROWS=8;";

I translate in english the error...

An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80040E37 Description: "The distributed transaction has been completed. To integrate this session in one new transaction or the Null transaction"

The comand line -

/DTS "\MSDB\DTS to SSIS\NEW_LISTINI_IMPORTA_XLS" /SERVER SQLTEST /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"SQLTEST.NEW_LISTINI" /SET "\Package.Variables[arrIDsources].Value";5

_ after many test __

I try many combinations of transaction option.

If I set package "required" works with logging on DB server, but If active "required" on two containers alwails falls.

Seems been not another way for loogging into SQLSERVER.

Or SET all transactions of containers to "supported" or set "Required" at packege level...

If somone heve another vision...here I'm to hear.
Alen

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 resultset

hi all,

in my execute sql task, i have a try catch statement to catch error code if there's an error...

begin try
insert into person.contact (contactid)
values ('1')

end try
begin catch

select @.@.error as ErrorCode
end catch

this ErrorCode will be mapped to a user variable.

my problem is the value for ResultSet.

in order to assign ErrorCode to the variable, i have to set the ResultSet value to Single row, but if this task does not fail, i need to set ResultSet value to None in order for it to run successfully.

How do i go about solving this problem?

Please help. thanks!

Try a case statement:

select case when @.@.error <> 0 then @.@.error else 0 end as ErrorCode|||

hi Phil,

thanks for your reply. but what resultset value should i set ? None or single row?

there'll still be an error if i put single row and there's no error.

if i put none value and there's an error, the error code won't be assign to the user variable..

|||It needs to be set to single row...

Are you saying you tested it with single row and my case statement and it still errored?

How about getting rid of the try and catch statements?

insert into ...

select case ...|||

without the try.. catch statement, the error code will return 0 even though there's an error.

declare @.errorvar int
insert into person.Contact (contactid)
values (1)

set @.errorvar = @.@.error

select case when @.errorvar<>0 then @.errorvar else 0 end as ErrorCode

i ran the above statement in SSMS and there's value for @.errorvar.

|||

Minority80,

I would tweak the approach in your first post to something like:

begin try

insert into contact (contactid)

values ('1')

Select -9999 as ErrorCode -- a predefined 'succesfull' execution code

end try

begin catch

select @.@.error as ErrorCode

end catch

That way, a single row would be always returned. Just make sure -9999 or the code you decide to use is not a valid error code. Then set the ResultSet property of the Execute SQL task to SingleRow.

|||I guess I don't understand. @.@.error should always return a value. So the first approach should work, at least according to BOL. So then, is it in how SSIS handles the SQL?|||

Phil Brammer wrote:

I guess I don't understand. @.@.error should always return a value. So the first approach should work, at least according to BOL. So then, is it in how SSIS handles the SQL?

Phil,

You are right, if there is no error @.@.error returns '0'. So, let me correct my suggestion:

begin try

insert into contact (contactid)

values ('1')

select @.@.error as ErrorCode -- Add this line

end try

begin catch

select @.@.error as ErrorCode

end catch

If you look again; the original post is missing the 'Add this line' I am suggesting; hence no 'SingleRow' is retrieved by the begin-end block and the execute sql task will error when configured as ResultSet= 'SingleRow'.

|||So then is the begin catch...end catch required?|||

thanks Rafeal and Phil too...

the begin catch .. end catch is required so that when error occurs, the errorcode can be captured..

|||

Phil Brammer wrote:

So then is the begin catch...end catch required?

Phil,

Actually is not required. Try and catch is new addition (a good one!) in SQL 2005 and it provides you more control over the error handeling logic in a t-sql block.

|||hmm.... now i'm confused. Rafael, why isn't try.. catch statement required?|||

I said that it is not required because if you omit it, the T-SQL block would be still valid. But if you want to avoid the Execute SQL task to fail because an error within the T-SQL statement, then you have to use it. Actually Try...catch is not available in version previous to 2005.

|||

oh.. but if i omit the try catch statement, the @.@.error value can't seem to be assigned to the package variable.. @.@.error always return 0.

i don't mind if the task fail cos then i can use event handler but @.@.error always return 0.

i assign @.@.error value to a package variable. i check the value by displaying the msgbox i put in a script task with the following code:

MsgBox(Dts.Variables("ErrorID").Value).

The value always return 0 even though there's an error.

Did i do something wrong? Thanks!

|||Well, I am not quite sure, but I think when you don't use the try....catch and a error occurs; the variable will not get populated as the task fails before populating the variable. The value '0' you get when it fails it may be the initial value of the variable (the one given in the variables panel). I am just guessing...