Tsql error

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 6 years and 8 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
jason@sbtc.com.tw
Posts: 26
Last visit: Wed Jan 17, 2024 11:18 pm

Tsql error

Post by jason@sbtc.com.tw »

run my script in the powershell studio 2017 version 5.4.142 will get error.
but run in powershell ISE is normal.
function ExecSQLCommand ()
{
param ( [string] $SqlQuery )
#SQLAuth = "uid={};pwd={}"
## Windows authentication
if ($SQLauth)
{
$authentication = $SQLAuth
}
else
{
$authentication = "Integrated Security=SSPI"
}
$connectionString = "Provider=sqloledb; " +
"Server=$RMDBHost; " +
"Database=$RMDB; " +
"$authentication; "

$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $SqlQuery,$connection
$connection.Open()
# Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet)
$connection.Close()
$dataSet.Tables | Select-Object -Expand Rows

}

#endregion

#檢查SP
#region SP content
$SPtext = "IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'SBTC_RMPrintMonitor')
BEGIN
DROP Procedure SBTC_RMPrintMonitor
END "

#endregion

ExecSQLCommand $SPtext


>> Debugging (ReleaseMailPrintMonitor-20170420.ps1) Script...
>> Platform: V5 64Bit (STA)
ERROR: 找到模稜兩可的符合項目。
ERROR: 位於 C:\Users\jason.SB\OneDrive - 盛博科技股份有限公司\個人\Powershell\PowerShell-Code\TSMC-Print\ReleaseMailPrintMonitor-20170420.ps1:184 字元:1
ERROR: + $connection.Open()
ERROR: + ~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : OperationStopped: (:) [], AmbiguousMatchException
ERROR: + FullyQualifiedErrorId : System.Reflection.AmbiguousMatchException
ERROR:
0
>> Script Ended
User avatar
Alexander Riedel
Posts: 8479
Last visit: Thu Mar 28, 2024 9:29 am
Answers: 19
Been upvoted: 37 times

Re: Tsql error

Post by Alexander Riedel »

Elevation? runspace contamination? restart the ISE to get rid of all preset variables and see if it still runs.
Run the script elevated from PowerShell Studio if you run the ISE elevated (default).
Make sure you choose the correct platform.
These are the common problems when it works in one but not the other.
Alexander Riedel
SAPIEN Technologies, Inc.
User avatar
jason@sbtc.com.tw
Posts: 26
Last visit: Wed Jan 17, 2024 11:18 pm

Re: Tsql error

Post by jason@sbtc.com.tw »

i try use Powershell Studio and Powershell ISE to run same script in same box.
Powershell Studio also show me the same error.
Powershell ISE shom me is normal.

correct platform? have any idea?
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Tsql error

Post by jvierra »

You have come subtle syntax issues that can cause issues. The SQL does not return a table. It will return a number to indicate the results. Trying to load a table will return nothing in all tools.

Code: Select all

function ExecSQLCommand{
	param (
		$SqlQuery,
		$Server,
		$DataBase
	)
	$connStr = "Provider=sqloledb;Integrated Security=SSPI;Server=$Server;Database=$DataBase;"	
	$conn = New-Object System.Data.OleDb.OleDbConnection($connStr)
	$conn.Open()
	$cmd = $conn.CreateCommand()
	$cmd.CommandText = $SqlQuery
	$cmd.ExecuteNonQuert()
}

$SPtext = @'
	IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'SBTC_RMPrintMonitor')
 	BEGIN
 		DROP Procedure SBTC_RMPrintMonitor
 	END
'@
ExecSQLCommand -SqlQuery $SPtext -Server myserver -DataBase MyDB
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Tsql error

Post by jvierra »

I should also note that OleDB to SQLServer does not use that kind of connection string. Uwe the SQLClient instead.
User avatar
jason@sbtc.com.tw
Posts: 26
Last visit: Wed Jan 17, 2024 11:18 pm

Re: Tsql error

Post by jason@sbtc.com.tw »

Thank you for your help.
the problem is fix.
This topic is 6 years and 8 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked