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)

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

AllanDoesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 + ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 = 'XXX'), 0)

OR

SELECT '' + ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 = 'XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68

ExecuteSQL task fails and I think it should not

I setup my ExecuteSQL task to have a "Single Row" resultset. The query returns no rows. It fails. I don't think it should but then maybe this is consistent with the lookup transform piping rows down the error output if there is not a lookup value returned.

The error returned is

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "Variable": "Single Row result set is specified, but no rows were returned.".
Thanks

Allan
Doesn't sound right to me. If you were getting the MAX of something its perfectly plausible that no results would be returned (if no records in the table).

-Jamie|||Exactly |||Hmm... interesting. Can you open this on BetaPlace, please?

thanks!
ash|||I'm working with the realease version and having the same problem. Any answer to this?
|||

The following discussion pertains to the Execute SQL Task Control flow.

THE SUGGESTIONS I MAKE ARE TO THE PROPERTIES WHICH YOU HAVE TO RIGHT CLICK AND GO TO PROPERTIES ON THE EXECUTE SQL TASK. DO NOT TRY TO CORRECT THIS PROBLEM IN THE EXECUTE SQL TASK EDITOR.

What you need to do is change the "ForceExecutionResults" property to "Success." This will fix your problem.

Secondly you need to be watchful of your MaximumErrorCount property else your system will fail out.

I hope this helps merry christmas.

|||

SELECT 0 +ISNULL((SELECT MAX( COLUMN1 ) FROM TABLE1 WHERE COLUMN2 ='XXX'), 0)

OR

SELECT '' +ISNULL((SELECT COLUMN1 FROM TABLE1 WHERE COLUMN2 ='XXX'), '')

doing so, you will always have a result the task can forward.

Not pretty but preferable vs. the ForceExecutionResults solution i think.

|||

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now. Any feedback about a permanent solution from the SSIS development team?

I also prefer the coded ISNULL workaround over setting the ForceExecutionResults property. You can also use the COALESCE function to the same effect, which I prefer for similar scenarios.

|||

highpockets wrote:

It took me a while to find this thread that describes my problem. It appears that this has never been addressed as I am running SP1 and am still having the problem. I would have thought this was a fairly basic bug and would have been fixed by now.

bug reports should be submitted here: http://connect.microsoft.com/feedback/default.aspx?SiteID=68sql

ExecuteSQL Fails from Variable

I am executing the following statement to setup a database:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

If this is formed inside a script task, assigned to a variable and then executed from the variable it fails. If I past it into the Execute SQL Task as a string it succeeds. Any ideas on where the difference may be? I have set a breakpoint and verified that the variable is being filled in correctly.

I get this error:

SSIS package "BuildTemplates.dtsx" starting.

Error: 0x0 at Create Database: Incorrect syntax near the keyword 'CREATE'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0x0 at Create Database: Incorrect syntax near 'GO'.

Error: 0xC002F210 at Create Database, Execute SQL Task: Executing the query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')

BEGIN

ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DROP DATABASE [WTemplate]

END

GO

CREATE DATABASE WTemplate ON PRIMARY

( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90

GO

EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'

GO

ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE WTemplate SET ANSI_NULLS OFF

GO

ALTER DATABASE WTemplate SET ANSI_PADDING OFF

GO

ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF

GO

ALTER DATABASE WTemplate SET ARITHABORT OFF

GO

ALTER DATABASE WTemplate SET AUTO_CLOSE OFF

GO

ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET AUTO_SHRINK OFF

GO

ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE WTemplate SET RECOVERY FULL

GO

ALTER DATABASE WTemplate SET MULTI_USER

GO

ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM

GO

" failed with the following error: "Incorrect syntax near 'GO'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I suspect what is going on is that the \ in the paths need to be doubled. I did that and the problem appeared to go away.

Thanks,

execute stored procedure

I have setup a user which has execute rights on a stored procedure. The sp is owned by dbo. The user can execute the stored procedure, but it fails, because the stored procedure calls other tables and procedures that the user does not have rights to. Is there a way to allow those procedures to execute without allowing access to everything else for the user I setup? Thanks!

a stored procedure is run under the security context of who ran the Sps. When the sp is run it is executed in the context of who ever run the Sps. if you deny the user on the base table. th sps will fail.

how about a view with an unbroken ownership chain.

how about using functions instead of Sp.

just a wild guess....

|||

Alternatives that you can use in SQL Server 2005:

- sign the procedure and grant permission to access the tables to the certificate used for signing

- use an EXECUTE AS clause for the procedure to make it execute under a different execution context.

For signing, I have an example at: http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

For EXECUTE AS, see documentation at:

http://msdn2.microsoft.com/en-us/library/ms187926.aspx

Thanks
Laurentiu

Execute Stored Proc from OLE DB Destination

is is posible to execute a stored proc (with parameters) from an OLE DB Destination ?

reason we are trying this is cos

our current setup is an OLE DB Command doing the first database update and then passing over to an OLE DB Destination that does the second update. There is error handling coming off the OLE DB Command to a Script component that passes to an OLE DB Destination.

we are having a problem getting the error reporting working from the OLE DB Command - the updates work fine - but not getting any updates of the error database when there is an error.

have got the error reporting fine on the OLE DB Destination.

if we can execute the stored proc from the OLE DB Destination, we will then do both updates via one stored procedure executed by the OLE DB Destination.

thx

m

n.b. think the updating of the error logs from the OLE DB Command used to work - but cant get it to work now ?!!!!?

No, you can't. Why not use another OLE DB command?|||

cos we cant get the error logging to work from the OLE DB Command

and we want to know if any records dont make it

m

|||Well, why can't you get error logging to work? Any, ahem, error messages you can provide us with?|||

phil

thx for this

we couldn't get it to work and could not understand why (followed and re-followed all of the instructions)

today

new member on project, new PC, new SQL installation (SP1), works fine ?

so

am uninstalling SQL from my machine and re-installing it (back to what it was - SP1) - and will see

m