Empty Column: query

Showing posts with label query. Show all posts

Thursday, March 29, 2012

End user query analyzer access

My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
backend for peoplesoft. Our end users are requesting sql query analyzer
access in order to run select queries. Peoplesoft does have a built in query
tool. I am against giving them query analyzer access because of the following
- The end users are not technical and I am concerned about their ability to
write correct queries
- A long running select can take down the system
- Escalation of security privleges
I am curious on other thoughts or if anyone knows of what microsofts
recommendation is on this. Or how this is handled in other shops."mmalatek" <u40725@.uwe> wrote in message news:7e5a365f773b0@.uwe...
> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
> backend for peoplesoft. Our end users are requesting sql query analyzer
> access in order to run select queries. Peoplesoft does have a built in
> query
> tool. I am against giving them query analyzer access because of the
> following
> - The end users are not technical and I am concerned about their ability
> to
> write correct queries
> - A long running select can take down the system
> - Escalation of security privleges
> I am curious on other thoughts or if anyone knows of what microsofts
> recommendation is on this. Or how this is handled in other shops.
>
From a security perspective. If they can already run the queries through
the PeopleSoft tool, then you could set them up with QA utilizing the same
permission sets.
As for the long running queries, again I point at the PS tool. They can
write poor queries there as well.
At my company, we have taken the time to train our PS developers to take
advantage of things like NOLOCK and TOP xxx when they run their queries.
In general we don't have issues with them tying up the systems.
Last point: Why do they need to run queries against production? They
should be running them against a development and/or a test environment.
Rick Sawtell|||Rick Sawtell wrote:
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
>From a security perspective. If they can already run the queries through
>the PeopleSoft tool, then you could set them up with QA utilizing the same
>permission sets.
>As for the long running queries, again I point at the PS tool. They can
>write poor queries there as well.
>At my company, we have taken the time to train our PS developers to take
>advantage of things like NOLOCK and TOP xxx when they run their queries.
>In general we don't have issues with them tying up the systems.
>Last point: Why do they need to run queries against production? They
>should be running them against a development and/or a test environment.
>Rick Sawtell
This is not against production. And these are not developers these are
accountants and other users. Who have no sql training what-so-ever.|||> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
You mean you hand over you military fighter jet to somebody who never piloted an airplane? I
wouldn't do that. Analyze what information they need and use some reporting tool to write reports
for that. Or, at the least, analyze what information they need, create views for them and they can
use some "power-user" tool (like SQL Server's Report Builder) against those views.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mmalatek" <u40725@.uwe> wrote in message news:7e5b507a82324@.uwe...
> Rick Sawtell wrote:
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
>>From a security perspective. If they can already run the queries through
>>the PeopleSoft tool, then you could set them up with QA utilizing the same
>>permission sets.
>>As for the long running queries, again I point at the PS tool. They can
>>write poor queries there as well.
>>At my company, we have taken the time to train our PS developers to take
>>advantage of things like NOLOCK and TOP xxx when they run their queries.
>>In general we don't have issues with them tying up the systems.
>>Last point: Why do they need to run queries against production? They
>>should be running them against a development and/or a test environment.
>>Rick Sawtell
> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
>|||Some people prefer to hand out Access or Excel over Query Analyzer for that
set of skill levels.
For what it is worth, I prefer to give them access to views rather than
directly to the tables. If either wise or necessary you can the do a couple
of things:
- Prepare views that do a basic set of common table joins, easing their work
and reducing the number of poorly designed joins.
- Make views that do not lock the tables so as to increase concurrency.
(Depending on the server level and the method used, this may raise other
problems. For both server versions read about 'isolation levels' and the
FROM statement's WITH option.
FWIW,
RLF
"mmalatek" <u40725@.uwe> wrote in message news:7e5b507a82324@.uwe...
> Rick Sawtell wrote:
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for
>> the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
>>From a security perspective. If they can already run the queries through
>>the PeopleSoft tool, then you could set them up with QA utilizing the same
>>permission sets.
>>As for the long running queries, again I point at the PS tool. They can
>>write poor queries there as well.
>>At my company, we have taken the time to train our PS developers to take
>>advantage of things like NOLOCK and TOP xxx when they run their queries.
>>In general we don't have issues with them tying up the systems.
>>Last point: Why do they need to run queries against production? They
>>should be running them against a development and/or a test environment.
>>Rick Sawtell
> This is not against production. And these are not developers these are
> accountants and other users. Who have no sql training what-so-ever.
>|||Consider, if budget and time permits, creating one or more Analysis Services
cubes and letting them play with those instead.
Possibly only appropriate if your end users are dealing with aggregated data
much of the time, but this is usually the case.
"mmalatek" <u40725@.uwe> wrote in message news:7e5a365f773b0@.uwe...
> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
> backend for peoplesoft. Our end users are requesting sql query analyzer
> access in order to run select queries. Peoplesoft does have a built in
> query
> tool. I am against giving them query analyzer access because of the
> following
> - The end users are not technical and I am concerned about their ability
> to
> write correct queries
> - A long running select can take down the system
> - Escalation of security privleges
> I am curious on other thoughts or if anyone knows of what microsofts
> recommendation is on this. Or how this is handled in other shops.
>|||Will Alber wrote:
>Consider, if budget and time permits, creating one or more Analysis Services
>cubes and letting them play with those instead.
>Possibly only appropriate if your end users are dealing with aggregated data
>much of the time, but this is usually the case.
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
I appreciate everyones input. What i am trying to get at is I want to make
sure my reasoning is on track. I have already offered to create views but I
need supporting reasons other then end users scare me.|||If you can create very simple views, and your users can cope with SQL, go
for it. Just ensure your database is fully locked down so no smart Alec
ends up DELETEing a whole bunch of records 'just because they could'!
I'd recommend keeping an eye on the performance. If this were a production
server, I'd never let users have access!
"mmalatek" <u40725@.uwe> wrote in message news:7e5c625b80010@.uwe...
> Will Alber wrote:
>>Consider, if budget and time permits, creating one or more Analysis
>>Services
>>cubes and letting them play with those instead.
>>Possibly only appropriate if your end users are dealing with aggregated
>>data
>>much of the time, but this is usually the case.
>> My company is currently utilizing MSSQL 2000 sp 4 and SQL 2005 sp2 for
>> the
>> backend for peoplesoft. Our end users are requesting sql query analyzer
>>[quoted text clipped - 10 lines]
>> I am curious on other thoughts or if anyone knows of what microsofts
>> recommendation is on this. Or how this is handled in other shops.
> I appreciate everyones input. What i am trying to get at is I want to make
> sure my reasoning is on track. I have already offered to create views but
> I
> need supporting reasons other then end users scare me.
>|||If there are performance problems you can set up a query server by
replicating the data. That should be no problem.
One of my customers has a query database at the production server.
This query database contains only nolock-views onto the production
database. They use Access as a query tool.
Often I would appreciate if they would use Query Analyzer because in
Access it is much too easy to busy sql server AND network! They write
poor queries and they cannot rely on their results - but they wouldn't
invest in a relyable and fast querying tool.
There is not one solution. In Query Analyzer you experience problems
in handing out the data to Excel. In Excel and Access you experience
problems in writing good sql ... and no enduser will write pass
through queries in Access. The best way is a querying tool. If they
need to build queries on their own, they have to know that they are
developers - and they have to behave as. Perhaps could some skillfull
power-users build queries for others - that would help.
Bye and good luck, Manfredsql

End User Filter Option

I am using RS 2005. Here is my questionâ?¦ I have two different date fields
(FileDate and TransDate) in my query and I want to give end user an option to
filter either one i.e on filedate or on Transdate and also a date range in
where clause. Is this possible? If so. How can I implement this
functionality?manipulate the information on the datasource side.
your datasource must be a stored proc created in such a way that if
filedate is null then use transdate.
eg.
create proc mydataset @.filedate datetime = null, @.transdate datetime =null
as
if @.filedate is null
begin
select fields from table where transdate = @.transdate
end
else
begin
select fields from table where filedate = @.filedate
end
Reddy wrote:
> I am using RS 2005. Here is my question... I have two different date fields
> (FileDate and TransDate) in my query and I want to give end user an option to
> filter either one i.e on filedate or on Transdate and also a date range in
> where clause. Is this possible? If so. How can I implement this
> functionality?

Tuesday, March 27, 2012

encryption with certificate

I am trying to create a encrypted row in my database
Everything here worked except that when i run the final query to decrypt the data
It just comes up with null for each row. Even if i do a query to show me the rows that are not null
It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.
Here is what I tried from start to finish

Create Certificate TestCertEncryptionBy Password ='Password'With Subject ='SQLCert',Expiry_Date ='12/01/2050';declare @.Testnvarchar(50)set @.Test='123456789'insert into testenc (testencry)Values (encryptbyCert(Cert_ID('TestCert'),@.Test ))selectconvert (Nvarchar(50),DecryptByCert(Cert_ID('TestCert'),testencry,N'Password'))As Testfrom testenc

I am using sql 2005 by the way|||Nevermind Just realized I need to use VarBinary instead of NvarChar to store the data!

Monday, March 26, 2012

Encryption not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enable
d
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Encryption not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enabled
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?
Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Thursday, March 22, 2012

Encryption and query performance

I am trying to implement encryption but have run into some serious performance issues. I am required to encrypt the SSN in our database. In and of itself, this is not a problem. The problem comes in because there is also a need to be able to query the table based on the SSN. Since the SSN is encrypted, the query basically performs an index scan, decrypting each value as it goes along. As a result, the query for one record out of 10 million records in the table takes three minutes. It needs to occur immediately.

If I could encrypt my SSN parameter and then compare it to the encrypted value in the column, it would work fine. Unfortunately, everytime you encrypt a particular value, the resultant encrypted value is different. Hence, I have to decrypt the column to match my parameter, instead of encrypting th parameter to match the column.

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Thanks for the links. They supplied the information that I was afraid I'd find. In the example of using the MAC key, is there a benefit of having one column containing the encrypted SSN, if another column contains the MAC of the plaintext? That is, if the MAC of the plaintext is secure enough to be able to use for indexing, then are things more secure by separately encrypting the SSN with a symmetric key?

Two ideas that were proposed for our situation were:

1.) Encrypt the SSN and also store, let's say the last four of the SSN in plaintext. Our last four field would be included in the query condition. That would, in effect, reduce the amount of the index searched by a factor of 10000 (e.g search and decrypt only 1000 of the 10 million records)

2.) Create a separate table containing the plaintext SSN (indexed) and the encrypted value of the PK to the customer table. The SSN would be plaintext, but would not be linkable to any particular customer. Searches by SSN would use this table to retrieve the one row and then decrypt the encrypted value of the PK to link to the customer table. I'm not sure I like this idea, as the SSN are all visible, regardless of whether they can be linked to a particular person.

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

The HMAC cannot be used to retrieve the original data, hence the encrypted value needs to be stored as well. The HMAC is only used for the search, to leverage the index for retrieving the row. Once the row is retrieved, the data is extracted from the encrypted column value.

I would avoid both ideas, as both are disclosing information about your customers' SSNs. The solution we recommend is to index an HMAC of the SSN, instead of indexing portions of the unencrypted SSN. There is no significant overhead compared to the other two solutions, so why not implement this approach?

Thanks
Laurentiu

|||

Again, thanks for your reply. Your answer makes sense to me. I, too, was not real keen on leaving SSN (or parts of it) in plaintext, but I was running short on other ideas and short on time. I hope you'll forgive my ignorance when it comes to HMAC, actually when it comes to encryption in general. When I looked at Raul's sample code for implementing this solution, I'll have to admit I was rather intimidated by it. I wish I had more time to really digest the whole thing. I will re-read his blog and try to digest it completely. Are there any other places where I might be able to get a crash course on HMAC's?

Thanks,

Larry

|||One other question I have is regarding replication. This table is one that I am replicating to another database. If I create these same certificates and keys on both database, can I still search from database B on an indexed value that was created on database A?|||

Laurentiu,

The example you pointed me to in Raul's blog implements the encryption and the generation of the MAC in a trigger. All of our data access logic (INSERTs and UPDATEs) currently resides in stored procedures. What are benefits or drawbacks to implementing the encryption logic in triggers as opposed to the stored procedures that we already have?

|||

You can generate the HMAC in procedures as well. Raul's example shows how you can compute the HMACs without changing existing stored procedures - this is why he's doing the HMAC computations in a trigger. If changing the stored procedures is not an issue for you, then you can move the HMAC computation in their code.

Also, for HMAC generation, you can find out more by looking at a cryptography book or at online resources. The algorithm that Raul uses is to concatenate the original value (the sensitive data) with a secret value (@.key) and then to compute a SHA1 hash of the resulting concatenation - that is the HMAC of the original value:

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Encryption and query performance

Does anyone have any suggestions to help alleviate this problem?

Thanks,

See the following links - they discuss this issue in detail:

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

Thanks
Laurentiu

|||

Laurentiu,

Two ideas that were proposed for our situation were:

What are your thoughts on either of these ideas?

Thanks,

Larry

|||

Thanks
Laurentiu

|||

Thanks,

Larry

Laurentiu,

|||

SELECT @.RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @.Message) + @.Key )

Thanks

Laurentiu

Sunday, March 11, 2012

encrypt/decrypt data

We like to secure datas.
Only a few people are autorized to read this information, but today, these informations are readable with a simple query with a query analyzer for exemple.

I'd like to encrypt datas with reversible function in one field of a table

Is there a function able to do this kind of work in SQLServer V7 or 2000 ?I would think that you would be better off using native SQL security to restrict access to the data rather than attempting to encrypt it. I personally prefer to write stored procs that "wrap" my tables and then I grant exec privileges on the stored procs to the appropriate roles. This way, I don't have to grant select on the underlying tables to anyone.

Alternatively, you can grant select privileges to specific roles based on need. You may also grant the select privilege down to a particular column(s) of data.

Note that you could also get an SSL certificate and force encryption between the client and the host, but I'm not certain that this will help in your circumstance.

hmscott

We like to secure datas.
Only a few people are autorized to read this information, but today, these informations are readable with a simple query with a query analyzer for exemple.

I'd like to encrypt datas with reversible function in one field of a table

Is there a function able to do this kind of work in SQLServer V7 or 2000 ?

encrypt(string) Question!

SQL Server 2000:

################################################## ######
I run the following as a normal query from Analyzer:
################################################## ######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
I run the following query from inside a stored proc:
################################################## #######

SELECT encrypt(user_password) FROM emp WHERE user_id = 1

################################################## #######
Question??
################################################## #######

If the data inside the emp table does not change, how can these two
queries return different values?

Any help would be much appreciated!

thanks,
Russ> SELECT encrypt(user_password) FROM emp WHERE user_id = 1
> SELECT encrypt(user_password) FROM emp WHERE user_id = 1

> If the data inside the emp table does not change, how can these two
> queries return different values?

They return different values because the encrypt function 'salts' the data
to prevent someone from just encrypting a bunch of stuff to figure out the
other data in the table.

The Unix crypt function used to do this by putting two random characters on
the front of the data string and also on the front of the encryption string
using the 'salt' as part of the key.

Regards,
Jim|||In addition to James's reply, note that the Encrypt function is undocumented
so its behaviour can change between versions of the product. Don't rely on
it in production code. Generate a password hash client-side would be my
suggestion.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0eadncyJC6oF1hzcRVn-tg@.giganews.com...
> In addition to James's reply, note that the Encrypt function is
undocumented
> so its behaviour can change between versions of the product. Don't rely on
> it in production code. Generate a password hash client-side would be my
> suggestion.

And in the at least one case I looked at, trivial to decrypt.

> --
> David Portas
> SQL Server MVP
> --

Wednesday, March 7, 2012

enabling/disabling query optimizer

I would like to run some tests to measure the performance gain through the query optimizer. Is it possible to turn off the query optimizer? Is there any command line switch or SQL operator for that?This is not possible. You can however write the SELECT statement or query with hints that force join orders or specific type of joins. See SELECT statement topic for more details.

Enabling the UnRestricted File Growth option using Query

Hello,
Can any one help me by providing me the scripts to Enable the
UNRESTRICTED FILE GROWTH FOR A DATABASE.
As i dont have access for the enterprise manage to open and do it
Thanks in Advance
Please it is very urgent
Cheers,
SaranALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
GO
HTH. Ryan
<csesaravana@.gmail.com> wrote in message
news:1147705749.879232.101310@.j73g2000cwa.googlegroups.com...
> Hello,
> Can any one help me by providing me the scripts to Enable the
> UNRESTRICTED FILE GROWTH FOR A DATABASE.
> As i dont have access for the enterprise manage to open and do it
> Thanks in Advance
> Please it is very urgent
> Cheers,
> Saran
>|||Saran,
Use "alter database" statement.
Example:
use master
go
exec sp_helpdb northwind
go
alter database northwind
modify file (name=Northwind, MAXSIZE = 100MB)
go
exec sp_helpdb northwind
go
alter database northwind
modify file (name=Northwind, MAXSIZE = UNLIMITED)
go
exec sp_helpdb northwind
go
AMB|||Thanks for your reply
But when i try to execute the following query ,,
alter database D0015c0c_des
modify file (name=D0015c0c_des_data, MAXSIZE = UNLIMITED)
go
The following Error message is displayed
Server: Msg 226, Level 16, State 6, Line 1
ALTER DATABASE statement not allowed within multi-statement
transaction.
Please help me...
Thanks and Regards,
Saran|||Where do you execute this command? Query Analyzer? SQL Server Management Studio? Visual Studio?
Bottom line is that you seem to have opened a transaction when you execute this command, and the
error message clearly states that you cannot execute this command inside a transaction.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Saran" <csesaravana@.gmail.com> wrote in message
news:1147709905.925400.202260@.i40g2000cwc.googlegroups.com...
> Thanks for your reply
> But when i try to execute the following query ,,
> alter database D0015c0c_des
> modify file (name=D0015c0c_des_data, MAXSIZE = UNLIMITED)
> go
>
> The following Error message is displayed
> Server: Msg 226, Level 16, State 6, Line 1
> ALTER DATABASE statement not allowed within multi-statement
> transaction.
>
> Please help me...
> Thanks and Regards,
> Saran
>|||Thanks for ur reply!
I have solved the issues
Thank you once again for your help!!
Regards,
Saran

Enabling the UnRestricted File Growth option using Query

Hello,
Can any one help me by providing me the scripts to Enable the
UNRESTRICTED FILE GROWTH FOR A DATABASE.
As i dont have access for the enterprise manage to open and do it
Thanks in Advance
Please it is very urgent
Cheers,
SaranALTER DATABASE Test1
MODIFY FILE
(NAME = test1dat3,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
GO
HTH. Ryan
<csesaravana@.gmail.com> wrote in message
news:1147705749.879232.101310@.j73g2000cwa.googlegroups.com...
> Hello,
> Can any one help me by providing me the scripts to Enable the
> UNRESTRICTED FILE GROWTH FOR A DATABASE.
> As i dont have access for the enterprise manage to open and do it
> Thanks in Advance
> Please it is very urgent
> Cheers,
> Saran
>|||Saran,
Use "alter database" statement.
Example:
use master
go
exec sp_helpdb northwind
go
alter database northwind
modify file (name=Northwind, MAXSIZE = 100MB)
go
exec sp_helpdb northwind
go
alter database northwind
modify file (name=Northwind, MAXSIZE = UNLIMITED)
go
exec sp_helpdb northwind
go
AMB|||Thanks for your reply
But when i try to execute the following query ,,
alter database D0015c0c_des
modify file (name=D0015c0c_des_data, MAXSIZE = UNLIMITED)
go
The following Error message is displayed
Server: Msg 226, Level 16, State 6, Line 1
ALTER DATABASE statement not allowed within multi-statement
transaction.
Please help me...
Thanks and Regards,
Saran|||Where do you execute this command? Query Analyzer? SQL Server Management Stu
dio? Visual Studio?
Bottom line is that you seem to have opened a transaction when you execute t
his command, and the
error message clearly states that you cannot execute this command inside a t
ransaction.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Saran" <csesaravana@.gmail.com> wrote in message
news:1147709905.925400.202260@.i40g2000cwc.googlegroups.com...
> Thanks for your reply
> But when i try to execute the following query ,,
> alter database D0015c0c_des
> modify file (name=D0015c0c_des_data, MAXSIZE = UNLIMITED)
> go
>
> The following Error message is displayed
> Server: Msg 226, Level 16, State 6, Line 1
> ALTER DATABASE statement not allowed within multi-statement
> transaction.
>
> Please help me...
> Thanks and Regards,
> Saran
>|||Thanks for ur reply!
I have solved the issues
Thank you once again for your help!!
Regards,
Saran

Enabling the Service Broker

I'm trying to enable the Service Broker for Sql Server 2005 because I want to be able to use a SqlDependency object.

I ran the following query to see if my local sql server service broker was enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'dbname';

It came back with a value of 0 (which means it is not enabled).

I tried executing the following sql command to enable it:
ALTER DATABASE dbname SET ENABLE_BROKER;

The query has been running for over 5 mins and just keeps spinning (should it take this long to enable the Service brfoker), so I cancel it.

I even try to issue the command to see if the service broker is enabled after I cancel the query and it is not enabled.

How can I properly enable the Service Broker?

You need exclusive access to the database. See http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

HTH,
~ Remus

|||

Thanks for the tip. It resolved my issue of enabling the service broker.

Now that I have it enabled, I am testing an application, and have code set up which is supposed to test whether a table got updated or not and am not haveing any luck with the Haschanges method changing to TRUE.

I have the following sql set up in my stored procedure:

<code>

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set NUMERIC_ROUNDABORT OFF
set ARITHABORT ON
GO
ALTER PROCEDURE [dbo].[SelectAllQCCSwitch]
AS
SET NOCOUNT ON;
SELECT GateWay, Description, CDRTable, Priority
FROM QCCSwitch

</code>

Here is my vb.net code that I have inside a class:

<code>

Public blnQCCSwitch As Boolean = False

Dim depQCCSwitch As New SqlDependency()

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try

If blnQCCSwitch = False Then

depQCCSwitch.AddCommandDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)

SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)

blnQCCSwitch = True

End If

If (depQCCSwitch.HasChanges) OrElse (blnQCCSwitch = False) Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

Else

If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then

clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch

SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)

End If

Return DstFindBatch2

Catch ex As Exception

Throw ex

Finally

clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()

clsSelectAllQCCSwitch.Dispose()

End Try

End Function

.</code>

Notice the BOLDFACED text above which is what specifically concerns this post.

During debugging, I simply manually added a record to the table which is included in the "clsSelectAllQCCSwitch.cmdSelectQCCSwitch" Select command object.

I was expecting the HasChanges method of the Dependency object to change to TRUE and read the DB again to refresh the data.

What am I missing that I need to do?

|||

After you started the subscription, you are going to receive the notifications on the OnChangeEventHandler callback. You shouldn't explictily check for changes, you should simply continue and rely on the callback to notify you when a change occured.

HTH,
~ Remus

|||

Remus, thanks for the info...

I took that example logic and applied it to my app. I'm still expecting the Onchange event to occur (which only happens the first time the table is hit in my code) after I manually change a record in the table. However, the OnChange event is not firing when I do this. Is this because of the ConnectionString (meaning the table would have to be changed via the same connectionstring other than me doing it manually in order for it to fire)?

Here is my middle tier logic code. If you can verify that is seems okay, I would appreciate it very much. Or if I need to change something else.....

<code>

Public Function LoadQCCSwitch() As dstFindBatch

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
SqlDependency.Stop(clsSelectAllQCCSwitch.Connection.ConnectionString)
SqlDependency.Start(clsSelectAllQCCSwitch.Connection.ConnectionString)
Dim depQCCSwitch As New SqlDependency(clsSelectAllQCCSwitch.cmdSelectQCCSwitch)
AddHandler depQCCSwitch.OnChange, AddressOf QCCSwitchDependency_OnChange
If DstFindBatch2.SelectAllQCCSwitch.Rows.Count = 0 Then
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
End If
Return DstFindBatch2
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
End Try

End Function

Private Sub QCCSwitchDependency_OnChange(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

Dim clsSelectAllQCCSwitch As New SelectAllQCCSwitchTableAdapter

Try
clsSelectAllQCCSwitch.dapQCCSwitch.SelectCommand = clsSelectAllQCCSwitch.cmdSelectQCCSwitch
SQLServerDataAccess.GetSQLData(clsSelectAllQCCSwitch.Connection, clsSelectAllQCCSwitch.dapQCCSwitch, DstFindBatch2.SelectAllQCCSwitch)
Catch ex As Exception
Throw ex
Finally
clsSelectAllQCCSwitch.dapQCCSwitch.Dispose()
clsSelectAllQCCSwitch.Dispose()
Dim dependency As SqlDependency = CType(sender, SqlDependency)
RemoveHandler dependency.OnChange, AddressOf QCCSwitchDependency_OnChange
End Try

End Sub
</code>

|||

Have a look at the MSDN SqlDependency sample at http://msdn2.microsoft.com/en-us/a52dhwx7.aspx

The updates that trigger the notification can happen on any session and under any settings, it is not related whatsoever to the connection string of the SqlDependency.

Each time the OnChange is fired, the underlying Query Notification gets torn down and you have to subscribe again to get a new notification next time a change occurs. In the MSDN sample, the client calls again GetData at the end of it's dependency_OnChange method. Inside the GetData method, it sets up again a notification.

If you believe you've set up the notification correctly and applied an update yet the notification does not fire here are some steps to investigate:

- make sure the notification is created, see it in select * from sys.dm_qn_subscriptions
- make sutre the notification is fired. The Profiler will show this as an Broker:Broker Conversation event with the subclass SEND.
- make sure the notification message is delivered. Look in the database sys.transmission_queue, if the notification is still pending there it should have a transmission_status explaining why it cannot be delivered. My blog has a troubleshooting guide, at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx. While is generic for Service Broker, it applies just as well to Query Notification messages.

If the notification is successfuly delivered, then the OnChange method should fire.

HTH,
~ Remus

Enabling SQL audit Logging for SELECT queries

Hi all,
We have a requirement in our project where we need to audit log any query
(SELECT queries inclusive) that are fired on a specific set of objects (Tabl
e
and views) in our database. We need to capture information like Who fired th
e
query, When and the actual query itself.
The approach we have thought of is:
Run SQL Profiler and log the output of the trace into a SQL table
Create an INSERT trigger on the SQL table.
Trigger should write data into a custom Audit table with limited information
.
However the di

vantages we see here are performance issues due to profiler
being run continuously, maintenance overhead to clear the SQL table where th
e
trace is written etc.
Can anyone suggest any other better alternative for this requirement?
Thanks
GSNot sure if it ius an option for you, but SQL 2005 contains DML
triggers which allow you to audit Select statements.
Markus|||How about using any of the 3:rd party tools put there? Check the log reader
tools, they tend to have
this support (possibly in special versions): http://www.karaszi.com/SQLServer/link
s.asp
These tools does use the transaction log to audit modifications and Profiler
to audit SELECT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GS" <GS@.discussions.microsoft.com> wrote in message
news:6A359D6A-CCD1-4EC5-AEA2-C74C7F551971@.microsoft.com...
> Hi all,
> We have a requirement in our project where we need to audit log any query
> (SELECT queries inclusive) that are fired on a specific set of objects (Ta
ble
> and views) in our database. We need to capture information like Who fired
the
> query, When and the actual query itself.
> The approach we have thought of is:
> Run SQL Profiler and log the output of the trace into a SQL table
> Create an INSERT trigger on the SQL table.
> Trigger should write data into a custom Audit table with limited informati
on.
> However the di

vantages we see here are performance issues due to profil
er
> being run continuously, maintenance overhead to clear the SQL table where
the
> trace is written etc.
> Can anyone suggest any other better alternative for this requirement?
> Thanks
> GS
>

Enabling service broker

SQL2K5 SP1
I found this much in BOL...
alter database msdb
set ENABLE_BROKER
But when I run this on either of my 2 test servers the query never
completes...I let it run all weekend, it was still running this morning.
Am I missing something in my install? Has anyone ever seen this?
I am supposed to be getting our backup plans into place today and still
cannot get the operator alert tasks working because of this. Any help is
graciously appreciated!!See if this helps:
http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
Chances are you are logged into the database when you are trying to run the
command so it will run until everyone logs out.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
> SQL2K5 SP1
> I found this much in BOL...
> alter database msdb
> set ENABLE_BROKER
>
> But when I run this on either of my 2 test servers the query never
> completes...I let it run all weekend, it was still running this morning.
> Am I missing something in my install? Has anyone ever seen this?
>
> I am supposed to be getting our backup plans into place today and still
> cannot get the operator alert tasks working because of this. Any help is
> graciously appreciated!!
>|||Hmm...tried that and got this error:
Msg 9776, Level 16, State 1, Line 1
Cannot enable the Service Broker in database "msdb" because the Service
Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does not
match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
How can I force this GUID to match?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
> See if this helps:
> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
> Chances are you are logged into the database when you are trying to run
> the command so it will run until everyone logs out.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this morning.
>> Am I missing something in my install? Has anyone ever seen this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help is
>> graciously appreciated!!
>>
>|||Used the SET NEW_BROKER
Although, I DID follow procedure on this db. msdb was backed up from one
server and restored on this one. This is proper as I was told.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
> Hmm...tried that and got this error:
> Msg 9776, Level 16, State 1, Line 1
> Cannot enable the Service Broker in database "msdb" because the Service
> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
> not match the one in sys.databases (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
> Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
> How can I force this GUID to match?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>|||Moving system datbases ([master], [model], [temp] and [msdb]) between
instances of the is never proper, no matter the steps involved.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
> Used the SET NEW_BROKER
> Although, I DID follow procedure on this db. msdb was backed up from one
> server and restored on this one. This is proper as I was told.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and still
>> cannot get the operator alert tasks working because of this. Any help
>> is graciously appreciated!!
>>
>>
>>
>|||But this is supposedly the only way to move maintenance plans from one
server to the other WHILE RETAINING the ability to modify them graphically.
If you import/export through integration services it never allows you to
use the designer on the resulting package.
Is there some other way around this restriction? I need to design these
backup plans on my development machine and then deploy them. But they
should still be maintainable from the server no?
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
> Moving system datbases ([master], [model], [temp] and [msdb]) between
> instances of the is never proper, no matter the steps involved.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from one
>> server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to run
>> the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>|||I know KB224071 gives steps on how to move system databases, but those steps
ignore new functionality, like server level event notifications, dbMail and
Service Broker. Not to mention any usage of the secret storage facilities
(keys, certificates, encrypted data)...
What you did (NEW_BROKER) will solve the issue of starting up the broker in
msdb after a move, but at the cost of loosing any active dialog in in the
database, thus loosing any pending mail sent through dbMail and any pending
servel level notifications. I understand that is highly questionable if you
would have such active items and still move the database...
You have to be aware that when moving the msdb you are moving and
ovewrwritting way more than just your maintenance plan graphical designer
state. By overwriting the other's server msdb with yours, you are
overwriting the state of any feature that relies on msdb, and there are
plenty (I just mentioned server level event notifications and dbMail).
You could use the SQL Feedback at
https://connect.microsoft.com/SQLServer/Feedback to mention the issue that
the graphical designer uses a system database to store it's state, thus
tying it to a specific instance.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OABOpZ%23FHHA.3976@.TK2MSFTNGP05.phx.gbl...
> But this is supposedly the only way to move maintenance plans from one
> server to the other WHILE RETAINING the ability to modify them
> graphically. If you import/export through integration services it never
> allows you to use the designer on the resulting package.
> Is there some other way around this restriction? I need to design these
> backup plans on my development machine and then deploy them. But they
> should still be maintainable from the server no?
>
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:O$p$cU%23FHHA.5104@.TK2MSFTNGP03.phx.gbl...
>> Moving system datbases ([master], [model], [temp] and [msdb]) between
>> instances of the is never proper, no matter the steps involved.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OFQHXD9FHHA.1232@.TK2MSFTNGP05.phx.gbl...
>> Used the SET NEW_BROKER
>> Although, I DID follow procedure on this db. msdb was backed up from
>> one server and restored on this one. This is proper as I was told.
>>
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:%23v8kw18FHHA.2464@.TK2MSFTNGP06.phx.gbl...
>> Hmm...tried that and got this error:
>> Msg 9776, Level 16, State 1, Line 1
>> Cannot enable the Service Broker in database "msdb" because the Service
>> Broker GUID in the database (3861FC2B-EFB6-4213-A5DC-864A3F21A018) does
>> not match the one in sys.databases
>> (A1AEBD82-B5CE-4B27-88CC-E465AA90F9E1).
>> Msg 5069, Level 16, State 1, Line 1
>> ALTER DATABASE statement failed.
>> How can I force this GUID to match?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23tN%23mv8FHHA.1064@.TK2MSFTNGP04.phx.gbl...
>> See if this helps:
>> http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx
>> Chances are you are logged into the database when you are trying to
>> run the command so it will run until everyone logs out.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:OBwDsr8FHHA.1784@.TK2MSFTNGP06.phx.gbl...
>> SQL2K5 SP1
>> I found this much in BOL...
>> alter database msdb
>> set ENABLE_BROKER
>>
>> But when I run this on either of my 2 test servers the query never
>> completes...I let it run all weekend, it was still running this
>> morning. Am I missing something in my install? Has anyone ever seen
>> this?
>>
>> I am supposed to be getting our backup plans into place today and
>> still cannot get the operator alert tasks working because of this.
>> Any help is graciously appreciated!!
>>
>>
>>
>>
>>
>

Enabling service broker

Sunday, February 26, 2012

Enable/Disable primary keys,FK,indexes

hi,
I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a table
through query
I have tried
ALTER TABLE employees DISABLE PRIMARY KEY
but it shows the following error
Incorrect syntax near the keyword 'PRIMARY'.
can u help me?
Thanks,
SouraAnswered in first post of the exact same question...
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:7DFCB99D-2BD3-4964-A279-83E0A2683159@.microsoft.com...
> hi,
> I need to ENABLE & Disable the primarykeys,foreignkeys & indexes of a
> table
> through query
> I have tried
> ALTER TABLE employees DISABLE PRIMARY KEY
> but it shows the following error
> Incorrect syntax near the keyword 'PRIMARY'.
> can u help me?
> Thanks,
> Soura
>

Sunday, February 19, 2012

Enable query logging

Hi all,
Is there a way to make SQL Server 2000 log every SQL query sent to it?
-Oleg.Yes, but you will need to turn on server side tracing. Doing server side
tracing will write what every you tell it to a trace file. Look in BOL at
all the sp_trace* stored procedures.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:1094nlms1ge0n74@.corp.supernews.com...
> Hi all,
> Is there a way to make SQL Server 2000 log every SQL query sent to it?
> -Oleg.
>

Thursday, March 29, 2012

End user query analyzer access

End user query analyzer access

End User Filter Option

Tuesday, March 27, 2012

encryption with certificate

Monday, March 26, 2012

Encryption not enabled on Server

Encryption not enabled on Server

Thursday, March 22, 2012

Encryption and query performance

Encryption and query performance

Sunday, March 11, 2012

encrypt/decrypt data

encrypt(string) Question!

Wednesday, March 7, 2012

enabling/disabling query optimizer

Enabling the UnRestricted File Growth option using Query

Enabling the UnRestricted File Growth option using Query

Enabling the Service Broker

Enabling SQL audit Logging for SELECT queries

Enabling service broker

Enabling service broker

Enabling service broker

Sunday, February 26, 2012

Enable/Disable primary keys,FK,indexes

Sunday, February 19, 2012

Enable query logging

Empty Column

Blog Archive

About Me