Fix a T-SQL process for MS-SQL

Completed Posted 7 years ago Paid on delivery
Completed Paid on delivery

I am getting an error with this T-SQL process I am writing. I have never worked with OPENROWSET and it shows. It needs to loop though a table from one database to get the databasename,user, pass and serverr info to open that database then do an update. Here is the code and the error.

--start process 104 UNFI PA

DECLARE @instanceName VARCHAR(50)

DECLARE @dbName VARCHAR(50)

DECLARE @dbUserName VARCHAR(50)

DECLARE @dbPassword VARCHAR(250)

DECLARE @vendorId INT

DECLARE @processStart DATETIME

DECLARE @processEnd DATETIME

DECLARE @sqlParent NVARCHAR(4000)

DECLARE @sqlQuery NVARCHAR(4000)

SET @sqlParent =' update product set ProductCount=((select sum(Inventory) from Product_Cost where ProductID=[url removed, login to view])) where ((select count(id) from Product_Cost where productID=[url removed, login to view] ) > 0)

FROM OPENROWSET (''SQLNCLI'', ''Server=<<INSTANCENAME>>;Database=<<DBNAME>>;Uid=<<USERNAME>>;Pwd=<<PASSWORD>>'') d'

DECLARE cDetail CURSOR FAST_FORWARD READ_ONLY

FOR

SELECT DB_IPAddress, [DB_Name], DB_UserName, DB_Pass, VendorID --, process_Start, process_End

FROM

OPENROWSET('SQLNCLI','Server=XXXXXXXXXXXXXXX;Database=XXXXXXXXXXXXXXX;Uid=XXXXXXXXXXXXXXX;XXXXXXXXXXXXXXX',

'select top 1 * from Customer_ImportFeeds where active = 0 and feedid = 104 and vendorid <> 0') as a

OPEN cDetail

FETCH cDetail INTO @instanceName, @dbName, @dbUserName, @dbPassword, @vendorId

WHILE @@FETCH_STATUS = 0 BEGIN

SET @sqlQuery = @sqlParent

SET @sqlQuery = REPLACE(@sqlQuery, '<<INSTANCENAME>>', @instanceName)

SET @sqlQuery = REPLACE(@sqlQuery, '<<DBNAME>>', @dbName)

SET @sqlQuery = REPLACE(@sqlQuery, '<<USERNAME>>', @dbUserName)

SET @sqlQuery = REPLACE(@sqlQuery, '<<PASSWORD>>', @dbPassword)

SET @sqlQuery = REPLACE(@sqlQuery, '<<VENDORID>>', @vendorId)

PRINT @instanceName + ', ' + @dbName + ': '

PRINT @sqlQuery

EXEC sp_executesql @sqlQuery

FETCH cDetail INTO @instanceName, @dbName, @dbUserName, @dbPassword, @vendorId

END

CLOSE cDetail

DEALLOCATE cDetail

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

ERROR:

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'FROM'.

Microsoft SQL Server SQL

Project ID: #12243805

About the project

6 proposals Remote project Active 7 years ago

Awarded to:

$155 USD in 0 days
(1 Review)
2.4

6 freelancers are bidding on average $135 for this job

enragetech

I have carefully gone through your job posting on “Fix a T-SQL process for MS-SQL”. I ’m an expert in web development with more than 5 years experiences. I am expert in skills like php, javascript, hquery, html5 and cs More

$155 USD in 1 day
(0 Reviews)
0.0