How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups
.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>

How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
ThanksSET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
--
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegroups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegroups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>sql

How do I measure the cost of compiling an execution plan?

My company is using a lot of ad hoc queries and I am trying to convince
them to move to stored procedures. However certain managers don't
believe that the compilation of ad hoc queries and the fact that they
usually aren't reused is really anything to worry about. They think
that the cost of doing this is minimal and instead want to focus on
tuning bad queries.
I of course agree that badly written queries should be tuned but it's
also a basic tenet of databases users that stored procedures should
always be used where possible to take advantage of precompilation and
plan caching. However I don't know how to actually prove that with
data.
How can I measure what the overhead is for compiling and building an
execution plan for an ad hoc query vs. the putting the query in a
stored procedure? I've looked at the graphical execution plan for an ad
hoc query and a stored procedure and they look the same.
Thanks
SET STATISTICS TIME ON
The first set of data before the query is executed will be the time for
parse and compile. The stats after the data is returned will be the time to
execute.
If the first set of data shows 0, it means the plan is being reused, and
note that SQL Server 2000 can reuse plans for queries other than stored
procedures.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||In addition to that you need to consider how much memory all those adhoc
plans are taking up in the procedure cache. I have no idea what yours is
like but I have seen systems with all adhoc queries use well over 1GB for
procedure cache on 32 bit systems and larger on 64 bit. That memory can
better be utilized for data and index caching instead of useless plans.
Andrew J. Kelly SQL MVP
<pshroads@.gmail.com> wrote in message
news:1116376117.157139.259730@.g44g2000cwa.googlegr oups.com...
> My company is using a lot of ad hoc queries and I am trying to convince
> them to move to stored procedures. However certain managers don't
> believe that the compilation of ad hoc queries and the fact that they
> usually aren't reused is really anything to worry about. They think
> that the cost of doing this is minimal and instead want to focus on
> tuning bad queries.
> I of course agree that badly written queries should be tuned but it's
> also a basic tenet of databases users that stored procedures should
> always be used where possible to take advantage of precompilation and
> plan caching. However I don't know how to actually prove that with
> data.
> How can I measure what the overhead is for compiling and building an
> execution plan for an ad hoc query vs. the putting the query in a
> stored procedure? I've looked at the graphical execution plan for an ad
> hoc query and a stored procedure and they look the same.
> Thanks
>
|||Thanks for your reply. Would I look at the size of syscacheobjects with
sp_spaceused to determine the amount of memory that the procedure cache
is using?
|||Use DBCC MEMORYSTATUS for that:
http://support.microsoft.com/?id=271624
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>
|||No, syscacheobjects is a pseudo-table and takes no space on disk, so
sp_spacedused reports 0. The size of the plans is in the pagesused column,
so you can just retrieve the SUM of pagesused.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
<pshroads@.gmail.com> wrote in message
news:1116388073.318788.301230@.f14g2000cwb.googlegr oups.com...
> Thanks for your reply. Would I look at the size of syscacheobjects with
> sp_spaceused to determine the amount of memory that the procedure cache
> is using?
>

How do I make use of begin transaction and commit transaction in SSIS.

Hi

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

Thanks,

Prashant

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

How do I make use of begin transaction and commit transaction in SSIS.

Hi

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

Thanks,

Prashant

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

How do I loop through dataset then replace?

How would I loop through the rows of a dataset, then replace certain character in a certain column?

I have a database which has a date field formatted 23/08/2007, I wish to loop through the dataset containing the results from the dataset, change the format of the date to 23.08.2007 then store the value back into the dataSet, which is called 'dataSet', and the table is called 'News'.

Using C# by the way.

Thanks in advance

I suggest you to do this in database level though stored procedure that is dynamic (receive your changing criteria via input parameters).
Looping in stored procedures can be done though cursor (I know it is not recomended to use cursor in database for performance issue, but I think in this case it will be much better than looping into your application).

Tip:
You can get many date format for your DATETIME column in database. For example try: Convert(varchar(25), MyDateTimeColumn,113) or Convert(varchar(25), MyDateTimeColumn,111) [Used SQL Books Online for more samples and examples].

Good luck.

|||

Thanks

I'm new to this, so I'll read up on stored procedures, infact, I'm sure I have a whole book on SQL somewhere.

|||

While I read up on stored procedures can someone tell me how to do this in the application? The dataset will only contain 3 rows so it shouldnt have too much of an effect on the application.

|||

If it is just 3 records, then it will not affect the performance that much!

Here is the idea:
Let yourSqlDataAdapterfillyourDataSet.
Now, you have the data in the DataSet, loop into those records in DataSet.
Use String.Replace for replacing issue you have.

http://msdn2.microsoft.com/en-us/library/system.string.replace(VS.71).aspx

Good luck.

|||

What I wanted to do was get data from a database in date order, then alter the format of the date once it was in the dataset.

Instead of faffing about with it, I simply set the date in the database twice, i.e. Date, then a display date.

Now the data is sorted using the Date, but it's the displayDate field which displays the date to the user.

Thanks for all the help

How do I lengthen the time out for SQL queries?

I'd like to make the connection time unlimited for a certain sql query to SQL Server from ASP.NET, how do I do this? I already tried setting "Connect Timeout=0" in the sql connection string, this doesn't work...

Please help.Have you tried setting the CommandTimeout property for the SqlCommand object?

SqlCommand.CommandTimeout = iSecondsForTimeout

|||Thanks, that was it.sql

How do I include all dates between 2 parameters in a table?

I'm working with a data set where some values have an aggretate total
(for example, a count of something) in a certain date range and others
do not. The problem I am having is that I need each page of the report
to show all the possible dates and the display a 0 when there are no
values for the date. Currently it is dropping that date.
For Example:
@.StartDate = 1/1/06
@.EndDate = 6/31/06
-- I get this on grouped values where there are empty months:
Jan06 5
Feb06 3
Apr06 7
Jun06 10
-- But I want this:
Jan06 5
Feb06 3
Mar06 0
Apr06 7
May06 0
Jun06 10
Due to other report properties and calculations, I can't really modify
my query to fix this problem. It seems like it should be easy to solve
but I'm not getting it!!!
Thanks.I think you must be using table control. so no way you can substitute 0 for
missing one. you need to modify query to use right join to include all the
rows.
if you hard code all the months value still you need to change the query to
get all the rows to column..
Try to change query, rather than breaking head.
Amarnath
"CanoAko" wrote:
> I'm working with a data set where some values have an aggretate total
> (for example, a count of something) in a certain date range and others
> do not. The problem I am having is that I need each page of the report
> to show all the possible dates and the display a 0 when there are no
> values for the date. Currently it is dropping that date.
> For Example:
> @.StartDate = 1/1/06
> @.EndDate = 6/31/06
> -- I get this on grouped values where there are empty months:
> Jan06 5
> Feb06 3
> Apr06 7
> Jun06 10
> -- But I want this:
> Jan06 5
> Feb06 3
> Mar06 0
> Apr06 7
> May06 0
> Jun06 10
> Due to other report properties and calculations, I can't really modify
> my query to fix this problem. It seems like it should be easy to solve
> but I'm not getting it!!!
> Thanks.
>|||Hmm... that doesn't really help. I admit now that I will probably have
to design a query to do it. The problem is that the query joins 2
tables and the date that I'm pulling is on the second table. So the
date doesn't exist to do an aggregate with a total of 0 in the first
place.
I'm going to try it with CASE to force it to happen, but I'm not too
excited about that. I've only been doing this for a month so I still
haven't figured everything out.
Amarnath wrote:
> I think you must be using table control. so no way you can substitute 0 for
> missing one. you need to modify query to use right join to include all the
> rows.
> if you hard code all the months value still you need to change the query to
> get all the rows to column..
> Try to change query, rather than breaking head.
> Amarnath
>
> "CanoAko" wrote:
> > I'm working with a data set where some values have an aggretate total
> > (for example, a count of something) in a certain date range and others
> > do not. The problem I am having is that I need each page of the report
> > to show all the possible dates and the display a 0 when there are no
> > values for the date. Currently it is dropping that date.
> >
> > For Example:
> >
> > @.StartDate = 1/1/06
> > @.EndDate = 6/31/06
> >
> > -- I get this on grouped values where there are empty months:
> > Jan06 5
> > Feb06 3
> > Apr06 7
> > Jun06 10
> >
> > -- But I want this:
> > Jan06 5
> > Feb06 3
> > Mar06 0
> > Apr06 7
> > May06 0
> > Jun06 10
> >
> > Due to other report properties and calculations, I can't really modify
> > my query to fix this problem. It seems like it should be easy to solve
> > but I'm not getting it!!!
> >
> > Thanks.
> >
> >|||Hi,
thats quite easy, use the following link to see how to generate a
calendar table, you can either persits it or create it on the fly.
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

How do I get the Table Name the column belongs to in SMO?

Hi guys,

I am writing some sort of tool and I need to get the table a column belongs to.Before you ask for certain reasons too long to explain I need to get it from the column.

I have found out that the following will give me the answer however before I start splitting the string which it could be messy.

is there another way to do it?

My way so far:

?col.Urn.Value Gives:
"Server[@.Name='MYServer']/Database[@.Name='MyDB]/Table[@.Name='UserTable' and @.Schema='dbo']/Column[@.Name='Surname']"

So now .How can I split it to get the table Name? or better is there a cleaner way to do it?

Thanks again for your time in advance

col.Parent will return you a SqlSmoObject which you can cast as a Table (or View).

Hope that helps.

Thanks,
Kuntal

|||That helped and worked thanks.

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens

How do I export data in certain table through management studio

Hi there,
In SQL Server 2005 How do I export data in certain table through management studio?
Thanks,
AnuRight Click on a database select all tasks and then export data, select just the table you want from the list in the grid.|||This is only possible if you have SQL Server 2005 and not with the SQL Server Management Studio.....
I have the same problem and would like to export my tables to the ASPNETDB after installing the latest version of ASP.NET 2.0 with VWD.

After upgrade, I cannot login anymore to my site... This is due to SQL Express Stored Procedures that have been changed... and even when changing manually the Stored Procedure, it doesn't work...

So... I think I will need to create my table again... Rrrrrrrrrrr.....

Anyone a suggestion or a hint how I can export my table and import to SQL Express again?

Thanks folks,
B.

How do I export data in certain table through management studio

Hi there,
In SQL Server 2005 How do I export data in certain table through management studio?
Thanks,
AnuRight Click on a database select all tasks and then export data, select just the table you want from the list in the grid.|||This is only possible if you have SQL Server 2005 and not with the SQL Server Management Studio.....
I have the same problem and would like to export my tables to the ASPNETDB after installing the latest version of ASP.NET 2.0 with VWD.

After upgrade, I cannot login anymore to my site... This is due to SQL Express Stored Procedures that have been changed... and even when changing manually the Stored Procedure, it doesn't work...

So... I think I will need to create my table again... Rrrrrrrrrrr.....

Anyone a suggestion or a hint how I can export my table and import to SQL Express again?

Thanks folks,
B.

how do I do this?

I want to change the order by criteria depending on a certain input paramater

So suppose

This is a simplified condensed version of what I need

declare @.sortID int
set @.sortID=1

So if the @.sortID=1 I wanna have this

select * from tblusersorder by name

else

select * from tblusers whereorder by age

so depending on the sort id the bolded text should change.

Only one way I know of ...

declare @.sortID int
set @.sortID=1
If (@.sortID = 1)
BEGIN
select * from tblusersorder by name
END
ELSE
BEGIN
select * from tblusers whereorder by age
END

I do not believe order by can be a parameter

|||

hello,

and other way to do, dynamic sql execution, some case is not good way to do but versstile

===============================

1declare @.idint2declare @.sqlnvarchar(max)34set @.id = 156set @.sql ='select top 10 productid, [name] from production.product '7+'order by ' +case @.id8when 1then'[name]'9when 2then'productid'10end1112execsp_executesql @.sql

===============================

|||busyweb is right. You could use dynamic SQL. I just never think of dynamic SQL because of the speed, security, maintainability problems. (smile)

How do I do restore only SOME DTS scripts from msdb database?

I understand that I can backup my DTS scripts by backing up the msdb
database, but how do I restore only CERTAIN DTS scripts from the database?
If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
related to other databases that I might NOT want to restore.
TIA,
Larry WoodsIf you want to move DTS from one server to another try with this.
Save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
I hope this help
Ana
"Larry Woods" wrote:
> I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>
>|||I would perhaps do another install of sql server, restore ALL of the
Packages. Then save each of the packages you need as a flat file, then open
them on the correct server and save as SQl..
Good luck...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:%23ewfQc7KFHA.244@.TK2MSFTNGP12.phx.gbl...
>I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>

How do I do restore only SOME DTS scripts from msdb database?

I understand that I can backup my DTS scripts by backing up the msdb
database, but how do I restore only CERTAIN DTS scripts from the database?
If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
related to other databases that I might NOT want to restore.
TIA,
Larry WoodsIf you want to move DTS from one server to another try with this.
Save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
I hope this help
Ana
"Larry Woods" wrote:

> I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>
>|||I would perhaps do another install of sql server, restore ALL of the
Packages. Then save each of the packages you need as a flat file, then open
them on the correct server and save as SQl..
Good luck...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:%23ewfQc7KFHA.244@.TK2MSFTNGP12.phx.gbl...
>I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>

How do I do restore only SOME DTS scripts from msdb database?

I understand that I can backup my DTS scripts by backing up the msdb
database, but how do I restore only CERTAIN DTS scripts from the database?
If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
related to other databases that I might NOT want to restore.
TIA,
Larry Woods
If you want to move DTS from one server to another try with this.
Save DTS as Structured Storage File. Then copy it to the
destination server and in EM right click Data Transformation Services and
Open. Choose your file. Click Package then Save AS and chose server in which
you want to save DTS.
I hope this help
Ana
"Larry Woods" wrote:

> I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>
>
|||I would perhaps do another install of sql server, restore ALL of the
Packages. Then save each of the packages you need as a flat file, then open
them on the correct server and save as SQl..
Good luck...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry Woods" <larry@.NOSPAMlwoods.com> wrote in message
news:%23ewfQc7KFHA.244@.TK2MSFTNGP12.phx.gbl...
>I understand that I can backup my DTS scripts by backing up the msdb
> database, but how do I restore only CERTAIN DTS scripts from the database?
> If I restore 'msdb' I might be overwriting DTS scripts, etc. that are
> related to other databases that I might NOT want to restore.
> TIA,
> Larry Woods
>