Incorrect syntax near the keyword 'Insert'.

I'm trying to build a command that gets a linkservername but gets this error
msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
Thnaks.
set @.cmd = Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
run_date))),
case j.enabled
when 0 then 'N'
when 1 then 'Y'
End,
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
End,
h.run_duration,
substring(h.message,1,300)
From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
[+@.linkservername+].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From [+@.linkservername+].[msdb].[dbo].
[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1
Message posted via http://www.webservertalk.comNaana,
I believe you'll need to use the EXEC statement and build your SQL statement
dynamically to do this. Google Dynamic SQL.
HTH
Jerry
"Naana via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:549AE24111A74@.webservertalk.com...
> I'm trying to build a command that gets a linkservername but gets this
> error
> msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
> Thnaks.
>
> set @.cmd = Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
> run_date))),
> case j.enabled
> when 0 then 'N'
> when 1 then 'Y'
> End,
> case h.run_status
> when 0 then 'Failed'
> when 1 then 'Successful'
> when 3 then 'Cancelled'
> when 4 then 'In Progress'
> End,
> h.run_duration,
> substring(h.message,1,300)
> From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
> [+@.linkservername+].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From [+@.linkservername+].[msdb].[dbo].
> [sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1
>
> --
> Message posted via http://www.webservertalk.com|||Hi
You are missing the many single quotes within the construction of @.cmd
Try:
set @.cmd = 'Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
case j.enabled
when 0 then ''N''
when 1 then ''Y''
End,
case h.run_status
when 0 then ''Failed''
when 1 then ''Successful''
when 3 then ''Cancelled''
when 4 then ''In Progress''
End,
h.run_duration,
substring(h.message,1,300)
From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From
['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1'
You may want to read:
http://www.sommarskog.se/dynamic_sql.html
John
"Naana via webservertalk.com" wrote:

> I'm trying to build a command that gets a linkservername but gets this err
or
> msg:Incorrect syntax near the keyword 'Insert'. Can anyone help.
> Thnaks.
>
> set @.cmd = Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.
> run_date))),
> case j.enabled
> when 0 then 'N'
> when 1 then 'Y'
> End,
> case h.run_status
> when 0 then 'Failed'
> when 1 then 'Successful'
> when 3 then 'Cancelled'
> when 4 then 'In Progress'
> End,
> h.run_duration,
> substring(h.message,1,300)
> From [+@.linkservername+].[msdb].[dbo].[sysjobhistory] h,
> [+@.linkservername+].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From [+@.linkservername+].[msdb].[dbo].
> [sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1
>
> --
> Message posted via http://www.webservertalk.com
>|||John Bell wrote:
>Hi
>You are missing the many single quotes within the construction of @.cmd
>Try:
>set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
>['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>You may want to read:
>http://www.sommarskog.se/dynamic_sql.html
>John
>
>[quoted text clipped - 30 lines]
Hi John,
Thanks, the sub_query is working now but I get the following message when
runn the whole query:
1) Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ACCURATE'.
and
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ''.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'and'.
The whole query:
Declare @.linkservername varchar(256)
Declare @.cmd varchar(8000)
Declare @.batchid bigint
-- Set the parms
exec pBatchInsert @.BatchID output
--select @.batchID
-- if exists drop then create table to hold error log results
If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
[Error_Log_Results_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
Drop Table [dbo].[Error_Log_Results_Temp]
End
Create Table [dbo].[Error_Log_Results_Temp]
([vchMessage] [varchar] (255),
[ID] [int])
-- if exists drop the create table to hold a list of servers this proc is
accesing.
If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
[Job_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
Drop Table [dbo].[Job_Status]
End
Create Table [dbo].[Job_Status]
(Status varchar(16),
Server varchar(64),
[TimeStamp] datetime)
-- cursur thru the sysservres table to get list of servers linked to this on
e.
Declare linkserver_cursor CURSOR
For Select srvname
From master..sysservers(nolock)
Where srvproduct = 'SQL Server'
and isremote = 1
and srvname not in ('DBPHQOVOM01\OVOPS')
-- and srvname = 'posrep'
Order by srvname
Open linkserver_cursor
Fetch Next From linkserver_cursor into @.linkservername
While ( @.@.fetch_status <> -1 )
Begin
If ( @.@.fetch_status <> -2 )
Begin
-- insert ststus
Insert into Job_Status
Select 'Started', @.linkservername, getdate()
Insert into Servers_Scaned
Select @.BatchID, @.linkservername
-- build command
set @.cmd = 'Insert into Job_Results
Select distinct '+convert(varchar(10),@.batchid)+',
'+@.linkservername+',
substring(j.name,1,32),
h.step_id,
substring(h.step_name,1,32),
convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
case j.enabled
when 0 then ''N''
when 1 then ''Y''
End,
case h.run_status
when 0 then ''Failed''
when 1 then ''Successful''
when 3 then ''Cancelled''
when 4 then ''In Progress''
End,
h.run_duration,
substring(h.message,1,300)
From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
Where j.job_id = h.job_id
and h.run_date = (Select max(h.run_date)
From
['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
where j.job_id = h.job_id)
and h.run_status <> 1'
-- exec command
Exec (@.cmd)
--select @.cmd
-- Begin loading info from error logs.
Set @.cmd = ' '
Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
[master].[dbo].[xp_readerrorlog]'
--select @.cmd
Exec (@.cmd)
-- insert error log results into temp table and tag it with a server name.
Insert into Error_Log_Results
Select @.batchid, @.linkservername,'0', *
From Error_Log_Results_Temp
Where (vchMessage like '%error:%'
or vchMessage like '%failed%'
or vchMessage like '%invalid%'
or vchMessage like '%full%'
or vchMessage like '%hung%'
or vchMessage like '%kill%'
or vchMessage like '%violation%'
or vchMessage like '%SqlDumpExceptionHandler%'
or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
or vchMessage like '%Stack%'
or vchMessage like '%terminating%'
or vchMessage like '%shutdown%'
or vchMessage like '%fatal%')
-- truncate table for load from the second error log.
Truncate Table Error_Log_Results_Temp
-- clear then build the command
Set @.cmd = ' '
Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
[master].[dbo].[xp_readerrorlog] 1'
--select @.cmd
Exec (@.cmd)
-- Insert results into temp error log table
Insert into Error_Log_Results
Select @.batchid, @.linkservername,'1', *
From Error_Log_Results_Temp
Where (vchMessage like '%error:%'
or vchMessage like '%failed%'
or vchMessage like '%invalid%'
or vchMessage like '%full%'
or vchMessage like '%hung%'
or vchMessage like '%kill%'
or vchMessage like '%violation%'
or vchMessage like '%SqlDumpExceptionHandler%'
or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
or vchMessage like '%Stack%'
or vchMessage like '%terminating%'
or vchMessage like '%shutdown%'
or vchMessage like '%fatal%')
Truncate Table Error_Log_Results_Temp
Insert into Job_Status
Select 'Finished', @.linkservername, getdate()
End
FETCH NEXT From linkserver_cursor into @.linkservername
End
CLOSE linkserver_cursor
DEALLOCATE linkserver_cursor
-- Cleanup
Drop Table [dbo].[Error_Log_Results_Temp]
Drop Table [dbo].[Job_Status]
-- print ' '
-- print '***************************************
***'
-- print '** End of Report: '+convert(varchar(64),getdate())+' **'
-- print '***************************************
***'
-- Return (0)
GO
Message posted via http://www.webservertalk.com|||Hi
The query runs but now get's this message for my server names as invalid
column names.
) Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ACCURATE'.
Thanks.
John Bell wrote:
>Hi
>You are missing the many single quotes within the construction of @.cmd
>Try:
>set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+', '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
>['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>You may want to read:
>http://www.sommarskog.se/dynamic_sql.html
>John
>
>[quoted text clipped - 30 lines]
Message posted via http://www.webservertalk.com|||Hi
Accurate must be in your data, therefore you may want to try using PRINT
@.cmd before your EXEC statement to show what the SQL is that you are
executing. You could then 'debug' the output from the PRINT statement so tha
t
it provides what you require and then figure out how to make up the command
in dynamic SQL.
It could be that you are wanting to output a literal value rather than the
value of a column from your variables e.g.
If @.batchid had a value 101 and @.linkservername was DBPHQOVOM01\OVOPSVR
Select distinct '+convert(varchar(10),@.batchid)+','+@.lin
kservername+',...
Would be looking for a column called 101 and DBPHQOVOM01\OVOPSVR (which is
it if a valid column should be escaped with [] such as [DBPHQOVOM01\OVOPSVR]
)when you executed @.cmd.
Therefore:
Select distinct '+convert(varchar(10),@.batchid)+',['+@.li
nkservername+'],...
would give
Select distinct 101, [DBPHQOVOM01\OVOPSVR], ...
If you actually wanted a constants to be output then you need to enquote the
n
Select distinct
'''+convert(varchar(10),@.batchid)+''',''
'+@.linkservername+''',...
this would give
Select distinct '101', 'DBPHQOVOM01\OVOPSVR', ...
HTH
John
"Naana via webservertalk.com" wrote:

> John Bell wrote:
>
> Hi John,
> Thanks, the sub_query is working now but I get the following message when
> runn the whole query:
> 1) Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'ACCURATE'.
> and
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ''.
> Server: Msg 156, Level 15, State 1, Line 27
> Incorrect syntax near the keyword 'and'.
> The whole query:
> Declare @.linkservername varchar(256)
> Declare @.cmd varchar(8000)
> Declare @.batchid bigint
> -- Set the parms
> exec pBatchInsert @.BatchID output
> --select @.batchID
> -- if exists drop then create table to hold error log results
> If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
> [Error_Log_Results_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> Begin
> Drop Table [dbo].[Error_Log_Results_Temp]
> End
> Create Table [dbo].[Error_Log_Results_Temp]
> ([vchMessage] [varchar] (255),
> [ID] [int])
> -- if exists drop the create table to hold a list of servers this proc is
> accesing.
> If exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].
> [Job_Status]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> Begin
> Drop Table [dbo].[Job_Status]
> End
> Create Table [dbo].[Job_Status]
> (Status varchar(16),
> Server varchar(64),
> [TimeStamp] datetime)
>
> -- cursur thru the sysservres table to get list of servers linked to this
one.
> Declare linkserver_cursor CURSOR
> For Select srvname
> From master..sysservers(nolock)
> Where srvproduct = 'SQL Server'
> and isremote = 1
> and srvname not in ('DBPHQOVOM01\OVOPS')
> -- and srvname = 'posrep'
> Order by srvname
> Open linkserver_cursor
> Fetch Next From linkserver_cursor into @.linkservername
> While ( @.@.fetch_status <> -1 )
> Begin
> If ( @.@.fetch_status <> -2 )
> Begin
> -- insert ststus
> Insert into Job_Status
> Select 'Started', @.linkservername, getdate()
> Insert into Servers_Scaned
> Select @.BatchID, @.linkservername
> -- build command
> set @.cmd = 'Insert into Job_Results
> Select distinct '+convert(varchar(10),@.batchid)+',
> '+@.linkservername+',
> substring(j.name,1,32),
> h.step_id,
> substring(h.step_name,1,32),
> convert(varchar(12),convert(datetime,con
vert(varchar(8),h.run_date))),
> case j.enabled
> when 0 then ''N''
> when 1 then ''Y''
> End,
> case h.run_status
> when 0 then ''Failed''
> when 1 then ''Successful''
> when 3 then ''Cancelled''
> when 4 then ''In Progress''
> End,
> h.run_duration,
> substring(h.message,1,300)
> From ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h,
> ['+@.linkservername+'].[msdb].[dbo].[sysjobs] j
> Where j.job_id = h.job_id
> and h.run_date = (Select max(h.run_date)
> From
> ['+@.linkservername+'].[msdb].[dbo].[sysjobhistory] h
> where j.job_id = h.job_id)
> and h.run_status <> 1'
>
> -- exec command
> Exec (@.cmd)
> --select @.cmd
> -- Begin loading info from error logs.
> Set @.cmd = ' '
> Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
> [master].[dbo].[xp_readerrorlog]'
> --select @.cmd
> Exec (@.cmd)
> -- insert error log results into temp table and tag it with a server name.
> Insert into Error_Log_Results
> Select @.batchid, @.linkservername,'0', *
> From Error_Log_Results_Temp
> Where (vchMessage like '%error:%'
> or vchMessage like '%failed%'
> or vchMessage like '%invalid%'
> or vchMessage like '%full%'
> or vchMessage like '%hung%'
> or vchMessage like '%kill%'
> or vchMessage like '%violation%'
> or vchMessage like '%SqlDumpExceptionHandler%'
> or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
> or vchMessage like '%Stack%'
> or vchMessage like '%terminating%'
> or vchMessage like '%shutdown%'
> or vchMessage like '%fatal%')
> -- truncate table for load from the second error log.
> Truncate Table Error_Log_Results_Temp
> -- clear then build the command
> Set @.cmd = ' '
> Set @.cmd = 'INSERT Error_Log_Results_Temp Exec ['+@.linkservername+'].
> [master].[dbo].[xp_readerrorlog] 1'
> --select @.cmd
> Exec (@.cmd)
> -- Insert results into temp error log table
> Insert into Error_Log_Results
> Select @.batchid, @.linkservername,'1', *
> From Error_Log_Results_Temp
> Where (vchMessage like '%error:%'
> or vchMessage like '%failed%'
> or vchMessage like '%invalid%'
> or vchMessage like '%full%'
> or vchMessage like '%hung%'
> or vchMessage like '%kill%'
> or vchMessage like '%violation%'
> or vchMessage like '%SqlDumpExceptionHandler%'
> or vchMessage like '%EXCEPTION_ACCESS_VIOLATION%'
> or vchMessage like '%Stack%'
> or vchMessage like '%terminating%'
> or vchMessage like '%shutdown%'
> or vchMessage like '%fatal%')
> Truncate Table Error_Log_Results_Temp
> Insert into Job_Status
> Select 'Finished', @.linkservername, getdate()
> End
> FETCH NEXT From linkserver_cursor into @.linkservername
> End
> CLOSE linkserver_cursor
> DEALLOCATE linkserver_cursor
>
> -- Cleanup
> Drop Table [dbo].[Error_Log_Results_Temp]
> Drop Table [dbo].[Job_Status]
> -- print ' '
> -- print '***************************************
***'
> -- print '** End of Report: '+convert(varchar(64),getdate())+' **'
> -- print '***************************************
***'
> -- Return (0)
>
> GO
>
> --
> Message posted via http://www.webservertalk.com
>

Incorrect syntax near 'Go'

Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
--
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.

Incorrect syntax near 'Go'

Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
Arek
GO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>
|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.

Incorrect syntax near 'Go'

Hi,
I am trying to create index on view. First of all in design view I
cannot use Manage Indexes command.(it is disabled)
Then if I try to add Create Index... command in the View Properties I
cannot use statement "GO"
System is prompting me: incorrent syntaxt near 'GO'
If I remove Go everything works well.
I was trying that on different views but I cannot use GO anywhere.
Thank you for help
ArekGO is not a T-SQL command. It is a command for Query Analyzer and OSQL
client apps, to tell them when to fininsh a bach. Look more about batches in
Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112854192.939546.198960@.o13g2000cwo.googlegroups.com...
> Hi,
> I am trying to create index on view. First of all in design view I
> cannot use Manage Indexes command.(it is disabled)
> Then if I try to add Create Index... command in the View Properties I
> cannot use statement "GO"
> System is prompting me: incorrent syntaxt near 'GO'
> If I remove Go everything works well.
> I was trying that on different views but I cannot use GO anywhere.
> Thank you for help
> Arek
>|||You can't put multiple statements in a view definition and GO isn't a
TSQL command anyway - it's a batch separator.
Does the view conform to the rules for indexed views? Maybe that
explains why the index management option is disabled. Query Analyzer is
a much better place to make schema changes so use QA rather than
Enterprise Manager.
David Portas
SQL Server MVP
--|||Thank you.
I was looking for all the rules, and couldn't create that indexed view
in Query Analazer anyway. I will read more about those rules.

Incorrect syntax near [SQL UPDATE COMMAND] > cmd.ExecuteNonQuery()

Please let me know what is wrong with my code below. I keep getting the "Incorrect syntax near 'UpdateInfoByAccountAndFullName'." error when I execute cmd.executenonquery. I highlighted the part that errors out. Thanks a lot.

-------------------------------------

public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

using (cn)
{
cn.Open();
return cmd.ExecuteNonQuery() > 1;
}
}

capture the return value to a variable and return that. You are trying to use a shortcut that doesn't exist.

|||

I added a variable that will hold the INTEGER value of the rows updated, but it still generates the same error.
-----------------------------------------
public bool Update(
string newaccount, string newfullname, string rep, string zip,
string comment, string oldaccount, string oldfullname
)
{
SqlConnection cn = new SqlConnection(_connectionstring);
SqlCommand cmd = new SqlCommand("UpdateInfoByAccountAndFullName", cn);
cmd.Parameters.AddWithValue("@.newaccount", newaccount);
cmd.Parameters.AddWithValue("@.newfullname", newfullname);
cmd.Parameters.AddWithValue("@.rep", rep);
cmd.Parameters.AddWithValue("@.zip", zip);
cmd.Parameters.AddWithValue("@.comments", comment);
cmd.Parameters.AddWithValue("@.oldaccount", oldaccount);
cmd.Parameters.AddWithValue("@.oldfullname", oldfullname);

int rowsAffected;

using (cn)
{
cn.Open();
rowsAffected = cmd.ExecuteNonQuery();
return rowsAffected > 1;
}
}

|||

try this

If (rowsAffected >1){

return true;}

else {

return false;}

|||

No luck... It still generates the same error even with the IF condition in place. Here is a copy of the error trace from the browser:

--------------------------------

Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'UpdateInfoByAccountAndFullName'.

Source Error:

Line 215: {
Line 216: cn.Open();
Line 217: rowsAffected = cmd.ExecuteNonQuery();
Line 218:
Line 219: if (rowsAffected > 1)

|||

post your stored procedure. This is where your problem is.

|||

I don't see the point of using rowsaffected >1

Why don't you just use return rowsAffected?

Isn't that going to give you the count anyway?

|||

Thanks for the quick reply.:

------------------------------

Create procedure UpdateInfoByAccountAndFullName(
@.newaccount varchar(50),
@.newfullname varchar(100),
@.rep varchar(10),
@.zip varchar(10),
@.comments varchar(2000),
@.oldaccount varchar(50),
@.oldfullname varchar(100)
)
as

update Info
Set ACCOUNT = @.newaccount,
FULL_NAME = @.newfullname,
REP = @.rep,
ZIP = @.zip,
COMMENTS = @.comments
where
ACCOUNT = @.oldaccount and
FULL_NAME = @.oldfullname

|||

Duduvi,

Why don't you just use return rowsAffected? Isn't that going to give you the count anyway? > I could use rowsAffected, but I don't really see the need of taking into account how many records where updated. This is why I used the boolean rowsAffected > 1. I just need to know that more than or 1 record(s) were updated.

|||

I don't see a problem. Try removing the return value. Just try executing the query and see if it works then you can eliminate the stored procedure as the problem.

|||

You are right. I dont see the problem neither. This is what freaks me out too. I already tried running the StoredProc and the SQL String from within SQL2005, and it did update the record. The only time it throws an exception is on my .NET code when I do a ExecuteNonQuery() with or without a return value.

|||

HA! I can't believe i missed it. you forgot to specify that your command is a stored procedure using the commandtype enum.

|||

Yep you're right. I forgot to specify the command type. Thanks again.

Incorrect syntax near @File

I am using this bulk insert command in procedure below. I am passing variable @.File inside of the procedure and I do not know the right syntax for it. Could you pls help me. When I enter the path for the file like 'C:\imp_file.csv' it works.

Thanks

ALTER procedure sp_BulkInsert1
@.File varchar(1000)

AS

BULK INSERT SQL_Tests.dbo.xRSA FROM @.File
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
CODEPAGE = 'RAW',
TABLOCK
)Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP|||:) Because the syntax requires a constant, you can't use a variable... At least not directly anyway!ALTER procedure sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT SQL_Tests.dbo.xRSA FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO-PatP

Why do I need to use Execute Command?|||If you check BOL for the syntax of the BULK INSERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp) command, you'll notice that the syntax requires a constant for the file name. The only way I know to make a variable appear as a constant is to execute it indirectly, via the EXECUTE statement. We're basically working around a limitation in the supoorted syntax.

-PatP|||Because it's dynamic sql...

Pat I can't get the injection in to the vien...maybe you can...I'm sure it can be done

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO

CREATE PROC sp_BulkInsert1
@.File varchar(1000)
AS

EXECUTE ('BULK INSERT myTable99 FROM ''' + @.File + '''
WITH
(
DATAFILETYPE = ''char''
, FIELDTERMINATOR = '',''
, ROWTERMINATOR = ''\n''
, CODEPAGE = ''RAW''
, TABLOCK
)' )

RETURN
GO

DECLARE @.x varchar(1000)
SELECT @.x = 'c:\config.sys' + '''' + ' GO SELECT ' + '''' + 'Lets execute some damaging sql' + '''' + ' GO'
EXEC sp_BulkInsert1 @.x
GO

DROP PROC sp_BulkInsert1
DROP TABLE myTable99
GO|||I'd use one of my quote fixers. I'm having to shoot from the hip since my system is toast at the moment, but it goes something like:CREATE FUNCTION dbo.FixQuote(@.pcIn VARCHAR(8000)) RETURNS VARCHAR(8000)
BEGIN
RETURN Replace(@.pcIn, '''', ''')
ENDGiven that little function, you could wrap it around the parameter to inhibit code injection. Note that it is MUCH better to prevent the injection at the source (the client/middleware machine) rather than trying to inhibit it at SQL Server.

-PatP

Incorrect parameters being received by SQL Server Stored Procedure ...

Hi Everybuddy,
I have a strange problem with VB6/SQL Server 2K. I am calling a stored
procedure from VB using ADODB.Command object. When I check the incomming
parameters into the stored procedure, that values in named parameters are
inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
Y=3 and Z=7 inside the procedure ABC).
I am totally having no clue of whats making this wierd situation. The code
snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
Procedire) are below.
[VB Code]
Set cnBuilty = New ADODB.Connection
Set cmdBuilty = New ADODB.Command
cnBuilty.CursorLocation = adUseClient
cnBuilty.Open APP_StdConnectionString
'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
adInteger, adParamReturnValue, 4, nRetVal)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
adInteger, adParamInput, 4, in_LR_No)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nDestinationId",
adInteger, adParamInput, 4, in_Town_ID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
adInteger, adParamInput, 4, in_SenderID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
adInteger, adParamInput, 4, in_ReceiverID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
adInteger, adParamInput, 4, in_MBT)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
adInteger, adParamInput, 4, in_MST)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
adInteger, adParamInput, 4, in_LH_FL)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
adInteger, adParamInput, 4, in_CBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
adInteger, adParamInput, 4, in_SCBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
adInteger, adParamInput, 4, in_Milk_Crt)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
adInteger, adParamInput, 4, in_POP)
cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
in_ConsignmentType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
adTinyInt, adParamInput, 1, in_InvType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szIsCancelled",
adVarChar, adParamInput, 1, "")
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
cmdBuilty.Name = "InsertConsignmentRecord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = "InsertConsignmentRecord"
cmdBuilty.CommandType = adCmdStoredProc
cmdBuilty.CommandTimeout = 45
Set rsBuilty = cmdBuilty.Execute()
'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
Dim qq As Integer
For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
Next qq
[Stored Procedure Code]
CREATE PROCEDURE dbo.InsertConsignmentRecord
@.nLR_No As Int,
@.dtLR_Date As DateTime,
@.nDestinationId As Int,
@.nConsignorId As Int,
@.nConsigneeId As Int,
@.nMBT As Int,
@.nMST As Int,
@.nLH_FL As Int,
@.nCBB As Int,
@.nSCBB As Int,
@.nMilkCaret As Int,
@.nPOP As Int,
@.dtInv_Date As DateTime,
@.szInvoiceId As VarChar(21),
@.szIsCancelled As VarChar(1),
@.nInvoiceType As TinyInt,
@.nConsignmentType As Int,
@.szClubbedId As VarChar(21)
AS
-- Problem: Received value is different from what is passed ...
-- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
ConsignmentType=36528001;
-- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
ConsignmentType=1;
Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
@.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
"ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As "ClubbedId"
-- Insert Into Consignment_Note
-- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
-- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
-- INVOICE_NO, INV_DATE, IS_CANCELLED,
-- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
-- Values
-- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
-- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
-- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
-- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
Return 0
If any one can trap some problem here, that would be great ...
Thanks,
*(Vipul)() ;
"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
> Hi Everybuddy,
> I have a strange problem with VB6/SQL Server 2K. I am calling a stored
> procedure from VB using ADODB.Command object. When I check the incomming
> parameters into the stored procedure, that values in named parameters are
> inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
> Y=3 and Z=7 inside the procedure ABC).
> I am totally having no clue of whats making this wierd situation. The code
> snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
> Procedire) are below.
> [VB Code]
> ----
> Set cnBuilty = New ADODB.Connection
> Set cmdBuilty = New ADODB.Command
> cnBuilty.CursorLocation = adUseClient
> cnBuilty.Open APP_StdConnectionString
> 'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
> adInteger, adParamReturnValue, 4, nRetVal)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
> adInteger, adParamInput, 4, in_LR_No)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nDestinationId",
> adInteger, adParamInput, 4, in_Town_ID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
> adInteger, adParamInput, 4, in_SenderID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
> adInteger, adParamInput, 4, in_ReceiverID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
> adInteger, adParamInput, 4, in_MBT)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
> adInteger, adParamInput, 4, in_MST)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
> adInteger, adParamInput, 4, in_LH_FL)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
> adInteger, adParamInput, 4, in_CBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
> adInteger, adParamInput, 4, in_SCBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
> adInteger, adParamInput, 4, in_Milk_Crt)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
> adInteger, adParamInput, 4, in_POP)
> cmdBuilty.Parameters.Append
> cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
> in_ConsignmentType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
> adTinyInt, adParamInput, 1, in_InvType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.szIsCancelled",
> adVarChar, adParamInput, 1, "")
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
> adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
> adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
> cmdBuilty.Name = "InsertConsignmentRecord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = "InsertConsignmentRecord"
> cmdBuilty.CommandType = adCmdStoredProc
> cmdBuilty.CommandTimeout = 45
> Set rsBuilty = cmdBuilty.Execute()
> 'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
> Dim qq As Integer
> For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
> Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
> Next qq
> ----
>
> [Stored Procedure Code]
> ----
> CREATE PROCEDURE dbo.InsertConsignmentRecord
> @.nLR_No As Int,
> @.dtLR_Date As DateTime,
> @.nDestinationId As Int,
> @.nConsignorId As Int,
> @.nConsigneeId As Int,
> @.nMBT As Int,
> @.nMST As Int,
> @.nLH_FL As Int,
> @.nCBB As Int,
> @.nSCBB As Int,
> @.nMilkCaret As Int,
> @.nPOP As Int,
> @.dtInv_Date As DateTime,
> @.szInvoiceId As VarChar(21),
> @.szIsCancelled As VarChar(1),
> @.nInvoiceType As TinyInt,
> @.nConsignmentType As Int,
> @.szClubbedId As VarChar(21)
> AS
> -- Problem: Received value is different from what is passed ...
> -- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
> ConsignmentType=36528001;
> -- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
> ConsignmentType=1;
> Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
> @.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
> "ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As
"ClubbedId"
> -- Insert Into Consignment_Note
> -- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
> -- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
> -- INVOICE_NO, INV_DATE, IS_CANCELLED,
> -- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
> -- Values
> -- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
> -- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
> -- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
> -- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
> Return 0
> ----
> If any one can trap some problem here, that would be great ...
> Thanks,
> *(Vipul)() ;
>
Best guess...
There is no 'data binding' in ADO. It is pure positional - it looks like you
have your parameters out of order from what the SP is expecting. Take a look
at the LR_DATE.
-ralph
|||Thanks a lot Ralph,
You are damn correct in one shot. It worked in first time after the
suggested change.
BTW, does this mean, we are not passing named parameters? The name of
parameter is *not* useful?
Thanks for your help ...
*(Vipul)() ;
"Ralph" <nt_consulting64@.yahoo.com> wrote in message
news:7sGdnbeE3_xPXLrYnZ2dnUVZ_qudnZ2d@.arkansas.net ...[vbcol=seagreen]
> "Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
are[vbcol=seagreen]
X=12,[vbcol=seagreen]
code[vbcol=seagreen]
cmdBuilty.CreateParameter("@.RETURN_VALUE",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.nDestinationId",
cmdBuilty.CreateParameter("@.nConsignorId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nConsigneeId",[vbcol=seagreen]
4,[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nInvoiceType",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.szIsCancelled",
cmdBuilty.CreateParameter("@.szInvoiceId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.szClubbedId",[vbcol=seagreen]
rsBuilty.Fields(qq)
> "ClubbedId"
> Best guess...
> There is no 'data binding' in ADO. It is pure positional - it looks like
you
> have your parameters out of order from what the SP is expecting. Take a
look
> at the LR_DATE.
> -ralph
>
|||"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:ue$sCwi6GHA.4732@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot Ralph,
> You are damn correct in one shot. It worked in first time after the
> suggested change.
> BTW, does this mean, we are not passing named parameters? The name of
> parameter is *not* useful?
> Thanks for your help ...
> *(Vipul)() ;
>
<snipped>
Essentially - yes.
That is what is meant by "not bounded". You should note that this is not
necessarily true for all providers or data access libraries. Unfortunately,
usually we have to get 'burnt' to find out if it is supported or not. <g>
Providing named parameters shouldn't be underestimated in any case. As it
makes your intentions very clear and identifying errant parameters becomes
easier. An advantage which becomes very invaluable should you have to
revisit this routine six months from now. <g>
-ralph

Incorrect parameters being received by SQL Server Stored Procedure ...

Hi Everybuddy,
I have a strange problem with VB6/SQL Server 2K. I am calling a stored
procedure from VB using ADODB.Command object. When I check the incomming
parameters into the stored procedure, that values in named parameters are
inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
Y=3 and Z=7 inside the procedure ABC).
I am totally having no clue of whats making this wierd situation. The code
snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
Procedire) are below.
[VB Code]
----
Set cnBuilty = New ADODB.Connection
Set cmdBuilty = New ADODB.Command
cnBuilty.CursorLocation = adUseClient
cnBuilty.Open APP_StdConnectionString
'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
adInteger, adParamReturnValue, 4, nRetVal)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
adInteger, adParamInput, 4, in_LR_No)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nDestinationId",
adInteger, adParamInput, 4, in_Town_ID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
adInteger, adParamInput, 4, in_SenderID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
adInteger, adParamInput, 4, in_ReceiverID)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
adInteger, adParamInput, 4, in_MBT)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
adInteger, adParamInput, 4, in_MST)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
adInteger, adParamInput, 4, in_LH_FL)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
adInteger, adParamInput, 4, in_CBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
adInteger, adParamInput, 4, in_SCBB)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
adInteger, adParamInput, 4, in_Milk_Crt)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
adInteger, adParamInput, 4, in_POP)
cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
in_ConsignmentType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
adTinyInt, adParamInput, 1, in_InvType)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
adDBTimeStamp, adParamInput, 8, in_LR_Date)
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szIsCancelled",
adVarChar, adParamInput, 1, "")
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
cmdBuilty.Name = "InsertConsignmentRecord"
cmdBuilty.ActiveConnection = cnBuilty
cmdBuilty.CommandText = "InsertConsignmentRecord"
cmdBuilty.CommandType = adCmdStoredProc
cmdBuilty.CommandTimeout = 45
Set rsBuilty = cmdBuilty.Execute()
'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
Dim qq As Integer
For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
Next qq
----
[Stored Procedure Code]
----
CREATE PROCEDURE dbo.InsertConsignmentRecord
@.nLR_No As Int,
@.dtLR_Date As DateTime,
@.nDestinationId As Int,
@.nConsignorId As Int,
@.nConsigneeId As Int,
@.nMBT As Int,
@.nMST As Int,
@.nLH_FL As Int,
@.nCBB As Int,
@.nSCBB As Int,
@.nMilkCaret As Int,
@.nPOP As Int,
@.dtInv_Date As DateTime,
@.szInvoiceId As VarChar(21),
@.szIsCancelled As VarChar(1),
@.nInvoiceType As TinyInt,
@.nConsignmentType As Int,
@.szClubbedId As VarChar(21)
AS
-- Problem: Received value is different from what is passed ...
-- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
ConsignmentType=36528001;
-- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
ConsignmentType=1;
Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
@.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
"ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As "ClubbedId"
-- Insert Into Consignment_Note
-- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
-- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
-- INVOICE_NO, INV_DATE, IS_CANCELLED,
-- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
-- Values
-- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
-- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
-- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
-- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
Return 0
----
If any one can trap some problem here, that would be great ...
Thanks,
*(Vipul)() ;"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
> Hi Everybuddy,
> I have a strange problem with VB6/SQL Server 2K. I am calling a stored
> procedure from VB using ADODB.Command object. When I check the incomming
> parameters into the stored procedure, that values in named parameters are
> inter-changed (e.g. : Calling ABC(X=3, Y=7, Z=12) will result into- X=12,
> Y=3 and Z=7 inside the procedure ABC).
> I am totally having no clue of whats making this wierd situation. The code
> snippet of Caller (VB 6.0) and Callee (SQL Server 2000 SP4, Stored
> Procedire) are below.
> [VB Code]
> ----
> Set cnBuilty = New ADODB.Connection
> Set cmdBuilty = New ADODB.Command
> cnBuilty.CursorLocation = adUseClient
> cnBuilty.Open APP_StdConnectionString
> 'cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.RETURN_VALUE",
> adInteger, adParamReturnValue, 4, nRetVal)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLR_No",
> adInteger, adParamInput, 4, in_LR_No)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.nDestinationId",
> adInteger, adParamInput, 4, in_Town_ID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsignorId",
> adInteger, adParamInput, 4, in_SenderID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nConsigneeId",
> adInteger, adParamInput, 4, in_ReceiverID)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMBT",
> adInteger, adParamInput, 4, in_MBT)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMST",
> adInteger, adParamInput, 4, in_MST)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nLH_FL",
> adInteger, adParamInput, 4, in_LH_FL)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nCBB",
> adInteger, adParamInput, 4, in_CBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nSCBB",
> adInteger, adParamInput, 4, in_SCBB)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nMilkCaret",
> adInteger, adParamInput, 4, in_Milk_Crt)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nPOP",
> adInteger, adParamInput, 4, in_POP)
> cmdBuilty.Parameters.Append
> cmdBuilty.CreateParameter("@.nConsignmentType", adInteger, adParamInput, 4,
> in_ConsignmentType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.nInvoiceType",
> adTinyInt, adParamInput, 1, in_InvType)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtInv_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.dtLR_Date",
> adDBTimeStamp, adParamInput, 8, in_LR_Date)
> cmdBuilty.Parameters.Append
cmdBuilty.CreateParameter("@.szIsCancelled",
> adVarChar, adParamInput, 1, "")
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szInvoiceId",
> adVarChar, adParamInput, 20, Trim(in_InvoiceNo))
> cmdBuilty.Parameters.Append cmdBuilty.CreateParameter("@.szClubbedId",
> adVarChar, adParamInput, 20, Trim(in_ClubbedWithId))
> cmdBuilty.Name = "InsertConsignmentRecord"
> cmdBuilty.ActiveConnection = cnBuilty
> cmdBuilty.CommandText = "InsertConsignmentRecord"
> cmdBuilty.CommandType = adCmdStoredProc
> cmdBuilty.CommandTimeout = 45
> Set rsBuilty = cmdBuilty.Execute()
> 'nRetVal = cmdBuilty.Parameters("@.RETURN_VALUE")
> Dim qq As Integer
> For qq = 0 To (rsBuilty.Fields.Count - 1) Step 1
> Debug.Print rsBuilty.Fields(qq).Name & " = " & rsBuilty.Fields(qq)
> Next qq
> ----
>
> [Stored Procedure Code]
> ----
> CREATE PROCEDURE dbo.InsertConsignmentRecord
> @.nLR_No As Int,
> @.dtLR_Date As DateTime,
> @.nDestinationId As Int,
> @.nConsignorId As Int,
> @.nConsigneeId As Int,
> @.nMBT As Int,
> @.nMST As Int,
> @.nLH_FL As Int,
> @.nCBB As Int,
> @.nSCBB As Int,
> @.nMilkCaret As Int,
> @.nPOP As Int,
> @.dtInv_Date As DateTime,
> @.szInvoiceId As VarChar(21),
> @.szIsCancelled As VarChar(1),
> @.nInvoiceType As TinyInt,
> @.nConsignmentType As Int,
> @.szClubbedId As VarChar(21)
> AS
> -- Problem: Received value is different from what is passed ...
> -- Received- InvoiceType=03 Oct 2006; MST=3; CBB=1; SCBB=0; LH=2;
> ConsignmentType=36528001;
> -- Originally Passed: Says- InvoiceType=1; MST=0; CBB=2; SCBB=1; LH=3;
> ConsignmentType=1;
> Select @.nInvoiceType As "InvoiceType", @.nMBT As "MBT", @.nMST As "MST",
> @.nLH_FL As "LH", @.nCBB As "CBB", @.nSCBB As "SCBB", @.nConsignmentType As
> "ConsignmentType", @.szInvoiceId As "InvoiceId", @.szClubbedId As
"ClubbedId"
> -- Insert Into Consignment_Note
> -- ( LR_NO, LR_DATE, DESTINATION_ID, CONSIGNOR_CODE, CONSIGNEE_CODE,
> -- MBT, MST, CBB, LH_FL, SCBB, MILK_CRT, POP,
> -- INVOICE_NO, INV_DATE, IS_CANCELLED,
> -- INVTYPE, CONSIGNMENT_TYPE, CLUBBED_ID)
> -- Values
> -- ( @.nLR_No,@.dtLR_Date, @.nDestinationId, @.nConsignorId, @.nConsigneeId,
> -- @.nMBT, @.nMST, @.nCBB, @.nLH_FL, @.nSCBB, @.nMilkCaret, @.nPOP,
> -- @.szInvoiceId, @.dtInv_Date, @.szIsCancelled,
> -- @.nInvoiceType, @.nConsignmentType, @.szClubbedId)
> Return 0
> ----
> If any one can trap some problem here, that would be great ...
> Thanks,
> *(Vipul)() ;
>
Best guess...
There is no 'data binding' in ADO. It is pure positional - it looks like you
have your parameters out of order from what the SP is expecting. Take a look
at the LR_DATE.
-ralph|||Thanks a lot Ralph,
You are damn correct in one shot. It worked in first time after the
suggested change.
BTW, does this mean, we are not passing named parameters? The name of
parameter is *not* useful?
Thanks for your help ...
*(Vipul)() ;
"Ralph" <nt_consulting64@.yahoo.com> wrote in message
news:7sGdnbeE3_xPXLrYnZ2dnUVZ_qudnZ2d@.ar
kansas.net...
> "Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
> news:OZlVvvh6GHA.4304@.TK2MSFTNGP03.phx.gbl...
are[vbcol=seagreen]
X=12,[vbcol=seagreen]
code[vbcol=seagreen]
cmdBuilty.CreateParameter("@.RETURN_VALUE",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.nDestinationId",
cmdBuilty.CreateParameter("@.nConsignorId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nConsigneeId",[vbcol=seagreen]
4,[vbcol=seagreen]
cmdBuilty.CreateParameter("@.nInvoiceType",[vbcol=seagreen]
> cmdBuilty.CreateParameter("@.szIsCancelled",
cmdBuilty.CreateParameter("@.szInvoiceId",[vbcol=seagreen]
cmdBuilty.CreateParameter("@.szClubbedId",[vbcol=seagreen]
rsBuilty.Fields(qq)[vbcol=seagreen]
> "ClubbedId"
> Best guess...
> There is no 'data binding' in ADO. It is pure positional - it looks like
you
> have your parameters out of order from what the SP is expecting. Take a
look
> at the LR_DATE.
> -ralph
>|||"Vipul Pathak" <vpathak@.impetus.co.in> wrote in message
news:ue$sCwi6GHA.4732@.TK2MSFTNGP03.phx.gbl...
> Thanks a lot Ralph,
> You are damn correct in one shot. It worked in first time after the
> suggested change.
> BTW, does this mean, we are not passing named parameters? The name of
> parameter is *not* useful?
> Thanks for your help ...
> *(Vipul)() ;
>
<snipped>
Essentially - yes.
That is what is meant by "not bounded". You should note that this is not
necessarily true for all providers or data access libraries. Unfortunately,
usually we have to get 'burnt' to find out if it is supported or not. <g>
Providing named parameters shouldn't be underestimated in any case. As it
makes your intentions very clear and identifying errant parameters becomes
easier. An advantage which becomes very invaluable should you have to
revisit this routine six months from now. <g>
-ralphsql