Determine Table's PK Columns

I want to do a query on a db's system tables to determine what column(s) are
part of the primary key.
Example.
Code:
SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
sysobjects.name='tablename'
Yields all the column names. But I just want to see those that are part of
the primary key. How?
Ryan
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
shuldn't you use information_schema?
.~. Might, Courage, Vision. Sincerity. http://www.linux-sxs.org
/ v \
/( _ )\ (Ubuntu 5.10) Linux 2.6.14.3
^ ^ 22:37:07 up 9 days 2:31 load average: 3.12 1.63 0.80|||Try:
select
*
from
information_schema.constraint_column_usage
where
table_name = 'Order Details'
and objectproperty (object_id (constraint_name), 'IsPrimaryKey') = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:037F8848-C843-4A77-B385-6994E6235CDA@.microsoft.com...
>I want to do a query on a db's system tables to determine what column(s)
>are
> part of the primary key.
> Example.
> Code:
> SELECT syscolumns.[name] FROM sysobjects,syscolumns,systypes WHERE
> sysobjects.id=syscolumns.id AND systypes.xtype=syscolumns.xtype AND
> sysobjects.name='tablename'
> Yields all the column names. But I just want to see those that are part of
> the primary key. How?
> Ryan|||Thank you both of you. Yes, Man-wai Chang, the information_schema looks lik
e
it's a lot easier than joining the system tables like I was doing. Thank
you, both.

Determine rowguid in column and edit column information ASP.NET 1.1 C#

What query should I run?

1. To determine whether a column is a rowguid or not using C# .NET 1.1

2. To add/modify column information and be able to set/change:

- Primary key

- Column Name

- Data Type

- Length

- Allow Null

- Default value

- Precision

- Scale

- Identity

- Identity Seed

- Identity Increment

- Row guid

Thanks a lot!

(1) SELECT OBJECTPROPRTTY(<tableId>,TableHasRowGuidCol) will tell you if the table has uniqueidentifier column.

(2) Please refer BOL.

|||

Hi, thanks for the answer but I tried to run this:

SELECT

OBJECTPROPERTY(OBJECT_ID('temp_aspnet_Permissions'),TableHasRowGuidCol)

and it gives me error:

Msg 207, Level 16, State 1, Line 1

Invalid column name 'TableHasRowGuidCol'.

|||

Never mind, I forgot about the quote:

SELECT

OBJECTPROPERTY(OBJECT_ID('temp_aspnet_Permissions'),'TableHasRowGuidCol')

Thanks again!

|||Btw, it only returning number 1 or 0, I need to know which column that has rowguid, so it still hasn't answer my question :(|||

I found it, you need to use columnproperty. Thanks again for the guidance!

SELECTCOLUMNPROPERTY(OBJECT_ID('testcol2'),'col2','IsRowGuidCol')

|||

SELECT

COLUMNPROPERTY(id,name,'IsRowGuidCol'),*

FROMsyscolumnsWHEREid=Object_Id('yourtable')

andCOLUMNPROPERTY(id,name,'IsRowGuidCol') = 1

Determine Quarter End and Beginning Dates

Hello,

I have a query that I would like to schedule in DTS. The criteria of
this query checks for records in the table that are within the current
quarter. Here is what I have.

WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'

I would like to dynamically generate the Quarter End and Quarter
Beginning dates within my where clause based on the date that DTWS
package is being executed on. Can anyone show me how this can be
accomplished?

Thank You."Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1112196440.142834.300110@.z14g2000cwz.googlegr oups.com...
> Hello,
> I have a query that I would like to schedule in DTS. The criteria of
> this query checks for records in the table that are within the current
> quarter. Here is what I have.
> WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
> I would like to dynamically generate the Quarter End and Quarter
> Beginning dates within my where clause based on the date that DTWS
> package is being executed on. Can anyone show me how this can be
> accomplished?
>
> Thank You.

Quick and dirty solution - see DATEPART in Books Online.

The longer answer is that using DATEPART might not be good for performance
(applying a function to a column prevents MSSQL using an index on that
column), so you may need another approach. One would be to write a stored
proc to return the first and last days of the current quarter, so you can
put them in variables and use them in your query; another would be to create
a calendar table (which is very useful anyway) and join on it in your query.

A couple of other small points - BETWEEN with datetime columns can give you
unexpected results if you don't allow for the time portion. In your case,
this is probably safer:

where submit_date >= '20050101' and submit_date < '20050401'

Also, try to use the YYYYMMDD date format if possible - it will always be
interpreted correctly by MSSQL regardless of client or server settings. More
information here:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||> WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
> I would like to dynamically generate the Quarter End and Quarter
> Beginning dates within my where clause based on the date that DTWS
> package is being executed on. Can anyone show me how this can be
> accomplished?

The easy way to do this is to set up a dates table that has columns for
quarter and year and then join
e.g. If you have a table: dates
D as datetime, Year as integer, Quarter as integer
20050101, 2005, 1
20050102, 2005, 1
...
20051231, 2005, 4

And then in your query:
Join (select Quarter, Year from dates where d = CONVERT(getdate(), datetime,
112) as t
Join dates on submitdate = d
where d.Quarter = t.Quarter and d.year = t.Year

The Hard Way is to calculate it in line:

If you want current quarter then:
WHERE submit_date BETWEEN
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-1) / 3 * 3 + 1 as varchar(2)),2) + '01' AND
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-1) / 3 * 3 + 4 as varchar(2)), 2) + '01'

Last quarter is harder:
WHERE submit_Date BETWEEN
CASE WHEN Month(GetDate()) < 4 THEN
CAST(Year(GetDate()) - 1 as varchar(4)) + '0901'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()-1) / 3 * 3 - 2 as varchar(2)),2) + '01'
END
AND
CASE WHEN Month(GetDate()) < 4 THEN
CAST(Year(GetDate()) as varchar(4)) + '0101'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()) / 3 * 3 + 1 as varchar(2)), 2) + '01'
END|||On 30 Mar 2005 07:27:20 -0800, Matt wrote:

>Hello,
>I have a query that I would like to schedule in DTS. The criteria of
>this query checks for records in the table that are within the current
>quarter. Here is what I have.
>WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
>I would like to dynamically generate the Quarter End and Quarter
>Beginning dates within my where clause based on the date that DTWS
>package is being executed on. Can anyone show me how this can be
>accomplished?
>
>Thank You.

Hi Matt,

In addition to the answers Simon and James gave, here's a quick formula
to calculate the first and last date of the quarter:

declare @.test datetime
set @.test = '20051201'
SELECT DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test),
'20000101'),
DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test) + 1,
'19991231')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Determine physical file names of database

Hi,
How do I determine the physical file names of an SQL Server database
using a query?
For example, I'm looking for a query that returns the following:
Logical Name Physical Name
ABC_Data C:\MSSQL7\data\ABC_Data.MDF
ABC_Log C:\MSSQL7\data\ABC_Log.LDF
George
Hi
use pubs
exec sp_helpfile
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
|||You can use sp_helpdb 'YourDatabase'
You could also query sysfiles:
select name, filename
from sysfiles
-Sue
On Tue, 24 May 2005 13:41:17 +0200, George
<gtog@._no___spam_myrealbox.com> wrote:

>Hi,
>How do I determine the physical file names of an SQL Server database
>using a query?
>For example, I'm looking for a query that returns the following:
>Logical Name Physical Name
>---
>ABC_Data C:\MSSQL7\data\ABC_Data.MDF
>ABC_Log C:\MSSQL7\data\ABC_Log.LDF
>George
|||SELECT NAME,FILENAME FROM SYSFILES
exec SP_HELPDB <db>
"George" wrote:

> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
>
|||Hi,
Execute the below query from master database:-
select db_name(dbid) as Database_name , name,filename from
master..sysaltfiles
Thanks
Hari
SQL Server MVP
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database using
> a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF ABC_Log
> C:\MSSQL7\data\ABC_Log.LDF
> George

Determine physical file names of database

Hi,
How do I determine the physical file names of an SQL Server database
using a query?
For example, I'm looking for a query that returns the following:
Logical Name Physical Name
---
ABC_Data C:\MSSQL7\data\ABC_Data.MDF
ABC_Log C:\MSSQL7\data\ABC_Log.LDF
GeorgeHi
use pubs
exec sp_helpfile
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George|||You can use sp_helpdb 'YourDatabase'
You could also query sysfiles:
select name, filename
from sysfiles
-Sue
On Tue, 24 May 2005 13:41:17 +0200, George
<gtog@._no___spam_myrealbox.com> wrote:

>Hi,
>How do I determine the physical file names of an SQL Server database
>using a query?
>For example, I'm looking for a query that returns the following:
>Logical Name Physical Name
>---
>ABC_Data C:\MSSQL7\data\ABC_Data.MDF
>ABC_Log C:\MSSQL7\data\ABC_Log.LDF
>George|||SELECT NAME,FILENAME FROM SYSFILES
exec SP_HELPDB <db>
"George" wrote:

> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
>|||Hi,
Execute the below query from master database:-
select db_name(dbid) as Database_name , name,filename from
master..sysaltfiles
Thanks
Hari
SQL Server MVP
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database using
> a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF ABC_Log
> C:\MSSQL7\data\ABC_Log.LDF
> Georgesql

Determine physical file names of database

Hi,
How do I determine the physical file names of an SQL Server database
using a query?
For example, I'm looking for a query that returns the following:
Logical Name Physical Name
---
ABC_Data C:\MSSQL7\data\ABC_Data.MDF
ABC_Log C:\MSSQL7\data\ABC_Log.LDF
GeorgeHi
use pubs
exec sp_helpfile
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George|||You can use sp_helpdb 'YourDatabase'
You could also query sysfiles:
select name, filename
from sysfiles
-Sue
On Tue, 24 May 2005 13:41:17 +0200, George
<gtog@._no___spam_myrealbox.com> wrote:
>Hi,
>How do I determine the physical file names of an SQL Server database
>using a query?
>For example, I'm looking for a query that returns the following:
>Logical Name Physical Name
>---
>ABC_Data C:\MSSQL7\data\ABC_Data.MDF
>ABC_Log C:\MSSQL7\data\ABC_Log.LDF
>George|||SELECT NAME,FILENAME FROM SYSFILES
exec SP_HELPDB <db>
"George" wrote:
> Hi,
> How do I determine the physical file names of an SQL Server database
> using a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF
> ABC_Log C:\MSSQL7\data\ABC_Log.LDF
> George
>|||Hi,
Execute the below query from master database:-
select db_name(dbid) as Database_name , name,filename from
master..sysaltfiles
Thanks
Hari
SQL Server MVP
"George" <gtog@._no___spam_myrealbox.com> wrote in message
news:eGpg$VFYFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How do I determine the physical file names of an SQL Server database using
> a query?
> For example, I'm looking for a query that returns the following:
> Logical Name Physical Name
> ---
> ABC_Data C:\MSSQL7\data\ABC_Data.MDF ABC_Log
> C:\MSSQL7\data\ABC_Log.LDF
> George

Determine last backup?

If I back up a database without using a maintenance plan, is there a query
that can be made to determine when the last full backup of that database
occurred?
Thanks in advance!Try:
select top 1
backup_finish_date
from
msdb.dbo.backupset
where
database_name = 'Northwind'
and type = 'D'
order by
backup_finish_date desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Jim Norton" <jim.norton@.joe.com> wrote in message
news:%237SbYsn%23FHA.1312@.TK2MSFTNGP09.phx.gbl...
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
>|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !|||Jim Norton wrote:
> If I back up a database without using a maintenance plan, is there a query
> that can be made to determine when the last full backup of that database
> occurred?
> Thanks in advance!
Hi Jim,
whether you use a maintenance plan or not - any backup is written to a
set of tables in msdb (4 of them, backupset, backupmediaset,
backupfile, backupmediafamily).
Their relations are a bit "complex" , because they all bear loads of
the "device" logic from times where you would backup multiple databases
to e.g. one tape (device..).
But something like this should work:
select top 1
substring(upper(database_name),1,12)
,type
,substring(description,1,20)
,convert(char(10),backup_finish_date,102) as 'enddate'
,physical_device_name
from msdb.dbo.backupset bup ,
msdb.dbo.backupmediafamily dsn
where bup.media_set_id = dsn.media_set_id
and
database_name = '''
and type = 'D'
order by backup_start_date desc
Adjust the where to your needs, see BOL for other types ...
Hope that helps...
GUI-alternative : use EM to point to the DB, switch to "taskpad" - view
- voila !

Determine last access of a database or table

Does anybody know of a way to determine the last date/time a table has been accessed (query/update)?

I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table.

I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience.

Any info will help greatly!

Thanks!

Why not put a SQL Trace on each suspect database? That way you can soon determine the activity.

|||

I like that idea. Thanks for the suggestion!

Determine if database is in standby/read-only mode?

Hi all,
Sorry for the frequent posts, but I have one other thing I'd like to figure
out.
Can I write a query to determine if a database is in standby or read-only
mode? I would like to put a check in the restore routine for custom log
shipping so that DIFFs and TRANS are restored when not in standby/read-only.
I tried to trace Enterprise Mangler, and see how it set the Read-only
checkbox for the database properties, and I saw this ...
USE [<DatabaseName>]
SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
dbo.sysfilegroups f
However, even though Enterprise Mangler shows read-only as checked, this
query keeps returning false (0).
Any thoughts? Am I going about this the wrong way?
Thanks for the help!
WadeNevermind, I found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
Thanks!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
> figure out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
> standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>|||Use function databaseproperty.
Example:
use master
go
select
[name],
databaseproperty([name], 'IsInStandBy') as IsInStandBy,
databaseproperty([name], 'IsInRecovery') as IsInRecovery
from
sysdatabases
go
AMB
"Wade" wrote:
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
>|||See DATABASEPROPERTY and DATABASEPROPERTYEX functions in SQL Server Books
Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>

Determine if database is in standby/read-only mode?

Hi all,
Sorry for the frequent posts, but I have one other thing I'd like to figure
out.
Can I write a query to determine if a database is in standby or read-only
mode? I would like to put a check in the restore routine for custom log
shipping so that DIFFs and TRANS are restored when not in standby/read-only.
I tried to trace Enterprise Mangler, and see how it set the Read-only
checkbox for the database properties, and I saw this ...
USE [<DatabaseName>]
SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
dbo.sysfilegroups f
However, even though Enterprise Mangler shows read-only as checked, this
query keeps returning false (0).
Any thoughts? Am I going about this the wrong way?
Thanks for the help!
WadeNevermind, I found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
Thanks!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
> figure out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
> standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>|||Use function databaseproperty.
Example:
use master
go
select
[name],
databaseproperty([name], 'IsInStandBy') as IsInStandBy,
databaseproperty([name], 'IsInRecovery') as IsInRecovery
from
sysdatabases
go
AMB
"Wade" wrote:

> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to figur
e
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in standby/read-onl
y.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
>|||See DATABASEPROPERTY and DATABASEPROPERTYEX functions in SQL Server Books
Online.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>

Determine if database is in standby/read-only mode?

Hi all,
Sorry for the frequent posts, but I have one other thing I'd like to figure
out.
Can I write a query to determine if a database is in standby or read-only
mode? I would like to put a check in the restore routine for custom log
shipping so that DIFFs and TRANS are restored when not in standby/read-only.
I tried to trace Enterprise Mangler, and see how it set the Read-only
checkbox for the database properties, and I saw this ...
USE [<DatabaseName>]
SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
dbo.sysfilegroups f
However, even though Enterprise Mangler shows read-only as checked, this
query keeps returning false (0).
Any thoughts? Am I going about this the wrong way?
Thanks for the help!
Wade
Nevermind, I found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
Thanks!
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
> figure out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
> standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
|||Use function databaseproperty.
Example:
use master
go
select
[name],
databaseproperty([name], 'IsInStandBy') as IsInStandBy,
databaseproperty([name], 'IsInRecovery') as IsInRecovery
from
sysdatabases
go
AMB
"Wade" wrote:

> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>
>
|||See DATABASEPROPERTY and DATABASEPROPERTYEX functions in SQL Server Books
Online.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:%23ZxMnTZrFHA.1128@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Sorry for the frequent posts, but I have one other thing I'd like to
figure
> out.
> Can I write a query to determine if a database is in standby or read-only
> mode? I would like to put a check in the restore routine for custom log
> shipping so that DIFFs and TRANS are restored when not in
standby/read-only.
> I tried to trace Enterprise Mangler, and see how it set the Read-only
> checkbox for the database properties, and I saw this ...
> USE [<DatabaseName>]
> SELECT FILEGROUPPROPERTY( f.groupname, N'IsReadOnly' ) FROM
> dbo.sysfilegroups f
> However, even though Enterprise Mangler shows read-only as checked, this
> query keeps returning false (0).
> Any thoughts? Am I going about this the wrong way?
> Thanks for the help!
> Wade
>

Determine fastest query in Query Analyzer

I am trying to determine which of three stored procedure designs are
fastest in the Query Analyzer:

One query is a straight SELECT query with all desired rows and a dozen
(tblName.RowName = @.param or @.param = Null) filters in the WHERE
statement.

One query populates a #Temp table with the UniqueIDs from the results
of the SELECT query in the above example, then joins that #Temp table
to get the desired rows.

One query users EXEC sp_executesql @.sql, @.paramlist, @.param
in which the @.param has the dozen filters.

What I'm trying to determine is which is the fastest.

Each time I run the query in Query Analyzer it returns the same
recordset (duh!) but with much different Time Statistics.

Are the Time Statisticts THE HOLY QRAIL as far as determining which is
fastest, and what so I want to look at, the Vale or the Average? I
notice there are different numbers of bytse sen and bytes received for
each of the three queries.

Any illumination on this is appreciated.
lqHi

You are looking at the client statistic! The topic "Query Window Statistics
Pane" in books online explains their values.

Time is a good indicator of performance, for instance if there are more
network round trips this should be noticed in the time taken. You may also
want to consider the number of reads/writes which may give some indication
of how well it will perform when the system in under a load. These can be
viewed using SQL profiler.

Expect the first time you run a query to take longer than subsequent times,
if your query is cached subsequent executions may be significantly faster.
Use DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to clear the procedure
cache and buffer pool.

John

"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1126976566.998344.13050@.g44g2000cwa.googlegro ups.com...
>I am trying to determine which of three stored procedure designs are
> fastest in the Query Analyzer:
> One query is a straight SELECT query with all desired rows and a dozen
> (tblName.RowName = @.param or @.param = Null) filters in the WHERE
> statement.
> One query populates a #Temp table with the UniqueIDs from the results
> of the SELECT query in the above example, then joins that #Temp table
> to get the desired rows.
> One query users EXEC sp_executesql @.sql, @.paramlist, @.param
> in which the @.param has the dozen filters.
> What I'm trying to determine is which is the fastest.
> Each time I run the query in Query Analyzer it returns the same
> recordset (duh!) but with much different Time Statistics.
> Are the Time Statisticts THE HOLY QRAIL as far as determining which is
> fastest, and what so I want to look at, the Vale or the Average? I
> notice there are different numbers of bytse sen and bytes received for
> each of the three queries.
> Any illumination on this is appreciated.
> lq|||Have a look at showplan whick give you an idea what the database is
doing to resolve your queries. Determining the fastest method can be
difficult especially with changing volumes of data, add or remove an
index will effect the results (faster or slower) so experiment a bit.

Sorry I cannot be more help

duncan|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> I am trying to determine which of three stored procedure designs are
> fastest in the Query Analyzer:
> One query is a straight SELECT query with all desired rows and a dozen
> (tblName.RowName = @.param or @.param = Null) filters in the WHERE
> statement.

@.param = Null?

Remember that NULL is never equal to anything, not an even another NULL.
NULL is an unknown value, and two nulls may be two different values.
Use "@.param IS NULL" instead.

> Are the Time Statisticts THE HOLY QRAIL as far as determining which is
> fastest, and what so I want to look at, the Vale or the Average? I
> notice there are different numbers of bytse sen and bytes received for
> each of the three queries.

When I need to benchmark queries I usually do:

DECLARE @.d datetime, @.tookms int
SELECT @.d = getdate()
-- Run query
SELECT @.tookms = datediff(ms, @.d, getdate())
PRINT 'It took ' + ltrim(str(@.tookms)) + ' ms.'

As John mentioned it is important to have the cache in mind. You can
do DBCC DROPCLEANBUFFERS to flush the cache, but don't this on a
production box! Often I'm lazy and run the queries several times, and
forget the first run, since that may include time reading from disk.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks. Yes, I use "Is Null". I wrote the question on the fly. I will
insert your @.tookms into my sprocs to see how they perform. That's a
great hint.
LQ

Determine closedate depending on a metatable and this closedate will be used in a query

Hi, This is a diffcult issue to explain. I hope to make my problem
clear to you.

SITUATION
I'm building A SLA Query for a customer. This customer has an awkward
way to determine the SLA results ;-) Depending on a category which is
stored in a headertable (Requests) a field and logic is determined how
to get a proper Close_Date. This Close_date can be the closedate of
the request. It is also possible that the close_date is a certain
detail record (Request_lines). Also It is possible that this
close_date is the ordered_date of a certain line_item.

DONE SO FAR
I have created a metatable with rules per category. With this rule i
would like to determine a close_date. This close_date will be used as
parameter in a function from which i determine the total minutes how
long this request has endured.

GOAL
I want to create something like this:
SELECT id, getdiffSLA(@.StartDate, GetCloseDate(some parameters))
FROM...

I've created the function 'GetCloseDate()' in which i want to
determine the closedate. In this function i want to determine the
rule. Execute the proper logic and get the date.

This is what i'm trying to do in a function (this is one rule of many)
:

.....
ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
BEGIN
.....
.....
SET @.vcSQL = N' SELECT @.max = MAX(Close_date)
FROM samis.dbo.SAD_A_REQUEST_LINES
WHERE Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'

Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT

SET @.dCloseDate = @.max

ELSE IF...
....

THE PROBLEM
This doesn't work and yeah i know i can't use exec /sp_executesql in a
function. But if i try this in a stored procedure i can't use this in
a Query.
SELECT id, storedprocedure FROM ... Doesn't work, also.

An option is that i could create a cursor, loop every record and call
the stored procedure, get the close_date, execute my SLA calculation
function, store the result in a temptable, use this temptable in the
query <pffff>.

But the table consists of 200000 records (with a detailtable) and
performance is a issue. It's used for loading a datawarehouse and not
in a OLTP system so a bit slow performance is allowed but not to much.

SO how do i do this without using a cursor?

Greetz

Hennie"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0407062303.44ff9664@.posting.google.c om...
> Hi, This is a diffcult issue to explain. I hope to make my problem
> clear to you.
> SITUATION
> I'm building A SLA Query for a customer. This customer has an awkward
> way to determine the SLA results ;-) Depending on a category which is
> stored in a headertable (Requests) a field and logic is determined how
> to get a proper Close_Date. This Close_date can be the closedate of
> the request. It is also possible that the close_date is a certain
> detail record (Request_lines). Also It is possible that this
> close_date is the ordered_date of a certain line_item.
> DONE SO FAR
> I have created a metatable with rules per category. With this rule i
> would like to determine a close_date. This close_date will be used as
> parameter in a function from which i determine the total minutes how
> long this request has endured.
> GOAL
> I want to create something like this:
> SELECT id, getdiffSLA(@.StartDate, GetCloseDate(some parameters))
> FROM...
> I've created the function 'GetCloseDate()' in which i want to
> determine the closedate. In this function i want to determine the
> rule. Execute the proper logic and get the date.
> This is what i'm trying to do in a function (this is one rule of many)
> :
> ....
> ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
> BEGIN
> .....
> .....
> SET @.vcSQL = N' SELECT @.max = MAX(Close_date)
> FROM samis.dbo.SAD_A_REQUEST_LINES
> WHERE Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
> @.vcIN_String + ')'
> Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT
> SET @.dCloseDate = @.max
> ELSE IF...
> ...
> THE PROBLEM
> This doesn't work and yeah i know i can't use exec /sp_executesql in a
> function. But if i try this in a stored procedure i can't use this in
> a Query.
> SELECT id, storedprocedure FROM ... Doesn't work, also.
> An option is that i could create a cursor, loop every record and call
> the stored procedure, get the close_date, execute my SLA calculation
> function, store the result in a temptable, use this temptable in the
> query <pffff>.
> But the table consists of 200000 records (with a detailtable) and
> performance is a issue. It's used for loading a datawarehouse and not
> in a OLTP system so a bit slow performance is allowed but not to much.
> SO how do i do this without using a cursor?
> Greetz
> Hennie

I don't really follow your description completely, but it seems that one key
issue is working with delimited strings, so you may want to look at this
article to see how to avoid using dynamic SQL:

http://www.sommarskog.se/arrays-in-sql.html

That might help you to create your function, but if not then you should
consider posting some more detailed information - the CREATE TABLE
statements for the tables, INSERTs of some sample data, and what you expect
to be returned for each category. That's usually clearer than a description,
and someone may be able to give more specific help.

Simon|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

>> Depending on a category which is stored in a header table (Requests)
a field [sic] and logic is determined how
to get a proper Close_Date. This Close_date can be the closedate of the
request. It is also possible that the close_date is a certain detail
record [sic] (Request_lines). Also It is possible that this close_date
is the ordered_date of a certain line_item. <<

Rows are not records; fields are not columns; tables are not files. I
also hope that those "vc_" prefixes did not mean "VARCHAR(n)" in
violation of ISO-11179 rules. It woiuld look like you are still writing
BASIC and procedural code, not SQL. No wonder you are thinking about
dynamic SQL kludgers and cursors.

>> I have created a metatable with rules per category. <<

I am not sure what that means. A decision table in SQL, perhaps?

>> So how do I do this without using a cursor? <<

My first thought is to write something like this:

UPDATE Requests
SET close_date
= CASE category
WHEN 1 THEN <<close_date of certain detail>>
WHEN 2 THEN <<ordered_date of certain detail>>
ELSE close_date END; -- do nothing

Without better specs, this is about as far as I can get. Each category
would lead to a scalar query expression that finds the desired data
value, maybe something like this:

CASE category
...
WHEN 2
THEN (SELECT MAX(close_date)
FROM SadRequestLines AS S1
WHERE S1.parent_quote = Requests.numberprgn
AND S1.part_no
IN (SELECT part_no FROM PartsCategory_2))

The important point is to avoid all procedural code, dynamic SQL and
cursors. If you use the CASE expression, you can move all the logic
into a single, optimizable statement. My guess would be that it should
be about 10x faster than your procedural approach.

If the conditions are really tricky, then I would consider a decision
table tool to generate the WHEN predicates. LOok up Logic Gem as an
example of such a thing.

--CELKO--

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Pfff what a lot of answers and questions again. OK i'll drop my query
stuff:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[XXX_FN_GetCloseDate]') and xtype in (N'FN', N'IF',
N'TF'))
drop function [dbo].[XXX_FN_GetCloseDate]
GO

CREATE FUNCTION XXX_FN_GetCloseDate(@.vcCategory as Varchar(50),
@.vcNumberPRGN as varchar(50)) RETURNS varchar(8000)
WITH ENCRYPTION
AS
DECLARE @.vcField Varchar(50)
DECLARE @.vcPartString Varchar(8000)
DECLARE @.iRuleNoVarchar(8000)
DECLARE @.vcsqlVarchar(8000)
DECLARE @.vcTempStringVarchar(8000)
DECLARE @.vcPartNovarchar(50)
DECLARE @.vcIN_StringVarchar(8000)
DECLARE @.dCloseDateDatetime
DECLARE @.maxDatetime

-- Intitialisation
SET @.vcIN_String = ''

DECLARE FieldsCur CURSOR
FOR
SELECT MET_Field, MET_Partno, Met_RuleNo
FROM xxxxx_control.dbo.xxxxx_PWC_SLA_Metrics
WHERE MET_Code = @.vcCategory

OPEN FieldsCur
FETCH FROM FieldsCur INTO @.vcField, @.vcPartString, @.iRuleNo

IF @.@.FETCH_STATUS = 0
BEGIN
IF @.iRuleNo = 1 --SAD_A_REQUESTS.CLOSE_DATE
BEGIN
SET @.vcSQL = 'SELECT '''+ @.vcCategory + ''',''' +
@.vcNumberPRGN + ''',' + 'Close_Date FROM xxxxxx.dbo.SAD_A_REQUESTS
WHERE NumberPRGN = ' + @.vcNumberPRGN
END
ELSE IF @.iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
BEGIN
-- Extract the partnos from the MET_Field.
SET @.vctempString = LTRIM(RTRIM(@.vcPartString))
WHILE Len(@.vctempString) <> 0
BEGIN
SET @.vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@.vctempString)
SET @.vcPartNo = LTRIM(RTRIM(@.vcPartString))
IF CHARINDEX(',', @.vctempString)<> 0
SET @.vcTempString = SubString(@.vctempString, CHARINDEX(',',
@.vctempString)+1, Len(@.vctempString) )
ELSE
SET @.vcTempString = ''
-- This string is created voor de IN in the Query.
IF Len(@.vctempString) <> 0 -- comma is needed
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + '''' + ','ELSE
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + ''''

END
SET @.vcSQL = 'SELECT '''+ @.vcCategory + ''',''' + @.vcNumberPRGN + ''','
+ 'MAX(Close_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'

CREATE TABLE #CloseDate (CloseDate DateTime)
INSERT INTO CloseDate EXEC ('SELECT MAX(Close_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @.vcNumberPRGN
+ ''' AND Part_No In ('+ @.vcIN_String + ')')
SET @.dCloseDate = (SELECT CloseDate FROM #CloseDate)
DROP TABLE #CloseDate
END
ELSE IF @.iRuleNo = 3
BEGIN
-- Extract the partnos from the MET_Field.
SET @.vctempString = LTRIM(RTRIM(@.vcPartString))
WHILE Len(@.vctempString) <> 0
BEGIN
SET @.vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@.vctempString)
SET @.vcPartNo = LTRIM(RTRIM(@.vcPartString))

IF CHARINDEX(',', @.vctempString)<> 0
SET @.vcTempString = SubString(@.vctempString, CHARINDEX(',',
@.vctempString)+1, Len(@.vctempString) )
ELSE
SET @.vcTempString = ''

-- This string is created voor de IN in the Query.
IF Len(@.vctempString) <> 0 -- comma is needed
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + '''' + ','
ELSE
SET @.vcIN_String = @.vcIN_String + '''' + @.vcPartNo + ''''

--Print '@.vcIN_String : ' + @.vcIN_String

END
--CREATE TABLE #OrderedDate (OrderedDate DateTime)
--INSERT INTO #OrderedDate
SET @.vcSQL = 'SELECT '+ @.vcCategory + ',' + @.vcNumberPRGN + ',' +
'MAX(Ordered_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @.vcNumberPRGN + ''' AND Part_No In ('+
@.vcIN_String + ')'
-- SET @.vcSQL = 'SELECT @.max = MAX(Ordered_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @.vcNumberPRGN
+ ''' AND Part_No In ('+ @.vcIN_String + ')'

Exec sp_executesql @.vcsql, N'@.Max datetime OUTPUT', @.max OUTPUT
SET @.dCloseDate = @.max
SET @.dCloseDate = (SELECT OrderedDate FROM #OrderedDate)
DROP TABLE #OrderedDate
END
--ELSE IF @.iRuleNo = 4
--BEGIN

--END

--Print CAST(@.dCloseDate as varchar)

END

CLOSE FieldsCur
DEALLOCATE FieldsCur

--RETURN @.dCloseDate
RETURN @.vcSQL
END

----------------

I've edited it quite a lot so i hope it's readable...

That i was creating it as a procedural program has been an idea of
myself too. So i tried to get the logic in a query: So tried this:

SELECT
A.NUMBERPRGN,
A.SubCategory,
C.MET_Field,
C.MET_Partno,
C.Met_RuleNo,
CASE
WHEN Met_RuleNo = 1 THEN
(SELECT cast(Close_Date as varchar) FROM xxxxx.dbo.SAD_A_REQUESTS WHERE
NumberPRGN = A.NumberPRGN)

WHEN Met_RuleNo = 2 THEN
(SELECT cast(MAX(Close_date) as varchar)
FROM xxxxx.dbo.SAD_A_REQUEST_LINES B WHERE B.Parent_Quote = A.NumberPRGN
AND B.Part_No In (SELECT PartNoFROM
xxxxxx_Control.dbo.xxxxx_FN_Convert_From_CSVTOTabl e(C.MET_Partno)))

WHEN Met_RuleNo = 3 THEN '3' ELSE '999999999999' END
FROM xxxxxx.dbo.SAD_A_Requests A
LEFT OUTER JOIN xxxxxx._Control.dbo.xxxxxx_PWC_SLA_Metrics C ON
A.SubCategory = C.MET_Code

I'm almost there where i want because When i replace C.MET_Partno in the
inline function (returns a table) with a string like
'COM_06,ATC_04,ANC_03,ALC_03,AAC_04,ASC_02,APC_05' it's working but i I
got the following error:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '.'.

Research teached me that Inline functions doesn't allow fields as
parameter only variables or constants. So now i'm right at the start and
running out of ideas. This problem is driving me nuts (Aargh)..

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hennie de Nooijer (hdenooijer@.hotmail.com) writes:
> I'm building A SLA Query for a customer. This customer has an awkward
> way to determine the SLA results ;-)

I know what a TLA is, but what is an SLA?

> An option is that i could create a cursor, loop every record and call
> the stored procedure, get the close_date, execute my SLA calculation
> function, store the result in a temptable, use this temptable in the
> query <pffff>.
> But the table consists of 200000 records (with a detailtable) and
> performance is a issue. It's used for loading a datawarehouse and not
> in a OLTP system so a bit slow performance is allowed but not to much.

Then you consider this: if you say:

SELECT dbo.my_udf(col) FROM tbl

then your SELECT statement becomes a cursor behind the scenes. SQL Server
does not have any method to call you function for all rows at once. It
may be faster than a real cursor, but in comparison with UDF-less SELECT
statement the difference may be stunning.

For your actual problem there is too little information to suggest
something, and in any case, it may too complex to address completely in
a newsgroup posting.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||This is the second time i enter a complete message and when i push
submit it is all gone. I hate this. Great. I forgot to click on the
radiobutton??!!! I will notice soon. Grrrrr. So my answer is shorter now
than i want.

So in short. SLA is SErvice line agreement and is an agreement between a
customer and a service deliverer.

I solved this issue by creating a temporary table and joining with this
table. Perhaps i didn't explain it to well or the problem was to
complicated. Thanx anyway...

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Detecting whether a trace is running

We have had some problems with getting a trace restarted after capturing the
trace output to a table. Is there a query or API call that can determine i
f a server trace is running?Randy,
I believe that fn_trace_getinfo (0) will give you what you need.
Russell Fields
"Randy Hersom" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAA134B-9472-4AC7-B619-128FD7B5B6B0@.microsoft.com...
> We have had some problems with getting a trace restarted after capturing
the trace output to a table. Is there a query or API call that can
determine if a server trace is running?
>sql

Detecting of record does not work in if query

Edit: Newer mind. I tested this query more after writing this, and now it seems to work!

I hope it continues to work.

In following query, else is never executed.

CREATE PROCEDURE Put_into_basket
( @.Product_code varchar(20))
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@.Product_code)
BEGIN
INSERT dbo.t_shopping_basket (Product_code, Name,Price)
SELECT Product_code, Name,Price
FROM dbo.t_product
WHERE Product_code= @.Product_code
END
ELSE --this part is never executed
BEGIN
UPDATE dbo.t_shopping_basket
SET Quantity=Quantity+1
WHERE Product_code=@.Product_code
END
END
GO

The query should test if there is a record or row with Product_code=@.Product_code.

If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of.

If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.

But when I test query, it never runs the quantity+1 part.

This is the code that I am using to do the same function. I use your method in other queries but also could not get it working for the cart. In my mind it was like the if exist can only take one other arguement either the update or the insert into but not both. So I changed mine around to this I also have to track another variable as there are different files they can access.

1SELECT2 @.CountItems = Count(ProductID)3FROM4 ShoppingCart5WHERE6 ProductID = @.ProductID7 AND8 CartID = @.CartID9 AND10Filenumber = @.FileNumber1112IF @.CountItems > 01314 UPDATE15 ShoppingCart16 SET17 Quantity = (@.Quantity + ShoppingCart.Quantity)18 WHERE19 ProductID = @.ProductID20 AND21 CartID = @.CartID22 AND23FileNumber = @.FileNumber2425ELSE26 INSERT INTO ShoppingCart27 (28 CartID,29 Quantity,30 ProductID,31FileNumber32 )33 VALUES34 (35 @.CartID,36 @.Quantity,37 @.ProductID,38@.FileNumber39 )

|||

Thanks, I'll keep this in mind. Expesially because my version didn't work at first. Hopefully it was my error and not something which may happen again.

I'm quite new at SQL, your code has some interesting features. Count(ProductID) for instance. You have all carts in same table?

Out of curiosity, where do need FileNumber field. That is, is specific to your solution?

Regards

Leif

|||

Yea it is specitic to my cart. They can buy the same report but covering different info. Not wanting to put a couple thousand individual prices in and having to update every time they added to the site, I have one price for each different report and then tell which report they bought. So the cart could show report1 several times and each one would have a different file number. If they buy something that is not related to a filenumber like a subscription then I just put a 0 in that field.

I use one table for the cart and if they buy, then I move the contents over to the order tables (2 tables) and delete the items from the cart. So that table should stay fairly small.

sql

Detecting a suspect database...

Howdy.
Hey, I am writing a proc that will monitor database vitals. does anyone know how to tell if a database is in suspect mode from query analyzer? I imagine you should be able to tell from the status column in sysdatabases, but I cant seem to figure it out.
TIAHere's some info
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp

and some more
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-d_5xrn.asp|||Type following on the SQL Query Analyser

USE <database_name>
SELECT DATABASEPROPERTY('<database_name>', 'IsSuspect')

Check result:
1 = TRUE
0 = FALSE
NULL = Invalid input

Hope this helps!!!!!

Detect Stored Procedure that refer to tables that do not exist

It is possible to construct a query that will list all stored procedures
that refer to tables that no longer exist in the database?
Thanks,
ChrisHi
Execute each SP with "SET NOEXEC ON" set.
From BOL
"The execution of statements in SQL Server consists of two phases:
compilation and execution. This setting is useful for having SQL Server
validate the syntax and object names in Transact-SQL code when executing. It
is also useful for debugging statements that would usually be part of a
larger batch of statements"
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"C-W" <nomailplease@.microsoft.nospam> wrote in message
news:eFzSEi3lFHA.3900@.TK2MSFTNGP09.phx.gbl...
> It is possible to construct a query that will list all stored procedures
> that refer to tables that no longer exist in the database?
>
> Thanks,
> Chris
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Execute each SP with "SET NOEXEC ON" set.
> From BOL
> "The execution of statements in SQL Server consists of two phases:
> compilation and execution. This setting is useful for having SQL
> Server validate the syntax and object names in Transact-SQL code when
> executing. It is also useful for debugging statements that would
> usually be part of a larger batch of statements"
> Regards
Mike,
NOEXEC doesn't seem to work for me. For some reason, it doesn't detect
the drop of the table in my example. However, when I tried using SET
FMTONLY ON, it worked and does not depend on any branching in the
procedure itself that would prevent access to the underlying missing
table.
create table abc123 (co1l int)
go
drop proc abc123test
go
create proc abc123test @.b bit
as
begin
if @.b = 0
Select co1l from abc123
else
Select id from sysobjects
end
go
exec abc123test 1
exec abc123test 0
drop table abc123
go
set fmtonly on
exec abc123test 1
exec abc123test 0
set fmtonly off
drop proc abc123test
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks,
Is it possible to perform the checking at the time the stored procedure is
created. The problem I have got is that it would take a long time to test a
thousand+ number of stored procedures using exec, supplying different
parameters to each one etc. So I thought it would be easier to generate a
script that attempts to drop and then recreate the stored procedures and
stop when it fails because the table does not exist.
So is it possible to perform the check when I actually do the CREATE PROC...
create proc abc123test @.b bit
as
Select co1l from abc123
go
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
> NOEXEC doesn't seem to work for me. For some reason, it doesn't detect the
> drop of the table in my example. However, when I tried using SET FMTONLY
> ON, it worked and does not depend on any branching in the procedure itself
> that would prevent access to the underlying missing table.
> create table abc123 (co1l int)
> go
> drop proc abc123test
> go
> create proc abc123test @.b bit
> as
> begin
> if @.b = 0
> Select co1l from abc123
> else
> Select id from sysobjects
> end
> go
> exec abc123test 1
> exec abc123test 0
> drop table abc123
> go
> set fmtonly on
> exec abc123test 1
> exec abc123test 0
> set fmtonly off
> drop proc abc123test
> go
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks,
> Is it possible to perform the checking at the time the stored
> procedure is created. The problem I have got is that it would take a
> long time to test a thousand+ number of stored procedures using exec,
> supplying different parameters to each one etc. So I thought it
> would be easier to generate a script that attempts to drop and then
> recreate the stored procedures and stop when it fails because the
> table does not exist.
> So is it possible to perform the check when I actually do the CREATE
> PROC...
> create proc abc123test @.b bit
> as
> Select co1l from abc123
> go
>
> Thanks,
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:eG73N$3lFHA.2852@.TK2MSFTNGP15.phx.gbl...
No. You can't do that at creation time. You could probably write
something pretty quickly to iterate through the syscolumns table for
each procedure and generate a dummy set of parameters. There is also
likely sample code out there to do the same.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks David
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
> No. You can't do that at creation time. You could probably write something
> pretty quickly to iterate through the syscolumns table for each procedure
> and generate a dummy set of parameters. There is also likely sample code
> out there to do the same.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||C-W wrote:
> Thanks David
> Chris
>
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23MGh30DmFHA.1372@.TK2MSFTNGP10.phx.gbl...
Ok. I whipped something together that you can use directly in a T-SQL
script. The script is mildly tested, so additional testing is required
at your end before using on a production system. The script creates the
necessary dynamic SQL to execute each procedure with NULL parameters and
then executes using SET FMTONLY ON and stored the results of the
execution in the temp table.
Good luck.
-- Stores the objects, execution SQL, and result
Create Table #ExecProcs (
ID INT IDENTITY NOT NULL,
Owner nvarchar(128) NOT NULL,
SPName nvarchar(128) NOT NULL,
ParameterCount INT NOT NULL,
ExecResult INT NULL,
ExecSQL nvarchar(2000) NOT NULL,
PRIMARY KEY (ID))
go
-- Get a list of procedures and create the execution SQL
Insert into #ExecProcs (
Owner,
SPName,
ParameterCount,
ExecSQL )
Select
USER_NAME(o.uid) as "Owner",
o.name as "SPName",
COUNT(p.name) as "ParameterCount",
'SET FMTONLY ON;' +
'EXEC [' +
USER_NAME(o.uid) + '].[' + o.name + '] ' +
CASE SIGN(COUNT(p.name))
WHEN 0
THEN ''
WHEN 1
THEN
REPLICATE('NULL,', COUNT(p.name) - 1) +
REPLICATE('NULL', COUNT(p.name) - (COUNT(p.name) - 1))
END + ';'
from
dbo.sysobjects o
left outer join
dbo.syscolumns p
on
o.id = p.id
where
o.type = 'P'
Group By
o.uid,
o.id,
o.name
Order By
o.uid,
o.id,
o.name
go
-- check the results
Select * from #ExecProcs
go
-- script to execute each procedure and store result in temp table
Declare @.Result INT
Declare @.SQL nvarchar(2000)
Declare @.ID INT
Declare @.Loop BIT
Set @.Loop = 1
While (@.Loop = 1)
Begin
SET @.ID = NULL
Select TOP 1
@.ID = ID,
@.SQL = ExecSQL
From
#ExecProcs
Where
ExecResult IS NULL
If @.ID IS NULL
SET @.Loop = 0
Else Begin
EXEC (@.SQL)
Update
#ExecProcs
Set
ExecResult = @.@.ERROR
Where
ID = @.ID
End
End
go
-- check results
Select * from #ExecProcs
go
-- drop the temp table
drop Table #ExecProcs
go
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David - this works great.
Thanks,
Chris
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OuJKWnEmFHA.2904@.TK2MSFTNGP14.phx.gbl...
> C-W wrote:
> Ok. I whipped something together that you can use directly in a T-SQL
> script. The script is mildly tested, so additional testing is required at
> your end before using on a production system. The script creates the
> necessary dynamic SQL to execute each procedure with NULL parameters and
> then executes using SET FMTONLY ON and stored the results of the execution
> in the temp table.
> Good luck.

Details Grouping

Can a table have more then one detail group? I need 4 detail groups show from a parent group. They all come from the same query dataset. Or is the a better way to do this?

Thanks

You can't have multiple detail groups in a table. However, you can add more rows for the parent group. Then in each of the rows, add a nested table/list to show the details.

Detail row doesn't repeat!

How do I set the detail row to show all similar records?
I created another report with same exact query and it shows all 25.
Thanks,
TrintPlease provide more details than this for us to be able to help you.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105127010.082333.226250@.z14g2000cwz.googlegroups.com...
> How do I set the detail row to show all similar records?
> I created another report with same exact query and it shows all 25.
> Thanks,
> Trint
>|||header --> stufff
details--> one row and should be 25.|||header --> stufff
details--> one row and should be 25.|||Sounds like a developer error to me.
You're obviously not eager to work at explaining your problem. Why should
we work to help you solve it?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105129238.972872.279370@.c13g2000cwb.googlegroups.com...
> header --> stufff
> details--> one row and should be 25.
>|||Ok,
What is required of a 'detail' row to display multiple instances of
records with, let's say '123' as the first similar column...is that a
setting in the layout?
Thanks,
Trint|||The hide duplicates property.
"trint" wrote:
> Ok,
> What is required of a 'detail' row to display multiple instances of
> records with, let's say '123' as the first similar column...is that a
> setting in the layout?
> Thanks,
> Trint
>

Design Solution Required

We are facing design issues, Could you please advice us how to proceed?

Problem description: Web App will pass a complex dynamic SQL query to
backend and it should return result set as fast as it can
Issue 1: SQL query will have lot of JOINS and WHERE clause
Issue 2: Each Table contain millions of records

Requirement: Turn around time of the SQL query should be as far as
possible minimum.

Could you please advice us which technology we should use, such that
users get the resultset in few seconds.

We are Microsoft Partner. We use only Microsoft technology for our
product development.

Your Help is much appreciated

With Regards
S a t h y a RCould you please advice us which technology we should use, such that

Quote:

Originally Posted by

users get the resultset in few seconds.

Pay particular attention to index and query tuning. Make sure you have
indexes that the optimizer can use to generate the most efficient plan.
Prioritize tuning so that the most often executed and expensive queries are
addressed first. Also consider indexed views, which are especially
appropriate for aggregated data. Keep in mind that too many indexes can
hurt performance if you do a lot of inserts/updates so you'll need to
perform cost-benefit analysis.

I suggest you get a good book that covers query and index tuning in depth.
I recommend Inside Microsoft SQL Server 2005: T-SQL Querying, ISBN
9780735623132.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sathya" <sathyamca@.gmail.comwrote in message
news:1164801200.132687.7070@.j72g2000cwa.googlegrou ps.com...

Quote:

Originally Posted by

We are facing design issues, Could you please advice us how to proceed?
>
Problem description: Web App will pass a complex dynamic SQL query to
backend and it should return result set as fast as it can
Issue 1: SQL query will have lot of JOINS and WHERE clause
Issue 2: Each Table contain millions of records
>
Requirement: Turn around time of the SQL query should be as far as
possible minimum.
>
>
Could you please advice us which technology we should use, such that
users get the resultset in few seconds.
>
We are Microsoft Partner. We use only Microsoft technology for our
product development.
>
>
Your Help is much appreciated
>
With Regards
S a t h y a R
>

|||Sathya wrote:

Quote:

Originally Posted by

We are facing design issues, Could you please advice us how to proceed?
>
Problem description: Web App will pass a complex dynamic SQL query to
backend and it should return result set as fast as it can
Issue 1: SQL query will have lot of JOINS and WHERE clause
Issue 2: Each Table contain millions of records

Quote:

Originally Posted by

Could you please advice us which technology we should use, such that
users get the resultset in few seconds.
>

Use sp_executesql to execute your dynamic SQL (not EXEC). Even better,
try to use a prepared statement. In your queries, make sure to use the
indexes, avoid calling functions and do not sort in SQL unless it is
absolutely necessary (sort on client side instead).

You could also save typical queries and run them through the Database
Tuning Advisor, which will suggest how to index your tables. This
wizard is available with SQL Server 2005 in the Management Studio, but
it can help to tune SQL Server 2000 databases as well.

If you can afford it, use SQL 2005 Enterprise Edition, which will allow
you to partition your tables. Partitions can greatly improve speed.
Again save a typical query and run it through the Database Tuning
Advisor, which can suggest how to create optimal partitions.

This wizard is just awesome, but of course if your queries are
completely random and different it won't be of much help since it need
a specific workload to make suggestions.

Regard,
lucm