get an error on a sample code

This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:

USE AdventureWorks;
GO
DECLARE @.tablename sysname
SET @.tablename = N'Person.AddressType'
table_loop:
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.tablename = RTRIM(UPPER(@.tablename))
EXEC ('SELECT ''' + @.tablename + ''' = COUNT(*) FROM '
+ @.tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @.tablename
IF (@.@.FETCH_STATUS <> -1) GOTO table_loop
GO

The errors are:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'PERSON.ADDRESSTYPE'.

Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'tnames_cursor' does not exist

The database is connected. Table Person.AddressType is a part of it.

What is wrong?

Thanks.

Did you get this from the GOTO help topic? This note is in the topic right above the example:

Note:

The tnames_cursor cursor is not defined. This example is for illustration only.

so effectively this example is expected not to work. If you need some specific help on using the GOTO syntax, I would suggest asking in the T-SQL forum, that's where the syntax gurus hang out.

Mike

Get all User Databases

When I execute the sp_databases proc all I get are the databases that the
logged in user has rights to. I want a stored procedure that will return
just the names of all the non-system databases that I can populate a
dropdown list with. I really don't want to add the user to every database
or give them an Administrator role just to see the user table names. Any
help on this would be much appreciated.
JohnHi, John
Use something like this:
SELECT name FROM master.dbo.sysdatabases
WHERE HAS_DBACCESS(name) = 1
AND name NOT IN ('master','tempdb','model','msdb')
ORDER BY name
Razvan|||John,
Try:
SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER','MSDB','TEMPDB','MODEL','DISTR
IBUTOR')
HTH
Jerry
"john wright" <riley_wright@.hotmail.com> wrote in message
news:%23jo41JN0FHA.2212@.TK2MSFTNGP15.phx.gbl...
> When I execute the sp_databases proc all I get are the databases that the
> logged in user has rights to. I want a stored procedure that will return
> just the names of all the non-system databases that I can populate a
> dropdown list with. I really don't want to add the user to every database
> or give them an Administrator role just to see the user table names. Any
> help on this would be much appreciated.
>
> John
>|||>> I want a stored procedure that will return just the names of all the
You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
system table to the the list of all databases. You can avoid the master,
model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
clause to get the list of non-system databases.
Anith|||Great. This works just fine.
John
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ulawJVN0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
> system table to the the list of all databases. You can avoid the master,
> model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
> clause to get the list of non-system databases.
> --
> Anith
>sql

Gererating Dynamic Queries During Run Time

How to Gererating Dynamic Queries During Run Time and execute the results

Thanks in advance

Suresh

You will have to use dynamic SQL.

I highly recommend that you review these articles -they will guide you, and they will explain the cautions and dangers.

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

sql

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating scripts, tables, views, procedures, roles...

Hi...

I'm trying to generate scripts in SQL Server Management Studio 2005.

When I choose 'Script all objects' I get an error when I try to execute it. When I generate the scripts in single files, only tables in one file, only views in one file etc. etc., the execution is succeded.

1) Why do I get an error when I try to execute the script containing tables, views, procedures in one file....?

2) I get an error in the view-file where one column is 'invalid' but I can see it in the view. A generated script should execute succesfully when it is generated one second ago on the same database and so on... Right?

Thanks....

Which exact error do you get ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

sorry, I cant reproduce the errors because my workbuddy has the day off...

But I remember it was one of the views with 2 columns in it, which caused the error: 'Invalid item on view...'

And the sequense in the script is wrong. For example I can see that the script are trying to create a view before the table is created?

|||

You may find that the Database Publishing Wizard offers you more functionality.

Database Publishing Wizard
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

|||Thomas, did you find a solution to your problem?

I have the same problem and Database Publishing Wizard does not work any better.

The CREATE VIEW is placed before the dependant tables in the script.

This ONLY happens when I'm trying to script a SQL Server 2005 Express db.

I run the script wizard in SQL Server Management Studio 2005.

SQL Server 2005 Express: 9.0.3042
SQL Server 2005: 9.0.1399

Thanks
Jonas
|||

Did you use the switch "create dependent objects" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Yes I did.

I've now updated to sp2 (9.0.3042) on the SQL Server 2005 too - no difference. Except that the default value of "create dependant objects" in the script wizard is now false.

I can not include any image of the properties selected, but all are default exept the dependant switch which is true.

The final report start with:

Generate Script Progress

- Determining objects in database '....MDF' that will be scripted. (Success)

- dbo.AllDataView (Success)

- dbo.FindNewRawData (Success)

Which also shows that a View is created first of all objects.

Thanks

Jonas

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale
Use sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas

"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generate SQL script to replicate the content of a table ?

Hi,

In SQL Server 2000, is it possible to generate a SQL script out of a table so that I can take that script file and execute on another server to replicate the table in the first database. It means, the script file will contain all the INSERT statements to insert records into the new table.

I know I can use the "Import and Export" functionality to transfer data between 2 servers; but because I am behind the firewall, I cannot do that.

Any help is really appreciated.

Thanks.http://www.mssqlcity.com/Scripts/ImpExp/DataAsInsertCommand.sql is a tool that creates a script.|||thanks a lot. That's exactly what I'm looking for.