Completed

Fix a T-SQL process for MS-SQL

This project was successfully completed by anamaria1979 for $155 USD in a day.

Get free quotes for a project like this
Employer working
Project Budget
$30 - $250 USD
Completed In
1 day
Total Bids
6
Project Description

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'.

Completed by:
Skills Required

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online