Problem in executing xp_cmdshell with Least Privileged SQL Login account in SQL 2005

Hi,

I have a least privileged SQL Login “Client” and have granted execute rights on XP_Cmdshell SP at master db. When I execute master.. XP_Cmdshell ‘dir’ I’m getting the below error.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Please note it is SQL Login account and not windows account. I have checked everywhere for similar problem and no luck.

Thanks for you help in advance

With regards

GK

Here are a few links that seem to cover your situation:

XP_CMDSHELL error
http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic20720.aspx

Eralper's Blog on Software Development
http://www.kodyaz.com/blogs/software_development_blog/archive/2006/11/23/478.aspx

The problem of xp_cmdshell_proxy_account
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=576326&SiteID=17

Problem in executing xp_cmdshell with Least Privileged SQL Login account in SQL 2005

Hi,

I have a least privileged SQL Login “Client” and have granted execute rights on XP_Cmdshell SP at master db. When I execute master.. XP_Cmdshell ‘dir’ I’m getting the below error.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Please note it is SQL Login account and not windows account. I have checked everywhere for similar problem and no luck.

Thanks for you help in advance

With regards

GK

See my reply to your other related thread:

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

You need to read the Books Online articles I pointed out in my answer there.

Thanks
Laurentiu

problem in executing package in production environment

hi all!

This is my problem. My package executes fine when i set the connection string with the same database where i execute the query. If i execute with another database connection stirng if fails bacause while executing the pacakge it trys to access the same connection string at design mode.

when i try to execute through cmd prompt by setting \conn <new database connection string> it fails.

Is package configuration is the only solution. how can i change conn string depending on different server?

Any help would be appreciated.

Thanks,

Jas

As far as my knowledge, package configuration is the only one solution.|||

Package configurations will work but you'll need to reference the configuration at runtime. You can also use the /SET switch from dtexec.exe to set a property dynamically at runtime. If you're trying to set a connection string though, you can set it with the /Connection swtich. Try doing this from DtsExecUI.exe first to see if you have better luck then grab the command line from the last page down. Hope this helps!

Brian

|||

i am currently trying to set the variable with the /set and give connection string as /conn but when i do it another environment and change the conn string it does not work. Now thats my problem. Now i am trying through package configuration but that too i have some issues. I don't want to use environment variable and cannot use parent pacakge varibale for this particular pacakge cos it is the main parent pacakge. I want to use registry key but if i give the value other than current user. it does not work. I am trying to use the HKEY_local machine /software / .../ .../ value. How doi set the registry key for this in pacakge configuration. And i also tried the config file. I works well through indirect method only in another environment where i need to set the environment variable to hold the path of the config file.

Is there any suggestions?

Thanks,

JAs

sql

problem in executing package in production environment

hi all!

This is my problem. My package executes fine when i set the connection string with the same database where i execute the query. If i execute with another database connection stirng if fails bacause while executing the pacakge it trys to access the same connection string at design mode.

when i try to execute through cmd prompt by setting \conn <new database connection string> it fails.

Is package configuration is the only solution. how can i change conn string depending on different server?

Any help would be appreciated.

Thanks,

Jas

As far as my knowledge, package configuration is the only one solution.|||

Package configurations will work but you'll need to reference the configuration at runtime. You can also use the /SET switch from dtexec.exe to set a property dynamically at runtime. If you're trying to set a connection string though, you can set it with the /Connection swtich. Try doing this from DtsExecUI.exe first to see if you have better luck then grab the command line from the last page down. Hope this helps!

Brian

|||

i am currently trying to set the variable with the /set and give connection string as /conn but when i do it another environment and change the conn string it does not work. Now thats my problem. Now i am trying through package configuration but that too i have some issues. I don't want to use environment variable and cannot use parent pacakge varibale for this particular pacakge cos it is the main parent pacakge. I want to use registry key but if i give the value other than current user. it does not work. I am trying to use the HKEY_local machine /software / .../ .../ value. How doi set the registry key for this in pacakge configuration. And i also tried the config file. I works well through indirect method only in another environment where i need to set the environment variable to hold the path of the config file.

Is there any suggestions?

Thanks,

JAs

problem in executing extended stored procedure

Hi ll,
When i am trying to execute extended stored procedure im getting
error as:
Msg 22048, Level 15, State 0, Line 0
Error executing extended stored procedure: Invalid Parameter
Can u guys help me out
Hi,
Tell us exactly which command and parameters are you using.
By the way, have you checked BOL (if applies) for the parameters that you
need to specify?
Hope this helps,
Ben Nevarez
"mohit" wrote:

> Hi ll,
> When i am trying to execute extended stored procedure im getting
> error as:
> Msg 22048, Level 15, State 0, Line 0
> Error executing extended stored procedure: Invalid Parameter
> Can u guys help me out
>
|||On Jan 14, 12:18 pm, Ben Nevarez
<BenNeva...@.discussions.microsoft.com> wrote:
> Hi,
> Tell us exactly which command and parameters are you using.
> By the way, have you checked BOL (if applies) for the parameters that you
> need to specify?
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
>
> - Show quoted text -
Hi,
Question is something like this:
In procedure create folder for each database and backup of the
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.
For this i am using command as Execute xp_create_subdir and parameter
as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
help if u could tell.
|||Not sure about what exactly your problem is but something like this works
declare @.pathDir nvarchar(80)
set @.pathDir = 'c:\YourDB'
execute master.dbo.xp_create_subdir @.pathDir
By the way, xp_create_subdir is used on the Maintenance Plans but it looks
like it is not documented on BOL.
Hope this helps,
Ben Nevarez
"mohit" wrote:

> On Jan 14, 12:18 pm, Ben Nevarez
> <BenNeva...@.discussions.microsoft.com> wrote:
> Hi,
> Question is something like this:
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.
> For this i am using command as Execute xp_create_subdir and parameter
> as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
> help if u could tell.
>
>
|||On Jan 14, 1:48Xpm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Not sure about what exactly your problem is but something like this works
> declare @.pathDir nvarchar(80)
> set @.pathDir = 'c:\YourDB'
> execute master.dbo.xp_create_subdir @.pathDir
> By the way, xp_create_subdir is used on the Maintenance Plans but it looks
> like it is not documented on BOL.
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
>
>
>
>
>
>
> - Show quoted text -
hi,
The query which you gave is working fine.
But mine is not working can you just check out
create proc [dbo].[backup_proc4] @.para1 varchar(120)
As
declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
select @.str='Adventureworks,Adventureworksdw,test,model'
while(1 <> 2)
begin
select @.var1=substring(@.str,0,charindex(',',@.str))
if (@.var1='')
begin
break
end
select @.para1=@.var1
declare @.str1 varchar(200)
set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
print @.dirPath
execute master.dbo.xp_create_subdir @.dirPath
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
xp_create_subdir @.dirPath
--exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
xp_create_subdir @.dirPath)
select @.str=right(@.str,len(@.str)-len(substring(@.str,
1,charindex(',',@.str))))
end
--select @.para1=@.str
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
\backup_of_db\'+ @.para1+'.bak'''
--exec(@.str1)
|||Hi,
I have not tested your stored procedure but at least I removed the syntax
errors here.
create proc [dbo].[backup_proc4] @.para1 varchar(120)
As
declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
select @.str='Adventureworks,Adventureworksdw,test,model'
while(1 <> 2)
begin
select @.var1=substring(@.str,0,charindex(',',@.str))
if (@.var1='')
begin
break
end
select @.para1=@.var1
declare @.str1 varchar(200)
set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
print @.dirPath
execute master.dbo.xp_create_subdir @.dirPath
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
exec xp_create_subdir @.dirPath
--exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
exec xp_create_subdir @.dirPath
select @.str=right(@.str,len(@.str)-len(substring(@.str,
1,charindex(',',@.str))))
end
--select @.para1=@.str
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
--\backup_of_db\'+ @.para1+'.bak'''
--exec(@.str1)
Hope this helps,
Ben Nevarez
"mohit" wrote:

> On Jan 14, 1:48 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> hi,
> The query which you gave is working fine.
> But mine is not working can you just check out
>
> create proc [dbo].[backup_proc4] @.para1 varchar(120)
> As
> declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> select @.str='Adventureworks,Adventureworksdw,test,model'
> while(1 <> 2)
> begin
> select @.var1=substring(@.str,0,charindex(',',@.str))
> if (@.var1='')
> begin
> break
> end
> select @.para1=@.var1
> declare @.str1 varchar(200)
> set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
> print @.dirPath
> execute master.dbo.xp_create_subdir @.dirPath
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
> xp_create_subdir @.dirPath
> --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
> xp_create_subdir @.dirPath)
> select @.str=right(@.str,len(@.str)-len(substring(@.str,
> 1,charindex(',',@.str))))
> end
> --select @.para1=@.str
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
> \backup_of_db\'+ @.para1+'.bak'''
> --exec(@.str1)
>
|||On Jan 14, 2:30Xpm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi,
> I have not tested your stored procedure but at least I removed the syntax
> errors here.
> create proc [dbo].[backup_proc4] @.para1 varchar(120)
> As
> declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> select @.str='Adventureworks,Adventureworksdw,test,model'
> while(1 <> 2)
> begin
> select @.var1=substring(@.str,0,charindex(',',@.str))
> if (@.var1='')
> begin
> break
> end
> select @.para1=@.var1
> declare @.str1 varchar(200)
> set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
> print @.dirPath
> execute master.dbo.xp_create_subdir @.dirPath
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
> exec xp_create_subdir @.dirPath
> --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
> exec xp_create_subdir @.dirPath
> select @.str=right(@.str,len(@.str)-len(substring(@.str,
> 1,charindex(',',@.str))))
> end
> --select @.para1=@.str
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
> --\backup_of_db\'+ @.para1+'.bak'''
> --exec(@.str1)
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hi,
Thanks buddy.

problem in executing extended stored procedure

Hi ll,
When i am trying to execute extended stored procedure im getting
error as:
Msg 22048, Level 15, State 0, Line 0
Error executing extended stored procedure: Invalid Parameter
Can u guys help me outHi,
Tell us exactly which command and parameters are you using.
By the way, have you checked BOL (if applies) for the parameters that you
need to specify?
Hope this helps,
Ben Nevarez
"mohit" wrote:
> Hi ll,
> When i am trying to execute extended stored procedure im getting
> error as:
> Msg 22048, Level 15, State 0, Line 0
> Error executing extended stored procedure: Invalid Parameter
> Can u guys help me out
>|||On Jan 14, 12:18 pm, Ben Nevarez
<BenNeva...@.discussions.microsoft.com> wrote:
> Hi,
> Tell us exactly which command and parameters are you using.
> By the way, have you checked BOL (if applies) for the parameters that you
> need to specify?
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
> > Hi ll,
> > When i am trying to execute extended stored procedure im getting
> > error as:
> > Msg 22048, Level 15, State 0, Line 0
> > Error executing extended stored procedure: Invalid Parameter
> > Can u guys help me out- Hide quoted text -
> - Show quoted text -
Hi,
Question is something like this:
In procedure create folder for each database and backup of the
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.
For this i am using command as Execute xp_create_subdir and parameter
as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
help if u could tell.|||Not sure about what exactly your problem is but something like this works
declare @.pathDir nvarchar(80)
set @.pathDir = 'c:\YourDB'
execute master.dbo.xp_create_subdir @.pathDir
By the way, xp_create_subdir is used on the Maintenance Plans but it looks
like it is not documented on BOL.
Hope this helps,
Ben Nevarez
"mohit" wrote:
> On Jan 14, 12:18 pm, Ben Nevarez
> <BenNeva...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > Tell us exactly which command and parameters are you using.
> >
> > By the way, have you checked BOL (if applies) for the parameters that you
> > need to specify?
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> > "mohit" wrote:
> > > Hi ll,
> > > When i am trying to execute extended stored procedure im getting
> > > error as:
> > > Msg 22048, Level 15, State 0, Line 0
> > > Error executing extended stored procedure: Invalid Parameter
> >
> > > Can u guys help me out- Hide quoted text -
> >
> > - Show quoted text -
> Hi,
> Question is something like this:
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.
> For this i am using command as Execute xp_create_subdir and parameter
> as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
> help if u could tell.
>
>|||On Jan 14, 1:48=A0pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Not sure about what exactly your problem is but something like this works
> declare @.pathDir nvarchar(80)
> set @.pathDir =3D 'c:\YourDB'
> execute master.dbo.xp_create_subdir @.pathDir
> By the way, xp_create_subdir is used on the Maintenance Plans but it looks=
> like it is not documented on BOL.
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
> > On Jan 14, 12:18 pm, Ben Nevarez
> > <BenNeva...@.discussions.microsoft.com> wrote:
> > > Hi,
> > > Tell us exactly which command and parameters are you using.
> > > By the way, have you checked BOL (if applies) for the parameters that =you
> > > need to specify?
> > > Hope this helps,
> > > Ben Nevarez
> > > "mohit" wrote:
> > > > Hi ll,
> > > > =A0 =A0 When i am trying to execute extended stored procedure im get=ting
> > > > error as:
> > > > =A0 Msg 22048, Level 15, State 0, Line 0
> > > > Error executing extended stored procedure: Invalid Parameter
> > > > Can u guys help me out- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > =A0 =A0Question is something like this:
> > =A0 =A0In procedure create folder for each database and backup of the
> > database should get saved in that folder.
> > For e.g =A0: If your database name is 'Test' then in procedure create
> > folder 'test' and store backup of test database in test folder. Then
> > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > and store backup in that folder.
> > For this i am using command as Execute xp_create_subdir and parameter
> > as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
> > help if u could tell.- Hide quoted text -
> - Show quoted text -
hi,
The query which you gave is working fine.
But mine is not working can you just check out
create proc [dbo].[backup_proc4] @.para1 varchar(120)
As
declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
select @.str=3D'Adventureworks,Adventureworksdw,test,model'
while(1 <> 2)
begin
select @.var1=3Dsubstring(@.str,0,charindex(',',@.str))
if (@.var1=3D'')
begin
break
end
select @.para1=3D@.var1
declare @.str1 varchar(200)
set @.dirPath=3D'''D:\'+@.para1+'\'+@.para1+'.bak'''
print @.dirPath
execute master.dbo.xp_create_subdir @.dirPath
--set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ exec
xp_create_subdir @.dirPath
--exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+' '+ exec
xp_create_subdir @.dirPath)
select @.str=3Dright(@.str,len(@.str)-len(substring(@.str,
1,charindex(',',@.str))))
end
--select @.para1=3D@.str
--set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ '''D:
\backup_of_db\'+ @.para1+'.bak'''
--exec(@.str1)|||Hi,
I have not tested your stored procedure but at least I removed the syntax
errors here.
create proc [dbo].[backup_proc4] @.para1 varchar(120)
As
declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
select @.str='Adventureworks,Adventureworksdw,test,model'
while(1 <> 2)
begin
select @.var1=substring(@.str,0,charindex(',',@.str))
if (@.var1='')
begin
break
end
select @.para1=@.var1
declare @.str1 varchar(200)
set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
print @.dirPath
execute master.dbo.xp_create_subdir @.dirPath
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
exec xp_create_subdir @.dirPath
--exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
exec xp_create_subdir @.dirPath
select @.str=right(@.str,len(@.str)-len(substring(@.str,
1,charindex(',',@.str))))
end
--select @.para1=@.str
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
--\backup_of_db\'+ @.para1+'.bak'''
--exec(@.str1)
Hope this helps,
Ben Nevarez
"mohit" wrote:
> On Jan 14, 1:48 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> > Not sure about what exactly your problem is but something like this works
> >
> > declare @.pathDir nvarchar(80)
> > set @.pathDir = 'c:\YourDB'
> > execute master.dbo.xp_create_subdir @.pathDir
> >
> > By the way, xp_create_subdir is used on the Maintenance Plans but it looks
> > like it is not documented on BOL.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> > "mohit" wrote:
> > > On Jan 14, 12:18 pm, Ben Nevarez
> > > <BenNeva...@.discussions.microsoft.com> wrote:
> > > > Hi,
> >
> > > > Tell us exactly which command and parameters are you using.
> >
> > > > By the way, have you checked BOL (if applies) for the parameters that you
> > > > need to specify?
> >
> > > > Hope this helps,
> >
> > > > Ben Nevarez
> >
> > > > "mohit" wrote:
> > > > > Hi ll,
> > > > > When i am trying to execute extended stored procedure im getting
> > > > > error as:
> > > > > Msg 22048, Level 15, State 0, Line 0
> > > > > Error executing extended stored procedure: Invalid Parameter
> >
> > > > > Can u guys help me out- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Hi,
> > > Question is something like this:
> >
> > > In procedure create folder for each database and backup of the
> > > database should get saved in that folder.
> >
> > > For e.g : If your database name is 'Test' then in procedure create
> > > folder 'test' and store backup of test database in test folder. Then
> > > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > > and store backup in that folder.
> >
> > > For this i am using command as Execute xp_create_subdir and parameter
> > > as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
> > > help if u could tell.- Hide quoted text -
> >
> > - Show quoted text -
> hi,
> The query which you gave is working fine.
> But mine is not working can you just check out
>
> create proc [dbo].[backup_proc4] @.para1 varchar(120)
> As
> declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> select @.str='Adventureworks,Adventureworksdw,test,model'
> while(1 <> 2)
> begin
> select @.var1=substring(@.str,0,charindex(',',@.str))
> if (@.var1='')
> begin
> break
> end
> select @.para1=@.var1
> declare @.str1 varchar(200)
> set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
> print @.dirPath
> execute master.dbo.xp_create_subdir @.dirPath
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
> xp_create_subdir @.dirPath
> --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
> xp_create_subdir @.dirPath)
> select @.str=right(@.str,len(@.str)-len(substring(@.str,
> 1,charindex(',',@.str))))
> end
> --select @.para1=@.str
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
> \backup_of_db\'+ @.para1+'.bak'''
> --exec(@.str1)
>|||On Jan 14, 2:30=A0pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi,
> I have not tested your stored procedure but at least I removed the syntax
> errors here.
> create proc [dbo].[backup_proc4] @.para1 varchar(120)
> As
> declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> select @.str=3D'Adventureworks,Adventureworksdw,test,model'
> while(1 <> 2)
> begin
> select @.var1=3Dsubstring(@.str,0,charindex(',',@.str))
> if (@.var1=3D'')
> begin
> break
> end
> select @.para1=3D@.var1
> declare @.str1 varchar(200)
> set @.dirPath=3D'''D:\'+@.para1+'\'+@.para1+'.bak'''
> print @.dirPath
> execute master.dbo.xp_create_subdir @.dirPath
> --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ exec
> exec xp_create_subdir @.dirPath
> --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+' '+ exec
> exec xp_create_subdir @.dirPath
> select @.str=3Dright(@.str,len(@.str)-len(substring(@.str,
> 1,charindex(',',@.str))))
> end
> --select @.para1=3D@.str
> --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ '''D:=
> --\backup_of_db\'+ @.para1+'.bak'''
> --exec(@.str1)
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
> > On Jan 14, 1:48 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> > wrote:
> > > Not sure about what exactly your problem is but something like this wo=rks
> > > declare @.pathDir nvarchar(80)
> > > set @.pathDir =3D 'c:\YourDB'
> > > execute master.dbo.xp_create_subdir @.pathDir
> > > By the way, xp_create_subdir is used on the Maintenance Plans but it l=ooks
> > > like it is not documented on BOL.
> > > Hope this helps,
> > > Ben Nevarez
> > > "mohit" wrote:
> > > > On Jan 14, 12:18 pm, Ben Nevarez
> > > > <BenNeva...@.discussions.microsoft.com> wrote:
> > > > > Hi,
> > > > > Tell us exactly which command and parameters are you using.
> > > > > By the way, have you checked BOL (if applies) for the parameters t=hat you
> > > > > need to specify?
> > > > > Hope this helps,
> > > > > Ben Nevarez
> > > > > "mohit" wrote:
> > > > > > Hi ll,
> > > > > > =A0 =A0 When i am trying to execute extended stored procedure im= getting
> > > > > > error as:
> > > > > > =A0 Msg 22048, Level 15, State 0, Line 0
> > > > > > Error executing extended stored procedure: Invalid Parameter
> > > > > > Can u guys help me out- Hide quoted text -
> > > > > - Show quoted text -
> > > > Hi,
> > > > =A0 =A0Question is something like this:
> > > > =A0 =A0In procedure create folder for each database and backup of th=e
> > > > database should get saved in that folder.
> > > > For e.g =A0: If your database name is 'Test' then in procedure creat=e
> > > > folder 'test' and store backup of test database in test folder. Then=
> > > > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > > > and store backup in that folder.
> > > > For this i am using command as Execute xp_create_subdir and paramete=r
> > > > as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great=
> > > > help if u could tell.- Hide quoted text -
> > > - Show quoted text -
> > hi,
> > =A0 =A0The query which you gave is working fine.
> > =A0 =A0But mine is not working can you just check out
> > create proc [dbo].[backup_proc4] @.para1 varchar(120)
> > As
> > declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> > select @.str=3D'Adventureworks,Adventureworksdw,test,model'
> > while(1 <> 2)
> > begin
> > select @.var1=3Dsubstring(@.str,0,charindex(',',@.str))
> > if (@.var1=3D'')
> > begin
> > break
> > end
> > select @.para1=3D@.var1
> > declare @.str1 varchar(200)
> > set @.dirPath=3D'''D:\'+@.para1+'\'+@.para1+'.bak'''
> > print @.dirPath
> > execute master.dbo.xp_create_subdir @.dirPath
> > --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ exe=c
> > xp_create_subdir @.dirPath
> > --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+' '+ exec
> > xp_create_subdir @.dirPath)
> > select @.str=3Dright(@.str,len(@.str)-len(substring(@.str,
> > 1,charindex(',',@.str))))
> > end
> > --select @.para1=3D@.str
> > --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ '''=D:
> > \backup_of_db\'+ @.para1+'.bak'''
> > --exec(@.str1)- Hide quoted text -
> - Show quoted text -
Hi,
Thanks buddy.

Problem in Displaying NTEXT field from database?

Hello,

I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.

I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.

any ideas really appriciated.

Thanks

Ram

text and ntext fields should always be either the only data returned or the last item in a select statement.

For example:

SELECT ntextField FROM myTable WHERE something = true

or

SELECT myFirstField, myOtherField, ntextField FROM myTable WHERE something = true

This is a limitation of MSSQL server and (some versions) of MySQL. If you are using MSSQL Server 2005, I would suggest changing the database column to a nvarchar(MAX) as this will store the same number of characters as an ntext column. (I believe they intended to remove text and ntext from the next release of MSSQL Server in favor of varchar(MAX) and nvarchar(MAX), but don't quote me on that.)

|||

I am using SQLserver 2000, What will be the possible solutions for this problem.

Thanks

|||

You will have to select each ntext field individually. So you will need 7 select statements each time.

However, I would recommend changing the database to use a different type of column. If you know the data will never be larger than 8K you can still use nvarchar(8000) on SQL Server 2000. Or you could combine some of the columns and then use delimiters so that you have one huge column with something like ||| between entries (but this may cause other problems.)

In general text, ntext and blob fields should be used as rarely as possible, it is usually easier to put that much data into a file and then store the files name and location in SQL Server.

Let me know if this doesn't really answer your question.

|||

Hi,

Its working for me now..What I did is ...

I have got all ntext fields from the recordset first into some varialbles before acesing non ntext fields and mapped to the form controls.

thats it.

thanks

Problem executing the SSIS package using C# exe

Hi,

I am using C# exe to run the SSIS packages. I have placed the SSIS packages in a folder. So everytime i try to execute the SSIS through the exe i get the error "

An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user '[UserName]'.".

While creating the SSIS, i have saved the password. But when i open the SSIS to check I just see that the password field is blank.

Is there any other property which i need to set to keep the password saved forever?

Regards,

Search the online help for ProtectionLevel. The various settings for this package property will allow you to save the password in the package. Or you could use a configuration to store the password.

Problem executing the SSIS package using C# exe

Hi,

I am using C# exe to run the SSIS packages. I have placed the SSIS packages in a folder. So everytime i try to execute the SSIS through the exe i get the error "

An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user '[UserName]'.".

While creating the SSIS, i have saved the password. But when i open the SSIS to check I just see that the password field is blank.

Is there any other property which i need to set to keep the password saved forever?

Regards,

Search the online help for ProtectionLevel. The various settings for this package property will allow you to save the password in the package. Or you could use a configuration to store the password.

Problem Executing Stored Procedures

We have an application that uses SQL-DMO to get a directory/file list for
the server by calling extended stored procedures, xp_availablemedia and
xp_cmdshell.
The app can successfully navigate the directories on systems running MSDE on
Window 2000 Professional, however, it fails to do so on systems running
Windows XP Professional.
There are no differences in the environment the application runs in other
than the version of Windows. Some of the characteristics of the
installation are: The application and MSDE are installed using the same
scripts on both versions of Windows. The systems where the app is installed
are running as part of a workgroup rather than a domain. We have run
svrnetcn.exe and verified that named pipes and TCP/IP are both enabled. To
confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
the command line. Also, we can successfully execute the stored procedures
using SQL statements so it appears that the problems are related to SQLDMO.
Is there something additional or different that needs to be done on XP than
on 2000? Does anyone have any suggestions about how to solve this problem?
Thanks,
Tom
hi Tom,
"news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
I do currently use SQL-DMO with success both on Win2k, WinXP pro and Win2003
server std...
I never had the need to manually register this somponent when installing
MSDE, and both procedures you are mentioning are run with success...
just the usual caveat... does the account running SQL Server and SQL Server
Agent have the right privileges?
did you try some simple SQL-DMO code to test it?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||My guess is that it has nothing to do with SQL-DMO, but is instead a
permissions issue. xp_cmdshell has some rather strict permission issues when
being invoked (its all in the BOL). By default, XP has a tighter security
setup than Win2k, so this may be part of the root of your problem. The fact
that you are running in a Workgroup network may also be a factor, since its
security model is much different than a domain network. Have you tried
running the profiler against the system when running your application
against MSDE on XP?
Jim
"news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
> Thanks,
> Tom
>
|||Andrea,
Both SQL Server and Agent are running under the Local System account so they
should have enough privileges.
The same application that is having problems with SQL-DMO also uses SQL-DMO
to do backups and restores and doesn't have any problems.
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:c43nsp$2cuegu$1@.ID-207518.news.uni-berlin.de...
> hi Tom,
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
> I do currently use SQL-DMO with success both on Win2k, WinXP pro and
Win2003
> server std...
> I never had the need to manually register this somponent when installing
> MSDE, and both procedures you are mentioning are run with success...
> just the usual caveat... does the account running SQL Server and SQL
Server
> Agent have the right privileges?
> did you try some simple SQL-DMO code to test it?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Jim,
Unfortunately, we have no control over the environment where the application
is being run, but, it is usually installed on stand-alone systems or
pier-to-pier networks that are not part of a domain.
Since we can execute the extended stored procedures in SQL commands, we are
in the process of converting the application so it doesn't use SQL-DMO
except for the backup and restore.
Tom
"J Young" <thorium48@.hotmail.com> wrote in message
news:OTbnG4OFEHA.2052@.TK2MSFTNGP11.phx.gbl...
> My guess is that it has nothing to do with SQL-DMO, but is instead a
> permissions issue. xp_cmdshell has some rather strict permission issues
when
> being invoked (its all in the BOL). By default, XP has a tighter security
> setup than Win2k, so this may be part of the root of your problem. The
fact
> that you are running in a Workgroup network may also be a factor, since
its
> security model is much different than a domain network. Have you tried
> running the profiler against the system when running your application
> against MSDE on XP?
> Jim
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
>

Problem executing stored procedure to update all rows of a table

Hi,
I got a stored procedure which interacts with the pubs database. The
following is the code for the stored procedure:
CREATE PROCEDURE usp_UpdatedPrices_para
@.Type char(12)= '%',
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type
Now, if I execute the above stored procedure(from QA) in the following manne
r:
exec usp_UpdatedPrices_para 'Business' , 1
all four rows of the price field of titles table corresponding to the
'Business' type gets updated.
Now I need to execute the stored procedure so that all the rows get the
updates for all the various types. Since I have a default value for the type
as '%', I am
executing the stored procedure as
exec usp_UpdatedPrices_para , 1
to which I am getting a syntax error as the following:
Line 1: Incorrect syntax near ','
If anybody could suggest me something about the error, it would be helpful.
Thanks.Hello, Jack
You can call the procedure like this:
EXEC usp_UpdatedPrices_para DEFAULT, 1
or:
EXEC usp_UpdatedPrices_para @.Percent=1
However, this will not get you the expected result, because your
condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
the default for the @.Type parameter (leave it to be NULL) and use a
condition like this:
WHERE Type=@.Type OR @.Type IS NULL
Razvan

Problem executing stored procedure to update all rows of a tab

Thanks Razvan for your help. As per your advise I have changed the stored
procedure as follows:
CREATE PROCEDURE usp_UpdatedPrices_para1
@.Type char(12),
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
Now I am trying to execute the stored procedure as:
exec usp_UpdatedPrices_para1 @.percent = 10
To the above command I am getting the following error message:
Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was not
supplied.
Do you have any further ideas for resolution. Thanks.
"Razvan Socol" wrote:

> Hello, Jack
> You can call the procedure like this:
> EXEC usp_UpdatedPrices_para DEFAULT, 1
> or:
> EXEC usp_UpdatedPrices_para @.Percent=1
> However, this will not get you the expected result, because your
> condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
> the default for the @.Type parameter (leave it to be NULL) and use a
> condition like this:
> WHERE Type=@.Type OR @.Type IS NULL
> Razvan
>When passing parameters to a stored procedure using the @.variable=value
syntax, you must explicity set a valure for all input parameters that don't
have a default value assigned in the procedure defenition; even if you want
it to be NULL.
Try
EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
Alternatively, you could define the procedure to use a default of NULL for
the variable @.Type
CREATE PROCEDURE usp_UpdatedPrices_para2
@.Type char(12)=NULL,
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
And then execute as
EXEC sp_UpdatedPrices_para2 @.Percent=1
"Jack" wrote:
> Thanks Razvan for your help. As per your advise I have changed the stored
> procedure as follows:
> CREATE PROCEDURE usp_UpdatedPrices_para1
> @.Type char(12),
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> Now I am trying to execute the stored procedure as:
> exec usp_UpdatedPrices_para1 @.percent = 10
> To the above command I am getting the following error message:
> Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was n
ot
> supplied.
> Do you have any further ideas for resolution. Thanks.
> "Razvan Socol" wrote:
>|||Thanks a lot Mark for your generous help. I tried executing both the
procedures and both worked great. Best Regards.
"Mark Williams" wrote:
> When passing parameters to a stored procedure using the @.variable=value
> syntax, you must explicity set a valure for all input parameters that don'
t
> have a default value assigned in the procedure defenition; even if you wan
t
> it to be NULL.
> Try
> EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
> Alternatively, you could define the procedure to use a default of NULL for
> the variable @.Type
> CREATE PROCEDURE usp_UpdatedPrices_para2
> @.Type char(12)=NULL,
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> And then execute as
> EXEC sp_UpdatedPrices_para2 @.Percent=1
>
> "Jack" wrote:
>

Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comIf the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you cant do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
Message posted via http://www.droptable.com|||This link describes using parameterized queries.
1191519.html" target="_blank">http://www.experts-exchange.com/Pro...>
1191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.SQ
droptable.com...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.droptable.com

Problem executing stored procedure from asp

We have an ASP page which executes a stored procedure this works fine on
windows 2000 server/SQL 2000 server. We are trying to use the same page
locally on WinXP SP2/MSDE 2000A and we seem to be experiencing a strange
problem. When run locally the asp complains that it cannot find the stored
procedure. We have run profiler on both Win2000 and locally and noticed that
the commands which are executed are very different.See below
Win 2000 Server/SQL Server 2000
RPC:Completed exec spShowPhysicalBlockSeats 'TEST', 'E16' Microsoft(R)
Windows (R) 2000 Operating System sa 0 2104 0 173 1304 71 2004-11-15
11:37:49.787
WinXP/MSDE2000A
SQL:BatchCompleted spShowPhysicalBlockSeats Microsoft Windows Operating
System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
RPC:Completed exec spShowPhysicalBlockSeats Microsoft Windows Operating
System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
SQL:BatchCompleted select * from spShowPhysicalBlockSeats Microsoft
Windows Operating System sa 0 4 0 16 3840 55 2004-11-15 11:36:25.927
The asp page istelf is unchanged apart from the connection string, as you
can imagine we are a little confused about why the execution is so
different, and why the WinXP version seems to execute it 3 times in slightly
different ways.
Any help or advice would be greatly received.
Cheers
Andy
Well I can account for two differences: (a) on Windows 2000, you didn't
capture the SQL:BatchCompleted event in profiler, and (b) you called the
stored procedure with different parameters.
Can you show us the code for the stored procedure, the ASP Code that is
calling it, and whether you see any major differences aside from what your
*different* traces show using *different* SP calls?
http://www.aspfaq.com/
(Reverse address to reply.)
"Andy Kerner" <andrewkerner@.hotmail.com> wrote in message
news:#bSUxhwyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have an ASP page which executes a stored procedure this works fine on
> windows 2000 server/SQL 2000 server. We are trying to use the same page
> locally on WinXP SP2/MSDE 2000A and we seem to be experiencing a strange
> problem. When run locally the asp complains that it cannot find the stored
> procedure. We have run profiler on both Win2000 and locally and noticed
that
> the commands which are executed are very different.See below
> Win 2000 Server/SQL Server 2000
> --
> RPC:Completed exec spShowPhysicalBlockSeats 'TEST', 'E16' Microsoft(R)
> Windows (R) 2000 Operating System sa 0 2104 0 173 1304 71 2004-11-15
> 11:37:49.787
> WinXP/MSDE2000A
> --
> SQL:BatchCompleted spShowPhysicalBlockSeats Microsoft Windows Operating
> System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
> RPC:Completed exec spShowPhysicalBlockSeats Microsoft Windows Operating
> System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
> SQL:BatchCompleted select * from spShowPhysicalBlockSeats Microsoft
> Windows Operating System sa 0 4 0 16 3840 55 2004-11-15 11:36:25.927
> The asp page istelf is unchanged apart from the connection string, as you
> can imagine we are a little confused about why the execution is so
> different, and why the WinXP version seems to execute it 3 times in
slightly
> different ways.
> Any help or advice would be greatly received.
> Cheers
> Andy
>

Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.com
If the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you cant do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.droptable.co m...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.droptable.co m...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
Message posted via http://www.droptable.com
|||This link describes using parameterized queries.
http://www.experts-exchange.com/Prog..._21191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.droptable.co m...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.droptable.com

Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comIf the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you can´t do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.SQLMonster.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.SQLMonster.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
--
Message posted via http://www.sqlmonster.com|||This link describes using parameterized queries.
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.SQLMonster.com...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.sqlmonster.com

Problem executing procedure and how can I use their results in a select statement

I have this Stored Procedure that walk through a table that stores hierarchical data and reorganize the output, so the resultset will be ordered by this hierarchy.

The table structure is (fieldnames in english between parentheses for better comprehension):

CD_CATEGORIA (CD_CATEGORY)
DS_CATEGORIA (DS_CATEGORY)
CD_CATEGORIAMAE (CD_MOTHERCATEGORY)

Here is the Stored Procedure code:

CREATE PROCEDURE [dbo].[sp_RetornaCategorias]

-- Add the parameters for the stored procedure here

@.ID int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @.TabelaSaida table(

cd_categoria int,

ds_categoria varchar(70),

nr_nivel int);

declare @.i int

select @.i = 0

-- keep going until no more rows added

while @.@.rowcount > 0

begin

select @.i = @.i + 1

insert @.TabelaSaida

-- Get all children of previous level

select Categorias.cd_categoria, Categorias.ds_categoria, @.i + 1

from Categorias, @.TabelaSaida AS TblSaida

where nr_nivel = @.i

and Categorias.cd_categoriamae = TblSaida.cd_categoria

end

-- Saída de dados

-- output with hierarchy formatted

select space((nr_nivel-1)*4) + ds_categoria

from @.TabelaSaida

order by nr_nivel

END

But when I try to execute this Stored Procedure, it runs but nothing is returned.

I'm using this code to execute it:

EXEC [dbo].[sp_RetornaCategorias]

@.ID = 1

Are there anything wrong with it? How can I fix this?

And how can I call a Stored Procedure and get its resultset from a SELECT statement?

Hi Juliano,

Your SP wont actually return anything unless you declare a Variable as OUTPUT.

Your SP's resultset is from the select statement.

There is great MSDN documentation on SP's here; http://msdn2.microsoft.com/en-us/netframework/aa479373.aspx

With regards to fixing it, im not entirely sure its broken yet.

|||

It looks like your insert into the tablevariable is based in a join against that same tablevariable, but when you start out, it's newly created and thus empty..
So, the insert would then yield 0 rows, and the loop will break.

Try to run the SQL statements in a query window, then you can see what happens in each step.

/Kenneth

|||

ur procedure and calling seems to be alright...just check the data in the tyables ur refering...and is there actually nething to be returned.......basically run the select query seperately and check..

this 1...does this gives ne values ?

select Categorias.cd_categoria, Categorias.ds_categoria

from Categorias, @.TabelaSaida AS TblSaida

and Categorias.cd_categoriamae = TblSaida.cd_categoria

|||

I don't see how this could possibly return any rows, since @.TableSaida that is used in the join is newly declared and created, and thus is also empty.

/Kenneth

Problem executing a stored proc, please help

Hi All,
I have stored proc that processes about 60,000 rows using a cursor. When I
call the SP from Query Analyzer, I get the following error message after
processing about 12,000 records :
Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
12614 records
What can i do to make this SP run sucessfully ? I even tried using a table
variable instead of a cursor, but got the same result. Please suggest .
THE output from SP_CONFIG on database server is
Option
config_value
------
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
Cross DB Ownership Chaining 0
cursor threshold -1
default full-text language 1033
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 255
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
open objects 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 0
scan for startup procs 0
set working set size 0
show advanced options 1
two digit year cutoff 2049
user connections 0
user options 0
Hi
It would help if you posted DDL and example data such as
http://www.aspfaq.com/etiquettXXe.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.XXhtm#inserts
Connection broken implies a network failure/disconnection, possibly a
timeout but you do not indicate how long this process takes.
Check your SQL server version and service pack level, you may also want to
check MDAC version and consistancy along with the SQL Server log and event
log to see if there is any more information that may help.
Other posts on this http://tinyurl.com/4ebus may be helpful.
John
"rajeshlh" <rajeshlh@.discussions.microsoft.com> wrote in message
news:F1F399D6-BB17-4FC9-BF10-4F7D3A31F7B1@.microsoft.com...
> Hi All,
> I have stored proc that processes about 60,000 rows using a cursor. When I
> call the SP from Query Analyzer, I get the following error message after
> processing about 12,000 records :
>
> Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
>
> Connection Broken
>
>
> 12614 records
>
> What can i do to make this SP run sucessfully ? I even tried using a table
> variable instead of a cursor, but got the same result. Please suggest .
>
> THE output from SP_CONFIG on database server is
> Option
> config_value
> ------
> affinity mask 0
> allow updates 0
> awe enabled 0
> c2 audit mode 0
> cost threshold for parallelism 5
> Cross DB Ownership Chaining 0
> cursor threshold -1
> default full-text language 1033
> default language 0
> fill factor (%) 0
> index create memory (KB) 0
> lightweight pooling 0
> locks 0
> max degree of parallelism 0
> max server memory (MB) 2147483647
> max text repl size (B) 65536
> max worker threads 255
> media retention 0
> min memory per query (KB) 1024
> min server memory (MB) 0
> nested triggers 1
> network packet size (B) 4096
> open objects 0
> priority boost 0
> query governor cost limit 0
> query wait (s) -1
> recovery interval (min) 0
> remote access 1
> remote login timeout (s) 20
> remote proc trans 0
> remote query timeout (s) 0
> scan for startup procs 0
> set working set size 0
> show advanced options 1
> two digit year cutoff 2049
> user connections 0
> user options 0
>
>
>

Problem executing a stored proc, please help

Hi All,
I have stored proc that processes about 60,000 rows using a cursor. When I
call the SP from Query Analyzer, I get the following error message after
processing about 12,000 records :
Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
12614 records
What can i do to make this SP run sucessfully ? I even tried using a table
variable instead of a cursor, but got the same result. Please suggest .
THE output from SP_CONFIG on database server is
Option
config_value
------
affinity mask 0
allow updates 0
awe enabled 0
c2 audit mode 0
cost threshold for parallelism 5
Cross DB Ownership Chaining 0
cursor threshold -1
default full-text language 1033
default language 0
fill factor (%) 0
index create memory (KB) 0
lightweight pooling 0
locks 0
max degree of parallelism 0
max server memory (MB) 2147483647
max text repl size (B) 65536
max worker threads 255
media retention 0
min memory per query (KB) 1024
min server memory (MB) 0
nested triggers 1
network packet size (B) 4096
open objects 0
priority boost 0
query governor cost limit 0
query wait (s) -1
recovery interval (min) 0
remote access 1
remote login timeout (s) 20
remote proc trans 0
remote query timeout (s) 0
scan for startup procs 0
set working set size 0
show advanced options 1
two digit year cutoff 2049
user connections 0
user options 0Hi
It would help if you posted DDL and example data such as
http://www.aspfaq.com/etiquett­­e.asp?id=5006 and
example data as insert statements
http://vyaskn.tripod.com/code.­­htm#inserts
Connection broken implies a network failure/disconnection, possibly a
timeout but you do not indicate how long this process takes.
Check your SQL server version and service pack level, you may also want to
check MDAC version and consistancy along with the SQL Server log and event
log to see if there is any more information that may help.
Other posts on this http://tinyurl.com/4ebus may be helpful.
John
"rajeshlh" <rajeshlh@.discussions.microsoft.com> wrote in message
news:F1F399D6-BB17-4FC9-BF10-4F7D3A31F7B1@.microsoft.com...
> Hi All,
> I have stored proc that processes about 60,000 rows using a cursor. When I
> call the SP from Query Analyzer, I get the following error message after
> processing about 12,000 records :
>
> Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (InvalidParam()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
>
> Connection Broken
>
>
> 12614 records
>
> What can i do to make this SP run sucessfully ? I even tried using a table
> variable instead of a cursor, but got the same result. Please suggest .
>
> THE output from SP_CONFIG on database server is
> Option
> config_value
> ------
> affinity mask 0
> allow updates 0
> awe enabled 0
> c2 audit mode 0
> cost threshold for parallelism 5
> Cross DB Ownership Chaining 0
> cursor threshold -1
> default full-text language 1033
> default language 0
> fill factor (%) 0
> index create memory (KB) 0
> lightweight pooling 0
> locks 0
> max degree of parallelism 0
> max server memory (MB) 2147483647
> max text repl size (B) 65536
> max worker threads 255
> media retention 0
> min memory per query (KB) 1024
> min server memory (MB) 0
> nested triggers 1
> network packet size (B) 4096
> open objects 0
> priority boost 0
> query governor cost limit 0
> query wait (s) -1
> recovery interval (min) 0
> remote access 1
> remote login timeout (s) 20
> remote proc trans 0
> remote query timeout (s) 0
> scan for startup procs 0
> set working set size 0
> show advanced options 1
> two digit year cutoff 2049
> user connections 0
> user options 0
>
>
>