Invoke-SQLCMD

Question

Invoke-SQLCMD

Bruin

SSCrazy

Points: 2031
More actions
May 30, 2024 at 1:05 pm

#4425061

If my server has dashes seems like that is throwing an error
-serverinstance
at-ts-cblsql is server name
Anyway to get around that?
THanks

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246819 More actions · Answer 1

Try surrounding the server name with double quotes

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bruin SSCrazy Points: 2031 More actions · Answer 2

This is the snippet from script. The serverInstance and Query both use servers that have the Dash(-) in their names.

stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
# Define your SQL Server instance and database
$ServerInstance = 'at-sqltest'
$Database = 'Cost'


# Define your query to read data from your source table
$Query = "SELECT CAST(sqlToExec AS NVARCHAR(MAX)) FROM at-ts-cblsql.CB_SandBox.dbo.crtDelete where ID > 0 and ID < 1000000"




$counter = 1

# Execute the query and store the results
$Data = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query




# Loop through each row of data and insert into the destination table
$Data | ForEach-Object {
$query = $($_.Column1)
$error.clear()


Invoke-SQLCMD -Query $query -ErrorAction Continue -ServerInstance $ServerInstance -Database $Database -OutputSqlErrors $true

Jeffrey Williams SSC Guru Points: 90279 More actions · Answer 3

The problem isn't in the PS portion - it is in the SQL query. You need to enclose the 'linked' server reference in the query - although I have no idea why you would be connecting to one server and then using a linked server to pull data from another server.

Much easier to just connect to the correct server and pull the information down to PS - no need for the double-hop across the network to get there.

If the reason for doing this is because you don't have access directly - then you need to talk to whoever grants that access and get that corrected. You obviously do have access (through the linked server) so you should be able to get direct access.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2031 More actions · Answer 4

The data that's in field sqlToExec is from a Table on a different server that I'm applying too. This is where I'm applying too:

$ServerInstance = 'at-sqltest'

$Database = 'Cost'

I enclosed "Linked Server" still didn't work:

SELECT CAST(sqlToExec AS NVARCHAR(MAX)) FROM at-ts-cblsql.CB_SandBox.dbo.crtDelete where ID > 0 and ID < 1000000"

Doesn't like the dashes ..

Jeffrey Williams SSC Guru Points: 90279 More actions · Answer 5

Your query needs to have the server name quoted - as in: SELECT ... FROM [server-with-dashes].database.schema.table

And - you most likely need to quote the server name in $ServerInstance - as $ServerInstance = "[server-with-dashes]"

Neither of which you have done in your examples.

And I still question why you need to query server [at-sqltest] to run a query that uses a linked server to get data from server [at-ts-cblsql] instead of just pulling the data directly - as in: $ServerInstance = "[at-ts-cblsql]" and $Query = "SELECT ... FROM db.schema.table WHERE ...".

It makes no sense to execute a query on server A to pull data from server B - and it doesn't make sense to use 3-part naming in the query to pull from a different database.

This whole thing needs to be reconsidered.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2031 More actions · Answer 6

The sqltoexec contains statements that need to execute against:

$ServerInstance = 'at-sqltest'

$Database = 'Cost'

It contains Insert Into statements against a Table that exists in the COST(DB) over on another server(at-sqltest). The process that creates these records in table CrtDelete doesn't have access to server(at-sqltest) so I have to load up those requests and then

execute them against:

$ServerInstance = 'at-sqltest'

$Database = 'Cost'

If there is a better way open to suggestions or an example of how it could be done more efficiently.

Thanks.

frederico_fonseca SSCoach Points: 16088 More actions · Answer 7

you have already been advised in your other threads around this process to review how its done and scrap it - you are keeping a wrong approach to a problem you have, and you don't know how to solve it - and keep trying different ways, using different tools, to try and perpetuate a bad design.

your lack of knowledge of SQL and Powershell is one of the main reasons why you are still working on this bad design.

you NEED to hire a consultant that knows what they are doing and heed their advice on how to do things properly.

for others reading this the background is on following threads

https://www.sqlservercentral.com/forums/topic/process-records-in-loop

https://www.sqlservercentral.com/forums/topic/insert-using-table-unput

Jeffrey Williams SSC Guru Points: 90279 More actions · Answer 8

I agree with everything already said - this is a bad design. With that said - let's start with the first part and see if you can get that working:

$sourceInstance = "[at-ts-cblsql]";
$sourceDatabase = "[CB_SandBox]";

$sourceQuery = "SELECT TOP (1) d.ID, sqlToExec = CAST(d.sqlToExec AS nvarchar(max)) FROM dbo.crtDelete d ORDER BY d.ID";

$sqlToExec = Invoke-SqlCmd -ServerInstance $sourceInstance -Database $sourceDatabase -Query $sourceQuery;

$sqlToExec;

This should list out the first row in the crtDelete table - if that works, please provide a screenshot of the results so we can see exactly what is returned from the table.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2031 More actions · Answer 9

Thanks

Using exactly what you have above returns error:

Invoke-SqlCmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server.

<><><><><>

If I change to this:

$sourceInstance = "localhost";

$sourceDatabase = "CB_SandBox";

$sourceQuery = "SELECT TOP (1) d.ID, sqlToExec = CAST(d.sqlToExec AS nvarchar(max)) FROM dbo.crtDelete d ORDER BY d.ID";

$sqlToExec = Invoke-SqlCmd -ServerInstance $sourceInstance -Database $sourceDatabase -Query $sourceQuery;

$sqlToExec;

Returns:

ID sqlToExec

-- ---------

1 DELETE FROM cstSums WHERE PartNbr='816000000' AND PlantCd='CORP' AND CostKey='Standard'

Jeffrey Williams SSC Guru Points: 90279 More actions · Answer 10

So now even more confusion - because you cannot seem to provide the information requested. Why are you able to connect to 'localhost' but not to the actual server in the 'linked server' of the query you are trying to run on some other server?

With that said - the next step is easy (and something I know you should be familiar with - because I have worked with you on similar things in the past).

$sourceInstance = "[at-ts-cblsql]";
$sourceDatabase = "[CB_SandBox]";

$destInstance = "at-sqltest";
$destDatabase = "Cost";

$sourceQuery = "SELECT TOP (1) d.ID, sqlToExec = CAST(d.sqlToExec AS nvarchar(max)) FROM dbo.crtDelete d ORDER BY d.ID";

$sqlToExec = Invoke-SqlCmd -ServerInstance $sourceInstance -Database $sourceDatabase -Query $sourceQuery;

$sqlToExec | {
    $id = $_.ID;
    $sqlStmt = $_.sqlToExec;

    Invoke-SqlCmd -ServerInstance "" -Database "" -Query $sqlStmt;

    $cleanupQuery = "DELETE FROM dbo.crtDelete WHERE ID = $($id)";
    Invoke-SqlCmd -ServerInstance $sourceInstance -Database = $sourceDatabase -Query $cleanupQuery;
}

This is just an example and NOT TO BE USED AS YOUR SOLUTION. This is just to point out how it could be done and to give you some ideas on how to approach solving the problem.

Please review this and learn something - instead of expecting me (and everyone else) to write the code for you.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Perry Whittle SSC Guru Points: 234011 More actions · Answer 11

Bruin wrote:

This is the snippet from script. The serverInstance and Query both use servers that have the Dash(-) in their names.

stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
# Define your SQL Server instance and database
$ServerInstance = 'at-sqltest'
$Database = 'Cost'


# Define your query to read data from your source table
$Query = "SELECT CAST(sqlToExec AS NVARCHAR(MAX)) FROM at-ts-cblsql.CB_SandBox.dbo.crtDelete where ID > 0 and ID < 1000000"




$counter = 1

# Execute the query and store the results
$Data = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query $Query




# Loop through each row of data and insert into the destination table
$Data | ForEach-Object {
$query = $($_.Column1)
$error.clear()


Invoke-SQLCMD -Query $query -ErrorAction Continue -ServerInstance $ServerInstance -Database $Database -OutputSqlErrors $true

That snippet doesnt parse correctly, you're missing a closing brace around $error.clear()

Single quotes are used to literally interpret what is inside the string, double quotes allow for expansion and insertaion of variables, etc

I connect just fine using single quotes on server or db name.

Questions are around the script syntax and what it's trying to achieve

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Bruin SSCrazy Points: 2031 More actions · Answer 12

Good catch, and thanks for your reply.

This is what I'm trying to accomplish.

The purpose of the script processes records from a table which is on a different server than where the DDL statements from SqltoExec resides. The process that creates the DDL has to be on what I call as Source and where I'm trying to apply(table) resides on the Dest Server.

It's getting the Data in the $Query

# Define your query to read data from your source table

$Query = "SELECT CAST(sqlToExec AS NVARCHAR(MAX)) FROM at-ts-cblsql.CB_SandBox.dbo.crtDelete where ID > 0 and ID < 1000000"

Then applying to DEST:

# Define your SQL Server instance and database

$ServerInstance = 'at-sqltest'

$Database = 'Cost'

I'm also trying to build in the error handling to see if any SQL statement fails log it to a file, and the same for all that process without errors .

This may not be the cleanest way, but sometimes to play the hand your dealt.

Perry Whittle SSC Guru Points: 234011 More actions · Answer 13

ok, but as suggested by others why dont you just connect directly to the source first, build your recordset and then connect to the target and perform whatever work needs doing

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Bruin SSCrazy Points: 2031 More actions · Answer 14

In my original Post that's what I was trying to accomplish ..