How can I 'copy' a SQL Express database to SQL Everywhere?

Now that SQL Everywhere can be used on the desktop, how can I create an
'Everywhere' version of a database that I have set up in Express or 2000?
Clearly, as' Everywhere' is a subset, the mobile version may not be
identical, but there must be a way to create the tables, columns and indexes.
Thanks,
--
John AustinHi,
Take a look into the SQLCMD utility in books online.
Thanks
Hari
SQL Server MVP
"John Austin" wrote:
> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and indexes.
> Thanks,
> --
> John Austin|||In management studio you should be able to script any objects you want. In
most cases the DDL commands are the same or should work with minor
modifications.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:BDE7588B-9EA7-4B2B-B1CC-4FB89FBF7109@.microsoft.com...
> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and
> indexes.
> Thanks,
> --
> John Austin|||Dear John,
From your description, I understand that:
You wanted to know how to create a SQL Everywhere database that you have
created in SQL Express or 2000.
If I have misunderstood, please let me know.
Unfortunately by now SQL Server Everywhere Edition support service may not
be available in newsgroup. For your concerns, I recommend that you contact
Microsoft Customer Support Services (CSS) via telephone so that a dedicated
Support Professional can assist you in a more efficient manner. Please be
advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Also, I would like to share you my experiences on SQL Server 2005
Everywhere Edition.
From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
Everywhere) - Performing Common Database Tasks), we can see there are five
ways to create an Everywhere database:
1. Create a SQL Server Everywhere database on the server
2. Create a SQL Server Everywhere Database on a Connected Device
3. Create a SQL Server Everywhere Database by Using the Engine Object
(Programmatically)
4. Create a SQL Server Everywhere Database by Using the Replication Object
(Programmatically)
5. Create a Database by Using OLE DB (Programmatically)
Practically I just tried the first and the third method, but I recommend
that you use the third method because I can just find "SQL Server Mobile"
in the "server type" list, not "SQL Server Everywhere".
You can use T-SQL as well as SQL Server 2000 and other editions to create
tables, columns and indexes. For more information, you can refer to SQL
Server 2005 Everywhere Edition Books Online (Especially the How To chapter).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks, I will try your suggestion. My first attempt was to script a table
from the Express database and paste it into the SQL window for the Everywhere
database in VS 2005, but the script would not execute.
--
John Austin
"Charles Wang[MSFT]" wrote:
> Dear John,
> From your description, I understand that:
> You wanted to know how to create a SQL Everywhere database that you have
> created in SQL Express or 2000.
> If I have misunderstood, please let me know.
> Unfortunately by now SQL Server Everywhere Edition support service may not
> be available in newsgroup. For your concerns, I recommend that you contact
> Microsoft Customer Support Services (CSS) via telephone so that a dedicated
> Support Professional can assist you in a more efficient manner. Please be
> advised that contacting phone support will be a charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Also, I would like to share you my experiences on SQL Server 2005
> Everywhere Edition.
> From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
> Everywhere) - Performing Common Database Tasks), we can see there are five
> ways to create an Everywhere database:
> 1. Create a SQL Server Everywhere database on the server
> 2. Create a SQL Server Everywhere Database on a Connected Device
> 3. Create a SQL Server Everywhere Database by Using the Engine Object
> (Programmatically)
> 4. Create a SQL Server Everywhere Database by Using the Replication Object
> (Programmatically)
> 5. Create a Database by Using OLE DB (Programmatically)
> Practically I just tried the first and the third method, but I recommend
> that you use the third method because I can just find "SQL Server Mobile"
> in the "server type" list, not "SQL Server Everywhere".
> You can use T-SQL as well as SQL Server 2000 and other editions to create
> tables, columns and indexes. For more information, you can refer to SQL
> Server 2005 Everywhere Edition Books Online (Especially the How To chapter).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Dear John,
Appreciate your response.
I look forward to your resolving this issue.
Please feel free to let me know if you have any other questions or concerns.
Sincerely,
Charles Wang
Microsoft Online Community Support|||I guess what would be really useful, would be a utility that generates an
everywhere compatible script from a SQL Server table.
The problem with using script is that the number of deletions and amendments
needed are quite huge.
--
John Austin
"Charles Wang[MSFT]" wrote:
> Dear John,
> Appreciate your response.
> I look forward to your resolving this issue.
> Please feel free to let me know if you have any other questions or concerns.
> Sincerely,
> Charles Wang
> Microsoft Online Community Support
>|||Dear John,
As you mentioned in email, you are just surprised that Microsoft have no
such migration tool.
I would like that you could give Microsoft feedback which will route to SQL
team so that this tool will be released in future.
You can submit your feedback via:
http://connect.microsoft.com/feedback/default.aspx?SiteID=68
Note: please logon before submitting a feedback.
If you have any other questions or concerns, please feel free to let me
know. It's always my pleasure to be of assistance.
Sincerely,
Charles Wang
Microsoft Online Community Support

How can I 'copy' a SQL Express database to SQL Everywhere?

Now that SQL Everywhere can be used on the desktop, how can I create an
'Everywhere' version of a database that I have set up in Express or 2000?
Clearly, as' Everywhere' is a subset, the mobile version may not be
identical, but there must be a way to create the tables, columns and indexes
.
Thanks,
--
John AustinHi,
Take a look into the SQLCMD utility in books online.
Thanks
Hari
SQL Server MVP
"John Austin" wrote:

> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and index
es.
> Thanks,
> --
> John Austin|||In management studio you should be able to script any objects you want. In
most cases the DDL commands are the same or should work with minor
modifications.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"John Austin" <John.Austin@.nospam.nospam> wrote in message
news:BDE7588B-9EA7-4B2B-B1CC-4FB89FBF7109@.microsoft.com...
> Now that SQL Everywhere can be used on the desktop, how can I create an
> 'Everywhere' version of a database that I have set up in Express or 2000?
> Clearly, as' Everywhere' is a subset, the mobile version may not be
> identical, but there must be a way to create the tables, columns and
> indexes.
> Thanks,
> --
> John Austin|||Dear John,
From your description, I understand that:
You wanted to know how to create a SQL Everywhere database that you have
created in SQL Express or 2000.
If I have misunderstood, please let me know.
Unfortunately by now SQL Server Everywhere Edition support service may not
be available in newsgroup. For your concerns, I recommend that you contact
Microsoft Customer Support Services (CSS) via telephone so that a dedicated
Support Professional can assist you in a more efficient manner. Please be
advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Also, I would like to share you my experiences on SQL Server 2005
Everywhere Edition.
From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
Everywhere) - Performing Common Database Tasks), we can see there are five
ways to create an Everywhere database:
1. Create a SQL Server Everywhere database on the server
2. Create a SQL Server Everywhere Database on a Connected Device
3. Create a SQL Server Everywhere Database by Using the Engine Object
(Programmatically)
4. Create a SQL Server Everywhere Database by Using the Replication Object
(Programmatically)
5. Create a Database by Using OLE DB (Programmatically)
Practically I just tried the first and the third method, but I recommend
that you use the third method because I can just find "SQL Server Mobile"
in the "server type" list, not "SQL Server Everywhere".
You can use T-SQL as well as SQL Server 2000 and other editions to create
tables, columns and indexes. For more information, you can refer to SQL
Server 2005 Everywhere Edition Books Online (Especially the How To chapter).
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks, I will try your suggestion. My first attempt was to script a table
from the Express database and paste it into the SQL window for the Everywher
e
database in VS 2005, but the script would not execute.
--
John Austin
"Charles Wang[MSFT]" wrote:

> Dear John,
> From your description, I understand that:
> You wanted to know how to create a SQL Everywhere database that you have
> created in SQL Express or 2000.
> If I have misunderstood, please let me know.
> Unfortunately by now SQL Server Everywhere Edition support service may not
> be available in newsgroup. For your concerns, I recommend that you contact
> Microsoft Customer Support Services (CSS) via telephone so that a dedicate
d
> Support Professional can assist you in a more efficient manner. Please be
> advised that contacting phone support will be a charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/defaul...US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Also, I would like to share you my experiences on SQL Server 2005
> Everywhere Edition.
> From SQL Server 2005 Everywhere Edition Books Online (How To (SQL Server
> Everywhere) - Performing Common Database Tasks), we can see there are five
> ways to create an Everywhere database:
> 1. Create a SQL Server Everywhere database on the server
> 2. Create a SQL Server Everywhere Database on a Connected Device
> 3. Create a SQL Server Everywhere Database by Using the Engine Object
> (Programmatically)
> 4. Create a SQL Server Everywhere Database by Using the Replication Object
> (Programmatically)
> 5. Create a Database by Using OLE DB (Programmatically)
> Practically I just tried the first and the third method, but I recommend
> that you use the third method because I can just find "SQL Server Mobile"
> in the "server type" list, not "SQL Server Everywhere".
> You can use T-SQL as well as SQL Server 2000 and other editions to create
> tables, columns and indexes. For more information, you can refer to SQL
> Server 2005 Everywhere Edition Books Online (Especially the How To chapter
).
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
> Charles Wang
> Microsoft Online Partner Support
> PLEASE NOTE: The partner managed newsgroups are provided
> to assist with break/fix issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader:
> microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Dear John,
Appreciate your response.
I look forward to your resolving this issue.
Please feel free to let me know if you have any other questions or concerns.
Sincerely,
Charles Wang
Microsoft Online Community Support|||I guess what would be really useful, would be a utility that generates an
everywhere compatible script from a SQL Server table.
The problem with using script is that the number of deletions and amendments
needed are quite huge.
John Austin
"Charles Wang[MSFT]" wrote:

> Dear John,
> Appreciate your response.
> I look forward to your resolving this issue.
> Please feel free to let me know if you have any other questions or concern
s.
> Sincerely,
> Charles Wang
> Microsoft Online Community Support
>|||Dear John,
As you mentioned in email, you are just surprised that Microsoft have no
such migration tool.
I would like that you could give Microsoft feedback which will route to SQL
team so that this tool will be released in future.
You can submit your feedback via:
http://connect.microsoft.com/feedba...aspx?SiteID=68
Note: please logon before submitting a feedback.
If you have any other questions or concerns, please feel free to let me
know. It's always my pleasure to be of assistance.
Sincerely,
Charles Wang
Microsoft Online Community Support

How can I check if table already exists in a DB?

How can I check if table already exists in a DB?
Today I just create my table at startup, if it exist I get a error telling
me that the table already exist, ignoring the error message.
But this is a dirty way of doing it. Any other idea.
It must be quick and clean.Hi !

quote:

---

> How can I check if table already exists in a DB?

---

If you generate sql script for table and check "generate drop object" on ,
you will see in generated scipt something like this :
if exists (select * from dbo.sysobjects where id = object_id(N'[accounts]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [accounts]
where 'accounts' is my tablename.
You can use this approach or , using odbc API , try to get table metadata,
i'm sure it should be way to get it, just never tried it.
Regards,Alexander|||use SQLTables function
"GTi" <nospam@.online.com> wrote in message
news:PUsQb.1148$O41.64225@.amstwist00...

quote:

---

> How can I check if table already exists in a DB?
> Today I just create my table at startup, if it exist I get a error telling
> me that the table already exist, ignoring the error message.
> But this is a dirty way of doing it. Any other idea.
> It must be quick and clean.
>

---

|||Or try this:
USE [YourDB]
IF EXISTS (
SELECT name
FROM sysobjects
WHERE type = 'u' AND
name = N'YourTable' -- Remove N if not using unicode
)
BEGIN
-- Do action
END
Regards,
Johan
"Furer Alexander" <alex_f@.sentry-com.co.il> wrote in message
news:OLebgPN6DHA.1636@.TK2MSFTNGP12.phx.gbl...
use SQLTables function
"GTi" <nospam@.online.com> wrote in message
news:PUsQb.1148$O41.64225@.amstwist00...

quote:

---

> How can I check if table already exists in a DB?
> Today I just create my table at startup, if it exist I get a error telling
> me that the table already exist, ignoring the error message.
> But this is a dirty way of doing it. Any other idea.
> It must be quick and clean.

---

|||Thanks to you all...
"GTi" <nospam@.online.com> wrote in message
news:PUsQb.1148$O41.64225@.amstwist00...

quote:

---

> How can I check if table already exists in a DB?
> Today I just create my table at startup, if it exist I get a error telling
> me that the table already exist, ignoring the error message.
> But this is a dirty way of doing it. Any other idea.
> It must be quick and clean.
>

---

|||IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 1
print 'Authors is a table'
"GTi" <nospam@.online.com> wrote in message
news:PUsQb.1148$O41.64225@.amstwist00...

quote:

---

> How can I check if table already exists in a DB?
> Today I just create my table at startup, if it exist I get a error telling
> me that the table already exist, ignoring the error message.
> But this is a dirty way of doing it. Any other idea.
> It must be quick and clean.
>

---

How can I change the reports wizard templates?

I can easily create reports using the wizard, but I have to spend a lot of
time changing them.
Is there a way to change the templates the wizard uses?No. But you can create your own basic report as a template and change the
items needed by hand instead of using the wizard.
--
| From: "geri" <dsfds>
| Subject: How can I change the reports wizard templates?
| Date: Thu, 6 Jan 2005 12:21:10 +0200
| Lines: 5
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OT9Urm98EHA.3828@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38816
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I can easily create reports using the wizard, but I have to spend a lot of
| time changing them.
| Is there a way to change the templates the wizard uses?
|
|
||||This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C50EA5.CCFB9680
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Then can you explain what the RS BOL topic "Creating a Report Using =Report Wizard" means by "Report Designer provides four style templates: =Bold, Casual, Corporate, and Compact. You can alter existing templates =or add new ones by editing the StyleTemplates.xml file in the =\80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles =folder in the Microsoft SQL Server program folder. This folder is =located on the computer on which Report Designer is installed. ", Please =? (Bold, italics, mine...)
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message =news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...
> No. But you can create your own basic report as a template and change =the > items needed by hand instead of using the wizard.
> --
> | From: "geri" <dsfds>
> | Subject: How can I change the reports wizard templates?
> | Date: Thu, 6 Jan 2005 12:21:10 +0200
> | Lines: 5
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <OT9Urm98EHA.3828@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181
> | Path: > =cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl =microsoft.public.sqlserver.reportingsvcs:38816
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > | I can easily create reports using the wizard, but I have to spend a =lot of
> | time changing them.
> | Is there a way to change the templates the wizard uses?
> | > | > | >
--=_NextPart_000_002E_01C50EA5.CCFB9680
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Then can you explain what the RS BOL =topic "Creating a Report Using Report Wizard" means by "Report Designer =provides four style templates: Bold, Casual, Corporate, and Compact. You =can alter existing templates or add new ones by editing the StyleTemplates.xml =file in the \80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles =folder in the Microsoft SQL Server program folder. This folder is =located on the computer on which Report Designer is installed. ", Please ? =(Bold, italics, mine...)
""Brad Syputa - MS"" wrote in message news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...> =No. But you can create your own basic report as a template and change the > items =needed by hand instead of using the wizard.> =--> | From: "geri" > | Subject: How can I change the =reports wizard templates?> | Date: Thu, 6 Jan 2005 12:21:10 +0200> =| Lines: 5> | X-Priority: 3> | X-MSMail-Priority: =Normal> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437> | =X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441> | Message-ID: > | Newsgroups: microsoft.public.sqlserver.reportingsvcs> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181> | Path: > cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09> phx.gbl> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38816> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs> | > | I can =easily create reports using the wizard, but I have to spend a lot of> | =time changing them.> | Is there a way to change the templates the =wizard uses?> | > | > | >

---

--=_NextPart_000_002E_01C50EA5.CCFB9680--|||This is a multi-part message in MIME format.
--=_NextPart_000_0090_01C50EB7.5E3039B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You CAN change the templates, but the BOL topic is deficient...
1.. CREATE A BACKUP of the StyleTemplates.xml file in the folder cited =in BOL (see post below).
2.. Open the file in a text editor (your favorite or XML editor...)
3.. Locate the "StyleTemplate" you want to change, e.g. "Compact"
4.. Make sure you understand what each element does. Refer to the BOL =Index for detailed info, e.g., "BorderWidth element" will give you blurb =on that element as used in rdl's AND the Template.
5.. Color values have NO spaces between words, e.g. Steel Blue is =coded SteelBlue.
6.. Font names CAN include spaces, e.g. <FontFamily>Arial =Black</FontFamily>
7.. DO NOT include a <TextAlign> pair in the "Table Header" Style - VS =barfs! (It seems to want to apply the alignment of the cell to the =corresponding table header cell - DUH!) A new post to be seen by MS =will hopefully get this recognized as a bug (if it hasn't already =been...).
8.. Despite setting a default font within the "Table" style, if you =expect it to be used when you add a Group, dream on...
9.. Save the edited XML file.
10.. NOW COPY THE SUCKER TO YOUR "LANGUAGE" FOLDER UNDER THE ..\Styles =FOLDER... e.g., for English, copy it to ..\Styles\EN\
11.. REBOOT (emotive term!) Visual Studio - it appears to cache the =Style file's XML the first time you use the "New Report Wizard".
Experiment with the Style File and you may be able to control things =like the page size, grid size, etc., but I've yet to pluck up that =degree of courage...
Hope this helps...
"SS_Newbie" <SS_Newbie@.community.nospam> wrote in message =news:uxuddjuDFHA.328@.tk2msftngp13.phx.gbl...
Then can you explain what the RS BOL topic "Creating a Report Using =Report Wizard" means by "Report Designer provides four style templates: =Bold, Casual, Corporate, and Compact. You can alter existing templates =or add new ones by editing the StyleTemplates.xml file in the =\80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles =folder in the Microsoft SQL Server program folder. This folder is =located on the computer on which Report Designer is installed. ", Please =? (Bold, italics, mine...)
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message =news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...
> No. But you can create your own basic report as a template and =change the > items needed by hand instead of using the wizard.
> --
> | From: "geri" <dsfds>
> | Subject: How can I change the reports wizard templates?
> | Date: Thu, 6 Jan 2005 12:21:10 +0200
> | Lines: 5
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <OT9Urm98EHA.3828@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181
> | Path: > =cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl =microsoft.public.sqlserver.reportingsvcs:38816
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > | I can easily create reports using the wizard, but I have to spend =a lot of
> | time changing them.
> | Is there a way to change the templates the wizard uses?
> | > | > | >
--=_NextPart_000_0090_01C50EB7.5E3039B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You CAN change the templates, but the =BOL topic is deficient...
CREATE A BACKUP of the =StyleTemplates.xml file in the folder cited in BOL (see post below).
Open the file in a text editor (your =favorite or XML editor...)
Locate the "StyleTemplate" you =want to change, e.g. "Compact"
Make sure you understand what each =element does. Refer to the BOL Index for detailed info, e.g., "BorderWidth element" =will give you blurb on that element as used in rdl's AND the =Template.
Color values have NO spaces between =words, e.g. Steel Blue is coded SteelBlue.
Font names CAN include spaces, e.g. Arial Black
DO NOT include a =pair in the "Table Header" Style - VS barfs! (It seems to want to apply the =alignment of the cell to the corresponding table header cell - DUH!) A =new post to be seen by MS will hopefully get this recognized as a bug (if it =hasn't already been...).
Despite setting a default font within =the "Table" style, if you expect it to be used when you add a Group, dream on...
Save the edited XML file.
NOW COPY THE SUCKER TO YOUR "LANGUAGE" =FOLDER UNDER THE ..\Styles FOLDER... e.g., for English, copy it to ..\Styles\EN\
REBOOT (emotive term!) Visual Studio - =it appears to cache the Style file's XML the first time you use the "New =Report Wizard".
Experiment with the Style File and you =may be able to control things like the page size, grid size, etc., but I've yet to =pluck up that degree of courage...
Hope this helps...
"SS_Newbie" wrote in message news:uxuddjuDFHA.328@.t=k2msftngp13.phx.gbl...
Then can you explain what the RS BOL =topic "Creating a Report Using Report Wizard" means by "Report Designer =provides four style templates: Bold, Casual, Corporate, and Compact. =You can alter existing templates or add new ones by editing the =StyleTemplates.xml file in the \80\Tools\Report Designer\Business Intelligence Wizards\Reports\Styles folder in the Microsoft SQL Server program folder. This folder is located on the computer on which =Report Designer is installed. ", Please ? (Bold, italics, mine...)

""Brad Syputa - MS"" wrote in message news:7BlC$jF9EHA.764@.cpmsftngxa10.phx.gbl...> No. But you =can create your own basic report as a template and change the > items =needed by hand instead of using the wizard.> --> =| From: "geri" > | Subject: How can I change the reports =wizard templates?> | Date: Thu, 6 Jan 2005 12:21:10 +0200> | =Lines: 5> | X-Priority: 3> | X-MSMail-Priority: Normal> =| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437> | =X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441> | Message-ID: > | Newsgroups: microsoft.public.sqlserver.reportingsvcs> | NNTP-Posting-Host: dbk-exc.dubek.co.il 62.219.254.181> | Path: > =cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNG=P09> phx.gbl> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:38816> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs> | > | I can =easily create reports using the wizard, but I have to spend a lot of> =| time changing them.> | Is there a way to change the templates the =wizard uses?> | > | > | >

---

--=_NextPart_000_0090_01C50EB7.5E3039B0--

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Hi

I have Try to Create Stored Procedure in C# with the following structure

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)

{

}

But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature

@.ImageID uniqueidentifier

@.ImageFileName nvarchar(4000)

@.Image varbinary(8000)

But I don’t want that Data types .. I want it to be in the following format

@.ImageID uniqueidentifier

@.ImageFileName nText

@.Image Image

How Can I Control the data type for each parameter ?

Or

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Or

How Can I defined the new Data type ?

Or

What's the solution to this problem ?

Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP

ALTER PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName nText,

@.Image Image

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

GO

And thanks with my best regarding

Fraas

Hi Fraas

You may change parameter types (from nvarchar(4000) to nvarchar(max), for example), but you can't pass values bigger than 8000 bytes directly...

If you need nText or Image handling inside your .NET sp's, you should use .NET wrappers for this SQL types. Consider following procedure definition:

public static void AddImage(Guid ImageID, SqlChars FileName, SqlBytes image)

{

SqlContext.Pipe.Send(String.Format("Image: id {0}, filename length: {1}, image size: {2}", ImageID.ToString(), FileName.Value.Length, image.Length));

}

After deploying this proc, parameter types are uniqueidentifier, nvarchar(max) and varbinary(max).
nvarchar(max) should be used instead of ntext, and varbinary(max) instead of image types accordingly. Ntext and image are deprecated.

simple test (values bigger than 8000 can pass! ;-)):

declare @.id uniqueidentifier, @.image varbinary(max), @.filename nvarchar(max)

select @.filename = (select * from AdventureWorks.Person.Contact for xml auto, elements)

--or select @.filename = (select convert(nvarchar(max), replicate(N'1', 8000)) + convert(nvarchar(max), replicate(N'2', 8000)) )

select @.image = convert(varbinary(max), @.filename)

select @.id = newid()

exec AddImage @.id, @.filename, @.image

WBR, Evergray

--

Words mean nothing...

P.S. Do you really need ntext for storing file name?

|||What you are seeing is Visual Studio's mapping of CLR types to SQL

types, where string/SqlString maps to nvarchar(4000) and byte[] maps to

varbinary(8000).

The ntext and image datatypes in SQL 2005 are now deprecated and you

should use nvarchar(max) and varbinary(max) instead. To automatically

get those types from your CLR code, you should use the SqlChars and SqlBinary types from the SqlTypes namespace instead.

Niels|||

Hi

Thanks for the replies

Surly I will not save the File Name in (nText) Data Type but this is only as example .. I need to use the nText Type to store large text Date in the filed

And I need the Image Data Type to store Large File In the Database

|||Let's just make sure we're all on the same page here. From your response above I'm not usre if you realize (if you do I apologize) that ntext/text and image in SQL 2005 are being deprecated and eventually will go away (they are there now only for backward compatibility). They are being replaced with the nvarchar(max)/varchar(max) and varbinary(max) datatypes. These new datatypes have the same storage capabilities as the old ntext and image, but are much easier to work with

So in SQL 2005 you would use the nvarchar(max) type to store larger text date and varbinary(max) to store a large file. Subsequently, when you use VS to create SQCLR assemblies and stored procs you can use the SqlChars and SqlBinary datatypes in order to get automatic mapping to ntext(max) and varbinary(max) during deployment.

Niels|||

Thanks for all replies .. as I can see it solve my problem when the nVarchar(Max) replace for nText

With my regarding

Fraas

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Hi

I have Try to Create Stored Procedure in C# with the following structure

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)

{

}

But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature

@.ImageID uniqueidentifier

@.ImageFileName nvarchar(4000)

@.Image varbinary(8000)

But I don’t want that Data types .. I want it to be in the following format

@.ImageID uniqueidentifier

@.ImageFileName nText

@.Image Image

How Can I Control the data type for each parameter ?

Or

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Or

How Can I defined the new Data type ?

Or

What's the solution to this problem ?

Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP

ALTER PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName nText,

@.Image Image

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

GO

And thanks with my best regarding

Fraas

Hi Fraas

You may change parameter types (from nvarchar(4000) to nvarchar(max), for example), but you can't pass values bigger than 8000 bytes directly...

If you need nText or Image handling inside your .NET sp's, you should use .NET wrappers for this SQL types. Consider following procedure definition:

public static void AddImage(Guid ImageID, SqlChars FileName, SqlBytes image)

{

SqlContext.Pipe.Send(String.Format("Image: id {0}, filename length: {1}, image size: {2}", ImageID.ToString(), FileName.Value.Length, image.Length));

}

After deploying this proc, parameter types are uniqueidentifier, nvarchar(max) and varbinary(max).
nvarchar(max) should be used instead of ntext, and varbinary(max) instead of image types accordingly. Ntext and image are deprecated.

simple test (values bigger than 8000 can pass! ;-)):

declare @.id uniqueidentifier, @.image varbinary(max), @.filename nvarchar(max)

select @.filename = (select * from AdventureWorks.Person.Contact for xml auto, elements)

--or select @.filename = (select convert(nvarchar(max), replicate(N'1', 8000)) + convert(nvarchar(max), replicate(N'2', 8000)) )

select @.image = convert(varbinary(max), @.filename)

select @.id = newid()

exec AddImage @.id, @.filename, @.image

WBR, Evergray

--

Words mean nothing...

P.S. Do you really need ntext for storing file name?

|||What you are seeing is Visual Studio's mapping of CLR types to SQL

types, where string/SqlString maps to nvarchar(4000) and byte[] maps to

varbinary(8000).

The ntext and image datatypes in SQL 2005 are now deprecated and you

should use nvarchar(max) and varbinary(max) instead. To automatically

get those types from your CLR code, you should use the SqlChars and SqlBinary types from the SqlTypes namespace instead.

Niels|||

Hi

Thanks for the replies

Surly I will not save the File Name in (nText) Data Type but this is only as example .. I need to use the nText Type to store large text Date in the filed

And I need the Image Data Type to store Large File In the Database

|||Let's just make sure we're all on the same page here. From your response above I'm not usre if you realize (if you do I apologize) that ntext/text and image in SQL 2005 are being deprecated and eventually will go away (they are there now only for backward compatibility). They are being replaced with the nvarchar(max)/varchar(max) and varbinary(max) datatypes. These new datatypes have the same storage capabilities as the old ntext and image, but are much easier to work with

So in SQL 2005 you would use the nvarchar(max) type to store larger text date and varbinary(max) to store a large file. Subsequently, when you use VS to create SQCLR assemblies and stored procs you can use the SqlChars and SqlBinary datatypes in order to get automatic mapping to ntext(max) and varbinary(max) during deployment.

Niels|||

Thanks for all replies .. as I can see it solve my problem when the nVarchar(Max) replace for nText

With my regarding

Fraas

How Can I automatic create all that Objects in assembly in simple way ?

Hi

I use SQL code snippet to attach the CLR assembly (dll) to the SQL Server Express

CREATE ASSEMBLY [DatabaseAndImages]

AUTHORIZATION [dbo]

FROM 'C:\CLR_File.dll'

WITH PERMISSION_SET = SAFE

That file content CRL Codes for 10 Stored Procedures & 3 UDT & 5 Functions & 6 Triggers

As you can see it's content large amount of DB objects !!

My Question is ..

Is there any simple way can I use it to extract or automatic create all that Objects in the Database without use separate SQL Statement for each one ?

By Example , I will use this SQL statement to create the sp_AddImage that already located inside the CLR dll file

CREATE PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName [nvarchar](max),

@.Image [varbinary](max)

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

But as you know .. I have many objects .. and I am in development phase and I will do change to that object many time and also may I will add much more …

I thing it's not good to write SQL Statement for each object and do changes every time when I change the object definition

Is there any one line of SQL statement can I use it to automatically create and extract all the objects inside the assembly ?

Or is there any way to do that Issue by simple operation ?

And thanks with my best regarding

Fraas

If you use Visual Studio 2005; it has this new project type: the Sql Server Project which allows you tol do automatic deployment of the assembly as well as creation of the objects. It has it's limitations though - only for VS 2005 Professional and above, and it can not do ALTER (it always re-deploys everything), and it doesn't script out the objects. But as I said, it does automatic deployment.

If you want the ability to script out the objects, etc you can try a project type (and deployment mechanism) I wrote as an add-in to Visual Studio. You can find more info here.

Niels|||

Hi Fraas,

This blog post: http://blogs.msdn.com/sqlclr/archive/2005/11/21/495438.aspx details a ddl trigger that will create all the objects in your assembly when you create it. This hopefully will solve your problem without requiring Visual Studio.

Steven

sql

How can i automate the emailing of the result set as a file.

I want to create a job that runs a stored procedure and
present the result set in txt. The administrator has to be
informed of sucess and also the result must be emailed to
the administrator. This will be happening on weekly basis.
I have created the stored procedure and the job that
informs the Administrator. The problem is how can i
automate the emailing of the result set as a file with the
alerting after running the job. Please help.What about something like
master..xp_sendmail
@.recipients = 'Allan Mitchell',
@.query = 'Exec byRoyalty 100',
@.dbuse = 'pubs',
@.attach_results = 'true'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Naz" <milnaz@.hotmail.com> wrote in message
news:3e9c01c37c2f$ca20f310$a501280a@.phx.gbl...
> I want to create a job that runs a stored procedure and
> present the result set in txt. The administrator has to be
> informed of sucess and also the result must be emailed to
> the administrator. This will be happening on weekly basis.
> I have created the stored procedure and the job that
> informs the Administrator. The problem is how can i
> automate the emailing of the result set as a file with the
> alerting after running the job. Please help.

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre Ament
No, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I alter an int column to IDENTITY

Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
--
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament

How can I / Should I reuse SqlCacheDependency? thanks

I can create a SqlCacheDependency, and link it to a cached item in httpcontext cache. When something change, it will remove the cached item from the cache. I think I have to redo the process when that happens - prepare sql command, create SqlCacheDependency and insert the item into cache. Now I only need a notification from my SQL when something changes in one of my table, I don;t need read anything from db, and I think I should find a way to not recreate the SqlCacheDependency object everytime?

any suggestion?

Maybe you need a update/insert trigger that raise an error. For example, if you want to get notification when something changes in t1, you can use such T-SQL command to create a trigger:

create trigger trg_t1 on t1 for update,insert,delete
as
RAISERROR ('Some change has been made to table t1',16, 1)

go

To learn more about RAISERROR command, please take a look at:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_5ooi.asp?frame=true

And for triggers you can start from here:

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_08_116g.asp?frame=true

|||I am not sure what the trigger you described can help in my case. What I need is a way to let DB notify ASP when something change, the change is made by other ASP httphandlers, so those change should go on without interrupt. Once the change is done, it should notify ASP, which will invalid the data that cached with SQLCacheDependency. The problem what I have is once that happens, the SQLCacheDepency object will be removed, I need recreate it again. I think it is unnecessory, since I only need a notification from DB.

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 control Transactions for creating Stored Procedure ?

I create StringBuilder type for

concating string to create a lot of stored procedure at once

However When I use this command

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

------- This any command

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

on any command

If I use

Create a lot of Tables

such as

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE TABLE [dbo].[Table1](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY]

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It correctly works.

But if I need create a lot of Stored procedure

as the following code :

BEGIN TRANSACTION
BEGIN TRY
--////////////////////// SQL COMMAND /////////////////////////

CREATE PROCEDURE [dbo].[DeleteItem1]
@.ProcId Int,
@.RowVersion Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId AND
[RowVersion] = @.RowVersion
END

CREATE PROCEDURE [dbo].[DeleteItem2]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

CREATE PROCEDURE [dbo].[DeleteItem3]
@.ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @.ProcId
END

--///////////////////////////////////////////////////////////
--COMMIT TRAN
END TRY

BEGIN CATCH
IF @.@.TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH

IF @.@.TRANCOUNT > 0
COMMIT TRANSACTION;

It occurs Error ???

Please help me

How should I solve them ?

the stored procedure create

CREATE PROCEDURE ..

have to be first in T-SQL command batch. You can do what you need by inserting each single procedure code into varchar(max) variable and run this code using EXEC command like

DECLARE @.lcCommand as varchar(max)

SET @.lcCommand ='CREATE PROCEDURE PROC1 .....'

EXEC (@.lcCommand)

SET @.lcCommand ='CREATE PROCEDURE PROC2 .....'

EXEC (@.lcCommand)

remember if you procedure definition is longer than 8000 chars split it into chunks not longer than 8000 chars to prevent errors( for some reason string passed to varchar(max) in single assign is cut at 8000 position if longer than 8000 chars)

how can a user who is in a role assign his role to another user?

I create a role(eg. HighOperators), and add user 'ABC01' into it. 'ABC01'
is not a member of the sysadmin fixed server role or the db_owner fixed data
base role or the db_securityadmin fixed database role, can he assign his ro
le to another user?
In SQL Server Books Online, it says " Role owners can execute sp_addrolememb
er to add a member to any SQL Server role they own". but I don't know how to
set or get the a role's Owner.The only way I know to set the role owner is when you create
the role, specify the owner of the role in the second
argument for sp_addrole.
In terms of retrieving the owner, I don't remember there
being a direct way. I think it may be that the altuid in
sysusers is the uid for the owner of the role.
-Sue
On Tue, 27 Apr 2004 05:31:04 -0700, samuelzhu
<anonymous@.discussions.microsoft.com> wrote:

>I create a role(eg. HighOperators), and add user 'ABC01' into it. 'ABC01'
is not a member of the sysadmin fixed server role or the db_owner fixed dat
abase role or the db_securityadmin fixed database role, can he assign his r
ole to another user?
>In SQL Server Books Online, it says " Role owners can execute sp_addrolemember to a
dd a member to any SQL Server role they own". but I don't know how to set or get the
a role's Owner.

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?
stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>