How to detect whether SMO is installed?

Hi, folks --
I am working on an install bootstrapper that (among other things)
will install Server Management Objects (SQLServer2005_XMO.msi from SQL
Server 2005 Feature Pack) iff it's not already installed.
So, how to detect programmatically whether or not it's already
installed?
What files/version/registry keys/usual suspects should I be looking
for?
Thanks in advance for your help.
-- Davidson
Hello n7dai@.comcast.net,
I normally look for C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.d ll.
I believe current-most is 9.0.3042.0.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||What about:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=577731&SiteID=1
Jens K. Suessmeyer.
http://www.sqlserver2005.de
<n7dai@.comcast.net> wrote in message
news:1175273193.753957.182290@.e65g2000hsc.googlegr oups.com...
> Hi, folks --
> I am working on an install bootstrapper that (among other things)
> will install Server Management Objects (SQLServer2005_XMO.msi from SQL
> Server 2005 Feature Pack) iff it's not already installed.
> So, how to detect programmatically whether or not it's already
> installed?
> What files/version/registry keys/usual suspects should I be looking
> for?
> Thanks in advance for your help.
> -- Davidson
>

how to detect transactional replication agent failure programmatic

Hello,
Right now, I have an alert set up in the SQL Server but is it possible for a
C#/C/C++/VB program to detect the replication agent failure? If so, how can
it be done?
Thanks in advance,
Frank
query the repl_errors table in the distirbution database.
select name, Publisher=p.srvname, publisher_db, publication,
Subscriber=s.srvname, subscriber_db, MSdistribution_history.time, comments,
error_type_id,source_type_id, source_name, error_code,error_text from
MSdistribution_history
join msrepl_errors on MSdistribution_history.error_id=msrepl_errors.id
join msdistribution_agents on
msdistribution_agents.id=MSdistribution_history.ag ent_id
join master.dbo.sysservers as p on p.srvid=publisher_id
join master.dbo.sysservers as s on s.srvid=publisher_id
order by 1
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"FJY" <FJY@.discussions.microsoft.com> wrote in message
news:1F219C21-2032-422F-AC54-C0D5BBC15CAA@.microsoft.com...
> Hello,
> Right now, I have an alert set up in the SQL Server but is it possible for
> a
> C#/C/C++/VB program to detect the replication agent failure? If so, how
> can
> it be done?
> Thanks in advance,
> Frank

How to detect the current settings of ANSI_NULLS?

Hello All.
How to detect the current settings of ANSI_NULLS at run-time of script?
For example:
SET ANSI_NULLS OFF - sets ANSI_NULLS to OFF
GO
GET ANSI_NULLS - gets the settings of ANSI_NULLS.Hi
dbcc useroptions
<burlaka@.yandex.ru> wrote in message
news:1143625595.136842.163590@.t31g2000cwb.googlegroups.com...
> Hello All.
> How to detect the current settings of ANSI_NULLS at run-time of script?
> For example:
> SET ANSI_NULLS OFF - sets ANSI_NULLS to OFF
> GO
> GET ANSI_NULLS - gets the settings of ANSI_NULLS.
>|||Thank you very much.

How to detect that the sql server already lost the connection?

Hi,
Sometimes when i use my application but suddenly the
sql server disconnected and i find out that my query
become very slow.
Can i check with you, how to detect the connection
already stop in my application? If i dun want to try and
catch for every sql transaction, how i can catch the sql
server disconnected error once the server already
disconnected? I don't want my application just seems
like 'hang' there.
Thanks for advice.
regards,
florence
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:078501c4ac35$087620a0$a301280a@.phx.gbl...
> Hi,
> Sometimes when i use my application but suddenly the
> sql server disconnected and i find out that my query
> become very slow.
> Can i check with you, how to detect the connection
> already stop in my application? If i dun want to try and
> catch for every sql transaction, how i can catch the sql
> server disconnected error once the server already
> disconnected? I don't want my application just seems
> like 'hang' there.
When I've had an application that performs periodic queries against the
database where the connection could be lost between queries, I've used a
simple query to start the next batch of queries just to see that the
connection was alive. For example, you might do a simple "SELECT 1" just to
see that the connection is alive, then do your complicated query.
This was in Java using the JDBC driver.
Rick
sql

How to detect that a sqlserver table row has foreign keys and cannot be deleted?

What is the best way to detect that a sqlserver table row has foreign keys and cannot be deleted?

Thanks,

Keith

sp_table_constraints_rowset 'yourTableName'

How to detect suspect status out of 100 servers in network.

1) Suppose 100 servers...if one goes in suspect db..how can we check which d
atabase is suspect..
all 100 servers are scattered around the world...and we need to immediate th
e server went to suspect database...
Hows that possible...
2)log shipping..
can stand by server in non recovery mode..be used to handle and transactions
whether it is Read only or write and read...
Can we use tempdb in standby server for normal creation and writing in temp
tables..sanjaya
1)
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @.dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'The database is not in suspect mode.'
RETURN (1)
END
2)
Please refer to BOL
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:D017C0EC-7C4D-42AE-AFF3-AEF62355A787@.microsoft.com...

quote:

---

> 1) Suppose 100 servers...if one goes in suspect db..how can we check which

---

database is suspect..

quote:

---

> all 100 servers are scattered around the world...and we need to immediate

---

the server went to suspect database...

quote:

---

> Hows that possible...
> 2)log shipping..
> can stand by server in non recovery mode..be used to handle and

---

transactions whether it is Read only or write and read...

quote:

---

> Can we use tempdb in standby server for normal creation and writing in

---

temp tables..

quote:

---

>
>

---

|||I scan and check the status of all my databases by
actaully querying them -- i.e. trying to run a simple
query. Then, I check the error code to determine what
state the database is in.
You can't make any change to a database in the standby
mode.
Linchi

quote:

---

>--Original Message--
>1) Suppose 100 servers...if one goes in suspect db..how

---

can we check which database is suspect..

quote:

---

>all 100 servers are scattered around the world...and we

---

need to immediate the server went to suspect database...

quote:

---

>Hows that possible...
>2)log shipping..
> can stand by server in non recovery mode..be used to

---

handle and transactions whether it is Read only or write
and read...

quote:

---

>Can we use tempdb in standby server for normal creation

---

and writing in temp tables..

quote:

---

>
>
>.
>

---

How to detect suspect status out of 100 servers in network.

1) Suppose 100 servers...if one goes in suspect db..how can we check which database is suspect.
all 100 servers are scattered around the world...and we need to immediate the server went to suspect database..
Hows that possible..
2)log shipping.
can stand by server in non recovery mode..be used to handle and transactions whether it is Read only or write and read..
Can we use tempdb in standby server for normal creation and writing in temp tables.sanjaya
1)
IF (SELECT COUNT(*) FROM master..sysdatabases
WHERE name = @.dbname AND status & 256 = 256) != 1
BEGIN
PRINT 'The database is not in suspect mode.'
RETURN (1)
END
2)
Please refer to BOL
"sanjaya" <anonymous@.discussions.microsoft.com> wrote in message
news:D017C0EC-7C4D-42AE-AFF3-AEF62355A787@.microsoft.com...
> 1) Suppose 100 servers...if one goes in suspect db..how can we check which
database is suspect..
> all 100 servers are scattered around the world...and we need to immediate
the server went to suspect database...
> Hows that possible...
> 2)log shipping..
> can stand by server in non recovery mode..be used to handle and
transactions whether it is Read only or write and read...
> Can we use tempdb in standby server for normal creation and writing in
temp tables..
>
>|||I scan and check the status of all my databases by
actaully querying them -- i.e. trying to run a simple
query. Then, I check the error code to determine what
state the database is in.
You can't make any change to a database in the standby
mode.
Linchi
>--Original Message--
>1) Suppose 100 servers...if one goes in suspect db..how
can we check which database is suspect..
>all 100 servers are scattered around the world...and we
need to immediate the server went to suspect database...
>Hows that possible...
>2)log shipping..
> can stand by server in non recovery mode..be used to
handle and transactions whether it is Read only or write
and read...
>Can we use tempdb in standby server for normal creation
and writing in temp tables..
>
>
>.
>

How to detect SQLServer 2005

How can I detect if my computer have installed SQLServer 2005 or SQLServer
2005 Express with program like c# or VB.NNET?
Hi
If you can connect to the instance you can run a query that returns the
result of the function SERVERPROPERTY
SELECT CONVERT(varchar(40), SERVERPROPERTY('edition'));
John
"ad" wrote:

> How can I detect if my computer have installed SQLServer 2005 or SQLServer
> 2005 Express with program like c# or VB.NNET?
>
>

How to detect SQLServer 2005

How can I detect if my computer have installed SQLServer 2005 or SQLServer
2005 Express with program like c# or VB.NNET?Hi
If you can connect to the instance you can run a query that returns the
result of the function SERVERPROPERTY
SELECT CONVERT(varchar(40), SERVERPROPERTY('edition'));
John
"ad" wrote:
> How can I detect if my computer have installed SQLServer 2005 or SQLServer
> 2005 Express with program like c# or VB.NNET?
>
>sql

How to detect sql servers on the net

Hi
Is there a way to detect any instances of sql server on the network.
Like all the Microsoft configuration tools do...
If there exists one, is it also possible to use it within the .net
environment?
Regards
StephanStephan Zaubzer wrote:

> Hi
> Is there a way to detect any instances of sql server on the network.
> Like all the Microsoft configuration tools do...
> If there exists one, is it also possible to use it within the .net
> environment?
> Regards
> Stephan
Use OSQL -L|||I was rather looking for a way to use it within an application
In my application I need a config window where I specify to which server
to connect to...
And I wanna show all available servers in the config window...
amish wrote:
> Stephan Zaubzer wrote:
>
>
> Use OSQL -L
>|||Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
news:438F2929.5030501@.schendl.at:

> I was rather looking for a way to use it within an application
> In my application I need a config window where I specify to which
> server to connect to...
> And I wanna show all available servers in the config window...
>
You can do this in two ways;
1. You can ask for a DataSourceEnumerator from your provider (the method
name may not be exactly that, but you can find it). 2. Use SMO to
enumerate the servers on the network.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||... and to my knowledge, this is still not reliable since broadcasting is u
sed. So a server might be
in another domain, might not respond in time etc and hence will not show up.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns971FB26F1D379nielsbdevelopcom@.20
7.46.248.16...
> Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
> news:438F2929.5030501@.schendl.at:
>
> You can do this in two ways;
> 1. You can ask for a DataSourceEnumerator from your provider (the method
> name may not be exactly that, but you can find it). 2. Use SMO to
> enumerate the servers on the network.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:uCt#Ilx9FHA.2816@.tk2msftngp13.phx.gbl:

> ... and to my knowledge, this is still not reliable since broadcasting
> is used. So a server might be in another domain, might not respond in
> time etc and hence will not show up.
>
absolutely!!!! The methods I mentioned earlier are very brittle, and you
can not rely 100% on them. Also, as they use broadcast the methods are
sloooow.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||DataSourcEnumerator exists only in .net 2.0
Since I am working in VS.net 2003 and .net 1.1 I can't use it.
What exactly is SMO and how would I use it?
regards
Stephan
Niels Berglund wrote:
> Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
> news:438F2929.5030501@.schendl.at:
>
>
> You can do this in two ways;
> 1. You can ask for a DataSourceEnumerator from your provider (the method
> name may not be exactly that, but you can find it). 2. Use SMO to
> enumerate the servers on the network.
> Niels
>|||Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
news:uDd8Y019FHA.252@.TK2MSFTNGP15.phx.gbl:
> DataSourcEnumerator exists only in .net 2.0
> Since I am working in VS.net 2003 and .net 1.1 I can't use it.
> What exactly is SMO and how would I use it?
> regards
> Stephan
> Niels Berglund wrote:
[snip]
Sorry Stephan abour DataSourceEnum. I'm afraid that SMO is in that case
not any better either. It is the new management object hierarchy in SQL
2005.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

How to detect replication doesnt work and get noticed?

I want to get notice by netsend, email or any other methods
when replication doesn't work or malfunction.
Does anyone know how to do this or any other solution you use for this kind of issue?
Thank you..Every agent that runs your replication (snapshot, logreader, distributot, and queuereader) have associated SQLAgent jobs. All you have to do is modify those jobs by going to Notification tab of the job properties and selecting notification method you'd like to get. You should have an appropriate operator set up before, or you can do it right there by selecting New Operator.|||Thank you for reply..

The main problem I have is how to find out if the replication doesn't work..
I can notice with agent history or icon.

Sometimes the icon become X marked, some times agent keep trying to connect and generate error with forever loop, sometimes gives I/O error while working..

How to find out these and what's best way to find out if replication doesn't work properly?|||Have you looked at what I posted about?|||Yes I did..
I knew that part..
Could you give me more detail with example with specific error cases?
Then, I understand more clearly.|||see attachment|||Thank you for the image..
I think I got it at this level..

I just wonder is there a way to run a custom application when job fails rather than sending Net-send..

Since I have to check hundreds of systems replicated, if I use net-send I will get too much of it and hard to maintain.
If I can run my custome application reporting problem organized, it will be easy to maintain.|||In that case you should create a batch file and call it from task window .

In SQL Scheduler when you click on the task and click advanced it says

" On failure action: and then there is a drop down . All you have to do is add another task to the existing job and call it here

It will say

"On failure Action : Goto Step #2" In that step #2 you can all the windows batch file .. (which can contain anything like ISQL, another program ...)|||Thank you!
I got it!|||Another way of doing it would be to create a custom alert and a scheduled task. When conditions for the alert are met you can invoke that scheduled task that may contain ... see previous post.

How to detect overlapping Time Entries

Hello,

I am trying to create a SQL Statement which will identify if an entry can be added to a table or not. My table consists of 4 fields which are:

. UserID (Integer)
. StartTime (datetime)
. EndTime (datetime)
. Activity (varchar)

This is a timesheet application. I am trying to identify if a time entered by a user is valid or not. Basically, times cannot overlap. I'm trying to figure out how to code for the following conditions:

Assume an entry already exists for User 1 as follows:

. UserID: 1
. StartTime: 2006-12-30 08:00:00
. EndTime: 2006-12-30 08:15:00
. Activity: Test

I want to make sure that the following entries cannot be added by that user because they would overlap the existing entry:

. StartTime: 2006-12-30 07:50:00
. EndTime: 2006-12-30 08:05:00

OR

. StartTime: 2006-12-30 07:45:00
. EndTime: 2006-12-30 08:45:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:30:00

OR

. StartTime: 2006-12-30 08:05:00
. EndTime: 2006-12-30 08:10:00

Any help is appreciated.

Thanks

Something like this...

select *
from timetrack_tbl
where
userid = 1
AND
(
(
'2006-12-30 07:50:00' between starttime and endtime
OR
'2006-12-30 08:05:00' between starttime and endtime
)
OR
(
'2006-12-30 07:50:00' <= starttime
AND
'2006-12-30 08:05:00' >= endtime
)
)
If you are going to be doing this continually, it might be a good choice for a function with the above code. Create the function to accept the userid, starting and ending dates and return some value indicating whether there is an overlap.

Example:
select dbo.CheckForOverlap(userid, '2006-12-30 07:50:00', '2006-12-30 08:05:00')

return something from the function that tells you if there is an overlap (like a bit, 1 = Overlap, 0 = No Overlap

|||

That worked like a charm.

Thank you so much for the quick response.

-- Val

How to detect NULL in SQL-table with VB.net ?

Hi,
I want to check with VB.net whether a field in a SQL-table is NULL or not.
This code doesnot work:
If xxx = NULL then
<statements>
End If
I got the error, that NULL is not supported ?
How do I code the check ?
Help is appreciated, Gr.

Hi,

it would beDBNull.Value oryou can also useIsDBNull function (returns boolean based on if the given object has DBNull value)

|||Joteke, thanks a lot for you help,
regards from the North Sea, Ger.

How to detect INSTALLED SQL version?

Hello,
I need to detect whether the computer has SQL Server 2000 SP3 installed
before
attempting to connect to it, for example during my custom setup program. Wha
t
is the recommended way of doing this?
TIA,
A.SELECT TOP 0 NULL AS Property , NULL AS Variable
UNION ALL SELECT 'Version' , SERVERPROPERTY( 'ProductVersion' )
UNION ALL SELECT 'Level' , SERVERPROPERTY( 'ProductLevel' )
UNION ALL SELECT 'Engine Edition' , SERVERPROPERTY( 'Edition' )
"Abbey" <Abbey@.discussions.microsoft.com> wrote in message
news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
> Hello,
> I need to detect whether the computer has SQL Server 2000 SP3 installed
> before
> attempting to connect to it, for example during my custom setup program.
What
> is the recommended way of doing this?
> TIA,
> A.
>|||HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQLServer\MSSQLServer\CurrentVe
rsion\
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<instance
name>\MSSQLServer\CurrentVersion\
Key name = CSDVersion (not CurrentVersion).
"Abbey" <Abbey@.discussions.microsoft.com> wrote in message
news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
> Hello,
> I need to detect whether the computer has SQL Server 2000 SP3 installed
> before
> attempting to connect to it, for example during my custom setup program.
> What
> is the recommended way of doing this?
> TIA,
> A.
>|||This won't work "before attempting to connect to it"... you have to be
connected to the server to run your query...

> SELECT TOP 0 NULL AS Property , NULL AS Variable
> UNION ALL SELECT 'Version' , SERVERPROPERTY( 'ProductVersion' )
> UNION ALL SELECT 'Level' , SERVERPROPERTY( 'ProductLevel' )
> UNION ALL SELECT 'Engine Edition' , SERVERPROPERTY( 'Edition' )|||Hello Aaron,
Thanks for that suggestion - I'll try it and let you know...
Regards,
Abbey
"Aaron Bertrand [SQL Server MVP]" wrote:

> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
> Server\MSSQLServer\MSSQLServer\CurrentVe
rsion\
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<instance
> name>\MSSQLServer\CurrentVersion\
> Key name = CSDVersion (not CurrentVersion).
>
> "Abbey" <Abbey@.discussions.microsoft.com> wrote in message
> news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
>
>|||Is there any harm in opening a connection to check the database version?
If it's the wrong version you can always run "wrong version" code and
disconnect the connection.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4362481f$0$134$7b0f0fd3@.mistral.news.newnet.co.uk...
> SELECT TOP 0 NULL AS Property , NULL AS Variable
> UNION ALL SELECT 'Version' , SERVERPROPERTY( 'ProductVersion' )
> UNION ALL SELECT 'Level' , SERVERPROPERTY( 'ProductLevel' )
> UNION ALL SELECT 'Engine Edition' , SERVERPROPERTY( 'Edition' )
>
> "Abbey" <Abbey@.discussions.microsoft.com> wrote in message
> news:1D2E126E-B6FE-411C-B157-FD3BBB49CE82@.microsoft.com...
> What
>sql

How to detect if SQL Reporting Services is installed ?

From an ASP.Net application I need to detect if on a given node, Repoting
services is installed and if so, which version. Does anyone know a stored
procedure or a Web Services API to do this ?
thanks,RaviHello Ravi,
There are a couple ways to determine RS version:
1) Check the registry at:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Reporting Services\Version
2) Use the webservice API like this:
For RS 2000:
ReportingService rs1 = new ReportingService();
rs1.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs1.ServerInfoHeaderValue = new ServerInfoHeader();
rs1.ListChildren("/", false);
Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerVersionNumber);
For RS 2005:
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.ServerInfoHeaderValue = new ServerInfoHeader();
rs.ListChildren("/", false);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
Note that there is a new property in 2005, "ReportServerVersion". Also be
aware that ReportServerVersionNumber returns differntly formatted values
for 2000 and 2005.
-Chris
> From an ASP.Net application I need to detect if on a given node,
> Repoting services is installed and if so, which version. Does anyone
> know a stored procedure or a Web Services API to do this ?
> thanks,Ravi
>|||Thanks Chris, that was very helpful.
I have started using the #1 to read the registry. But my requirement is that
I need to be able to detect RS and its version on local and remote machines,
including ones behind firewall. Reading Registry in these secanrios leads to
lot of security issues. Further, I also need to detect if RS is currently
running, I need this to enable a button to launch Report Builder.
Using the RS2005 Web Services seems to be a better approach. Do you know of
an API to also detect if Report Services is currently running ? Do you know
what happens if Report Services is not installed on a remote machine but we
try to call a Web Service ?
thanks for your time and help,
Ravi
"Chris Baldwin" wrote:
> Hello Ravi,
> There are a couple ways to determine RS version:
> 1) Check the registry at:
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Reporting Services\Version
> 2) Use the webservice API like this:
> For RS 2000:
> ReportingService rs1 = new ReportingService();
> rs1.Credentials = System.Net.CredentialCache.DefaultCredentials;
> rs1.ServerInfoHeaderValue = new ServerInfoHeader();
> rs1.ListChildren("/", false);
> Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerEdition);
> Console.WriteLine(rs1.ServerInfoHeaderValue.ReportServerVersionNumber);
> For RS 2005:
> ReportingService2005 rs = new ReportingService2005();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> rs.ServerInfoHeaderValue = new ServerInfoHeader();
> rs.ListChildren("/", false);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersion);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerEdition);
> Console.WriteLine(rs.ServerInfoHeaderValue.ReportServerVersionNumber);
> Note that there is a new property in 2005, "ReportServerVersion". Also be
> aware that ReportServerVersionNumber returns differntly formatted values
> for 2000 and 2005.
> -Chris
>
> > From an ASP.Net application I need to detect if on a given node,
> > Repoting services is installed and if so, which version. Does anyone
> > know a stored procedure or a Web Services API to do this ?
> >
> > thanks,Ravi
> >
>
>

How to detect if column data changed and know prev. and new value

I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

BillHi

Check out CREATE TRIGGGER in Books Online or at
http://msdn.microsoft.com/library/d...asp?frame=true

In particular the COLUMNS_UPDATED example of the IF UPDATE clause.

John

"Bill Tepe" <billtepe@.mssonline.net> wrote in message
news:7364847c.0309060600.7023b89a@.posting.google.c om...
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.
> Is there a "simple" way to do this? I know I could query the table
> before the update and compare to what the new value is and react
> accordingly.
> Just wondering if there is something nifty in Sql Server that I am
> missing that could help me with this.
> Thanks in advance for your help.
> Bill|||[posted and mailed, please reply in news]

Bill Tepe (billtepe@.mssonline.net) writes:
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.

In a trigger you can retrieve the new value in the "inserted" table
and the old value in the "deleted" tables. These tables are virtual
and are accessible only in the trigger.

Beware that a trigger in SQL Server fires once per statement, not once
per row as in some other products. Thus, the tables can old many rows.

You should also be aware of access to these tables when they contain
many rows can be slow. Therefore it is often good idea to start a trigger
with:

select * INTO #tblname_inserted FROM inserted
select * INTO #tblname_deleted FROM deleted

Since you are into auditing... If you are doing this on any large
scalce, you should probably consider third-party solutions rather
than reinventing the wheel. www.redmatrix.com has a product SQLAudit,
which I have no experience of myself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you need to do this at more of an enterprise level, you might look
into Lumigent's Entegra (haven't used it but buying it next year :))

http://lumigent.com/products/entegra/entegra.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

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

how to detect if an alias type is existed or not before create type?

how to detect if an alias type is existed or not before create type?Hello, Frank
Look into the sys.types catalog view. For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms188021.aspx
Razvan

How to detect if a schema if exists or not so that I will not create same schema agai

How to detect if a schema if exists or not so that I will not create same
schema again?
AND create schema statement should be the first statement of a bach?
--Frank, SQL2005devHello, Frank

> How to detect if a schema if exists or not so that I will not create same
> schema again?
Look into sys.schemas

> AND create schema statement should be the first statement of a bach?
Yes.
For example, you can use something like this:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
EXEC('CREATE SCHEMA YourSchema')
Razvan|||... or use
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>|||I got it, thanks.
"Razvan Socol" <rsocol@.gmail.com>
'?:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>

How to detect if a cursor has not been de-allocated or is still open?

Hi,

I am using try/catch block for my stored procedure (SQL Server 2005), when a cursor is used, how do I detect if the cursor has not been de-allocated or is still open?

Right now, I am using something such as

Begin Try

Begin Transaction

..................

-- mycursor will be declared and used here

....................

Commit Transaction

End Try

Begin Catch

Begin Try

Close mycursor

Deallocate mycursor

End Try

Begin Catch

--if mycursor was already closed/de-allocated,

End Catch

End Catch

I have a feeling that there must be a better way to do it.

Any suggestion?

Thanks.

Cathie

Actually this is not a bad way to go if you have to use global cursors (or really cursors at all, though that is another topic). If you aren't using global cursors (DECLARE CURSOR <name> LOCAL , or use a Variable SET @.cursor = CURSOR , or even set the database option:

ALTER DATABASE <databaseName>
SET CURSOR_DEFAULT GLOBAL

And your cursors will deallocate automatically when they lose scope.

|||

Hello,

Just query the results of the new dmf:

select * from sys.dm_exec_cursors(0) -- 0 being all sessions, otherwise pass in @.@.spid

Cheers,

Rob

|||

The following script might help you...

Read more on BOL about sp_cursor_list

Code Snippet

DECLARE My_Cursor CURSOR

FOR SELECT * FROM Sys.Objects

OPEN My_Cursor

FETCH NEXT FROM My_Cursor

DECLARE @.CursorName AS VARCHAR(100)

DECLARE @.Report CURSOR

EXEC master.dbo.sp_cursor_list @.cursor_return = @.Report OUTPUT,

@.cursor_scope = 3;

FETCH NEXT INTO @.CursorName from @.Report

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN

IF @.CursorName = 'My_Cursor'

BEGIN

CLOSE My_Cursor

DEALLOCATE My_Cursor

END

FETCH NEXT INTO @.CursorName from @.Report

END

CLOSE @.Report

DEALLOCATE @.Report

sql

How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. When
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT has
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a table?
Thanks,
Tom
Hi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
Thanks
Yogish
|||That will only show whether the table *has* an identity column, not whether IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USEROPTIONS, but that doesn't
expose the information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish
|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
Thanks
Yogish
|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SET
IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
Server? returns an error message that states SET IDENTITY_INSERT is already
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
Thanks
Yogish
|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row in
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:

> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
> Server? returns an error message that states SET IDENTITY_INSERT is already
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>