Insert Into OpenRowSet Syntax

Hi,

I am trying to insert a row into a table  through openrowset. I get syntax  Line 5: Incorrect syntax near '+'.


DECLARE @MaxDate_cdr DateTime
SET @MaxDate_cdr = (select MAX(CreatedDate) from Month10.dbo.cdr(nolock))
INSERT INTO OPENROWSET('SQLNCLI','Server=192.168.150.147;Trusted_Connection=True;',
     'master.dbo.configtable(Country,Type,LastUpdateTime)Values(''India'',''cdr'','+@MaxDate_cdr+')')

Open in new window

SQLM_MAsked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
That's a bit more tricky.  Try this please.

Greg


DECLARE @MaxDate_cdr DateTime, @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT @MaxDate_cdr = MAX(CreatedDate) from cdr_' + convert(varchar(8),getdate(),112)

EXECUTE sp_executesql @query = @SQL, @params = N'@MaxDate_cdr DATETIME OUTPUT', @MaxDate_cdr = @MaxDate_cdr OUTPUT

SET @SQL = 
'INSERT INTO OPENROWSET(''SQLNCLI'',''Server=192.168.150.147;Trusted_Connection=True;'',
 ''master.dbo.configtable(Country,Type,LastUpdateTime)Values(''India'',''cdr'',''' + CONVERT(VARCHAR(50), @MaxDate_cdr) + ''')'')'

EXECUTE sp_executesql @SQL

Open in new window

0
 
JestersGrindCommented:
Try this.  You have to use dynamic SQL to do that.

Greg


DECLARE @MaxDate_cdr DateTime, @SQL NVARCHAR(MAX)

SET @MaxDate_cdr = (select MAX(CreatedDate) from Month10.dbo.cdr(nolock))

SET @SQL = 
'INSERT INTO OPENROWSET(''SQLNCLI'',''Server=192.168.150.147;Trusted_Connection=True;'',
 ''master.dbo.configtable(Country,Type,LastUpdateTime)Values(''India'',''cdr'',''' + @MaxDate_cdr + ''')'')'

EXECUTE sp_executesql @SQL

Open in new window

0
 
SQLM_MConnect With a Mentor Author Commented:
Still i get conversion error. @MaxDate_cdr  is datetime.

And also how to make table name dynamic

SET @MaxDate_cdr = (select MAX(CreatedDate) from Month10.dbo.cdr_YYYYmmDD(nolock)

set  @Date= convert(varchar(8),getdate(),112)
SET @Tablename='cdr_'+ @Date
SET @sql='(select MAX(CreatedDate) from '@Tablename')'

Open in new window

0
 
SQLM_MAuthor Commented:
It works
0
 
SQLM_MAuthor Commented:
it works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.