Automation

How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
Dee
Hi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:

> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee
|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
[vbcol=seagreen]
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
[vbcol=seagreen]
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:

Automation

How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist an
d
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connectio
n
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:

> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a differenc
e
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the version of SQL Server that you are using! You can write
a
> stored procedure that will create the database/table if they do not exist
and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connect
ion
> properties.
> You could use OPENXML to load the file and compare the two entries (assumi
ng
> the same structure) and FOR XML to produce your output which would not nee
d
> DTS/SSIS.
> John
> "Dee" wrote:
>|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodin...-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
[vbcol=seagreen]
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
>|||But I also have SQL 2005 Standard installed. Can I do an Import/Export fro
m
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodin...-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) i
s
> probably the way to go.
> John
> "Dee" wrote:
>|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
[vbcol=seagreen]
> But I also have SQL 2005 Standard installed. Can I do an Import/Export f
rom
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
>

Automation

How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
> > How can I import an xml file to SQL at the same time every night? I will
> > need to create a new database first via the import after that I will be
> > appending to the database. Then I need to xport the data into a difference
> > xml file.
> >
> > Do I have to have the orginal xml file on my server or can I point to the
> > location of the xml file?
> >
> > Thank you
> > Dee|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
> > Hi
> >
> > You don't give the version of SQL Server that you are using! You can write a
> > stored procedure that will create the database/table if they do not exist and
> > then pass the database name to a DTS/SSIS package that will load the file.
> > Using this global variable for the package you can then change the connection
> > properties.
> >
> > You could use OPENXML to load the file and compare the two entries (assuming
> > the same structure) and FOR XML to produce your output which would not need
> > DTS/SSIS.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > How can I import an xml file to SQL at the same time every night? I will
> > > need to create a new database first via the import after that I will be
> > > appending to the database. Then I need to xport the data into a difference
> > > xml file.
> > >
> > > Do I have to have the orginal xml file on my server or can I point to the
> > > location of the xml file?
> > >
> > > Thank you
> > > Dee|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
> > John,
> >
> > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > and the standard for Windows XP installed.
> >
> > Will this work for both.
> >
> > Thanks
> > Dee
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You don't give the version of SQL Server that you are using! You can write a
> > > stored procedure that will create the database/table if they do not exist and
> > > then pass the database name to a DTS/SSIS package that will load the file.
> > > Using this global variable for the package you can then change the connection
> > > properties.
> > >
> > > You could use OPENXML to load the file and compare the two entries (assuming
> > > the same structure) and FOR XML to produce your output which would not need
> > > DTS/SSIS.
> > >
> > > John
> > >
> > > "Dee" wrote:
> > >
> > > > How can I import an xml file to SQL at the same time every night? I will
> > > > need to create a new database first via the import after that I will be
> > > > appending to the database. Then I need to xport the data into a difference
> > > > xml file.
> > > >
> > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > location of the xml file?
> > > >
> > > > Thank you
> > > > Dee|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
> > Hi
> >
> > Import/Export and Integration services is not on the feature list for SQL
> > Express see
> > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> > Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> > probably the way to go.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > John,
> > >
> > > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > > and the standard for Windows XP installed.
> > >
> > > Will this work for both.
> > >
> > > Thanks
> > > Dee
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You don't give the version of SQL Server that you are using! You can write a
> > > > stored procedure that will create the database/table if they do not exist and
> > > > then pass the database name to a DTS/SSIS package that will load the file.
> > > > Using this global variable for the package you can then change the connection
> > > > properties.
> > > >
> > > > You could use OPENXML to load the file and compare the two entries (assuming
> > > > the same structure) and FOR XML to produce your output which would not need
> > > > DTS/SSIS.
> > > >
> > > > John
> > > >
> > > > "Dee" wrote:
> > > >
> > > > > How can I import an xml file to SQL at the same time every night? I will
> > > > > need to create a new database first via the import after that I will be
> > > > > appending to the database. Then I need to xport the data into a difference
> > > > > xml file.
> > > > >
> > > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > > location of the xml file?
> > > > >
> > > > > Thank you
> > > > > Dee

Automating SQL Setup.

Is there a way that I can Automate SQL Setup via C#, PERL or VB?Hi

When you install SQLServer one of the Advanced options is to record a setup
script. This will allow you to do unattanded installs. There would be no
pressing reason to use a program to run this unless but you could probably
do it by spawning a process.

John

"KP" <snazystyl@.hotmail.com> wrote in message
news:dc2b0830.0310141453.3d489efb@.posting.google.c om...
> Is there a way that I can Automate SQL Setup via C#, PERL or VB?|||snazystyl@.hotmail.com (KP) wrote in message news:<dc2b0830.0310141453.3d489efb@.posting.google.com>...
> Is there a way that I can Automate SQL Setup via C#, PERL or VB?

See the sections "Performing an Unattended Installation" and
"Installing Desktop Engine" in Books Online, depending on whether you
want to install the full version of MSSQL, or MSDE.

Simon

automatically running sql query every week

I have a simple query (a select statement which retrieves results from 2 tables via join). I run this query every week and just copy the results from Management Studio (Ctrl-A) and then open Microsoft Excel and just paste it there.

Is there a way I can automatically run this every week and generate the Excel from the results? If so what steps I have to do?

I use SQL Server 2005 Express Edition.

You can use a DTS in SQL Server to schedule this operation to run every week. Have a look at this article:

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

|||

Does SQL 2005 also have DTS? I didnt see it, may be its hidden somewhere else? Because I am using SQL Server 2005 express edition (the free one)

|||

Nope, Express edition does not have DTS.

|||

You can code a windows script (vb.net or c#) using ADO to grab the data and the Excel object model to write out the spreadsheet.

Then schedule the script with the Windows scheduler.

Company I worked for do all their reporting that way. The script can also email the spreadsheet to a suitable distribution list etc.

|||

Hidotnet001 ,

bullpit:

You can use a DTS in SQL Server to schedule this operation to run every week. Have a look at this article:

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

bullpit is right. You can use DTS if you are using sql2000, or SSIS if you are using sql2005. Try to generate a SSIS package and put it under intergration service, after which you will be able to run it automatically.

But since you are using sql express, i would suggset you adopting the approachSalmonTraining suggested above. Use ADO.NET programming to write some programms and schedule it through windows schedule (you will have to handle pretty lots of more things in this case than in the first approach).

Hope my suggestion can help

sql

Automatically Preview reports

Hi all. Great Forum by the way

I have a question. I have some crystal reports that people open via a hyperlink on a web page. This brings up the crystal report but people have to then "refresh" or "run" the report themselves. They are then prompted for a "start" and "end" date for report.

Is it possible to have the report automically open prompting for the start and end dates? In other words open and run automically?

Regards

Johnu didn't mention what u r using to develop web pages.

If asp.net then use crystalreportviewer control it should automatically prompt for parameters.

Make sure u r not saving data with report.

I Hope this helps..

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 Execution of a Stored Proceedure

How do you set a stored proceedure for automatic execution?

--
Message posted via http://www.sqlmonster.comIf you mean execute it at a certain time, then you can create a
scheduled job to run it at that time - "Scheduling Jobs" in Books
Online. If you want to run a proc automatically when MSSQL starts, see
"Automatic Execution of Stored Procedures" and sp_procoption.

Simon

Automated regression testing of a cube

I am playing around in my head with the idea of automated regression testing of a cube or part of a cube or via a cube. We have a fairly complex import of production data into a small data warehouse. Sometimes we or the users spend significant time manually checking values in the cube or reports, and I was thinking of trying to automate some of the checking. I poked around online and didn't find anything so I am posting here.

Can anyone point me to articles or examples of automated regression testing of SSAS cubes? By "regression testing" I mean comparing current results with expected results and recognizing differences between them.

Here are some ideas I've had. The ideas properly reflect my ignorance and lack of clarity.

Import results into Excel and use some Excel comparison utility

Import results into Excel, save as XML, and use a diff utility

Use a diff utility on some XML representation of cube values

Automate some sort of programmatic query (SSIS script task? PowerShell?)

This is not a high-volume application and the cube is not very complex.

you could try to create a measure in your cube that uses linear regression to forecast your last period (e.g. the month or day you are importing) and then use this value to check against your real ones.

look at moshas blog (http://sqljunkies.com/WebLog/mosha/archive/2004/12/21/5689.aspx) to get a starting point for linear regression.

kind regards

markus

|||

I think that idea (an interesting one, by the way) addresses a different problem from the one I'm trying to ask about. So I will try to be clearer.

We have a complex SSIS process that imports data into our database and populates our cube. When we change parts of the process or the cube definition I want to reassure myself that we haven't broken other things we didn't intend to change. Also I'd like to be able to validate the setup when it's first deployed on a new server. My notion was that for a standard set of input data I'd somehow capture results. Then I'd run the same set of data through the modified or newly deployed setup and capture results from that. Then by some automated process (analogous to diff for simple text files) I'd compare the standard saved results with the newly captured results and identify differences. In the case of a new deployment there ought not to be any differences; in the case of changes there ought to be only differences intended as a result of the changes. In all cases of course one has to handle (by exclusion or substitution) things which look different but really aren't -- e.g., newly assigned sequential integer keys, or server names, or stuff like that.

So my list of alternatives in my first message is speculating about automated (or semi-automated) ways to capture results sets and automated ways to compare results sets. Importing defined parts of the cube into Excel seemed a possible way to reliably and consistently grab cube results, and XML files seemed a static, human-inspectable way to store results. Smart XML diff engines (that do not do mere text comparisons but pay attention to the XML elements and hierarchies) seemed a possible automated way to compare results sets.

automate Access project connection to MSDE database

I'm using Visual C++ to insert records into my MSDE database. I'd like the
user to be able to view the data via Access projects. In my application, is
it possible to configure the connection settings for an Access project to
connect to the MSDE database? For example, if I were to click the View button
in my application, can I have Access project connect to the database behind
the scenes and then display the contents of the database?
Thanks!
You can use automation to launch Access and display forms and reports.
I'm not a C++ programmer, so I couldn't tell you *how* to do that.
However, I'm not sure I really understand what you are trying to do.
The phrase, "display the contents of the database" covers a lot of
ground. Perhaps you can be more specific.
--Mary
On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:

>I'm using Visual C++ to insert records into my MSDE database. I'd like the
>user to be able to view the data via Access projects. In my application, is
>it possible to configure the connection settings for an Access project to
>connect to the MSDE database? For example, if I were to click the View button
>in my application, can I have Access project connect to the database behind
>the scenes and then display the contents of the database?
>Thanks!
|||When you create a new access project that connects to an existing database
there are certain settings that the wizard asks of you before connection to
the database can be made. How do I pass those parameters from my application
to access project? Once the project has been created after the connections
settings have been configured, the user will have full access to the tables
within the database. The user can then create queries and reports as needed.
Does this explain things a little better?
"Mary Chipman [MSFT]" wrote:

> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||When you create a new access project, there are connection settings that need
to be configured before a connection can be made. How do I pass those
parameters to the connection wizard via my application? Once the connection
is made to the MSDE database, Access will then display all the tables in the
database. The user can then generate queries or reports as needed. Forms are
not necessary because if I were to create custom forms I would have created
it within my application instead of connecting to an Access database. The
reason I chose to use Access project is because I'd like the users to be able
to create queries and reports of their choosing. My job is just to dump the
data into the table and have them decide on the type of queries and reports
to generate. Is this more clear?
"Mary Chipman [MSFT]" wrote:

> You can use automation to launch Access and display forms and reports.
> I'm not a C++ programmer, so I couldn't tell you *how* to do that.
> However, I'm not sure I really understand what you are trying to do.
> The phrase, "display the contents of the database" covers a lot of
> ground. Perhaps you can be more specific.
> --Mary
> On Fri, 4 Feb 2005 15:13:02 -0800, "luv2travel"
> <luv2travel@.discussions.microsoft.com> wrote:
>
>
|||I understand now. However, your choice to use an Access project is a
bad one. Users will not be able to create and save local queries
(although they can save local reports). You would need to grant them
permissions to create views or stored procedures on the server, and
this you probably don't want to do for two reasons: (1) security and
(2) clogging the server with a lot of user objects. Unless a user is
connected as a sysadmin, they will own all their own objects and will
need to grant others permissions to use them, which you don't want to
get into. In your situation, I'd use an .mdb. You can programmatically
link tables at runtime using DAO, and if users create their own
queries, they are saved locally, not on SQL Server. HTH,
Mary
On Sun, 6 Feb 2005 23:03:08 -0800, "luv2travel"
<luv2travel@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When you create a new access project, there are connection settings that need
>to be configured before a connection can be made. How do I pass those
>parameters to the connection wizard via my application? Once the connection
>is made to the MSDE database, Access will then display all the tables in the
>database. The user can then generate queries or reports as needed. Forms are
>not necessary because if I were to create custom forms I would have created
>it within my application instead of connecting to an Access database. The
>reason I chose to use Access project is because I'd like the users to be able
>to create queries and reports of their choosing. My job is just to dump the
>data into the table and have them decide on the type of queries and reports
>to generate. Is this more clear?
>"Mary Chipman [MSFT]" wrote:

autogrow speed

Hi,
SQL 2000
We set up all of our databases to autogrow 10%.
Given a 4G database, how fast would 10% autogrowth be?
How can I get notified via e-mail whenever autogrowth occurs?
Would autogrowth be logged in the SQL 2000 server log?
I'd appreciate if anybody can shed some light or point me to the right
direction to figure out myself.
Thanks,
Bing> Given a 4G database, how fast would 10% autogrowth be?
Create a database with a tiny log file and a 400MB size datafile and you will see how your
disksubsystem will perform. Autogrow performs the same way as the initial creation. On my machine
(desktop, 7200 RPM IDE) it took 8 seconds.
> How can I get notified via e-mail whenever autogrowth occurs?
Not easily. You can catch it through a Profiler trace. In order for you to get notified of it, you
would have to use the Profiler GUI and log to a table, on which you have a trigger defined.
> Would autogrowth be logged in the SQL 2000 server log?
No.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
>
As Tibor says test it.
However, I would NOT use 10% autogrowth.
If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
400MB of disk space.
Next time, 440 MB.
After that 484 MB.
And so on.
So each time it takes longer and longer.
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the response. That makes sense. Sounds like using percentage is
not good if autogrowth is performed frequently.
"Greg D. Moore (Strider)" wrote:
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> > Hi,
> >
> > SQL 2000
> >
> > We set up all of our databases to autogrow 10%.
> >
> > Given a 4G database, how fast would 10% autogrowth be?
> > How can I get notified via e-mail whenever autogrowth occurs?
> > Would autogrowth be logged in the SQL 2000 server log?
> >
> As Tibor says test it.
> However, I would NOT use 10% autogrowth.
> If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
> 400MB of disk space.
> Next time, 440 MB.
> After that 484 MB.
> And so on.
> So each time it takes longer and longer.
>
> > I'd appreciate if anybody can shed some light or point me to the right
> > direction to figure out myself.
> >
> > Thanks,
> >
> > Bing
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:B410EBB9-84DA-4557-B978-046FF0099275@.microsoft.com...
> Thanks for the response. That makes sense. Sounds like using percentage
> is
> not good if autogrowth is performed frequently.
>
Actually if you're performing autogrowth with any frequency you probably
have a problem.
You should try to manually manage space and grow it as necessary (if at all)
at slow times.
> "Greg D. Moore (Strider)" wrote:
>>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

autogrow speed

Hi,
SQL 2000
We set up all of our databases to autogrow 10%.
Given a 4G database, how fast would 10% autogrowth be?
How can I get notified via e-mail whenever autogrowth occurs?
Would autogrowth be logged in the SQL 2000 server log?
I'd appreciate if anybody can shed some light or point me to the right
direction to figure out myself.
Thanks,
Bing
> Given a 4G database, how fast would 10% autogrowth be?
Create a database with a tiny log file and a 400MB size datafile and you will see how your
disksubsystem will perform. Autogrow performs the same way as the initial creation. On my machine
(desktop, 7200 RPM IDE) it took 8 seconds.

> How can I get notified via e-mail whenever autogrowth occurs?
Not easily. You can catch it through a Profiler trace. In order for you to get notified of it, you
would have to use the Profiler GUI and log to a table, on which you have a trigger defined.

> Would autogrowth be logged in the SQL 2000 server log?
No.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
>
As Tibor says test it.
However, I would NOT use 10% autogrowth.
If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
400MB of disk space.
Next time, 440 MB.
After that 484 MB.
And so on.
So each time it takes longer and longer.

> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks for the response. That makes sense. Sounds like using percentage is
not good if autogrowth is performed frequently.
"Greg D. Moore (Strider)" wrote:

>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> As Tibor says test it.
> However, I would NOT use 10% autogrowth.
> If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
> 400MB of disk space.
> Next time, 440 MB.
> After that 484 MB.
> And so on.
> So each time it takes longer and longer.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>
|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:B410EBB9-84DA-4557-B978-046FF0099275@.microsoft.com...
> Thanks for the response. That makes sense. Sounds like using percentage
> is
> not good if autogrowth is performed frequently.
>
Actually if you're performing autogrowth with any frequency you probably
have a problem.
You should try to manually manage space and grow it as necessary (if at all)
at slow times.
[vbcol=seagreen]
> "Greg D. Moore (Strider)" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

autogrow speed

Hi,
SQL 2000
We set up all of our databases to autogrow 10%.
Given a 4G database, how fast would 10% autogrowth be?
How can I get notified via e-mail whenever autogrowth occurs?
Would autogrowth be logged in the SQL 2000 server log?
I'd appreciate if anybody can shed some light or point me to the right
direction to figure out myself.
Thanks,
Bing> Given a 4G database, how fast would 10% autogrowth be?
Create a database with a tiny log file and a 400MB size datafile and you wil
l see how your
disksubsystem will perform. Autogrow performs the same way as the initial cr
eation. On my machine
(desktop, 7200 RPM IDE) it took 8 seconds.

> How can I get notified via e-mail whenever autogrowth occurs?
Not easily. You can catch it through a Profiler trace. In order for you to g
et notified of it, you
would have to use the Profiler GUI and log to a table, on which you have a t
rigger defined.

> Would autogrowth be logged in the SQL 2000 server log?
No.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> Hi,
> SQL 2000
> We set up all of our databases to autogrow 10%.
> Given a 4G database, how fast would 10% autogrowth be?
> How can I get notified via e-mail whenever autogrowth occurs?
> Would autogrowth be logged in the SQL 2000 server log?
>
As Tibor says test it.
However, I would NOT use 10% autogrowth.
If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
400MB of disk space.
Next time, 440 MB.
After that 484 MB.
And so on.
So each time it takes longer and longer.

> I'd appreciate if anybody can shed some light or point me to the right
> direction to figure out myself.
> Thanks,
> Bing
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the response. That makes sense. Sounds like using percentage i
s
not good if autogrowth is performed frequently.
"Greg D. Moore (Strider)" wrote:

>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:715AA77D-9849-48AC-8CCA-7E862590769B@.microsoft.com...
> As Tibor says test it.
> However, I would NOT use 10% autogrowth.
> If you have a 4GB DB, the first time it tries to autogrow, it'll reserve
> 400MB of disk space.
> Next time, 440 MB.
> After that 484 MB.
> And so on.
> So each time it takes longer and longer.
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||"bing" <bing@.discussions.microsoft.com> wrote in message
news:B410EBB9-84DA-4557-B978-046FF0099275@.microsoft.com...
> Thanks for the response. That makes sense. Sounds like using percentage
> is
> not good if autogrowth is performed frequently.
>
Actually if you're performing autogrowth with any frequency you probably
have a problem.
You should try to manually manage space and grow it as necessary (if at all)
at slow times.
[vbcol=seagreen]
> "Greg D. Moore (Strider)" wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Auto-Grow and Shrink Via Code

Hey guys,
Does anybody know of a way to turn on the Auto-Grow feature for a
transaction log via T-SQL code? What we want to do is during a
scheduled job, turn the auto grow feature on then after the job is
finished, shrink the log, then cut auto grow back off?
Any thoughts? Thanks in advance!
Hunter
**************Please Post to Group so that all can benefit!Have you looked at the ALTER DATABASE topic in Books Online? Of particular
interest:
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic
shrinking.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Hunter" <bamared36054@.yahoo.com> wrote in message
news:2a0a8bc1.0312020836.3923452e@.posting.google.com...
> Hey guys,
> Does anybody know of a way to turn on the Auto-Grow feature for a
> transaction log via T-SQL code? What we want to do is during a
> scheduled job, turn the auto grow feature on then after the job is
> finished, shrink the log, then cut auto grow back off?
> Any thoughts? Thanks in advance!
> Hunter
> **************Please Post to Group so that all can benefit!|||Sorry, wrong section... 'grow' != 'shrink' Aaron! Look under filespec:
< filespec > ::=( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

auto number via a query

Hello,
I am wondering if somone could provide me with a sample SELECT for an
auto number query. For example I have a table called People with two columns
first_name, and last_name. There isn't a unique id to correspond with the
table but would like to dynamically make one during the return of the query.
So if there was 5 rows in the table it would return
1 John Alpha
2 John Beta
3 John Cat
4 John Delta
5 John Echo
Where the query was an order by last_name. Thanks in advance.
Jake"Jake Smythe" <someone@.microsoft.com> wrote in message
news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
> Hello,
> I am wondering if somone could provide me with a sample SELECT for an
> auto number query. For example I have a table called People with two
> columns first_name, and last_name. There isn't a unique id to correspond
> with the table but would like to dynamically make one during the return of
> the query. So if there was 5 rows in the table it would return
> 1 John Alpha
> 2 John Beta
> 3 John Cat
> 4 John Delta
> 5 John Echo
> Where the query was an order by last_name. Thanks in advance.
Something like this will work, you'll need to add the first name to the
comparison also.
CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
INSERT INTO People VALUES('John','Alpha')
INSERT INTO People VALUES('John','Beta')
INSERT INTO People VALUES('John','Cat')
INSERT INTO People VALUES('John','Delta')
INSERT INTO People VALUES('John','Echo')
SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
People.LastName) FROM People
ORDER BY LastName, FirstName
DROP TABLE People

> Jake
>|||While this kludge may work for the immediate need, you must be warned that t
here is no certainly that the order will be static. Each time the query exec
utes, the order may be different. And if will have problems with perfectly d
uplicate names. Try adding duplicate names and watch what happens...
SELECT
( SELECT sum(1)
FROM People p
WHERE ( p.LastName + p.FirstName ) <= ( p.LastName + p.FirstName )
) AS rownum
, p2.LastName
, p2.FirstName
FROM People p2
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Michael C" <nospam@.nospam.com> wrote in message news:%23j%23u8yZmGHA.4052@.TK2MSFTNGP05.phx
.gbl...
> "Jake Smythe" <someone@.microsoft.com> wrote in message
> news:O5zxrnZmGHA.4100@.TK2MSFTNGP05.phx.gbl...
>
> Something like this will work, you'll need to add the first name to the
> comparison also.
>
> CREATE TABLE People (FirstName VARCHAR(100), LastName VARCHAR(100))
> INSERT INTO People VALUES('John','Alpha')
> INSERT INTO People VALUES('John','Beta')
> INSERT INTO People VALUES('John','Cat')
> INSERT INTO People VALUES('John','Delta')
> INSERT INTO People VALUES('John','Echo')
> SELECT *, (SELECT COUNT(*) FROM People AS P1 WHERE P1.LastName <=
> People.LastName) FROM People
> ORDER BY LastName, FirstName
> DROP TABLE People
>
>
>
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
While this kludge may work for the immediate need, you must be warned that
there is no certainly that the order will be static. Each time the query
executes, the order may be different. And if will have problems with
perfectly duplicate names. Try adding duplicate names and watch what
happens...
I was going to add a warning that this method wasn't perfect but I had to
race off so just hit send. Of course the value will change if the order
changes :-) As for duplicates you'd just need to use the fields that make up
the primary key, if rows are duplicated then maybe they should have the same
values anyway. Maybe performance of this method might be a problem?
Michael|||Guys thanks for the responses. It's fine if the order changes each time I am
just looking for a identifier at run time.
"Michael C" <nospam@.nospam.com> wrote in message
news:e0x5fZbmGHA.4064@.TK2MSFTNGP02.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eQ0TcDamGHA.4076@.TK2MSFTNGP05.phx.gbl...
> While this kludge may work for the immediate need, you must be warned that
> there is no certainly that the order will be static. Each time the query
> executes, the order may be different. And if will have problems with
> perfectly duplicate names. Try adding duplicate names and watch what
> happens...
> I was going to add a warning that this method wasn't perfect but I had to
> race off so just hit send. Of course the value will change if the order
> changes :-) As for duplicates you'd just need to use the fields that make
> up the primary key, if rows are duplicated then maybe they should have the
> same values anyway. Maybe performance of this method might be a problem?
> Michael
>