Executing a SQL file from within Query Analyser | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Executing a SQL file from within Query Analyser

Is it possible to execute a file from within SQL 2K query analyser without having to pass in the login and password credentials (can one get the current connection login and password credentials?). I have tried the following two methods – both of which did not work. CREATE TABLE #SQL (SQL VARCHAR(8000))
GO
–Gets the data from teh SQL file to Temp table
BULK INSERT #SQL
FROM ‘C:Rik.sql’
WITH (ROWTERMINATOR = ‘
‘)
GO 1) Issue with the following is that 8000 characters really is not long enough
DECLARE @SQL VARCHAR(8000)
DECLARE @SQL_ALL VARCHAR(8000)
SELECT @SQL_ALL = ” declare c_SQL insensitive cursor for
(
select*
from#sql
)
open c_SQL
fetch next from c_SQL into @SQL
while (@@FETCH_STATUS <> -1)
begin
IF (@SQL LIKE ‘GO’)
BEGIN
EXECUTE (@SQL_ALL)
IF @@ERROR <> 0
PRINT ‘ERROR ENCOUNTERED’
SELECT @SQL_ALL = ”
END
ELSE
SELECT @SQL_ALL = @SQL_ALL + CHAR(13) + CHAR(10) + @SQL
fetch next from c_SQL into @SQL
end
close c_SQL
deallocate c_SQL
GO
DROP TABLE #SQL 2) This returns the contents of the file, but does not execute them
EXECUTE (‘EXEC sp_readerrorlog 1, ”C:Rik.sql”’) Any help greatly appreciated,
Regards,
Rik
You can use trusted connection to the server and execute the script using OSQL or ISQL with /E switch. 2) SP_READERRORLOG (undocumented sp) can be used to read the SQL Server error logs from within T-SQL. If you call sp_readerrorlog without any parameters, it will display the current error log. You can go back to previous error logs, by specifying a number as the first parameter. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hello there, Unfortunately I cannot used a trusted connection. …with regards to SP_READERRORLOG, if you pass in "1" as the first parameter, you can use it to read from any file type, not just error logs. Rehards,
Rik
quote:Originally posted by satya You can use trusted connection to the server and execute the script using OSQL or ISQL with /E switch. 2) SP_READERRORLOG (undocumented sp) can be used to read the SQL Server error logs from within T-SQL. If you call sp_readerrorlog without any parameters, it will display the current error log. You can go back to previous error logs, by specifying a number as the first parameter. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

This returns the contents of the file, but does not execute them
EXECUTE (‘EXEC sp_readerrorlog 1, ”C:Rik.sql”’)

True, SP_READERRORLOG just displays out the contents but not execute any functionality from the file. Without trusted connection its not possible to skip username and password if you make any connection to server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>