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 getting security for configuration database

Below is what I have put into an execute sql task container. When I try to run it I have output open and have posted the output. The code runs fine when I have the configuration database set up to only check one server. However, when I add anymore to it, the package will get the security from the first db and then error out. Below is the error output and script. Any help with my problem is greatly appreciated.

**********Begin script*******************

declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go

****************End Script***********************

****************Begin Error Output********************

[Execute SQL Task] Error: Executing the query "declare @.dbname varchar(200) declare @.mSql1 varchar(8000) DECLARE DBName_Cursor CURSOR FOR select name from master.dbo.sysdatabases where name not in ('mssecurity','tempdb') Order by name OPEN DBName_Cursor FETCH NEXT FROM DBName_Cursor INTO @.dbname WHILE @.@.FETCH_STATUS = 0 BEGIN Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin, db_securityadmin, db_ddladmin, db_datareader, db_datawriter, db_denydatareader, db_denydatawriter ) SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner, Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin , Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin, Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin, Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader, Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter, Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader, Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter from ( select b.name as USERName, c.name as RoleName from ' + @.dbName+'.dbo.sysmembers a '+char(13)+ ' join '+ @.dbName+'.dbo.sysusers b '+char(13)+ ' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c on a.groupuid = c.uid )s Group by USERName order by UserName' --Print @.mSql1 Execute (@.mSql1) FETCH NEXT FROM DBName_Cursor INTO @.dbname END CLOSE DBName_Cursor DEALLOCATE DBName_Cursor " failed with the following error: "Line 15: Incorrect syntax near '-'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

**************End Error Output**************

Not knowing your database names, I'd suspect that one or more of them have names that need to be bracketed in a SQL statement. The error indicates that one might have a hyphen in the name? Try putting brackets around the @.dbname variable. Ex: "from [' + @.dbName+'].dbo.sysmembers"|||I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.|||

kschlap wrote:

I don't get why the SSIS package would work for one database then. I will try what you suggest though. I am attempting to spearhead the SSIS learning in my department and learning has been pretty slow.

What you have would work fine for a database named FooBar, but it would fail with the error you posted if the database were named Foo-Bar.
|||

I"m sorry, I made a mistake in my last post. I meant to say it worked for one server, but won't work for any other server.

-Kyle

|||

I think I have figured out one of the problems. I need to change the OLE DB Connection Manager. However, it won't let me do this and hit ok because it says that the table in my FROM clause is not in the database. Well of course its not on the database because the table gets built dynamically and the servers are gathered dynamically. How can I change this?

-Kyle

|||SSIS tries to validate the tasks when you are editing them; so I guess the table have to exists while you are editing the package. At run time though you can set DelayValidation to true; so the package does not fail validation.|||

The problem with this is that the connection I am trying to use is generated dynamically too. Thus, there are no servers until run time that I could add the table to to get around this error.

-Kyle

|||I don't get that. Does this mean you don't have a development environment where you can create oll required stuff? remebember this is just for validation purposes.|||

No, I have the proper development environment. I just get an error that says that the table doesn't exist whenever I try to make a change to the OLE DB connection. Here is the message I sent to the template creator yesterday.

I am having trouble with changing an OLE DB source. The package is set to not validate until run time, but currently it won’t let me change one of the settings. I keep getting an error that the table it is referring to is not valid. However, I know that it isn’t created because it gets created at run-time. Is there a way to change OLE DB sources? As always any help is appreciated.

|||

I fixed the problem. The package needed me to make an advanced edit instead of a normal one? Weird, but I don't care why it worked just because it worked. If anybody else is building a sql dba configuration database, mine is from a template by sqlmag, please PM me and we can work together on them.

-Kyle

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

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 a Oracle Procedure using Execute Sql Task

Hi All,

I am trying to execute a Oracle Procedure through Execute Sql Task but it keeps throwing back an error ORA:00900. Is it possible to execute the procedure from this object? Do I need to use the script task to execute this. If yes then how?

Any help would be appreciated.

Thanks in advance.

Amol

hi, I did some stuff related that from a Sql Task but from Sql Server 2000 (dts), an Oracle function:

DECLARE

RetVal NUMBER;

begin

ACTUALIZAMARCAEMPLEADO;

end;

|||

Hi Amol,

Could you provide a bit more info:

1. What is the SQL statement you are using to execute the stored procedure?

2. What type of connection are you using? (ADO, OLE DB, ODBC)

Thanks,

~Matt

Problem during the process database

This is the error when i execute the code for the association algorithem. "Errors in the high-level relational engine. The data source view does not contain a definition for the 'receiptid' column in the 'receiptdesc' table or view.".

Dim ds As New RelationalDataSource("miningas", "miningas")

ds.ConnectionString = "Provider=SQLNCLI;Data Source=localhost;" & _

"Initial Catalog=miningas;Integrated Security=SSPI"

db.DataSources.Add(ds)

' Create connection to datasource to extract schema to dataset

Dim dset As New DataSet()

Dim cn As New SqlConnection("Data Source=localhost;" & _

"Initial Catalog=supermarket;Integrated Security=True")

' Create the receiptdesc data adapter

Dim daProduct As New SqlDataAdapter("SELECT * FROM dbo.receipt", cn)

daProduct.FillSchema(dset, SchemaType.Mapped, "receipt")

' Create the receipt data adapter

Dim dareceipt As New SqlDataAdapter("Select * from dbo.receiptdesc", cn)

dareceipt.FillSchema(dset, SchemaType.Mapped, "receiptdesc")

Dim drreceipt_desc As New DataRelation("Newreceipt_desc", _

dset.Tables("receipt").Columns("receiptid"), _

dset.Tables("receiptdesc").Columns("receiptid"))

dset.Relations.Add(drreceipt_desc)

' Create the dsv, add the dataset, and add to the database

Dim dsv As New DataSourceView("miningas", "miningas")

dsv.DataSourceID = "miningas"

dsv.Schema = dset.Clone()

db.DataSourceViews.Add(dsv)

' Update the database to create the objects on the server.

db.Update(UpdateOptions.ExpandFull)

End Sub

It seems like your column is not there after you create dsv. Can you double check? Otherwise, I have the sample code below to create dsv. Please check the difference.

RelationalDataSourceView dsv = new RelationalDataSourceView("SampleDSV", "SampleDSV");

OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False");

try

{

connection.Open();

//********** Fill Customers table in schema ************

OleDbDataAdapter adapter1 = new OleDbDataAdapter ("Select * from [dbo].[Customers] where 1=0", connection);

DataTable[] dataTables = adapter1.FillSchema(dsv.Schema, SchemaType.Mapped, "Customers");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Customers");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Customers");

}

//********** Fill Orders table in schema ****************

OleDbDataAdapter adapter2 = new OleDbDataAdapter ("Select * from [dbo].[Orders] where 1=0", connection);

dataTables = adapter2.FillSchema(dsv.Schema, SchemaType.Mapped, "Orders");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Orders");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Orders");

}

//********** Create relationship ****************

DataColumn parentColumn = dsv.Schema.Tables["Customers"].Columns["CustomerID"];

DataColumn childColumn = dsv.Schema.Tables["Orders"].Columns["CustomerID"];

dsv.Schema.Relations.Add("FK_Orders_Customers", parentColumn, childColumn, true);

}

finally

{

connection.Close();

}

|||Sorry, but the code i still cannot solve the problem. Can u able to give more support on this section. I very urgent need it. Thx|||

Couple things I notice in your code. the connection string in datasource is different from the connection string you get schema. Also, you don't need to clone the dataset before setting to the dsv.schema. You can just set it directly.

I used your code on new AS database and I have no problem. However, I used same connection string (at least same initial catalog). I am not sure if that makes difference.

BTW, it is better to use my code because you do need to set some extended properties.

Problem during the process database

This is the error when i execute the code for the association algorithem. "Errors in the high-level relational engine. The data source view does not contain a definition for the 'receiptid' column in the 'receiptdesc' table or view.".

Dim ds As New RelationalDataSource("miningas", "miningas")

ds.ConnectionString = "Provider=SQLNCLI;Data Source=localhost;" & _

"Initial Catalog=miningas;Integrated Security=SSPI"

db.DataSources.Add(ds)

' Create connection to datasource to extract schema to dataset

Dim dset As New DataSet()

Dim cn As New SqlConnection("Data Source=localhost;" & _

"Initial Catalog=supermarket;Integrated Security=True")

' Create the receiptdesc data adapter

Dim daProduct As New SqlDataAdapter("SELECT * FROM dbo.receipt", cn)

daProduct.FillSchema(dset, SchemaType.Mapped, "receipt")

' Create the receipt data adapter

Dim dareceipt As New SqlDataAdapter("Select * from dbo.receiptdesc", cn)

dareceipt.FillSchema(dset, SchemaType.Mapped, "receiptdesc")

Dim drreceipt_desc As New DataRelation("Newreceipt_desc", _

dset.Tables("receipt").Columns("receiptid"), _

dset.Tables("receiptdesc").Columns("receiptid"))

dset.Relations.Add(drreceipt_desc)

' Create the dsv, add the dataset, and add to the database

Dim dsv As New DataSourceView("miningas", "miningas")

dsv.DataSourceID = "miningas"

dsv.Schema = dset.Clone()

db.DataSourceViews.Add(dsv)

' Update the database to create the objects on the server.

db.Update(UpdateOptions.ExpandFull)

End Sub

It seems like your column is not there after you create dsv. Can you double check? Otherwise, I have the sample code below to create dsv. Please check the difference.

RelationalDataSourceView dsv = new RelationalDataSourceView("SampleDSV", "SampleDSV");

OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB.1;Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False");

try

{

connection.Open();

//********** Fill Customers table in schema ************

OleDbDataAdapter adapter1 = new OleDbDataAdapter ("Select * from [dbo].[Customers] where 1=0", connection);

DataTable[] dataTables = adapter1.FillSchema(dsv.Schema, SchemaType.Mapped, "Customers");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Customers");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Customers");

}

//********** Fill Orders table in schema ****************

OleDbDataAdapter adapter2 = new OleDbDataAdapter ("Select * from [dbo].[Orders] where 1=0", connection);

dataTables = adapter2.FillSchema(dsv.Schema, SchemaType.Mapped, "Orders");

if (dataTables.Length > 0)

{

DataTable dataTable = dataTables[0];

dataTable.ExtendedProperties.Add( “TableType”, “Table”);

dataTable.ExtendedProperties.Add( “DbSchemaName”, "dbo");

dataTable.ExtendedProperties.Add( “DbTableName”, "Orders");

dataTable.ExtendedProperties.Add( “FriendlyName”, "Orders");

}

//********** Create relationship ****************

DataColumn parentColumn = dsv.Schema.Tables["Customers"].Columns["CustomerID"];

DataColumn childColumn = dsv.Schema.Tables["Orders"].Columns["CustomerID"];

dsv.Schema.Relations.Add("FK_Orders_Customers", parentColumn, childColumn, true);

}

finally

{

connection.Close();

}

|||Sorry, but the code i still cannot solve the problem. Can u able to give more support on this section. I very urgent need it. Thx|||

Couple things I notice in your code. the connection string in datasource is different from the connection string you get schema. Also, you don't need to clone the dataset before setting to the dsv.schema. You can just set it directly.

I used your code on new AS database and I have no problem. However, I used same connection string (at least same initial catalog). I am not sure if that makes difference.

BTW, it is better to use my code because you do need to set some extended properties.

Problem creating a stored procedure with the Execute as clause.

I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.

I want this login to be able to execute the stored procedure, sp_helplogins.

The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.

I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.

What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.

Therefore, what I am attempting is the following:

I create the login, SecAdmin, and assign it to the securityadmin server role.

Then I right click sp_helplogins and select 'Modify'.

I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.

I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.

Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.

Line 73 is blank, but the next few lines read:

if(not(is_srvrolemember('securityadmin')= 1))

begin

raiserror(15247,-1,-1)

select @.RetCode = 1

goto label_86return

end

What am I doing wrong? Is their a better alternative to accomplish what I want to do?

Your assistance would be greatly appreciated.

It looks like when executing "AS SecAdmin" you are only executing the proc in the context of the user not the login. Therefore, it won't resolve the server permissions of the login SecAdmin but just the database permissions of the user SecAdmin.

In your scenario check:

execute as login = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

execute as user = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

I'm afraid i don't have any alternatives at this time but i'll have a think.

Hope this helps for now.

|||

Actually, is it possible to change the proc to be something along the lines of:

CREATE PROC blah
AS

EXECUTE AS LOGIN = 'SecAdmin'

EXEC sp_helplogins

Problem creating a stored procedure with the Execute as clause.

I have a server with a login named NewCreator. This login is assigned only to the dbcreator server role.

I want this login to be able to execute the stored procedure, sp_helplogins.

The sp_helplogins documentation says the caller of this proecedure needs securityadmin permissions.

I know that I should be able to create a login, say 'SecAdmin' and assign that login to the security admin server role, and then grant NewCreator Impersonate permission on SecAdmin.

What I don't like about this technique is that NewCreator can get on the server and gain complete authority over security when I really only want him to be able to execute sp_helplogins.

Therefore, what I am attempting is the following:

I create the login, SecAdmin, and assign it to the securityadmin server role.

Then I right click sp_helplogins and select 'Modify'.

I change the Alter to Create, change the name of the procedure to sp_myspecialone, and then add the sql statement "With Execute as 'SecAdmin'.

I execute the above and the new stored procedure is created. I then grant Execute on sp_myspecialone to NewCreator.

Everything works fine so far. But when I login as NewCreator and execute sp_myspecialone, I get an error in line 73 - no permission.

Line 73 is blank, but the next few lines read:

if (not (is_srvrolemember('securityadmin') = 1))

begin

raiserror(15247,-1,-1)

select @.RetCode = 1

goto label_86return

end

What am I doing wrong? Is their a better alternative to accomplish what I want to do?

Your assistance would be greatly appreciated.

It looks like when executing "AS SecAdmin" you are only executing the proc in the context of the user not the login. Therefore, it won't resolve the server permissions of the login SecAdmin but just the database permissions of the user SecAdmin.

In your scenario check:

execute as login = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

execute as user = 'SecAdmin'

SELECT is_srvrolemember('securityadmin')

I'm afraid i don't have any alternatives at this time but i'll have a think.

Hope this helps for now.

|||

Actually, is it possible to change the proc to be something along the lines of:

CREATE PROC blah
AS

EXECUTE AS LOGIN = 'SecAdmin'

EXEC sp_helplogins

Problem creating a job step to execute a SSIS package

Hello all,

I am having a problem creating a job step that will run a SSIS package. I go to add a job step (or edit an existing one), but when I select 'SQL Server Integration Services Package' as the type, an error box pops up with the following message:

The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)

After I hit OK, it goes back to the 'New Job Step' window and makes me select a different step type.

I am running on a Win2000 Advanced Server with SP4, a default instance of SQL2000 with SP4, and a named instance of SQL2005.

Does anyone know what is wrong or of a workaround?

Thanks in advance for any and all help/comments.

Jarret

You should probably post to the agent forum since this is an agent problem not an SSIS problem.

Thanks,
Matt

Problem creating a job step to execute a SSIS package

Hello all,

I am having a problem creating a job step that will run a SSIS package. I go to add a job step (or edit an existing one), but when I select 'SQL Server Integration Services Package' as the type, an error box pops up with the following message:

The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)

After I hit OK, it goes back to the 'New Job Step' window and makes me select a different step type.

I am running on a Win2000 Advanced Server with SP4, a default instance of SQL2000 with SP4, and a named instance of SQL2005.

Does anyone know what is wrong or of a workaround?

Thanks in advance for any and all help/comments.

Jarret

Whatever the problem was, restarting the server fixed it. I hadn't restarted the server since I installed SQL2005, because the install didn't require a reboot. I had a chance to do so over the weekend and when I checked on this problem, it was gone.

All is good now.

Thanks!

Jarret

Problem correctly setting a local_variable

Hi,

I've been trying to write a stored proc to no avail. I'm trying to set a local variable and every time I try to execute the stored proc I get the following error:

Running [dbo].[StoredProcedure2].

Conversion failed when converting the nvarchar value 'TEST2' to data type int.

No rows affected.

(0 row(s) returned)

@.RETURN_VALUE =

Finished running [dbo].[StoredProcedure2].

Here's the code (which I skimmed down to try and pinpoint where the issue was).

Initially, I was trying to assign a numeric string value stored as nvarchar(10) and anytime the value would be over '2147999999' (in that area) it would bomb... Now even this simple code won't work and I can't understand why. If anyone would be kind enough to enlighten me ;-)

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

RETURN @.ME

u must use Print/Select instead of Return.

fromBOL

RETURN

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed.

Syntax

RETURN [ integer_expression ]

alter PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

select @.me

Madhu

|||

The value following the RETURN keyword MUST be an integer value.

Strings are not allowable RETURN values.

Instead use:

Code Snippet

ALTER PROCEDURE dbo.StoredProcedure2

AS

DECLARE @.ME NVARCHAR(10);

SET @.ME = 'TEST2';

SELECT @.Me

GO

|||

It always better to use OUTPUT parameter, or Select statement to get the single row & column data from the SP. Return only supports integer.

NOTE:

PRINT – you can’t get the result on the UI. It will get suppressed.