How to manage all the SqlNK versions at the same time?

Hi everyone,

Nowadays, we've got four sql versions running around: 6.5, 7.0, 2000 and 2005.

If you try attach from Enterprise Manager 2005 servers it doesn't allow you because of 2005 uses SMO instead of DMO.

If you try attach from Management Studio 6.5 or 7.0 servers it doesn't allow you too.

We'd like to have from the same place a tool for all of them. Is it possible?

Thanks in advance,

I have client tools installed for SQL 6.5, 2000 & 2005 in order to manage our environment that is a mixture of SQL 6.5 to 2005 versions, I have no problem in having 3 of them and using at same time.

Could you please explain what you mean by 'if you try to attach....'.

|||

Hi Staya,

I meant, when you do this action: "New Sql Server Registration" from your client.

Yeah, I know, I can open three clients in my own workstation without problems.

I'm talking about that you can't attach from your Management Studio (2005) 7.0 or 6.5 clients.

Beyond of this, from Enterprise Manager (2000) you can attach any Sql Server 2005 because of sql2k uses DMO library instead of SMO (which is native for 2005).

Ok, you could do it from Query Analyzer.

|||I can connect to any of SQL 2k instances at my end using SSMS and check what is the service pack of those SQL instances that are in version 2000. Even the query analyzer in SQL 2005 should be able to connect to SQL 2000 instnaces.|||

Yeah, don't worry.

Idea was that we might have available an unique tool for see all of them.

My idea is maybe something crooked.

|||I believe SSMS is good one to go, but due to the limitations on backward compatibility you might not get what you want. So in this case you need to have 2 or 3 versions of SQL tools to continue the work. I'm sure there is a opportunity for a third party company to develop such tool.

How to make different instances of SQL Server visible to each other?

Two instances of SQL Server 2005 cannot "see" each other. One is Developer
edition and one is Standard edition. Standard editon is running under
Virtual PC. I would appreciate any suggestions on how make these two
instances visible to each other.
Thanks,
Keith
This should work pretty much like connecting any other two computers.
1) Can each computer ping the other successfully using TCP/IP? If not, it's
a virtual server problem.
2) Is the firewall on each computer configured to allow access through TCP
port 1433 (if you are using the default settings for a default instance)? If
not, change the firewall settings.
3) Is each SQL Server accepting TCP/IP connections? Check using the SQL
Server Surface Area Configuration tool.
4) If you are not using the default TCP port or if you installed a named
instance is the SQL Server Browser service running on each computer/virtual
machine to resolve the instance name to the port number? If not, start the
SQL Server Browser service.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"keith" <kbrickey@.dslextreme.com> wrote in message
news:%23QcBFI0SHHA.3428@.TK2MSFTNGP04.phx.gbl...
> Two instances of SQL Server 2005 cannot "see" each other. One is Developer
> edition and one is Standard edition. Standard editon is running under
> Virtual PC. I would appreciate any suggestions on how make these two
> instances visible to each other.
> Thanks,
> Keith
>

How to make different instances of SQL Server visible to each other?

Two instances of SQL Server 2005 cannot "see" each other. One is Developer
edition and one is Standard edition. Standard editon is running under
Virtual PC. I would appreciate any suggestions on how make these two
instances visible to each other.
Thanks,
KeithThis should work pretty much like connecting any other two computers.
1) Can each computer ping the other successfully using TCP/IP? If not, it's
a virtual server problem.
2) Is the firewall on each computer configured to allow access through TCP
port 1433 (if you are using the default settings for a default instance)? If
not, change the firewall settings.
3) Is each SQL Server accepting TCP/IP connections? Check using the SQL
Server Surface Area Configuration tool.
4) If you are not using the default TCP port or if you installed a named
instance is the SQL Server Browser service running on each computer/virtual
machine to resolve the instance name to the port number? If not, start the
SQL Server Browser service.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"keith" <kbrickey@.dslextreme.com> wrote in message
news:%23QcBFI0SHHA.3428@.TK2MSFTNGP04.phx.gbl...
> Two instances of SQL Server 2005 cannot "see" each other. One is Developer
> edition and one is Standard edition. Standard editon is running under
> Virtual PC. I would appreciate any suggestions on how make these two
> instances visible to each other.
> Thanks,
> Keith
>|||Hi,
in addition you will have to make sure that you configured your firewall to
let the Virtual PC access your host PC. it sometimes saw this, that although
ICMP was working the host refused to establish connections to the VPC if
they used Shared networking and not NAT.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> wrote in message
news:F0034F02-B236-4402-9180-7817FA21CB9F@.microsoft.com...
> This should work pretty much like connecting any other two computers.
> 1) Can each computer ping the other successfully using TCP/IP? If not,
> it's a virtual server problem.
> 2) Is the firewall on each computer configured to allow access through TCP
> port 1433 (if you are using the default settings for a default instance)?
> If not, change the firewall settings.
> 3) Is each SQL Server accepting TCP/IP connections? Check using the SQL
> Server Surface Area Configuration tool.
> 4) If you are not using the default TCP port or if you installed a named
> instance is the SQL Server Browser service running on each
> computer/virtual machine to resolve the instance name to the port number?
> If not, start the SQL Server Browser service.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "keith" <kbrickey@.dslextreme.com> wrote in message
> news:%23QcBFI0SHHA.3428@.TK2MSFTNGP04.phx.gbl...
>

How to make a File Share Subscription running on Vista?

I recently migrate to Vista. I had a bunch of reports with file share subscriptions that were running fine on XP.

After installing reporting services on vista (that part only was a challenge), I re-created my subscriptions using the report manager. As expected, when a subscription is executed, the ‘last run’ column shows the last time the report output has been delivered to the file share and the ‘status’ column shows ‘New Subscription’. I thought that this was the signature of a successfully configured subscription.

But surprisingly, there is nothing in the file share. The directory is empty. Anybody has an idea why? Anybody knows how I could possibly find information on my system to find the cause of the problem?

Also, in report manager – after spending hours to figure that I had to run IE in admin mode to see the content of the web app - I cannot delete subscriptions. After selecting the subscription and pressing the delete button I get a JavaScript error and nothing happens. Any trick I could use to get rid of the unused subscriptions (other than using the Reporting Services API).

Thanks,

Dom.

Hi Dom,

There are a few things here.

1. You mention that the subscription executed successfully as the "Last Run" column is updated. In that event the "Status" column should also be updated to something like "The file was successfully saved...." or "Failure writing file....", etc and not remain as "New Subscritpion".

2. Can you check if the fileshare has adequate permissions for the owner of the sunscription?

3. I'm not aware of the JavaScript error and using SOAP API(DeleteSubscription) would be the best way to go about it.

Thanks,
Sharmila

|||

Hi Sharmila,

Thanks for your reply. You are absolutely right; the status column shouldn't say 'new subscription' but 'the file was successfully ...'. Good call!

I checked the file share permissions and unless I am missing something I think the permissions are ok. Everything is running on the same machine (reporting services, sql server 2005 enterprise edition trial and the shared folder).

Let’s say my machine name is ‘ka’ and the user name is ‘boom’.Boom is an admin on the machine (remember I am running Vista).The owner of the subscription is ‘ka\boom’ and the file share delivery extension is set with ‘ka\boom’ and his password.

On the same machine, I have a shared folder and the owner permissions are given to ‘ka\boom’.

I have another machine running XP on my local network with reporting services.I tried to configure a subscription on the XP machine with a file share delivery pointing to the shared folder on my machine running Vista.The result is the same, the status column keep saying ‘new subscription’ while the last run column gets updated correctly.

Finally, yes I did use the soap api to get rid of the undesired subscriptions.

Thanks again,

Dom.

|||

Can you include the logs specifically around the time the subscription fired?

Thanks,
Sharmila

|||

I re-configured the whole thing today in order to get good log files for you and this time I got a different error message in the ‘status’ column of the report manager. I got: “Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

I zipped all the log files I could think might help to solve the problem. You can get the package at: http://dchoquette.aaafly.com/logs/log_files.zip

Let me know if you need more logs to diagnose the problem.

I created the subscription at 11:03 am today (February 22^nd, 2007) and the subscription fired at 11:10 am.

I am going to start looking on my side for answers related to that new error message that I got.

Thanks for you help.

|||

I was puzzled before that the "Status" column was not updated even though the subscription executed successfully. It makes sense now that both the "Last Run" and "Status" columns are updated. It looks like a permission issue to me. I do not have enough info from the logs. You need to send the report server service log of this format(Eg: ReportServerService__02_13_2007_14_38_32.log).

Thanks,

Sharmila

|||

I just updated the zip file, you can get it at the same location. It now contains the log file you requested and I briefly saw permission problems around 11:10 am. Not sure what is the cause though. I didn’t know there was a ReportServerService AND a ReportServerServiceMain in the logs directory :)

Let me know if you find out why I have that permission issue.

|||

Talking to the developer here, it looks like it could be two things:

1. RS Windows service account does not have enough rights.

2. Are you seeing this only on your Vista machine? Have you tried on a different machine?

Thanks,
Sharmila

|||

The RS Windows service is running under the ‘Local System account’ and the option ‘Allow service to interact with desktop’ is unchecked.This must be the default configuration because I do not remember changing the log on settings.

Yes I only experience this problem on Vista.The exact same subscription configuration is working fine on another machine running XP.

Thanks for helping me, I really appreciate it.

|||

To narrow down and track the real issue, can you try using different accounts for the RS Windows service to see if that works?

Thanks,
Sharmila

|||

I couldn't repro this in house and so was wondering if you have the latest released SP2 installed(not CTP3)? See if that makes a difference.

Thanks,

Sharmila

|||

I tried to change the log on account of the reporting services window service. I configured it with an administrator account on my machine. When I tried to create a file share subscription to test I got the following error message:

A subscription delivery error has occurred. (rsDeliveryError) Get Online Help

	The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
	Bad Data. (Exception from HRESULT: 0x80090005)

Not sure what to do to get that other problem out of the way. I changed the log on account of reporting services back to ‘local system account’ and now I can create the file share subscription but it still gives me the same error:

“Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

Concerning which service pack I have installed, I checked the digital signature of the executable I installed on my system and the Singing time is ‘Saturday, December 09, 2006 4:12:36 AM’. Hopefully you can tell if it is the latest release of SP2 and not CTP3.

Thanks,

Dom.

|||

It looks like you have CTP3. Check out the post about the release of SP2.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247133&SiteID=1

Thanks,

Sharmila

|||

I installed the new release of SP2 and I still get the same error. That's it, I give up :) ... I wrote a custom delivery extension that is writing the report output in a directory and it is working fine for my needs.

I had a question concerning the delivery extensions if you don't mind. Do you know if it is possible to reuse the fonctionality of the built-in email delivery extension from the custom delivery extension I wrote? If so, do you have a link to a page explaining how to do so?

Thanks a lot for your help, you have been very helpful.

Dom

How to make a File Share Subscription running on Vista?

I recently migrate to Vista. I had a bunch of reports with file share subscriptions that were running fine on XP.

After installing reporting services on vista (that part only was a challenge), I re-created my subscriptions using the report manager. As expected, when a subscription is executed, the ‘last run’ column shows the last time the report output has been delivered to the file share and the ‘status’ column shows ‘New Subscription’. I thought that this was the signature of a successfully configured subscription.

But surprisingly, there is nothing in the file share. The directory is empty. Anybody has an idea why? Anybody knows how I could possibly find information on my system to find the cause of the problem?

Also, in report manager – after spending hours to figure that I had to run IE in admin mode to see the content of the web app - I cannot delete subscriptions. After selecting the subscription and pressing the delete button I get a JavaScript error and nothing happens. Any trick I could use to get rid of the unused subscriptions (other than using the Reporting Services API).

Thanks,

Dom.

Hi Dom,

There are a few things here.

1. You mention that the subscription executed successfully as the "Last Run" column is updated. In that event the "Status" column should also be updated to something like "The file was successfully saved...." or "Failure writing file....", etc and not remain as "New Subscritpion".

2. Can you check if the fileshare has adequate permissions for the owner of the sunscription?

3. I'm not aware of the JavaScript error and using SOAP API(DeleteSubscription) would be the best way to go about it.

Thanks,
Sharmila

|||

Hi Sharmila,

Thanks for your reply. You are absolutely right; the status column shouldn't say 'new subscription' but 'the file was successfully ...'. Good call!

I checked the file share permissions and unless I am missing something I think the permissions are ok. Everything is running on the same machine (reporting services, sql server 2005 enterprise edition trial and the shared folder).

Let’s say my machine name is ‘ka’ and the user name is ‘boom’. Boom is an admin on the machine (remember I am running Vista).The owner of the subscription is ‘ka\boom’ and the file share delivery extension is set with ‘ka\boom’ and his password.

On the same machine, I have a shared folder and the owner permissions are given to ‘ka\boom’.

I have another machine running XP on my local network with reporting services.I tried to configure a subscription on the XP machine with a file share delivery pointing to the shared folder on my machine running Vista.The result is the same, the status column keep saying ‘new subscription’ while the last run column gets updated correctly.

Finally, yes I did use the soap api to get rid of the undesired subscriptions.

Thanks again,

Dom.

|||

Can you include the logs specifically around the time the subscription fired?

Thanks,
Sharmila

|||

I re-configured the whole thing today in order to get good log files for you and this time I got a different error message in the ‘status’ column of the report manager. I got: “Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

I zipped all the log files I could think might help to solve the problem. You can get the package at: http://dchoquette.aaafly.com/logs/log_files.zip

Let me know if you need more logs to diagnose the problem.

I created the subscription at 11:03 am today (February 22^nd, 2007) and the subscription fired at 11:10 am.

I am going to start looking on my side for answers related to that new error message that I got.

Thanks for you help.

|||

I was puzzled before that the "Status" column was not updated even though the subscription executed successfully. It makes sense now that both the "Last Run" and "Status" columns are updated. It looks like a permission issue to me. I do not have enough info from the logs. You need to send the report server service log of this format(Eg: ReportServerService__02_13_2007_14_38_32.log).

Thanks,

Sharmila

|||

I just updated the zip file, you can get it at the same location. It now contains the log file you requested and I briefly saw permission problems around 11:10 am. Not sure what is the cause though. I didn’t know there was a ReportServerService AND a ReportServerServiceMain in the logs directory :)

Let me know if you find out why I have that permission issue.

|||

Talking to the developer here, it looks like it could be two things:

1. RS Windows service account does not have enough rights.

2. Are you seeing this only on your Vista machine? Have you tried on a different machine?

Thanks,
Sharmila

|||

The RS Windows service is running under the ‘Local System account’ and the option ‘Allow service to interact with desktop’ is unchecked. This must be the default configuration because I do not remember changing the log on settings.

Yes I only experience this problem on Vista.The exact same subscription configuration is working fine on another machine running XP.

Thanks for helping me, I really appreciate it.

|||

To narrow down and track the real issue, can you try using different accounts for the RS Windows service to see if that works?

Thanks,
Sharmila

|||

I couldn't repro this in house and so was wondering if you have the latest released SP2 installed(not CTP3)? See if that makes a difference.

Thanks,

Sharmila

|||

I tried to change the log on account of the reporting services window service. I configured it with an administrator account on my machine. When I tried to create a file share subscription to test I got the following error message:

A subscription delivery error has occurred. (rsDeliveryError) Get Online Help

	The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
	Bad Data. (Exception from HRESULT: 0x80090005)

Not sure what to do to get that other problem out of the way. I changed the log on account of reporting services back to ‘local system account’ and now I can create the file share subscription but it still gives me the same error:

“Failure writing file Test.pdf : An impersonation error occurred using the security context of the current user.”

Concerning which service pack I have installed, I checked the digital signature of the executable I installed on my system and the Singing time is ‘Saturday, December 09, 2006 4:12:36 AM’. Hopefully you can tell if it is the latest release of SP2 and not CTP3.

Thanks,

Dom.

|||

It looks like you have CTP3. Check out the post about the release of SP2.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1247133&SiteID=1

Thanks,

Sharmila

|||

I installed the new release of SP2 and I still get the same error. That's it, I give up :) ... I wrote a custom delivery extension that is writing the report output in a directory and it is working fine for my needs.

I had a question concerning the delivery extensions if you don't mind. Do you know if it is possible to reuse the fonctionality of the built-in email delivery extension from the custom delivery extension I wrote? If so, do you have a link to a page explaining how to do so?

Thanks a lot for your help, you have been very helpful.

Dom

sql

How to make a connection from 2000 to 2005?

We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tcInstall the SQL Server 2005 Client Tools on the SQL Server 2000 server. This
will give you the SQL Server Management Studio, which can be used to connect
to SQL Server 2005.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tc|||You should be able to create a linked server between the boxes. You could
probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
Server 2000 box.
As you have probably discovered, Enterprise Manager does not connect to SQL
Server 2005 boxes. You will need to use Query Analyzer or SQL Server
Management Studio (which is the client tools for SQL Server 2005). SSMS can
connect to a SQL Server 2000 install.
Keith Kratochvil
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
> We are using SQL Server Enterprise Manager to connect to all of our SQL
> 2000 servers - one server, one box. Now we have a new box running SQL
> Server 2005. Is there anyway we can make a connection from a SQL Server
> 2000 box to SQL Server 2005 box? We need a way to 'see' the new server
> from the 2000 box. SQL Server 2000 box is running .NET Framework 1.1 and
> SQL Server 2005 box is running .NET Framework 2.0.
> Thanks in advance.
> -tc
>|||I installed SQL Server Management Studio and it works fine. Thanks a lot.
-tc
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OQsFGHYtGHA.2020@.TK2MSFTNGP03.phx.gbl...
> You should be able to create a linked server between the boxes. You could
> probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
> Server 2000 box.
> As you have probably discovered, Enterprise Manager does not connect to
> SQL Server 2005 boxes. You will need to use Query Analyzer or SQL Server
> Management Studio (which is the client tools for SQL Server 2005). SSMS
> can connect to a SQL Server 2000 install.
> --
> Keith Kratochvil
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
>

How to make a connection from 2000 to 2005?

We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tcInstall the SQL Server 2005 Client Tools on the SQL Server 2000 server. This
will give you the SQL Server Management Studio, which can be used to connect
to SQL Server 2005.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tc|||You should be able to create a linked server between the boxes. You could
probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
Server 2000 box.
As you have probably discovered, Enterprise Manager does not connect to SQL
Server 2005 boxes. You will need to use Query Analyzer or SQL Server
Management Studio (which is the client tools for SQL Server 2005). SSMS can
connect to a SQL Server 2000 install.
--
Keith Kratochvil
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
> We are using SQL Server Enterprise Manager to connect to all of our SQL
> 2000 servers - one server, one box. Now we have a new box running SQL
> Server 2005. Is there anyway we can make a connection from a SQL Server
> 2000 box to SQL Server 2005 box? We need a way to 'see' the new server
> from the 2000 box. SQL Server 2000 box is running .NET Framework 1.1 and
> SQL Server 2005 box is running .NET Framework 2.0.
> Thanks in advance.
> -tc
>|||I installed SQL Server Management Studio and it works fine. Thanks a lot.
-tc
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OQsFGHYtGHA.2020@.TK2MSFTNGP03.phx.gbl...
> You should be able to create a linked server between the boxes. You could
> probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
> Server 2000 box.
> As you have probably discovered, Enterprise Manager does not connect to
> SQL Server 2005 boxes. You will need to use Query Analyzer or SQL Server
> Management Studio (which is the client tools for SQL Server 2005). SSMS
> can connect to a SQL Server 2000 install.
> --
> Keith Kratochvil
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
>> We are using SQL Server Enterprise Manager to connect to all of our SQL
>> 2000 servers - one server, one box. Now we have a new box running SQL
>> Server 2005. Is there anyway we can make a connection from a SQL Server
>> 2000 box to SQL Server 2005 box? We need a way to 'see' the new server
>> from the 2000 box. SQL Server 2000 box is running .NET Framework 1.1 and
>> SQL Server 2005 box is running .NET Framework 2.0.
>> Thanks in advance.
>> -tc
>sql

How to log uses of SELECT

Hello,
Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or entered) their data.
-So I need to be able to keep a log of which users have seen which patient files, but I don't control the app code (and wish to god there were such a thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure into the app?
Thanks,
JohnThere is no trigger on SELECT, and Profiler is probably not valuable enough
for the cost of it running constantly, but you should try it out. You might
investigate some of the auditing tools listed in http://www.aspfaq.com/2496
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:3F88A456-3DFF-4362-A1F8-E130E1A546E4@.microsoft.com...
> Hello,
> Here's my scenario:
> -I have a vendor application running sql2k.
> -I do not control the code and the vendor isn't currently interested in
implementing what I want.
> -I keep medical data in this app.
> -Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
> -So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
> Can I do this without necessarily being able to write a logging procedure
into the app?
> Thanks,
> John
>|||> If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
> select username
> from audit_selects
> where patient_id = XXX
> and get a list of people who've seen this.
Yikes, so if username does SELECT * FROM patients they're going to get a row
in your audit table for every row in your data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Here's my scenario:
-I have a vendor application running sql2k.
-I do not control the code and the vendor isn't currently interested in
implementing what I want.
-I keep medical data in this app.
-Patients, by law, are entitled to know who's seen (not just changed or
entered) their data.
-So I need to be able to keep a log of which users have seen which patient
files, but I don't control the app code (and wish to god there were such a
thing as a select trigger).
If Joe comes to me soon, and says "I need to know who's seen my data." I'd
like to do something like:
select username
from audit_selects
where patient_id = XXX
and get a list of people who've seen this.
Can I do this without necessarily being able to write a logging procedure
into the app?
Thanks,
John
--
It would be difficult because selects are not logged. You can turn on
profiler trace and capture all selects on specific tables. But reviewing
this output is not as straightforward as filtering by a certain patient_id.
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Take a look at Lumigent Entegra (www.lumigent.com), which
is an audit tool for SQL Server. I'm not affiliated with
them, but I'm currently evaluating the product. It does
audit queries.
Linchi
>--Original Message--
>Hello,
>Here's my scenario:
>-I have a vendor application running sql2k.
>-I do not control the code and the vendor isn't currently
interested in implementing what I want.
>-I keep medical data in this app.
>-Patients, by law, are entitled to know who's seen (not
just changed or entered) their data.
>-So I need to be able to keep a log of which users have
seen which patient files, but I don't control the app code
(and wish to god there were such a thing as a select
trigger).
>If Joe comes to me soon, and says "I need to know who's
seen my data." I'd like to do something like:
>select username
>from audit_selects
>where patient_id = XXX
>and get a list of people who've seen this.
>Can I do this without necessarily being able to write a
logging procedure into the app?
>Thanks,
>John
>.
>

How to load a big database?

Hello,
Does everyone have experience to load a big database? My SQL Server 2K is running on W2K. The database is 4GB, and the biggest table has 12 million records. Thanks a lot.
ytzBCP utility|||What does "to load a big database" mean?|||What does "to load a big database" mean?
Please explain exactly what do you want to do .|||"to load a big database" is to import a new big database from another SQL Server.

Thanks everyone|||Full back up the primary data base. And than restore the .bak file in the target server ( it may be take sometime but it always work).|||If you can spare the downtime, close the SQL Sever Services,
go to your Data and Log file and copy to your destination DB and attach it.

There are many ways to do this, but I prefer this way as its clean, and
does not be a burden to the existing DB for extra processing if using import.|||detach/attach (or like mentioned shutdown your sql server service and copy to the destination), copy database wizard, bcp, bulk load, dts ...

How to list all history/snapshots of a given report

We are running SSRS2005 and produce multiple reports.

Our users need history report data so we set up snapshots executions to run weekly.

How do we display the list of these historical snapshots to the users for selection?

Currently we code:

http://myserver/Reports/Pages/Report.aspx?ItemPath=/myreports/Available+Floor+Space&rs:Command=Render&SelectedTabId=SnapshotsTab

That is not preferred as the user is presented the Report Manager interface and can go use other functionality such as subscriptions etc... that we do not support.

Is there (a) a way to suppress the Report Manager controls? or (b)another way to list the snapshots executed given a report name( maybe by querying the report server database?) or (c) code http://myserver/Reportserver/?/myreports/Available+Floor+Space&rs:Command=Render&? to get the history listing?

Thanks

Hi,

The best way is to use Reporting Services Web Services.

You need to add a Web Reference to your Reporting Server and name it <ReferenceName>. Once it is setup you can call the web services using the <ReferenceName>.

In your class first you need to add the "using" statement....

using <NameSpace>.<ReferenceName>.

then in the code you can use...

ReportingService rs = new ReportingService();

rs.Credentials = <your credentials>;

ReportHistorySnapshot[] historySnapshotList = rs.ListReportHistory(<report name>);

Hope this will help you.

Thanks.

Hammad

How to limit processors used (for licensing purposes)?

Is it possible in SQL Server 2005 to limit the number of processors used? For cost reasons, we are consolidating servers and want to start running SQL Server 2005 on one of our dual-processor Win2K3 machines instead of the standalone machine it's currently running on. Because we have about 75 users, it's only cost effective to purchase a processor license (vs. a server license with CALs). But right now we only need and can only afford a single processor license, not two. So...

Is there any way in 2005 to limit the number of processors used so that we only need to purchase one processor license? I know in 2000 you could set this on the "Processor" tab of the "SQL Server Properties" dialog. In 2005, is this accomplished by unchecking the "Processor Affinity" and "I/O Affinity" checkboxes for processor #2 on the "Processors" page of the "Server Properties" dialog? If I uncheck these two options does that fully disable SQL Server 2005 from accessing the second processor in any way? From things I've read I can't tell if it restricts access to the second processor completely or if it just places some limitations on the ways it accesses the second processor. Also, the licensing information for SQL Server 2005 leads me to believe that if you are going down the "processor licensing" route that you have to buy a processor license for every processor that the OS itself has access to and not just what processors SQL Server has access to. I thought I understood that in SQL Server 2000 the licensing information did allow you to buy a processor license just for each processor that SQL Server 2000 had access to, but has that changed for 2005?

Hope someone can provide some clarification on limiting processor access and the licensing implications for SQL Server 2005.

Thanks,

Scott

A sample:

If you have a 2 processor machine (not cores, as only physical sockets are licenced) you will have to disable one processor at BIOS level (not in the server properties tab) to be legal-compliant. The processor must not be accessible to the OS itself. This is only for processor licences, having a server licence and appropiate CALs for your client you can have as many procs as you wish.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

If Jen's suggestion of completely disabling processors doesn't sit too well, the other option is to create a Virtual Server, using MS Virtural Server (available free here). Assign processor affinity to the VS, and install SQL Server in the VS. Then you will be in compliance with licensing requirements.

Just setting processor affinity in SQL Server properties does not satisfy the licensing requirements.

From: http://www.microsoft.com/sql/howtobuy/faq.mspx

Q.

Do I have to acquire licenses for all of the processors in a server?

A.

You have to acquire licenses only for processors that are accessible to any copy of the operating system upon which SQL Server 2005 is installed. In a virtualized environment, processor licenses are required for every processor that is accessed by a virtual machine. Please see the virtualization licensing brief and virtualization white paper for more information on how to license under a virtualized environment.

|||Thanks Jens and Arnie, this is the info I needed.

How to Launch an Oracle SP from SQL Trigger?

I need to know a few things about this..
1. What do I need running or available on SQL Server machine (presuming an Oracle client)
2. Has anyone done this and perhaps can give me some quidance?
3. Any concerns about going from outside a firewall to the Oracle instance?

Architecture is probably going look like this:

SQL -> ON INSERT Trigger -> business logic -> Call Oracle Procedure

Any help is appreciated, thanks!

JTGISDo NOT try to create this Frankenstein monster of database architecture.
The scope of triggers should be limited to their own table if possible, and to their own database at the most. Otherwise, you risk your database stability and data integrity on a chain of technologies stretching across multiple database engines and right through your network.
This is NOT a good idea, and trying to do this sort of thing is what has given triggers a bad name over the years.
I encourage you, instead, to use a trigger on your table to populate a staging table, and then running a frequent batch process (scheduler can run once per minute) to process the data in the staging table and make whatever external calls to the Oracle database are necessary.
The big advantage of this setup is that if you network connection fails or your Oracle database goes down, your SQL Server database is unaffected and continues running merrily along loading data into the staging table, which can then be processed once all systems are up and working again with no loss of data and no loss of service on your SQL Server database.|||Blindman, thanks. I appreciate what you said and it makes sense. Ok, so no trigger other than to populate and use a storage table with the salient records.

What mechanisms exist to push this data into an Oracle table? That is the really important part because I do not want Oracle to have to pull that data. I know Oracle has transparent gateways and access managers for manipulating other data sources. Where do I go to make the same sorts of things happen for SQL server? My biggest constraint is that the data must be no older than a few seconds for this application is analyzing utility data for electrical outages. Optimally I could populate the salient records to an oracle resident table and the oracle side can handle itself (I already have that side built but I am having to "punt" due to problems with an SNMP tool failure that indicates the need for a better, more straight forward architecture).

Any pointers would be appreciated.|||To send the data to Oracle, use either a DTS package or the OPENQUERY function.

How to know which report is running

Hi All,

Is there any way or tool to monitor Report Server so we could see which report is currently running?

And also can we alert/warn user about the processing time of the report?

Thanks and Regards,

Uzzi

You could setup a Report that queried a ReportExecutionLog table that would tell you what reports are running.

The table could be populated by inserting sql statements at the start and end of each report's stored procedure.

SQL Profiler and/or Performance monitor could also be used. There may be an entry in the rs log files, though I think that these are populated after report has completed execution.

For the warning message, other than putting it in the report description or writing a custom report portal there doesn't appear to be any way to do this.

http://ssw.com.au/ssw/Standards/BetterSoftwareSuggestions/ReportingServices.aspx

cheers,

Andrew

|||

Thanks Andrew for your reply. I guess I have to write my own Report Monitor tool to make it looks nicer :)

By the way, by looking at the log, can we kill or terminate a report which currently running?

Cheers,

Uzzi

|||

Depending on the stage of the report processing, it could be possible to kill the report by using the KILL command against the SPID that the report stored procedure is running under in SQL Server.

|||

Hi Andrew,

Thanks for your reply.

I want to be able to terminate the report while it's running (generating stage).

If we use stored procedure and suppose we can use KILL command, how do we get the SPID for that stored procedure? I think you might refer to one of the system tables. Is that right?

And if the report is not based on stored procedure, may be based on SSIS or Report Model or XML/external file. How do we terminate it?

Cheers,

UT

|||

This may work, though I'm not sure that it kills execution, only jobs.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_sampleapps_v1_9psy.asp

|||

Hi Andrew,

Thanks for your reply. Again you just gave me a very good idea.

I'll see what I can do with that.

BTW, can contact you outside this forum?

Cheers,

Uzzi

|||Sure... asears (at) rogers.com|||

You can check the ExecutionLog table to find history of reports which have been run. However, this table does not get populated until the reporting completed execution. The RunningJobs table shows reports which are currently executing. You will need to join this reportid from this table to the itemid in the catalog table to get the actual report name.

How to know which report is running

Hi All,

Is there any way or tool to monitor Report Server so we could see which report is currently running?

And also can we alert/warn user about the processing time of the report?

Thanks and Regards,

Uzzi

You could setup a Report that queried a ReportExecutionLog table that would tell you what reports are running.

The table could be populated by inserting sql statements at the start and end of each report's stored procedure.

SQL Profiler and/or Performance monitor could also be used. There may be an entry in the rs log files, though I think that these are populated after report has completed execution.

For the warning message, other than putting it in the report description or writing a custom report portal there doesn't appear to be any way to do this.

http://ssw.com.au/ssw/Standards/BetterSoftwareSuggestions/ReportingServices.aspx

cheers,

Andrew

|||

Thanks Andrew for your reply. I guess I have to write my own Report Monitor tool to make it looks nicer :)

By the way, by looking at the log, can we kill or terminate a report which currently running?

Cheers,

Uzzi

|||

Depending on the stage of the report processing, it could be possible to kill the report by using the KILL command against the SPID that the report stored procedure is running under in SQL Server.

|||

Hi Andrew,

Thanks for your reply.

I want to be able to terminate the report while it's running (generating stage).

If we use stored procedure and suppose we can use KILL command, how do we get the SPID for that stored procedure? I think you might refer to one of the system tables. Is that right?

And if the report is not based on stored procedure, may be based on SSIS or Report Model or XML/external file. How do we terminate it?

Cheers,

UT

|||

This may work, though I'm not sure that it kills execution, only jobs.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_sampleapps_v1_9psy.asp

|||

Hi Andrew,

Thanks for your reply. Again you just gave me a very good idea.

I'll see what I can do with that.

BTW, can contact you outside this forum?

Cheers,

Uzzi

|||Sure... asears (at) rogers.com|||

You can check the ExecutionLog table to find history of reports which have been run. However, this table does not get populated until the reporting completed execution. The RunningJobs table shows reports which are currently executing. You will need to join this reportid from this table to the itemid in the catalog table to get the actual report name.

How to know which node connected to

Hi ,
Is ther any way to know which node am connecting to or which node the
cluster is running on .
Can we find out the same from Query Analyzer .
Thanks
ARR
From
http://www.microsoft.com/technet/pro.../failclus.mspx
"From a SQL Server perspective, the node hosting the SQL Server resource
does a looks-alive check every 5 seconds. This is a lightweight check to see
whether the service is running and may succeed even if the instance of SQL
Server is not operational. The IsAlive check is more thorough and involves
running a SELECT @.@.SERVERNAME Transact SQL query against the server to
determine whether the server itself is available to respond to requests; it
does not guarantee that the user databases are up. If this query fails, the
IsAlive check retries five times and then attempts to reconnect to the
instance of SQL Server. If all five retries fail, the SQL Server resource
fails. Depending on the failover threshold configuration of the SQL Server
resource, Windows Clustering will attempt to either restart the resource on
the same node or fail over to another available node. The execution of the
query tolerates a few errors, such as licensing issues or having a paused
instance of SQL Server, but ultimately fails if its threshold is exceeded."
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23G0a6WP4EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> Is ther any way to know which node am connecting to or which node the
> cluster is running on .
> Can we find out the same from Query Analyzer .
> Thanks
> ARR
>
|||Thanks for the reply ,
But I want to know a query or some way to know which node is currently
hosting sql services .
select @.@.servername returns instance name not hostname or nodename .
Thanks
ARR
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:uP3$9SR4EHA.1524@.TK2MSFTNGP09.phx.gbl...
> From
>
http://www.microsoft.com/technet/pro.../failclus.mspx
> "From a SQL Server perspective, the node hosting the SQL Server resource
> does a looks-alive check every 5 seconds. This is a lightweight check to
see
> whether the service is running and may succeed even if the instance of SQL
> Server is not operational. The IsAlive check is more thorough and involves
> running a SELECT @.@.SERVERNAME Transact SQL query against the server to
> determine whether the server itself is available to respond to requests;
it
> does not guarantee that the user databases are up. If this query fails,
the
> IsAlive check retries five times and then attempts to reconnect to the
> instance of SQL Server. If all five retries fail, the SQL Server resource
> fails. Depending on the failover threshold configuration of the SQL Server
> resource, Windows Clustering will attempt to either restart the resource
on
> the same node or fail over to another available node. The execution of the
> query tolerates a few errors, such as licensing issues or having a paused
> instance of SQL Server, but ultimately fails if its threshold is
exceeded."
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23G0a6WP4EHA.2624@.TK2MSFTNGP11.phx.gbl...
>
|||Here's one way:
If you know the group name of the SQL Server resource,
EXEC xp_cmdshell 'cluster group'
If you know the resource name of the SQL Server resource,
EXEC xp_cmdshell 'cluser resource'
Note that group names and resource names can be arbitrary. In our
enviornments, we follow certain naming conventions, so it's easy to tell
which virtual server is in which group.
Also, note that you may consider using EXEC xp_cmdshell 'hostname'.
Unfortunately, on a clustered instance, this gives you the virtual server
name instead of the hostname of teh node. Similarly, none of the following
will give you the node name:
SERVERPROPERTY('MachineName')
SERVERPROPERTY('InstanceName')
SERVERPROPERTY('ServerName')
@.@.servername
Linchi
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23G0a6WP4EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> Is ther any way to know which node am connecting to or which node the
> cluster is running on .
> Can we find out the same from Query Analyzer .
> Thanks
> ARR
>

how to know standard or enterprise

Hi ,
Is there any way to know the sql edition running is standard or enterprise
Thanks
ARRHi
SELECT SERVERPROPERTY ('Edition')
Regards
Mike
"Aju" wrote:
> Hi ,
> Is there any way to know the sql edition running is standard or enterprise
>
> Thanks
> ARR
>
>

how to know standard or enterprise

Hi ,
Is there any way to know the sql edition running is standard or enterprise
Thanks
ARRHi
SELECT SERVERPROPERTY ('Edition')
Regards
Mike
"Aju" wrote:

> Hi ,
> Is there any way to know the sql edition running is standard or enterprise
>
> Thanks
> ARR
>
>

how to know standard or enterprise

Hi ,
Is there any way to know the sql edition running is standard or enterprise
Thanks
ARR
Hi
SELECT SERVERPROPERTY ('Edition')
Regards
Mike
"Aju" wrote:

> Hi ,
> Is there any way to know the sql edition running is standard or enterprise
>
> Thanks
> ARR
>
>

How to know if a job is running...

Howdy folks... just a quick question. Is there a system table that holds a variable that states if a job is currently running or not? Something like a status column for jobs. If so, where is it? and if not, any suggestions aside from setting a flag in a table when it runs and stops?
TIAha! Found my own answer... sp_help_job

How to know a server is running on a specified port?

Dear all,
How to know that whether a server is running on a specified port?
For example, server A is running on port 1888. For a client, how to know
this?
Thanks,
GordonHi ,
Easy way is to check the SQL Server Error Log.
Thanks
Hari
MCDBA
"Gordon" <sdgwedh@.hotmail.com> wrote in message
news:#fyAagwyDHA.2620@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Dear all,
> How to know that whether a server is running on a specified port?
> For example, server A is running on port 1888. For a client, how to know
> this?
> Thanks,
> Gordon
>

---

|||You can check :
1. SQL Errorlog
2. NT application log
3. Run netstat -an
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks. But my question is for a client, how to know this.
A client doesn't have permission to check the items you list.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:nX$xzv9yDHA.3532@.cpmsftngxa07.phx.gbl...

quote:

---

> You can check :
> 1. SQL Errorlog
> 2. NT application log
> 3. Run netstat -an
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||Hey Gordon,
Our MDAC 2.6 and greater clients will send a UDP packet to the server to
determine what port the server's listening on as long as it's a Named
Instance.
Otherwise, the NT admin or SQL Admin would have to tell the client what
port they're listening on. Typically, if you were to roll out an
application that was listening on an alternate port, you would either
instruct the clients how to connect or configure an alias for them. The
only other way a client could find this information out would be to port
scan the remote machine.
265808 INF: How to Connect to an SQL Server 2000 Named Instance with the
http://support.microsoft.com/?id=265808
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.