• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1510
  • Last Modified:

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

0
SQLM_M
Asked:
SQLM_M
  • 3
  • 2
2 Solutions
 
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_MAuthor 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
 
JestersGrindCommented:
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
 
SQLM_MAuthor Commented:
It works
0
 
SQLM_MAuthor Commented:
it works
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now