EXECUTE AS question: scripts

Showing posts with label scripts. Show all posts

Thursday, March 29, 2012

Executing SQL scripts from files

Hi, I want to create many files with SQL statements and then I want to write one SQL script file, which executes SQL statement in the other files. How can I do it?

Thanks, Radco

You could do it with Stored Procedures - create a bunch of them - then create one last one, which will execute the others.

If this doesn't do what you want - maybe you could explain your scenario in more detail

|||No it is not what I wanted. I can say to it, that I have a very long script which creates a database for testing, It has more than 2100 lines of SQL statements (mostly INSERT). It begins to be hardly manageable. So I want to split this one script into many files to simplify it. If I want to change something now I need to search 2100 lines of code. If I split it I will only change one smaller file.|||

It is rather not possible to read SQL statement from file and run on SQL server using T-SQL, but you can create your files and next merge them together and run final one. or you can write simple VB application which will read your query and execute them on SQL server.

You can also try to use command line SQL tool osql to run your queries from inside dos batch file.

Thanks

Executing sql scripts from aspx....plz Help!

Hi All,
How to execute the sql scripts (which may be in .sql file or a string) from code behind files?

Thanx
Veeru.SqlCommand.ExecuteNonQuery()

The only problem I can think of is that .SQL scripts generated by Query Analyzer often have the 'GO' keyword in them, which is not a SQL keyword, but rather used for batching in QA.|||True, going to have to parse out GO statement. Some more things needed

SqlCommand comm = New SqlCommand("select * from table", sqlConnection)
comm.CommandType = CommandType.Textsql

Tuesday, March 27, 2012

Executing Multiple Scripts

Hey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly....
any suggestions...
thanks,
jonathanOSQL with a batch file. DTS/SSIS. Scheduled job.
Many ways to do this...|||do you know of a site or book where I can read how to do so....
a good search string for google....|||just giving it away today...

DECLARE @.SQLServer VARCHAR(100)
DECLARE @.Database VARCHAR(100)
DECLARE @.UserName VARCHAR(100)
DECLARE @.Password VARCHAR(100)
DECLARE @.UseWindowsAuthentication BIT
DECLARE @.Path VARCHAR(1000)
DECLARE @.FilePathToSQLFiles VARCHAR(200)

/*################################################# ###########################
If you are unsure of your sql server name, you can use the following
SELECT @.@.SERVERNAME. This should be the sql server where the database resides
that you are updating.
################################################## ###########################*/
SET @.SQLServer = 'MyServer'

/*################################################# ###
@.Database is the name of the database you are updating.
################################################## ####*/
SET @.Database = 'MyDB'

/*################################################# ###
Is the path to the sql files that you wish to execute.
Example FilePath : C:\SQL Scripts\
################################################## ###*/
SET @.FilePathToSQLFiles = 'C:\SQL Scripts\Create Scripts\'

/*################################################# ###################################
If you choose to use windows auth, you do not have to fill in a user name or password,
but your network account has to be a sysadmin on the sql server.
1 = use windows auth
0 = sql auth
################################################## ##################################*/

SET @.UseWindowsAuthentication = 1
SET @.UserName = ''
SET @.Password = ''

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Path

DECLARE cFiles CURSOR FOR
SELECT DISTINCT [SQLFileName]
FROM #SQLFiles
WHERE [SQLFileName] IS NOT NULL AND
[SQLFileName] <> 'NULL'
ORDER BY [SQLFileName]

DECLARE @.vFileName VARCHAR(200)
DECLARE @.vSQLStmt VARCHAR(4000)

OPEN cFiles

IF @.UseWindowsAuthentication = 0
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -U ' + @.UserName + ' -P ' + @.Password + ' -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

IF @.UseWindowsAuthentication = 1
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -E -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

CLOSE cFiles
DEALLOCATE cFiles

Print '################################################# ################################################'
Print 'Please review the log file located at ' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt'
Print '################################################# ################################################'
GO
DROP TABLE #SQLFiles
GO|||Much much appreciated...|||just giving it away today...

Slut

This message is to short

Executing Multi-lines SQL Scripts From Command-line

Hello,

I'm trying to execute a file containing few SQL statements.

CREATE VIEW test1 AS SELECT * FROM table1;
CREATE VIEW test2 AS SELECT * FROM table2;

The standard SQL way is to end a statement with semi-colon.
But doing that,it doesn't work in SQL Server.
After changing ";" to "GO", it works fine.

Is there anyway we can stick to ";" to indicate the end of statement.
I don't want to create scripts which works only in SQL Server.

Please comment.

Thanks in advance.Simon Hayes (sql@.hayes.ch) writes:
> Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by the
> SQL Server client tools as a batch delimiter. The semi-colon is the Oracle
> equivalent, as far as I know.

And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
He has semi-colons all over the place.

Semicolon as a statement terminator is indeed standard SQL, and it is a
pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
Microsoft added semicolons as a optional terminator in SQL7, but it would
constitute a major blow to existing code to make it mandatory. (But if
MS would supply a tool that added all missing semicolons to existing
code, it could be worth the effort.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||James (ehchn1@.hotmail.com) writes:
> I'm trying to execute a file containing few SQL statements.
> CREATE VIEW test1 AS SELECT * FROM table1;
> CREATE VIEW test2 AS SELECT * FROM table2;
> The standard SQL way is to end a statement with semi-colon.
> But doing that,it doesn't work in SQL Server.
> After changing ";" to "GO", it works fine.
> Is there anyway we can stick to ";" to indicate the end of statement.
> I don't want to create scripts which works only in SQL Server.

This is not legal T-SQL:

CREATE VIEW test1 AS SELECT * FROM table1;
go
CREATE VIEW test2 AS SELECT * FROM table2;
go

For some explicable reason ; is not permitted here. (Probably because
CREATE VIEW must be alone in a batch.

However, if you change the batch separator to with the -c option as
Simon Hayes suggested, this works:

CREATE VIEW test1 AS SELECT * FROM table1
;
CREATE VIEW test2 AS SELECT * FROM table2
;

And it still legal in ANSI-compliant engines.

Since the batch-separator must be alone on a line, this solution
can work decently. Of course if a developer for some reason puts a
lone semicolon in the middle of a stored procedure, he effectively
splits that procedure in two.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93D3E36904A63Yazorman@.127.0.0.1...
> Simon Hayes (sql@.hayes.ch) writes:
> > Neither the semi-colon nor GO are 'standard' SQL. GO is recognized by
the
> > SQL Server client tools as a batch delimiter. The semi-colon is the
Oracle
> > equivalent, as far as I know.
> And the ANSI equivalent. Hey, have you never seen Joe Celko's postings?
> He has semi-colons all over the place.
> Semicolon as a statement terminator is indeed standard SQL, and it is a
> pity that Sybase way back in the 1980s settled on a semicolon-free syntax.
> Microsoft added semicolons as a optional terminator in SQL7, but it would
> constitute a major blow to existing code to make it mandatory. (But if
> MS would supply a tool that added all missing semicolons to existing
> code, it could be worth the effort.)
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Interesting, I didn't know there was any standard at all in that area. It's
a good point about Celko's posts, though, given his insistence on
platform-independent code - I guess I should have worked that one out...

Simon

Executing MAXL Scripts in Execute Process Task

Hello,

first of all a few words about our achitecture:

we have a windows 2003 server with sql sever 2005 and connect on this server with 3 clients (Visual Studio) on the Windows 2003 Server there is also an Hyperion Essbase Server installed.

In the Visual Studio I try to execute a batch script which is located on the server. But how i develop that? I have tried to rebuilt the servers File structure and save the Package on the Server. < didn't worked

My Question how can I make a package in which I execute a bat file which is located on the Server without developing it on the Server?

Thank you in advance!

Can you put the batch file in a share, so that you can use a UNC path to reference it? That will work from server or client.

Executing large SQL-scripts through DMO

Hi there!

I'd like to execute some large filebased scripts through the DMO (in javascript).

Any thoughts about the best way to do this?

Regards

Jesper

This code works for me:

Dim oServer
Set FSO = CreateObject("Scripting.FileSystemObject")
Set SQLScript = FSO.OpenTextFile("C:\SQLTricks\SqlScript.sql")

dim sSQL
sSQL = ""
do until SQLScript.atendofstream = True
sSQL = sSQL + SQLScript.readline
loop

Set oServer=CreateObject("SQLDMO.Sqlserver")
oServer.LoginSecure=True
oServer.Connect("MyServer")

set oDatabases=oServer.Databases
for each db in oDatabases
if not (db.Name = "master" or db.Name = "model" or db.Name = "msdb" or db.Name = "tempdb") then
db.ExecuteImmediate sSQL, SQLDMOExec_ContinueOnError
end if
next
oServer.Disconnect

set oServer=Nothing

Note that it'll run the script against every database that isn't a system database. Modify to your own needs.

Friday, March 23, 2012

Executing a practice script

Im trying to execute four practice scripts for a SQL fundamentals class
setup. The first three scripts run fine. The last script gives me the
following errors:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'Orders', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'Product', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'Customer', because it does not exist in the system
catalog.
Server: Msg 128, Level 15, State 1, Line 8
The name 'C001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'C002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'C003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'C004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'C005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 13
The name 'C006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 14
The name 'C007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 15
The name 'C008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 3
The name 'P010' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 4
The name 'P009' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 5
The name 'P008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 6
The name 'P007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 7
The name 'P006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 8
The name 'P005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'P004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'P003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'P002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'P001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 3
The name 'O001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 4
The name 'O002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 5
The name 'O003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 6
The name 'O004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 7
The name 'O005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 8
The name 'O006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'O007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'O008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'O009' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'O010' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
When I connect from the client, I can connect to the database but no
information is available in my select statement.www.aspfaq.com/5006
Based on the info you posted, it's doubtful anyone could tell you any more
than the error messages you already received...
"Buffalotech" <Buffalotech@.discussions.microsoft.com> wrote in message
news:354B7594-309E-4E1D-8C88-48288225D0DA@.microsoft.com...
> Im trying to execute four practice scripts for a SQL fundamentals class
> setup. The first three scripts run fine. The last script gives me the
> following errors:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'Orders', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Product', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Customer', because it does not exist in the system
> catalog.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'C001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'C002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'C003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'C004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'C005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 13
> The name 'C006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 14
> The name 'C007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 15
> The name 'C008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'P010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'P009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'P008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'P007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'P006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'P005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'P004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'P003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'P002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'P001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'O001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'O002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'O003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'O004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'O005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'O006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'O007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'O008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'O009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'O010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> When I connect from the client, I can connect to the database but no
> information is available in my select statement.|||Hi
At a guess there are a several possible reasons for this!
1. The script is wrong in that assumes the objects exist before trying to
drop them. This may imply that you have either not executed all the scripts,
or have not run them in the correct order.
2. You are running the script against the wrong database.
3. A combination of the above as the script uses the USE command to set the
database, but you have created a different database to the one documented as
there was already one that existed with the given name!
John
"Buffalotech" <Buffalotech@.discussions.microsoft.com> wrote in message
news:354B7594-309E-4E1D-8C88-48288225D0DA@.microsoft.com...
> Im trying to execute four practice scripts for a SQL fundamentals class
> setup. The first three scripts run fine. The last script gives me the
> following errors:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'Orders', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Product', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Customer', because it does not exist in the system
> catalog.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'C001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'C002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'C003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'C004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'C005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 13
> The name 'C006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 14
> The name 'C007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 15
> The name 'C008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'P010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'P009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'P008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'P007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'P006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'P005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'P004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'P003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'P002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'P001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'O001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'O002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'O003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'O004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'O005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'O006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'O007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'O008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'O009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'O010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> When I connect from the client, I can connect to the database but no
> information is available in my select statement.|||On Sun, 27 Mar 2005 18:55:02 -0800, Buffalotech wrote:
(snip)
>Server: Msg 128, Level 15, State 1, Line 8
>The name 'C001' is not permitted in this context. Only constants,
>expressions, or variables allowed here. Column names are not permitted.
(snip)
Another wild guess: this error is often caused because string constants
are enclosed in "double quotes" instead of 'single quotes'.
The good way to solve this is to open the script and replace all quotes.
The quikc and ugly kludge is to force non-standard interpretation of
quotes. Look up SET QUOTED_IDENTIFIER in BOL.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Executing a practice script

Im trying to execute four practice scripts for a SQL fundamentals class
setup. The first three scripts run fine. The last script gives me the
following errors:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'Orders', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'Product', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'Customer', because it does not exist in the system
catalog.
Server: Msg 128, Level 15, State 1, Line 8
The name 'C001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'C002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'C003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'C004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'C005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 13
The name 'C006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 14
The name 'C007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 15
The name 'C008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 3
The name 'P010' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 4
The name 'P009' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 5
The name 'P008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 6
The name 'P007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 7
The name 'P006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 8
The name 'P005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'P004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'P003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'P002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'P001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 3
The name 'O001' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 4
The name 'O002' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 5
The name 'O003' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 6
The name 'O004' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 7
The name 'O005' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 8
The name 'O006' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 9
The name 'O007' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 10
The name 'O008' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 11
The name 'O009' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
Server: Msg 128, Level 15, State 1, Line 12
The name 'O010' is not permitted in this context. Only constants,
expressions, or variables allowed here. Column names are not permitted.
When I connect from the client, I can connect to the database but no
information is available in my select statement.
www.aspfaq.com/5006
Based on the info you posted, it's doubtful anyone could tell you any more
than the error messages you already received...
"Buffalotech" <Buffalotech@.discussions.microsoft.com> wrote in message
news:354B7594-309E-4E1D-8C88-48288225D0DA@.microsoft.com...
> Im trying to execute four practice scripts for a SQL fundamentals class
> setup. The first three scripts run fine. The last script gives me the
> following errors:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'Orders', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Product', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Customer', because it does not exist in the system
> catalog.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'C001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'C002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'C003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'C004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'C005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 13
> The name 'C006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 14
> The name 'C007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 15
> The name 'C008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'P010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'P009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'P008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'P007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'P006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'P005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'P004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'P003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'P002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'P001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'O001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'O002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'O003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'O004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'O005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'O006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'O007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'O008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'O009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'O010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> When I connect from the client, I can connect to the database but no
> information is available in my select statement.
|||Hi
At a guess there are a several possible reasons for this!
1. The script is wrong in that assumes the objects exist before trying to
drop them. This may imply that you have either not executed all the scripts,
or have not run them in the correct order.
2. You are running the script against the wrong database.
3. A combination of the above as the script uses the USE command to set the
database, but you have created a different database to the one documented as
there was already one that existed with the given name!
John
"Buffalotech" <Buffalotech@.discussions.microsoft.com> wrote in message
news:354B7594-309E-4E1D-8C88-48288225D0DA@.microsoft.com...
> Im trying to execute four practice scripts for a SQL fundamentals class
> setup. The first three scripts run fine. The last script gives me the
> following errors:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'Orders', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Product', because it does not exist in the system
> catalog.
> Server: Msg 3701, Level 11, State 5, Line 2
> Cannot drop the table 'Customer', because it does not exist in the system
> catalog.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'C001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'C002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'C003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'C004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'C005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 13
> The name 'C006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 14
> The name 'C007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 15
> The name 'C008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'P010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'P009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'P008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'P007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'P006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'P005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'P004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'P003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'P002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'P001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 3
> The name 'O001' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 4
> The name 'O002' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 5
> The name 'O003' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 6
> The name 'O004' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 7
> The name 'O005' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 8
> The name 'O006' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 9
> The name 'O007' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 10
> The name 'O008' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 11
> The name 'O009' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> Server: Msg 128, Level 15, State 1, Line 12
> The name 'O010' is not permitted in this context. Only constants,
> expressions, or variables allowed here. Column names are not permitted.
> When I connect from the client, I can connect to the database but no
> information is available in my select statement.
|||On Sun, 27 Mar 2005 18:55:02 -0800, Buffalotech wrote:
(snip)
>Server: Msg 128, Level 15, State 1, Line 8
>The name 'C001' is not permitted in this context. Only constants,
>expressions, or variables allowed here. Column names are not permitted.
(snip)
Another wild guess: this error is often caused because string constants
are enclosed in "double quotes" instead of 'single quotes'.
The good way to solve this is to open the script and replace all quotes.
The quikc and ugly kludge is to force non-standard interpretation of
quotes. Look up SET QUOTED_IDENTIFIER in BOL.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Executing a practice script

Wednesday, March 21, 2012

Executing 2 SQL script files

I have 2 Transact-SQL scripts saved in 2 files :
ClearData - HR.sql (DDL and DML)
ClearData - Others.sql (only DDL)
I want to write a Transact-SQL script which can executes the above 2 files.
And I want to save it in a seperate file (.sql extension). How can I do
that?
Thanks
RizwanI know I can do it if I created 2 stored procedures for "ClearData - HR.sql"
and "ClearData - Others.sql" respectively and then calling these stored
procedures from my script.
But what I would love to do is call a sql script file from another sql
script file and execute it. Any input?
Thanks
Rizwan
"Rizwan" <hussains@.pendylum.com> wrote in message
news:2CT4e.16736$Fy3.977058@.news20.bellglobal.com...
> I have 2 Transact-SQL scripts saved in 2 files :
> ClearData - HR.sql (DDL and DML)
> ClearData - Others.sql (only DDL)
> I want to write a Transact-SQL script which can executes the above 2
files.
> And I want to save it in a seperate file (.sql extension). How can I do
> that?
> Thanks
> Rizwan
>|||Hi
I am not sure exactly what you require but there is the :r command when
running osql interactively see
http://msdn.microsoft.com/library/d...br />
1wxl.asp
This may also help:
http://groups-beta.google.com/group...
d4ca6efcd9ac3
John
"Rizwan" wrote:

> I know I can do it if I created 2 stored procedures for "ClearData - HR.sq
l"
> and "ClearData - Others.sql" respectively and then calling these stored
> procedures from my script.
> But what I would love to do is call a sql script file from another sql
> script file and execute it. Any input?
> Thanks
> Rizwan
>
> "Rizwan" <hussains@.pendylum.com> wrote in message
> news:2CT4e.16736$Fy3.977058@.news20.bellglobal.com...
> files.
>
>sql

Wednesday, March 7, 2012

Execute sql scripts saved in a text column of a table

How can I execute sql scripts saved in a text column of a table?
Thanks,
PeterUse dynamic sql statement
EXAMPLE (using Northwind database)
DECLARE @.vSQL VARCHAR(1000), @.numrows INT
SELECT @.numrows = 25
SELECT @.vSQL = 'SELECT TOP ' + CONVERT(VARCHAR, @.numrows) + ' * FROM
Products ORDER BY ProductName'
EXECUTE(@.vSQL)
This evaluates to:
SELECT TOP 25 * FROM Products ORDER BY ProductName
Good luck and hope it helps!
"Peter" wrote:

> How can I execute sql scripts saved in a text column of a table?
>
> Thanks,
> Peter|||Hi,
Go for a cursor that will loop through all the rows and get the text field
in a variable and use dynamic SQL (EXEC or sp_Executesql )
But again, is it a text field or varchar field?
If its text and can span over 8000 characters.
Then you will have to split it to 8000 char length strings, say str1, str2
then you can use
exec(str1 +str2)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Peter" wrote:

> How can I execute sql scripts saved in a text column of a table?
>
> Thanks,
> Peter|||If one has a query exceeding 8000 characters, using dynamic SQL is the least
of the worries.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:85C96AC5-6DD2-44B8-B0EE-79737C41C94B@.microsoft.com...
> Hi,
> Go for a cursor that will loop through all the rows and get the text field
> in a variable and use dynamic SQL (EXEC or sp_Executesql )
> But again, is it a text field or varchar field?
> If its text and can span over 8000 characters.
> Then you will have to split it to 8000 char length strings, say str1, str2
> then you can use
> exec(str1 +str2)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Peter" wrote:
>

Execute sp_OAMethod Fails

Hi,

I am struggeling with the execution of DTS packages from within T-SQL scripts on one of many SQL server instances on my Network (SQL 7.0 and SQL 2000 on Win 2000). The DTS Packages will execute, but one of the Tasks will always fail for some reason. The Tasks that fails are:

Execute SQL Task
Transform Data Task

I am using the OLE stored procedures, because DTSRUN on SQL 7.0 does not support passing global variables to the DTS package.

All the global variables that are sent to the DTS Packages are validated under execution and causes no problems to my knowledge. The DTS packages executes with no problems from the Enterprise Manager.

The computer where I get this strange behaviour is named KL-PC979. The "-" character is not included on any othe machine on the nettwork and I suspects that It is the cause for all my troubles.

Here is how I load:

'LoadFromSQLServer("KL-PC979", "MyUser", "UserPWD", 0, "", , , "MY_PKG")'

have tried both "." AND "local" for the SERVERNAME, but no luck.

The error logs gives me this information:

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step 'DTSStep_DTSExecuteSQLTask_3' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:No value given for one or more required parameters.
Step Error code: 80040E10
Step Error Help File:
Step Error Help Context ID:0

I have not been able to find some useful information about this, but hopfully there is someone out there...

ThanksRE:
The computer where I get this strange behaviour is named KL-PC979. The "-" character is not included on any othe machine on the network and I suspect that it is the cause for all my troubles.

Question I
Perhaps, and perhaps not; one might consider testing to see if an execution using an alias (without a '-') succeeds (or at least, does not fail at the same point / in the same manner)?

{An alias would be expected to address the issue if indeed the '-' in the name is the source of the issue (not tested).}

RE:
one of the Tasks will always fail for some reason. The Tasks that fails are:
Execute SQL Task
Transform Data Task

The error logs give me this information:

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step 'DTSStep_DTSExecuteSQLTask_2' succeeded
Step 'DTSStep_DTSExecuteSQLTask_3' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:No value given for one or more required parameters.
Step Error code: 80040E10

Question II
This suggests a missing parameter; what is in the Execute SQL Task?

Question III
Does the tsql in the the Execute SQL Task run from QA, (if not, consider posting it with the applicable ddl, sample data, etc., etc.)?|||Answer to Question I
I tried this through the Client Network Utility, but it did not work. Is it possible to make an alias on the server side?

Answer to Question II
The SQL Tasks execute a single procedure with a couple of parameters (globals). I have validated the globals in earlier Tasks. The procedures either selects or inserts a smal amount of data from or to a table. Query Timeout is set to 0.

Answer to Question III
The Procedure runs without problems from QA on this machine.

Any ideas?

essal|||RE:
Answer to Question I
Q1 I tried this through the Client Network Utility, but it did not work. Is it possible to make an alias on the server side?
A1 The approach is correct the Client Network Utility is the correct tool to make an alias (generally on clients, but the Client Network Utility works on servers in a similar {client} fashion).

RE:
Answer to Question II
The SQL Tasks execute a single procedure with a couple of parameters (globals). I have validated the globals in earlier Tasks. The procedures either selects or inserts a small amount of data from or to a table. Query Timeout is set to 0.

Question IV
If a simple tsql statement is substituted e.g.(Select 'TestResult' As 'TestOfTask') for the SQL Tasks (that seem to be failing per the error message content) are the same errors still returned?

RE:
Q2 Any ideas?
A2 Yes. But unfortunately, few good ones. :(

Idea 1:
If not currently using named pipes:
a enable the named pipe protocol with a default pipe name e.g.(\\.\pipe\NoDash\query)
b Create another alias (named pipes) to that pipe
c Using the alias name attempt to reproduce the KL-PC979 error

Idea 2:
Since both "." AND "local" have been tried:
a try "(local)" and "(.)"

Idea 3:
Install a development (test) server with:
a A similar dashed '-' name e.g.(KL-PC979Test)
b Make the test server, KL-PC979Test otherwise identical to KL-PC979
c Reproduce the KL-PC979 error on the KL-PC979Test server
d Change the name of the test server to KLPC979Test
e Attempt to reproduce the KL-PC979 error on the renamed KLPC979Test server
f If e fails at least the issue is definitively identified (along with a potentially painful remedy)|||Question IV
Through this simple test I managed to determine that it was the globals that tricked me. I tried, as You mentioned, to use a simple hardcoded Query, and the Package executed without errors. The reason I was sure the globals where read correctly is because the Package write them to a file using VBscript during execution.

The SQL Tasks where I discovered the problem was calling procedures like this
sp_MyProc ?, ?, ?, ?
and I had done the Parameter mapping against the Global variables.

This did not work in my enviorment when I was calling the Package through OLE. (??)

Since the ActiveX Script Task could read and write the globals correctly I removed the question marks (?) and build up the query from such a Task. Works perfect.

Thanks|||RE:
Question IV
Through this simple test I managed to determine that it was the globals that tricked me. I tried, as You mentioned, to use a simple hardcoded Query, and the Package executed without errors. The reason I was sure the globals where read correctly is because the Package write them to a file using VBscript during execution.

The SQL Tasks where I discovered the problem was calling procedures like this
sp_MyProc ?, ?, ?, ?
and I had done the Parameter mapping against the Global variables.

This did not work in my enviorment when I was calling the Package through OLE. (??)

Since the ActiveX Script Task could read and write the globals correctly I removed the question marks (?) and build up the query from such a Task. Works perfect.

Thanks

You are quite welcome.

I am pleased that Question IV at least spared you from further fruitless efforts (especially Idea 3).

Sunday, February 19, 2012

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM ########################################
#####
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM ########################################
#####
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM ########################################
#####
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txt
Joe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.
|||I knew there had to be an easier way. I was scripting 35 scripts at a
time. Between opening and closing files and the occasional, "Didn't I
run that already?" I knew it was time to find a better way.
Glad to help,
Joe
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Friday, February 17, 2012

Execute Multiple Scripts from one directory

I got tired of having to execute multiple scripts one at a time from
my testing server to my production server. So I decided to create a
batch routine that utilizes the command line routine OSQL. All you
have to do to use it is place the batch file in a directory containing
your SQL scripts and execute the batch file. I have placed in the
command the argument to print a result file for each sql script. If
you need your SQL scripts run in a particular order simply rename them
accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
enjoy
REM #############################################
REM Author: Joe Ocampo
REM Date: 04/17/2004
REM Version: 1.0.1
REM #############################################
REM You must execute the script from the SQL server itself
REM and you must use the SA login or the DBO login of the
REM target database.
REM #############################################
SET login=sa
SET password=password
SET server=local
SET database=pubs
for %%a in (*.sql) do osql -d %database% -U %login% -P %password% -i
%%a -o %%a_result.txtJoe wrote:
> I got tired of having to execute multiple scripts one at a time from
> my testing server to my production server. So I decided to create a
> batch routine that utilizes the command line routine OSQL. All you
> have to do to use it is place the batch file in a directory containing
> your SQL scripts and execute the batch file. I have placed in the
> command the argument to print a result file for each sql script. If
> you need your SQL scripts run in a particular order simply rename them
> accordingly. i.e. 001_updatecustomer.sql, 002_update_sp.sql
Exceedingly simple, but incredibly useful, and something that I was going to
have to write myself pretty soon - thanks.
John.

Execute DTS Package from Asp.net

Hi All,
How we can call and Execute DTS Scripts From Asp.net.
Can any one pls give me some help.
Regards
VAsu

There are many explanations of this on the Web. A simple Google search yielded these.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://www.sqlteam.com/item.asp?ItemID=19595

Thursday, March 29, 2012

Executing SQL scripts from files

Executing sql scripts from aspx....plz Help!

Tuesday, March 27, 2012

Executing Multiple Scripts

Executing Multi-lines SQL Scripts From Command-line

Executing MAXL Scripts in Execute Process Task

Executing large SQL-scripts through DMO

Friday, March 23, 2012

Executing a practice script

Executing a practice script

Executing a practice script

Wednesday, March 21, 2012

Executing 2 SQL script files

Wednesday, March 7, 2012

Execute sql scripts saved in a text column of a table

Execute sp_OAMethod Fails

Sunday, February 19, 2012

Execute Multiple Scripts from one directory

Execute Multiple Scripts from one directory

Friday, February 17, 2012

Execute Multiple Scripts from one directory

Execute DTS Package from Asp.net

EXECUTE AS question

Blog Archive

About Me