Fix a T-SQL process for MS-SQL
$30-250 USD
Plačilo ob prevzemu
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'.
ID projekta: #12243805
Več o projektu
Dodeljeno:
6 freelancerjev ponuja v povprečju za $135 na tem delu
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 Več