0

I am exploring powershell to run the following SQL script stored in my machine with name "TestSQLScript.sql"

USE TestDB
GO
SELECT TOP (1000) [DocId]
      ,[DocumentInfo]
  FROM [TestDB].[dbo].[Docs]

INSERT INTO WorkDocs 
(       [DocId]
      ,[DocumentInfo])
SELECT [DocId]
      ,[DocumentInfo]
  FROM [TestDB].[dbo].[Docs]

SELECT TOP (1000) [DocId]
      ,[DocumentInfo]
  FROM [TestDB].[dbo].[WorkDocs]

When I use the following command it executed well.

invoke-sqlcmd -inputfile "D:\TestSQLScript.sql" -serverinstance "MYPC\MSSQLSERVER2019" -Username "sa" -Password "***" -database "master"

When I am running the following it through error while accessing table WorkDocs.

invoke-DbaQuery -SQLInstance "MYPC\MSSQLSERVER2019" -sqlcredential $Cred -File "D:\TestSQLScript.sql" -database "master"

In the initial finding it is noticed that tables referenced as [TestDB].[dbo].[WorkDocs] are accessible, and table referenced without [TestDB].[dbo] is not accessible at Insert statement, while I have already used the database reference on top in the SQL file "TestSQLScript.sql"

USE TestDB
GO

I can not reference all the tables with [TestDB].[dbo] as I have hundreds of script and there are multiple database references in each SQL script. Can you suggest how can I resolve this?

In addition to this How can I get the result count for each SQL query in the file. I have Multiple Select, Insert, Update and truncate SQL command in one SQL file and I want to log each execution result. Please refer to below for more.

  1. For Select Statements - 1000 Records fetched
  2. For Insert Statements - 100 rows inserted into 'Table Name'
  3. For Update Statements - 50 rows affected in 'Table Name'
  4. For Truncate Statements - 'Table Name' has been truncated.
Thom A
  • 88,727
  • 11
  • 45
  • 75
Avtansh
  • 81
  • 1
  • 1
  • 9
  • FYI, there is no SQL Server 2018; the latest release is SQL Server 2019 and the one prior to that was 2017. What version of SQL Server are you *really* using? – Thom A May 18 '22 at 11:11
  • [Edit] the question and show the exact error message. What's more, you are using different authentication methods, so make sure the credentials are the same. – vonPryz May 18 '22 at 11:44
  • _I can not reference ..._ Yes you can - you choose not to. This is a problem that was long in making - made worse by writing "hundreds of scripts" (honestly who does that?) and using inconsistent code practices. In general, the **connection** should determine the database to use for your object references. Start changing them and put everything into source control. Perhaps start thinking about a more organized approach to scripting generally. Lastly - one post, one question please. – SMor May 18 '22 at 12:08
  • @Larnu You are correct, Here I mentioned SSMS version instead of SQL server. Here is the SQL server version - Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 10.0 (Build 19043: ) – Avtansh May 19 '22 at 06:25
  • @vonPryz, Thank you for the reply, The credentials are same, the issue is with the database reference. If I put DB reference like [TestDB].[dbo].[WorkDocs] in SELECT, UPDATE or INSERT statements. it works, but I want this to pick from SQL file with the "Use [TestDB] GO" statement. – Avtansh May 19 '22 at 06:30
  • @Smor, Appreciate your input. I am doing data conversion using ETL. I have SourceDb and DestinationDB. These hundreds of scripts are responsible for extracting data from source database, transform it and then push it into the destination database. I was doing it manually earlier and now I want to automate this process. That's why these scripts can run SQL statements to different database within the same script file. That was working for me using 'invoke-sqlcmd' but it could not take credentials from cred file as it is configured for 'invoke-DbaQuery'. – Avtansh May 19 '22 at 07:41

0 Answers0