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.
Showing posts with label state. Show all posts
Showing posts with label state. Show all posts
Wednesday, March 28, 2012
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.
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.
Monday, March 12, 2012
Problem doing a count query using 2 tables
I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
adding date component to my earlier query can have query as shown in
following example.
ex:
create table emp_master(name varchar(25),city varchar(25), dt datetime)
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1', getdate())
insert into emp_master values('name2','city1', getdate())
insert into emp_master values('name3','city2', getdate() -1)
insert into emp_master values('name4','city2', getdate() -1)
insert into emp_master values('name5','city2', getdate() -2)
insert into emp_master values('name7','city4', getdate() -2)
insert into emp_master values('name8','city5', getdate() -3)
insert into emp_master values('name9','city5', getdate() -3)
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
where a.dt between '20041016' and '20041018 23:59:59'
group by b.state
If above does not satisfy your requirement post DDL/some sample records and
expected result-set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
adding date component to my earlier query can have query as shown in
following example.
ex:
create table emp_master(name varchar(25),city varchar(25), dt datetime)
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1', getdate())
insert into emp_master values('name2','city1', getdate())
insert into emp_master values('name3','city2', getdate() -1)
insert into emp_master values('name4','city2', getdate() -1)
insert into emp_master values('name5','city2', getdate() -2)
insert into emp_master values('name7','city4', getdate() -2)
insert into emp_master values('name8','city5', getdate() -3)
insert into emp_master values('name9','city5', getdate() -3)
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
where a.dt between '20041016' and '20041018 23:59:59'
group by b.state
If above does not satisfy your requirement post DDL/some sample records and
expected result-set.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
Problem doing a count query using 2 tables
I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
If i understand your requirement correctly, following example may help you.
Please post relevent table structure/sample records/expected result set to
understand your problem correctly.
ex:
create table emp_master(name varchar(25),city varchar(25))
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1')
insert into emp_master values('name2','city1')
insert into emp_master values('name3','city2')
insert into emp_master values('name4','city2')
insert into emp_master values('name5','city2')
insert into emp_master values('name7','city4')
insert into emp_master values('name8','city5')
insert into emp_master values('name9','city5')
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
group by b.state
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
If i understand your requirement correctly, following example may help you.
Please post relevent table structure/sample records/expected result set to
understand your problem correctly.
ex:
create table emp_master(name varchar(25),city varchar(25))
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1')
insert into emp_master values('name2','city1')
insert into emp_master values('name3','city2')
insert into emp_master values('name4','city2')
insert into emp_master values('name5','city2')
insert into emp_master values('name7','city4')
insert into emp_master values('name8','city5')
insert into emp_master values('name9','city5')
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
group by b.state
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
Wednesday, March 7, 2012
Problem creating stored procedures
I am getting the following error message every time I try to create a stored procedure - any stored procedure:
Msg 6354, Level 16, State 10, Procedure AuditOperations, Line 14
Target string size is too small to represent the XML instance
A search of books online and MSDN didn't return anything.
Thanks.
Please post some code.
Adamus
|||OK, but the code doesn't appear to matter. I get the message for any view or stored proc I try to create. I've scripted some of AW views and stored procedures with the same result.
CREATE PROCEDURE ListEmployeesByDepartment
@.DepartmentName NVARCHAR(50)
AS
SELECT c.Lastname, c.FirstName
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeDepartmentHistory h
ON e.EmployeeID = h.EmployeeID
INNER JOIN HumanResources.Department d
ON h.DepartmentID = d.DepartmentID
WHERE d.name = @.DepartmentName AND h.EndDate IS NULL
ORDER BY 1
|||TennesseeSQL wrote:
OK, but the code doesn't appear to matter. I get the message for any view or stored proc I try to create. I've scripted some of AW views and stored procedures with the same result.
Do you have any triggers on the table?
If so, disable them temporarily.
Adamus
|||The message indicates the object in which the error happened. So check "AuditOperations" SP or trigger code.|||Geez! How stupid?! I'm working through examples preparing for the 70-441 test. That was it. Thanks so much!
Subscribe to:
Posts (Atom)