Automating Generate Script in Command Line

Hello,
Is there a way to automate Generate Script in 2005, so by clicking a link it
generates script which is using a command line and not using Management
Studio Tasks->Generate Script ?
--
MikeMike
There are a new objects library SQL SMO (.NET) to deal with such reports.
Previously we used ( and if you still have SQL Server 2000) SQL DMO object
library
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:3F3E26B8-C60A-4E37-A7DC-87BA9A515A1D@.microsoft.com...
> Hello,
> Is there a way to automate Generate Script in 2005, so by clicking a link
> it
> generates script which is using a command line and not using Management
> Studio Tasks->Generate Script ?
> --
> Mike|||What do you want to script ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||SQl Server 2005 database.
--
Mike
"Jens" wrote:

> What do you want to script ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Automatically run query plan.

Is there a way to tell the SQL Server 2005 to automatically generate
query plan for certain stored procedure (as they run on the production
server)? (I know it's a bad idea, but still).
Thanks.Can you elaborate? A plan *is* created when you execute the procedure. You can't create a plan
without executing the procedure since the plan is dependent on a number of things like environment
settings (like SET) and also parameter values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:OQ$48kjyHHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is there a way to tell the SQL Server 2005 to automatically generate query plan for certain stored
> procedure (as they run on the production server)? (I know it's a bad idea, but still).
> Thanks.|||Frank,
Do you mean that you want to compile the stored procedure as soon as it
is created/deployed?
In that case, look up SET NOEXEC in BOL. You could set NOEXEC on and
call the SP.
HTH,
Gert-Jan
Frank Rizzo wrote:
> Is there a way to tell the SQL Server 2005 to automatically generate
> query plan for certain stored procedure (as they run on the production
> server)? (I know it's a bad idea, but still).
> Thanks.|||Tibor Karaszi wrote:
> Can you elaborate? A plan *is* created when you execute the procedure.
> You can't create a plan without executing the procedure since the plan
> is dependent on a number of things like environment settings (like SET)
> and also parameter values.
Understood. However, after the proc is ran, I'd love to have SQL Server
save somewhere that execution plan that it ran the proc with.|||On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
> Understood. However, after the proc is ran, I'd love to have SQL Server
> save somewhere that execution plan that it ran the proc with.
SQL 2005 profiler can capture this information. You can also attach plan to
a proc call to have SQL server use your preferred plan if you have tested
over and over and can't get SQL server to pick the plan you want. (I would
submit to http://connect.microsoft.com as they like to hear about those
kind of issues.
-Chuck Lathrope
www.sqlwebpedia.com|||SQL Server 2005 automatically captures this information and the plan is
available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
all, as when you create or exec the proc WITH RECOMPILE). You don't need
profiler.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chuck Lathrope" <computerguy_chuck@.fixmehotmail.com> wrote in message
news:c0ajevo59mxn.na0e1n0vrpyc$.dlg@.40tude.net...
> On Thu, 19 Jul 2007 14:49:47 -0700, Frank Rizzo wrote:
>> Tibor Karaszi wrote:
>> Can you elaborate? A plan *is* created when you execute the procedure.
>> You can't create a plan without executing the procedure since the plan
>> is dependent on a number of things like environment settings (like SET)
>> and also parameter values.
>> Understood. However, after the proc is ran, I'd love to have SQL Server
>> save somewhere that execution plan that it ran the proc with.
> SQL 2005 profiler can capture this information. You can also attach plan
> to
> a proc call to have SQL server use your preferred plan if you have tested
> over and over and can't get SQL server to pick the plan you want. (I would
> submit to http://connect.microsoft.com as they like to hear about those
> kind of issues.
> -Chuck Lathrope
> www.sqlwebpedia.com|||Yes, as Tibor and Kalen said, the plan is created automatically when you
executed your procedure, it is stored in a cache of SQL Server and you can
query it from sys.dm_exec_cached_plans.
In addition, sometimes you may want to have your query execute with a
specific execution plan. In this case, you can save the execution plan in
XML format and then force your query to use the query plan by using "USE
PLAN".
For more information, please refer to:
How to: Save an Execution Plan in XML Format
http://msdn2.microsoft.com/en-us/library/ms190646.aspx
Forcing Query Plans
http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx
Hope this helps. Please feel free to let us know if you have any other
questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================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.
======================================================|||Kalen Delaney wrote:
> SQL Server 2005 automatically captures this information and the plan is
> available in sys.dm_exec_cached_plans. (Unless the plan is never cached at
> all, as when you create or exec the proc WITH RECOMPILE). You don't need
> profiler.
Thank you. How do I now derive the actual plan from this view?
I'd like to get it into XML format so that I can open the execution plan
in SQL Server Management Studio (for inspection).|||You can a column called "plan_handle". You feed the value from this into the function
sys.dm_exec_query_plan(). You can also use CROSS APPLY:
SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank Rizzo" <none@.none.com> wrote in message news:%23KV37%23xyHHA.4184@.TK2MSFTNGP06.phx.gbl...
> Kalen Delaney wrote:
>> SQL Server 2005 automatically captures this information and the plan is available in
>> sys.dm_exec_cached_plans. (Unless the plan is never cached at all, as when you create or exec the
>> proc WITH RECOMPILE). You don't need profiler.
> Thank you. How do I now derive the actual plan from this view?
> I'd like to get it into XML format so that I can open the execution plan in SQL Server Management
> Studio (for inspection).
>|||Hi,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let us know.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================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.
======================================================sql

Automatically Generate Aggregation Designs for cube partitions?

Hi Everyone,

Is there a way to automatically generate aggregations for cube partitions?

We're using the SyncAdvWorksPartition as our basis for dynamically creating measure group partitions. Our poblem is that newly created partitions does not have any aggregation designs implemented on it. We would like to place some aggregation designs on each of our partitions. Is there a way to automatically create aggregation designs on a partition just by using XMLA or other means?

Thanks,

Joseph

Sitting with the same thing myself right now . Using AMO the core of it is shown on the bottom of this page:

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

It works fine. My current problem is that I get complaints from the aggregation designer about attributes containing zero values. I'm guessing this means one has to set the EstimatedCount property for them before calling the aggregation wizards. Wondering if anyone can confirm this?

|||

> My current problem is that I get complaints from the aggregation designer about attributes containing zero values. I'm guessing this means one has to set the EstimatedCount property for them before calling the aggregation wizards. Wondering if anyone can confirm this?

Yes, you need to set the following properties before designing aggregations:

- EstimatedCount property (= estimated number of members) for all the DimensionAttributes of the Dimension used in the MeasureGroup (the MeasureGroup containing the AggregationDesign you are creating)

- EstimatedRows property of the MeasureGroup

You can run SQL queries manually to get the values for these properties. Or you can get them programatically: generate and run the SQL statement that does a SELECT DISTINCT; given a DimensionAttribute, use its KeyColumns to see what table and columns is based on; given a MeasureGroup, use the Sources of its Measures to get the fact table. An AMO code sample is being written for exactly this and it will be released in Books Online + MSDN.

Once you setup those properties, save the Dimensions and the MeasureGroup (with .Update method) before calling InitializeDesign.

Adrian Dumitrascu

|||

Hi Adrian,

you mention the AMO code sample - do you know when this will be available? I am unable to programmatically let my end user simply select a cube (or measure-group) via my app and choose the % aggregation they want... I ran into the EstimatedCount problem as above and then when I have this (via the WIzard), how to create and save aggregations for a partition is proving very difficult (following the MS sample code, it looks like I can create aggregations, but I have no idea how to save them in the partition I am working with) Really frustrating... if I know the counts, how can I simply replace the Aggregation Design Wizard with a "aggregate to X%" function? Also - if I don't know the counts, sample code for getting and setting them would be helfpul too.

Thanks!

-cf

|||

Hi,

the AMO code sample to assign the EstimatedCount and EstimatedRows has been released?

Can you point me to it?

Thank you

Marco Bergonzini

|||

I, too, would like to see the official Microsoft AMO sample code. Until that arrives, BIDS Helper has such a feature:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Update%20Estimated%20Counts&referringTitle=Home

Feedback is welcome as we just added it a few days ago.

Automatically Generate Aggregation Designs for cube partitions?

Hi Everyone,

Is there a way to automatically generate aggregations for cube partitions?

We're using the SyncAdvWorksPartition as our basis for dynamically creating measure group partitions. Our poblem is that newly created partitions does not have any aggregation designs implemented on it. We would like to place some aggregation designs on each of our partitions. Is there a way to automatically create aggregation designs on a partition just by using XMLA or other means?

Thanks,

Joseph

Sitting with the same thing myself right now . Using AMO the core of it is shown on the bottom of this page:

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

It works fine. My current problem is that I get complaints from the aggregation designer about attributes containing zero values. I'm guessing this means one has to set the EstimatedCount property for them before calling the aggregation wizards. Wondering if anyone can confirm this?

|||

> My current problem is that I get complaints from the aggregation designer about attributes containing zero values. I'm guessing this means one has to set the EstimatedCount property for them before calling the aggregation wizards. Wondering if anyone can confirm this?

Yes, you need to set the following properties before designing aggregations:

- EstimatedCount property (= estimated number of members) for all the DimensionAttributes of the Dimension used in the MeasureGroup (the MeasureGroup containing the AggregationDesign you are creating)

- EstimatedRows property of the MeasureGroup

You can run SQL queries manually to get the values for these properties. Or you can get them programatically: generate and run the SQL statement that does a SELECT DISTINCT; given a DimensionAttribute, use its KeyColumns to see what table and columns is based on; given a MeasureGroup, use the Sources of its Measures to get the fact table. An AMO code sample is being written for exactly this and it will be released in Books Online + MSDN.

Once you setup those properties, save the Dimensions and the MeasureGroup (with .Update method) before calling InitializeDesign.

Adrian Dumitrascu

|||

Hi Adrian,

you mention the AMO code sample - do you know when this will be available? I am unable to programmatically let my end user simply select a cube (or measure-group) via my app and choose the % aggregation they want... I ran into the EstimatedCount problem as above and then when I have this (via the WIzard), how to create and save aggregations for a partition is proving very difficult (following the MS sample code, it looks like I can create aggregations, but I have no idea how to save them in the partition I am working with) Really frustrating... if I know the counts, how can I simply replace the Aggregation Design Wizard with a "aggregate to X%" function? Also - if I don't know the counts, sample code for getting and setting them would be helfpul too.

Thanks!

-cf

|||

Hi,

the AMO code sample to assign the EstimatedCount and EstimatedRows has been released?

Can you point me to it?

Thank you

Marco Bergonzini

|||

I, too, would like to see the official Microsoft AMO sample code. Until that arrives, BIDS Helper has such a feature:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Update%20Estimated%20Counts&referringTitle=Home

Feedback is welcome as we just added it a few days ago.

Automatic XSD generation

Hello everyone,
Is there standard functionality to generate XSD files, which i can use
from within a program?
If so, please let me know where i can find more information.
Thanks
'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The first
thing people say is, oh, you're a raven, go on, say the N word... If I had a
penny for every time that's happened, I'd-'
(Soul Music)
There are quite a few ways to handle XSDs.
From SQL, using XMLDATA attaches the Schema to your results. If you run a
query like:
SELECT * FROM Customers
FOR XML AUTO, XMLDATA
you will have a schema attached that is attribute centric. You can change to
elements like:
SELECT * FROM Customers
FOR XML AUTO, ELEMENTS, XMLDATA
But, you still have a schema attached to the top of an XML snippet (no root
tag). If you just want schema, you end up having to remove the records.
Something like:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
You are still missing root tag, however, which makes the XSD only
semi-useful, IMO.
With .NET, you can create a DataSet and return the XML Schema. This creates
an XSD that is valid. BUT, you end up with the extra weight of the DataSet
tags that surround your table of records. This is not a huge burden, overall,
but it can add a bit of extra work to those consuming your XML outside of the
..NET platform. It is, overall, easier than working directly with the SQL
"autogen" capabilities.

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Mark Pustjens" wrote:

> Hello everyone,
> Is there standard functionality to generate XSD files, which i can use
> from within a program?
> If so, please let me know where i can find more information.
> Thanks
> --
> 'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The first
> thing people say is, oh, you're a raven, go on, say the N word... If I had a
> penny for every time that's happened, I'd-'
> (Soul Music)
>
|||Note that the XMLDATA directive generates the older XDR format and not the
XSD format.
Best regards
Michael
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:0888E260-B1EC-49D5-B7BE-0FD4A383156E@.microsoft.com...[vbcol=seagreen]
> There are quite a few ways to handle XSDs.
> From SQL, using XMLDATA attaches the Schema to your results. If you run a
> query like:
> SELECT * FROM Customers
> FOR XML AUTO, XMLDATA
> you will have a schema attached that is attribute centric. You can change
> to
> elements like:
> SELECT * FROM Customers
> FOR XML AUTO, ELEMENTS, XMLDATA
> But, you still have a schema attached to the top of an XML snippet (no
> root
> tag). If you just want schema, you end up having to remove the records.
> Something like:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
> You are still missing root tag, however, which makes the XSD only
> semi-useful, IMO.
> With .NET, you can create a DataSet and return the XML Schema. This
> creates
> an XSD that is valid. BUT, you end up with the extra weight of the DataSet
> tags that surround your table of records. This is not a huge burden,
> overall,
> but it can add a bit of extra work to those consuming your XML outside of
> the
> .NET platform. It is, overall, easier than working directly with the SQL
> "autogen" capabilities.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "Mark Pustjens" wrote:

Automatic XSD generation

Hello everyone,
Is there standard functionality to generate XSD files, which i can use
from within a program?
If so, please let me know where i can find more information.
Thanks
'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The first
thing people say is, oh, you're a raven, go on, say the N word... If I had a
penny for every time that's happened, I'd-'
(Soul Music)There are quite a few ways to handle XSDs.
From SQL, using XMLDATA attaches the Schema to your results. If you run a
query like:
SELECT * FROM Customers
FOR XML AUTO, XMLDATA
you will have a schema attached that is attribute centric. You can change to
elements like:
SELECT * FROM Customers
FOR XML AUTO, ELEMENTS, XMLDATA
But, you still have a schema attached to the top of an XML snippet (no root
tag). If you just want schema, you end up having to remove the records.
Something like:
SELECT * FROM Customers
WHERE 1 = 0
FOR XML AUTO, ELEMENTS, XMLDATA
You are still missing root tag, however, which makes the XSD only
semi-useful, IMO.
With .NET, you can create a DataSet and return the XML Schema. This creates
an XSD that is valid. BUT, you end up with the extra weight of the DataSet
tags that surround your table of records. This is not a huge burden, overall
,
but it can add a bit of extra work to those consuming your XML outside of th
e
.NET platform. It is, overall, easier than working directly with the SQL
"autogen" capabilities.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Mark Pustjens" wrote:

> Hello everyone,
> Is there standard functionality to generate XSD files, which i can use
> from within a program?
> If so, please let me know where i can find more information.
> Thanks
> --
> 'I'm a raven, aren't I?' it said. 'One of the few birds who speak. The fir
st
> thing people say is, oh, you're a raven, go on, say the N word... If I had
a
> penny for every time that's happened, I'd-'
> (Soul Music)
>|||Note that the XMLDATA directive generates the older XDR format and not the
XSD format.
Best regards
Michael
"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@.comcast.netNoSpamM> wrote
in message news:0888E260-B1EC-49D5-B7BE-0FD4A383156E@.microsoft.com...
> There are quite a few ways to handle XSDs.
> From SQL, using XMLDATA attaches the Schema to your results. If you run a
> query like:
> SELECT * FROM Customers
> FOR XML AUTO, XMLDATA
> you will have a schema attached that is attribute centric. You can change
> to
> elements like:
> SELECT * FROM Customers
> FOR XML AUTO, ELEMENTS, XMLDATA
> But, you still have a schema attached to the top of an XML snippet (no
> root
> tag). If you just want schema, you end up having to remove the records.
> Something like:
> SELECT * FROM Customers
> WHERE 1 = 0
> FOR XML AUTO, ELEMENTS, XMLDATA
> You are still missing root tag, however, which makes the XSD only
> semi-useful, IMO.
> With .NET, you can create a DataSet and return the XML Schema. This
> creates
> an XSD that is valid. BUT, you end up with the extra weight of the DataSet
> tags that surround your table of records. This is not a huge burden,
> overall,
> but it can add a bit of extra work to those consuming your XML outside of
> the
> .NET platform. It is, overall, easier than working directly with the SQL
> "autogen" capabilities.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> ***************************
> Think Outside the Box!
> ***************************
> "Mark Pustjens" wrote:
>

automatic sequence number by id

Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
Edgar
Hi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL, [Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:

> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why does this data need to be stored, when you could always retrieve Seq at
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>
|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David
|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:

>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>
|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
sql

automatic sequence number by id

Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
EdgarHi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL,
[Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:

> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why does this data need to be stored, when you could always retrieve Seq a
t
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you a
re
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need t
o
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:

>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
>

automatic script generation

Hi,
Is it possible to automate the 'All tasks - generate
sqlscripts' for tables and stored procs thru SQL
enterprise manager. I want the scripts to be automatically
generated monthly instead of doing manually - pls help.
regards,
bharathbharath,
Make and application that leverages the SQL-DMO libraries.
There are classes which can be used to generate scripts.
You may also use sp_OA* procs instead of a little app,
but I have done so, and I will advise you that
it is an exercise in tedium. Better to make a little app.
See "SQL-DMO" in Books Online.
James Hokes
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> Hi,
> Is it possible to automate the 'All tasks - generate
> sqlscripts' for tables and stored procs thru SQL
> enterprise manager. I want the scripts to be automatically
> generated monthly instead of doing manually - pls help.
> regards,
> bharath|||Hi,
JAmes.. thanks a lot.. can u give me an example so that i
understand better (regarding the apps)...
regards,
bharath
>--Original Message--
>bharath,
>Make and application that leverages the SQL-DMO libraries.
>There are classes which can be used to generate scripts.
>You may also use sp_OA* procs instead of a little app,
>but I have done so, and I will advise you that
>it is an exercise in tedium. Better to make a little app.
>See "SQL-DMO" in Books Online.
>James Hokes
>"bharath" <anonymous@.discussions.microsoft.com> wrote in
message
>news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
>> Hi,
>> Is it possible to automate the 'All tasks - generate
>> sqlscripts' for tables and stored procs thru SQL
>> enterprise manager. I want the scripts to be
automatically
>> generated monthly instead of doing manually - pls help.
>> regards,
>> bharath
>
>.
>|||For sp_OA* variant, you can find a nice script at
http://support.microsoft.com/default.aspx?scid=kb;en-us;233392.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...
> Hi,
> JAmes.. thanks a lot.. can u give me an example so that i
> understand better (regarding the apps)...
> regards,
> bharath
> >--Original Message--
> >bharath,
> >
> >Make and application that leverages the SQL-DMO libraries.
> >There are classes which can be used to generate scripts.
> >
> >You may also use sp_OA* procs instead of a little app,
> >but I have done so, and I will advise you that
> >it is an exercise in tedium. Better to make a little app.
> >
> >See "SQL-DMO" in Books Online.
> >
> >James Hokes
> >
> >"bharath" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> >> Hi,
> >>
> >> Is it possible to automate the 'All tasks - generate
> >> sqlscripts' for tables and stored procs thru SQL
> >> enterprise manager. I want the scripts to be
> automatically
> >> generated monthly instead of doing manually - pls help.
> >>
> >> regards,
> >> bharath
> >
> >
> >.
> >|||There is also a nice program to do this that is included in Ken Hendersons
new book "SQL Server Architecture"
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> Hi,
> Is it possible to automate the 'All tasks - generate
> sqlscripts' for tables and stored procs thru SQL
> enterprise manager. I want the scripts to be automatically
> generated monthly instead of doing manually - pls help.
> regards,
> bharath|||bharath,
Well, off the top of my head, there's an SQL Server class, and first you
create an instance of that.
Then there's the databases collection off of the Server class, and you drill
down through the object hierarchy to get at the .Script method of the actual
object you're interested in.
There are separate collections for Tables, Views, StoredProcedures, etc.
Anyhow, there are a bizzillion flags you can set, such as whether or not to
include permissions, indexes, triggers, etc.
As I said, all the documentation you'll ever need is in Books Online, so I
can't really give you 'sample code'. I don't even know what language you're
planning to write it in.
James Hokes
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...
> Hi,
> JAmes.. thanks a lot.. can u give me an example so that i
> understand better (regarding the apps)...
> regards,
> bharath
> >--Original Message--
> >bharath,
> >
> >Make and application that leverages the SQL-DMO libraries.
> >There are classes which can be used to generate scripts.
> >
> >You may also use sp_OA* procs instead of a little app,
> >but I have done so, and I will advise you that
> >it is an exercise in tedium. Better to make a little app.
> >
> >See "SQL-DMO" in Books Online.
> >
> >James Hokes
> >
> >"bharath" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> >> Hi,
> >>
> >> Is it possible to automate the 'All tasks - generate
> >> sqlscripts' for tables and stored procs thru SQL
> >> enterprise manager. I want the scripts to be
> automatically
> >> generated monthly instead of doing manually - pls help.
> >>
> >> regards,
> >> bharath
> >
> >
> >.
> >|||Dejan,
That articles does not mention the sp_OA* variation on this concept.
Looks more like VBA in Microsoft Access.
James Hokes
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#5FRhuj5DHA.488@.TK2MSFTNGP12.phx.gbl...
> For sp_OA* variant, you can find a nice script at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;233392.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "bharath" <anonymous@.discussions.microsoft.com> wrote in message
> news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...
> > Hi,
> >
> > JAmes.. thanks a lot.. can u give me an example so that i
> > understand better (regarding the apps)...
> >
> > regards,
> > bharath
> >
> > >--Original Message--
> > >bharath,
> > >
> > >Make and application that leverages the SQL-DMO libraries.
> > >There are classes which can be used to generate scripts.
> > >
> > >You may also use sp_OA* procs instead of a little app,
> > >but I have done so, and I will advise you that
> > >it is an exercise in tedium. Better to make a little app.
> > >
> > >See "SQL-DMO" in Books Online.
> > >
> > >James Hokes
> > >
> > >"bharath" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> > >> Hi,
> > >>
> > >> Is it possible to automate the 'All tasks - generate
> > >> sqlscripts' for tables and stored procs thru SQL
> > >> enterprise manager. I want the scripts to be
> > automatically
> > >> generated monthly instead of doing manually - pls help.
> > >>
> > >> regards,
> > >> bharath
> > >
> > >
> > >.
> > >
>sql

automatic process

I need to automatically generate via SQL, export to Excel and e-mailed
to other people montly. what should I setup in the sql server?Much too vague to do anything but guess at an answer.
One wild idea is a Task that launches Excel which in turn runs a stored
procedure to return the results set into excel. Another task could send the
email with attached file.
Could also run a process from the OS level , running the query output into
excel and then email it.

"TedJM" <ted_gear@.hotmail.com> wrote in message
news:55b4f192.0412011101.15038fe4@.posting.google.c om...
>I need to automatically generate via SQL, export to Excel and e-mailed
> to other people montly. what should I setup in the sql server?|||TedJM,

Take a look at Gadami: http://www.kripsoft.com. It's a program (EXE)
that lets you output the results of one or more SQL queries to Excel
and email them using an available SMTP server.

-Krip|||The best way is to create a DTS package, that uses a query to output a
file with a pre-defined global variable as the path and file anme and
use sql mail to generate an email with an attachment.... And schedule
this DTS in a sql job...!

automatic printing from trigger

Hi,
I am new to Sql Server 2000. I am looking for help on how to automatically generate a report to a network printer after a record is added/updated/changed in a table. How should I do this? With a Trigger? With DTS?
Please help.With a Trigger ...|||Thanks for the info. I think I was unclear in my initial question and would like to know what are the exact steps needed to perform this function? I don't know how to create a trigger to do this and would be looking for an example of some sort or instructions for the code.

Any help greatly appreciated.|||What about sp_OA... procedures, write component in any language
and use these SP for communication.

Export with DTS or SQLDMO objects.

Multitask temp tables with :
1.X=OBJECT_ID('temp..#temptbl')
2.Send X to object
3.Get temp name
use tempdb
GO
Y=OBJECT_NAME(X)

Too much work to be done ...
MSSQLSERVER2K has native support for e-mail and pager.

Automate Generate Sql Script?

Hi
I would like to autmate the Generate Sql Script.
I am running NUnit testing with one of my projects and i am resetting the
database frequently
Now, whenever i make any change to my database, i need to regenerate the Sql
Script for this to work.
This proves quite monotonous.
Is there a way to automate this process?
What i'm doing now:
1) Right click DB, click all tasks -> Generate Sql Script
2) Click Show All, then Script all objects
3) Click options, check all the Table Scripting Options
4) Click Ok
5) Navigate to where i would like the script saved (defaults to My
Documents every time, i don't want it there)
6) Save and click yes to replace the existing script
So, this gets quite repetitive and annoying at times.
Any suggestions (other then just editing the Sql Script myself)
TIA
Grant,
SQLDMO would be the obvious choice and Googling for it will show you several
links (eg http://www.dbazine.com/sql/sql-articles/larsen4).
If you want to use it directly, most objects have a Script method. If you
want a convenient wrapper around the SQLDMO code, there is an exe that you
could experiment with, eg this will script out the northwind database on
lon999:
"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s lon999
/I /d northwind /f c:\masterscriptfile.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grant Merwitz" <grant@.workshare.com> wrote in message news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the database frequently
> Now, whenever i make any change to my database, i need to regenerate the Sql Script for this to
> work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My Documents every time, i
> don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>
|||Thanks Tibor and Paul for both your answers
I ended up using "scptxfr.exe" as it was so simple
Thanks Again
"Grant Merwitz" <grant@.workshare.com> wrote in message
news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
> database frequently
> Now, whenever i make any change to my database, i need to regenerate the
> Sql Script for this to work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My
> Documents every time, i don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>

Automate Generate Sql Script?

Hi
I would like to autmate the Generate Sql Script.
I am running NUnit testing with one of my projects and i am resetting the
database frequently
Now, whenever i make any change to my database, i need to regenerate the Sql
Script for this to work.
This proves quite monotonous.
Is there a way to automate this process?
What i'm doing now:
1) Right click DB, click all tasks -> Generate Sql Script
2) Click Show All, then Script all objects
3) Click options, check all the Table Scripting Options
4) Click Ok
5) Navigate to where i would like the script saved (defaults to My
Documents every time, i don't want it there)
6) Save and click yes to replace the existing script
So, this gets quite repetitive and annoying at times.
Any suggestions (other then just editing the Sql Script myself)
TIAGrant,
SQLDMO would be the obvious choice and Googling for it will show you several
links (eg http://www.dbazine.com/sql/sql-articles/larsen4).
If you want to use it directly, most objects have a Script method. If you
want a convenient wrapper around the SQLDMO code, there is an exe that you
could experiment with, eg this will script out the northwind database on
lon999:
"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s lon999
/I /d northwind /f c:\masterscriptfile.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grant Merwitz" <grant@.workshare.com> wrote in message news:eedutdS4FHA.3460@.TK2MSFTNGP12.ph
x.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
database frequently
> Now, whenever i make any change to my database, i need to regenerate the S
ql Script for this to
> work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My Docu
ments every time, i
> don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>|||Thanks Tibor and Paul for both your answers
I ended up using "scptxfr.exe" as it was so simple
Thanks Again
"Grant Merwitz" <grant@.workshare.com> wrote in message
news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
> database frequently
> Now, whenever i make any change to my database, i need to regenerate the
> Sql Script for this to work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My
> Documents every time, i don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>

Automate Generate Sql Script?

Hi
I would like to autmate the Generate Sql Script.
I am running NUnit testing with one of my projects and i am resetting the
database frequently
Now, whenever i make any change to my database, i need to regenerate the Sql
Script for this to work.
This proves quite monotonous.
Is there a way to automate this process?
What i'm doing now:
1) Right click DB, click all tasks -> Generate Sql Script
2) Click Show All, then Script all objects
3) Click options, check all the Table Scripting Options
4) Click Ok
5) Navigate to where i would like the script saved (defaults to My
Documents every time, i don't want it there)
6) Save and click yes to replace the existing script
So, this gets quite repetitive and annoying at times.
Any suggestions (other then just editing the Sql Script myself)
TIAGrant,
SQLDMO would be the obvious choice and Googling for it will show you several
links (eg http://www.dbazine.com/sql/sql-articles/larsen4).
If you want to use it directly, most objects have a Script method. If you
want a convenient wrapper around the SQLDMO code, there is an exe that you
could experiment with, eg this will script out the northwind database on
lon999:
"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s lon999
/I /d northwind /f c:\masterscriptfile.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grant Merwitz" <grant@.workshare.com> wrote in message news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the database frequently
> Now, whenever i make any change to my database, i need to regenerate the Sql Script for this to
> work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My Documents every time, i
> don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>|||Thanks Tibor and Paul for both your answers
I ended up using "scptxfr.exe" as it was so simple
Thanks Again
"Grant Merwitz" <grant@.workshare.com> wrote in message
news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
> database frequently
> Now, whenever i make any change to my database, i need to regenerate the
> Sql Script for this to work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My
> Documents every time, i don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>

Automate a sql server restore

Is there a way to automate a sql server restore? I have a db that I need to include in an installation. I could use generate script but there is info in the db that needs to be copied too. What is the best way to do this?
Thanx
weisenbrHow much data do you need to add? If it's a minimal amount, it can be done through scripting. I would recommend the IbuySpy portal db scripts as a way of seeing how this is done. Probably the Portal Starter kit has this as well, but I haven't looked into that yet. But download one of those, and check out their scripts and you'll see how they can insert data.

If it's a lot of data, you could also do automated backups (using EM). I haven't done this in an installation, but you could also look into attaching a database (sp_attach_single_file_db). I would only recommend this route if the folks installing the db are sql server savvy or at least have a good knowledge of IT methodology.

Personally I think scripting your database tables, and even initial data is the way to go. But I'm not sure if you're talking small amounts of data or large amounts.

AutoIncrement Fields

I am trying to upsize a Microsoft Access database.
I have used Autoincrement/random to generate some key fields
Is there a way to duplicate this fuctionality in MSDE? ( I can find the
ability to increment starting with a seed)
thanks
Ed Warren.
Hi,
See IDENTITY property in SQL Server books online. Usage is
CREATE TABLE TESTTABLE(i int IDENTITY(1,1), Name Varchar(10))
So the value for i start with 1 and increment by 1
Thanks
Hari
SQL Server MVP
"Ed Warren" <eowarren@.fakeaddress.zzz> wrote in message
news:exV1d5rNFHA.1500@.TK2MSFTNGP09.phx.gbl...
>I am trying to upsize a Microsoft Access database.
> I have used Autoincrement/random to generate some key fields
> Is there a way to duplicate this fuctionality in MSDE? ( I can find the
> ability to increment starting with a seed)
> thanks
> Ed Warren.
>
|||Ed,
You can use a trigger to give you a random auto increment - the upsizing
wizard can do this for you or you can add your own similar to
CREATE TRIGGER [StudentsTrig] ON dbo.Students
FOR INSERT
AS
SET NOCOUNT ON
Declare @.randc int, @.newc int
SET @.randc=0
WHILE @.randc=0
BEGIN
SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
END
SELECT @.newc = (SELECT [Stud ID] FROM inserted)
UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
You can modify the select statement if you want to allow negative numbers
(like Access).
Russ Stevens
|||That's what I'm looking for, thanks a lot
Ed Warren
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:e3FZJAtNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Ed,
> You can use a trigger to give you a random auto increment - the upsizing
> wizard can do this for you or you can add your own similar to
> CREATE TRIGGER [StudentsTrig] ON dbo.Students
> FOR INSERT
> AS
> SET NOCOUNT ON
> Declare @.randc int, @.newc int
> SET @.randc=0
> WHILE @.randc=0
> BEGIN
> SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
> END
> SELECT @.newc = (SELECT [Stud ID] FROM inserted)
> UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
> You can modify the select statement if you want to allow negative numbers
> (like Access).
> Russ Stevens
>

Autogenerate reply or ServerProc generated message............

Hello,

I got my sample application to work that I am building my proof of concept out of. I need to be able to auto generate a reply message that would normally be in the run routine. The only way I see to do this is pull it from a table but I do not want to do that. I have tried tests where I change the code to see if I can send a message back but I have to reinstall the assembly into the database which is not what I am looking for. I am looking for a way to change the message by either accessing the GUI and getting the string, calling another function to do this, or something like that. I want it so I can change the code in the run routine in VS 2005 but this does not work. I am sure their is a trick to do this but am not sure what that trick is. What is a good way to do this other than accessing a table in the database?

Thanks,

Scott Allison...

What do you mean by auto-generate a reply? A reply could either be static (i.e. independent of the request and state), a stateless (i.e. dependent only on the request) or stateful (i.e. dependent on both the request as well as some state... the state would normally be stored in the database, but may also be stored separately). I cannot see why you need to recompile and redeploy your app to meet any of the above patterns.

Rushi

|||

Hello Rushi,

It calls the ServiceProc in my code and that is loaded into the database. I am writing this for applications that will need to report the status of a current event. If this is not stored in the database then they would not want to write anything to the database. This means they will get the request then need to reply to that request with a text message that they generate. In the worst case situation it can come from the database but would rather just be retrieved from a function call. It seems as though from your message this can be done so I am sure I have a setting incorrect. What I have is currently static and I want the response dependent on the request. What I meant from Autogenerated is retrieved from another assembly or another part of that service.

Thanks,

Scott Allison...

|||If the function that generates a response from request is a method in a different assembly, you could deploy that assembly into the database and simply invoke the method from ServiceProc.|||

Hello Rushi,

This is a remote system and will not be able to deploy all of the assemblies to the database. It consists of several services and needs dynamic access to what the message. Are you telling me that the only way to get a dynamic message is to write it to a database and have the ServiceProc read it? This is a draw back beacuse it will slow things down but I want to make sure before I tell people that they have to do that.

Thanks,

Scott Allison...

Autofill Date Parameters do not work when deployed to server

Good morning all,

I have a report which measures supplier performance for the previous month. It takes an age to generate so I am trying to cache a copy to a null location first thing in the morning to speed up the process. The problem I'm having is in getting the report to select the first day and last day of the month for the two parameters that the report needs to run.

In BIDS the report runs perfectly when previewed, however, when it is deployed to the report server I get the following error: 'Error during processing of ‘RP2’ report parameter. (rsReportParameterProcessingError)'

I have used the following two statements for the default values of the parameters, which work in BIDS, so I can't understand why they don't when it's deployed. I'm also sure there is an easier way of doing this, but after about an hour searching yesterday and not finding anything it only took me about half that time to use these statements:

for opening date:

=IIf(Month(Now()) = 1, CDate("01/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("01/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("01/02/" & Year(Now())), IIf(Month(NOw()) = 4, CDate("01/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("01/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("01/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("01/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("01/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("01/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("01/09/" & Year(Now())), IIf(Month(Now()) = 11, Cdate("01/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("01/11/" & Year(Now())), CDate("01/12/1900")))))))))))))

for closing date (pretty similar really, this is the parameter with which the report server finds an error):

=IIf(Month(Now()) = 1, CDate("31/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("31/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("28/02/" & Year(Now())), IIf(Month(Now()) = 4, CDate("31/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("30/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("31/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("30/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("31/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("31/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("30/09/" & Year(Now())), IIf(Month(Now()) = 11, CDate("31/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("30/11/" & Year(Now())), CDate("31/12/1900")))))))))))))

The only caviate to using these statements is that it wont recognise when a leap year occurs, other than that, if it would work when deployed to the report server it would work perfectly for the purposes of what we need.

If anyone can see the flaw, or knows of a better and easier way of doing this please let me know.

Humble thanks,

Paul

I think I've found out what is going on, just not sure how to fix it yet.

The problem is unique to me, everyone else can run the report from the report server and the auto date parameters work just fine.

When I built the report I used English United Kingdom as the default language, the report server's language is set as default to English United Kingdom, as is my machine. For some reason when I try to view the report it changes the date format to English US, or some other equivalent mm/dd/yyyy format and as there are only 12 months in a year the end date parameter falls over because it sees the day as being a month. However, no one else in our organisation has this problem and the report runs perfectly with the parameters being filled automatically as they should.

If I find the reason why my system default date format is being bypassed I'll post it on the forum in case anyone else encounters a similar problem.

Paul

|||

Okay, I've been a dumb schmuck, the language in Internet Explorer was set as English US, and as Reporting Services is a Web-based service it was using this information as the default language setting.

Since I've changed it to English UK everything works as it should.

Paul

Auto SQL Mail

Hi,

I have to generate mails automatically based on databse (SQL SERVER) table,In that table we have expirydate as one column and

based on expirydate I have to generate the mails automatically,Please guide me to solve this issue.

where we have to run the stored procedure.

Do we have to use jobscheduler?

please guide me how to use it

Thanks in avance

regards,

Raja.

Hi,

check following links ...

you have to schedule store procedure ...which will send mail ...

http://support.microsoft.com/kb/312839

http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054928,00.html

Auto Restore of Production to Developement Db on same Server

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

IS there an easier way.

rik

Howdy;

I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.

anyway here is the problem

[this server has many databases, on SQL 2000 sp2]

1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.

2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.

3. User does NOT want me to do this by hand, but schedule it.

ok,

a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has

a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:\MSSQL\BACKUP\DB\PRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.

Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ?

then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.

any help appreciated.

rik

|||

Here is a script that dump the filename sorted by latest date last. You should be to process it and create your desired restore statement.

create table #tb(i int identity primary key, name nvarchar(80) null)

declare @.sql nvarchar(1000), @.dir sysname
set @.dir='c:\windows'
set @.sql='dir '+@.dir+' /b /od /aa /ar'

insert #tb(name)
exec xp_cmdshell @.sql

select * from #tb

|||

ok, this was what I was afraid of: having to always write sql code.

oh, well. Too bad the EM just doesn't give you the option to ALWAYS ask if you want to schedule what you have just done as a job. That way I could create code on the fly by drag and drop.

|||

If you feel this can make your life and others easier, file a request at http://connect.microsoft.com/sqlserver

Be sure to include a business case if you want it to have any merit.

|||

thanks

appreciate it.

rik