How can I change Table Scripting Options defaults?

I'm using SQL Server 2000.
In Enterprise Manager, if I right-click a table then: All Tasks/ Generate
SQL Script /Options tab, there are Table Scripting Options. How can I
change the defaults?
For example, I would like "Script PRIMARY Keys, ..." to be checked by
default.
Thanx.This is not configurable, I'm afraid.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <vincent.rineer@.capbluecross.com> wrote in message
news:%23rMrMwbSFHA.3972@.TK2MSFTNGP14.phx.gbl...
> I'm using SQL Server 2000.
> In Enterprise Manager, if I right-click a table then: All Tasks/ Generate
> SQL Script /Options tab, there are Table Scripting Options. How can I
> change the defaults?
> For example, I would like "Script PRIMARY Keys, ..." to be checked by
> default.
> Thanx.
>

How can I change Table Scripting Options defaults?

I'm using SQL Server 2000.
In Enterprise Manager, if I right-click a table then: All Tasks/ Generate
SQL Script /Options tab, there are Table Scripting Options. How can I
change the defaults?
For example, I would like "Script PRIMARY Keys, ..." to be checked by
default.
Thanx.
This is not configurable, I'm afraid.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <vincent.rineer@.capbluecross.com> wrote in message
news:%23rMrMwbSFHA.3972@.TK2MSFTNGP14.phx.gbl...
> I'm using SQL Server 2000.
> In Enterprise Manager, if I right-click a table then: All Tasks/ Generate
> SQL Script /Options tab, there are Table Scripting Options. How can I
> change the defaults?
> For example, I would like "Script PRIMARY Keys, ..." to be checked by
> default.
> Thanx.
>
sql

How can I change Table Scripting Options defaults?

I'm using SQL Server 2000.
In Enterprise Manager, if I right-click a table then: All Tasks/ Generate
SQL Script /Options tab, there are Table Scripting Options. How can I
change the defaults?
For example, I would like "Script PRIMARY Keys, ..." to be checked by
default.
Thanx.This is not configurable, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"msnews.microsoft.com" <vincent.rineer@.capbluecross.com> wrote in message
news:%23rMrMwbSFHA.3972@.TK2MSFTNGP14.phx.gbl...
> I'm using SQL Server 2000.
> In Enterprise Manager, if I right-click a table then: All Tasks/ Generate
> SQL Script /Options tab, there are Table Scripting Options. How can I
> change the defaults?
> For example, I would like "Script PRIMARY Keys, ..." to be checked by
> default.
> Thanx.
>

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

Hi,
I have a sql server 2005 .
But my old customers have a sql 2000 database.

I want to create table script, and upgrade my custumers tables..
i script table in sql 2005 and run at sql 2000. but it doesnt work?

for example: sql 2005 create this 1uery on the test table:

"USE [test]
GO
/****** Object: Table [dbo].[TBL_TEST] Script Date: 11/24/2006 13:21:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL,
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]"

then i run to sql 2000, but it doesnt work.
but when i convert manually this query, it works.
"
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[TBL_TEST] WITH NOCHECK ADD
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
"

SO i must convert sql 2000 format to this query. but i have a lot of table and always add new table . i could'nt change always.

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

You will find that there are some changes in the TSQL generated that will not work on sql 2000. But you should be able to have your database on the sql 2005 box run in sql 2000 compatability mode, then export the scripts... it should work then. You can change this setting in the Database properties.

|||thanks .
but my 2005 database currently run on compatability mode:Sql server 2000 (80)
so it couldn't work..|||You might try a free app I wrote called scriptdb. It will script out all objects in your database, with a separate file for each. It's useful for getting all your objects into source control if they aren't already. The source code is freely available. get it here:
http://www.elsasoft.org/tools.htm
hope it helps!
|||I have used ScriptDB which is an useful one in this case, I can second Jezemine's reference.|||

I had a similar situation,

here is my SOLUTION.

If you are useing Microsoft SQL Server Management Studio Express

A. Select your Database in the Object Explorer

B. Right Click, to get your context menu and choose: Task -> Generate Scripts... ( this is the only one I know if this will work on )

C. Click the Next button

D. Select your DB from the list

E. About Halfway down the options list set "Script for Server Version" to "SQL Server 2000"

F. Next...

G. Select your DB objects and generate your scripts

the script will work in SQL 2000

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

Hi,
I have a sql server 2005 .
But my old customers have a sql 2000 database.

I want to create table script, and upgrade my custumers tables..
i script table in sql 2005 and run at sql 2000. but it doesnt work?

for example: sql 2005 create this 1uery on the test table:

"USE [test]
GO
/****** Object: Table [dbo].[TBL_TEST] Script Date: 11/24/2006 13:21:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL,
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]"

then i run to sql 2000, but it doesnt work.
but when i convert manually this query, it works.
"
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[TBL_TEST] WITH NOCHECK ADD
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
"

SO i must convert sql 2000 format to this query. but i have a lot of table and always add new table . i could'nt change always.

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

You will find that there are some changes in the TSQL generated that will not work on sql 2000. But you should be able to have your database on the sql 2005 box run in sql 2000 compatability mode, then export the scripts... it should work then. You can change this setting in the Database properties.

|||thanks .
but my 2005 database currently run on compatability mode:Sql server 2000 (80)
so it couldn't work..|||You might try a free app I wrote called scriptdb. It will script out all objects in your database, with a separate file for each. It's useful for getting all your objects into source control if they aren't already. The source code is freely available. get it here:
http://www.elsasoft.org/tools.htm
hope it helps!
|||I have used ScriptDB which is an useful one in this case, I can second Jezemine's reference.|||

I had a similar situation,

here is my SOLUTION.

If you are useing Microsoft SQL Server Management Studio Express

A. Select your Database in the Object Explorer

B. Right Click, to get your context menu and choose: Task -> Generate Scripts... ( this is the only one I know if this will work on )

C. Click the Next button

D. Select your DB from the list

E. About Halfway down the options list set "Script for Server Version" to "SQL Server 2000"

F. Next...

G. Select your DB objects and generate your scripts

the script will work in SQL 2000

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

Hi,
I have a sql server 2005 .
But my old customers have a sql 2000 database.

I want to create table script, and upgrade my custumers tables..
i script table in sql 2005 and run at sql 2000. but it doesnt work?

for example: sql 2005 create this 1uery on the test table:

"USE [test]
GO
/****** Object: Table [dbo].[TBL_TEST] Script Date: 11/24/2006 13:21:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KOLON1] [nchar](10) NULL,
[KOLON2] [nchar](10) NULL,
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]"

then i run to sql 2000, but it doesnt work.
but when i convert manually this query, it works.
"
CREATE TABLE [dbo].[TBL_TEST](

[ID] [int] IDENTITY(1,1) NOT NULL,

[KOLON1] [nchar](10) NULL,

[KOLON2] [nchar](10) NULL

) ON [PRIMARY]

ALTER TABLE [dbo].[TBL_TEST] WITH NOCHECK ADD
CONSTRAINT [PK_TBL_TEST] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
"

SO i must convert sql 2000 format to this query. but i have a lot of table and always add new table . i could'nt change always.

How can i "script table as" at sql server 2005 to sql server 2000 compatibility?

You will find that there are some changes in the TSQL generated that will not work on sql 2000. But you should be able to have your database on the sql 2005 box run in sql 2000 compatability mode, then export the scripts... it should work then. You can change this setting in the Database properties.

|||thanks .
but my 2005 database currently run on compatability mode:Sql server 2000 (80)
so it couldn't work..|||You might try a free app I wrote called scriptdb. It will script out all objects in your database, with a separate file for each. It's useful for getting all your objects into source control if they aren't already. The source code is freely available. get it here:
http://www.elsasoft.org/tools.htm
hope it helps!
|||I have used ScriptDB which is an useful one in this case, I can second Jezemine's reference.|||

I had a similar situation,

here is my SOLUTION.

If you are useing Microsoft SQL Server Management Studio Express

A. Select your Database in the Object Explorer

B. Right Click, to get your context menu and choose: Task -> Generate Scripts... ( this is the only one I know if this will work on )

C. Click the Next button

D. Select your DB from the list

E. About Halfway down the options list set "Script for Server Version" to "SQL Server 2000"

F. Next...

G. Select your DB objects and generate your scripts

the script will work in SQL 2000

how can attack database from sqlserver management studio express?

I have created a database named s_office,now i want add file data.MDF and log.LDF into it. .How can i do it?Could you show me the script that attack file .MDF and .LDF into database.thankxhttp://msdn2.microsoft.com/en-us/library/ms179877.aspx

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

How accurate is dm_db_index_usage_stats

All,

I've been running this script below regularly to see the supposed usage of my indexes:

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

Before I start disabling/dropping indexes, just how accurate is the this dmv?

Thanks,

Ian

The data for the dmvs is real time.|||

It records all activity since SQL was last started, in real-time. I have found it to be very reliable and helpful in deciding which indexes are actually being used for reads. Try running this query to get a better picture:

-- Possible bad Indexes (writes > reads)

DECLARE @.dbid int

SELECT @.dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @.dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

how 2 install client tools for SQl Server

In Server Explorer of VS .NET 2003, I tried to generate SQL script of the dbo.Portal (ibs)
but got
"To use this feature, you must install the client tols for SQL Server"

When I searched for Help, I've got
"1.Insert the MS SQL Server 2000 compact disc in your CD-ROM drive"

I installed and use MSDE and cannot recall the use of any CD for installation.

1)
Does it mean that I cannot install client tools from MSDE installation?

If not...

2)
I have installation of MS SQL Server200 Enterprise that cannot be installed on Windows Xp Professional that I use. Can I still install "client tools" from Enterprise Ed. for MSDEMSDE !== SQL Server

1. you get the client tools with SQL Server, not with MSDE, which is just the data engine.
2. you should be fine to install the SQL Ent client tools on XP - but it won't let you install the server.|||Thanx, Atrax,
I aslo found in
http://www.microsoft.com/sql/msde/howtobuy/msdeuse.asp

You can only use SQL Server tools and services in conjunction with MSDE if you acquired MSDE through SQL Server 2000 (Developer Edition, Standard Edition, or Enterprise Edition), and if you are using MSDE in conjunction with a properly licensed copy of SQL Server 2000.

I am now in doubt where from I have installed MSDE, since I vaguely recall that I have MSDE installables in various places:
- free downloads
- various editions of SQL Server from CDs,
- MS Office and VS .NET installations
I remember I reinstalled them in various combinations on various PCs.

How can I determine whether MSDE is from Developer, Standard or Enterprise Ed.s or from free download?
Or it is not important?

How "NOT" to expire the ReportSession and ControlID in the request

This question is related to VSTS and above parameters in the request. Question is:

A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.

You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)

You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.

|||

Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.

Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!

|||

I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)

|||

It won't. The expiration time is computed when the session is created.

Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?

|||

Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like

GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007

Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).

With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.

<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>

Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.

Thanks for your input.

|||can some one reply to this please|||

The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1

How "NOT" to expire the ReportSession and ControlID in the request

This question is related to VSTS and above parameters in the request. Question is:

A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.

You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)

You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.

|||

Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.

Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!

|||

I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)

|||

It won't. The expiration time is computed when the session is created.

Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?

|||

Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like

GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007

Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).

With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.

<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>

Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.

Thanks for your input.

|||can some one reply to this please|||

The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1