How to ensure column uniqueness

Hi,
Coming from an Oracle background, I'm used to being able
to create a unique key on a column that allows many null
values, ie if a value exists it must be unique, otherwise
it can be null.
It appears as though SQL Server allows only 1 null value
in the same situation, ie create a unique constraint on a
column and once you try to insert a 2nd row with a null
value I get a constraint violation. [Thx to those that
replied to my last question on this]
I don't really want to write triggers testing for such
conditions. Is there some form of constraint that can do
this for me?
TIA,
SJTYour observations are correct. You can create a view conatining all rows but the NULL. Then create a
unique index (or possible a unique constraint) on that index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"SJT" <scott.taylor@.pwcs.com.au> wrote in message news:054601c36ad9$46dd1f90$a501280a@.phx.gbl...
> Hi,
> Coming from an Oracle background, I'm used to being able
> to create a unique key on a column that allows many null
> values, ie if a value exists it must be unique, otherwise
> it can be null.
> It appears as though SQL Server allows only 1 null value
> in the same situation, ie create a unique constraint on a
> column and once you try to insert a 2nd row with a null
> value I get a constraint violation. [Thx to those that
> replied to my last question on this]
> I don't really want to write triggers testing for such
> conditions. Is there some form of constraint that can do
> this for me?
>
> TIA,
> SJT|||You can use an indexed view to enforce uniqueness only for non-NULL values:
CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
GO
CREATE VIEW Sometable_Unique_Non_NULL
WITH SCHEMABINDING
AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)
--
David Portas
--
Please reply only to the newsgroup
--

How to enforce integrity across databases

I would like to enforce integrity across two databases on the same server. A
s
far as I know there is no way to create a FK constraint pointing to another
database. So my options are either a trigger (which I would like avoid) or a
check constraint.
Does anyone have any expericence/recommendations they can share on how to
implement this?Using triggers.
AMB
"DBA72" wrote:

> I would like to enforce integrity across two databases on the same server.
As
> far as I know there is no way to create a FK constraint pointing to anothe
r
> database. So my options are either a trigger (which I would like avoid) or
a
> check constraint.
> Does anyone have any expericence/recommendations they can share on how to
> implement this?|||"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:27E1F221-1355-447A-B052-542680E9DD5F@.microsoft.com...
> I would like to enforce integrity across two databases on the same server.
As
> far as I know there is no way to create a FK constraint pointing to
another
> database. So my options are either a trigger (which I would like avoid) or
a
> check constraint.
> Does anyone have any expericence/recommendations they can share on how to
> implement this?
IIRC, CHECK constraints cannot reference outside their table, so you are
left with triggers, or data validation at the stored proc level.
I would suggest triggers in this case.
Rick Sawtell

How to encrypt all existing stored procedure?

I know that we can CREATE PROCEDURE procedure_name WITH ENCRYPTION.

But how about if I want encrypt existing stored procedures?

Which command should I use ?

how about scripting them all off, and using the following:

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

add your encryption, and you're all set......

|||There is no easy way. You have to script out the SQL modules (Create or Alter) and then add the WITH ENCRYPTION option to the definitions. There is no DDL to just encrypt SQL modules without specifying the definition. Why do you want to encrypt your code? The encryption mechanism is really obfuscation and it is a reversible encryption. So it is possible to get the original code from the encrypted content. If you want to protect your code then you should look at legal binding terms and copyright mechanisms.|||As of now, I mark this thread as no answer.

How to enable the option of " Create New SQL Server database " from Database Explorer

Hi there

I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.

Can any one tell me how to enable that option please ?

You are probably in the wrong forum for this question, but do you have SQL Server Express installed?

http://msdn.microsoft.com/vstudio/express/sql/

|||Why not download SQL Server Managment Studio Express? If you click the Add Connection option, you can create a database in the dialog that comes up. Just type a filename in the textbox that doesn't exist and you'll be prompted to create it.

How to enable the option of " Create New SQL Server database " from Database Explo

Hi there

I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.

Can any one tell me how to enable that option please ?

You are probably in the wrong forum for this question, but do you have SQL Server Express installed?

http://msdn.microsoft.com/vstudio/express/sql/

|||Why not download SQL Server Managment Studio Express? If you click the Add Connection option, you can create a database in the dialog that comes up. Just type a filename in the textbox that doesn't exist and you'll be prompted to create it.sql

How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

sql

How to Enable CLR Integration in SQL Server Express and Create SQLCLR Project in VB 2005 Express

Hi all,

In my SQL Server Express (that is installed in my Windows XP Pro PC), SQL Server 2005 Network Configuration has Protocols for SQLEXPRESS. I tried to do "Enabling CLR Integration" in my SQL Server Express: (1) If I clicked on "Surface Area Configuration for Services and Connections", I got an error "An exception occurred in SMO while trying to manage a service, (Microsoft.SqlServer.Smo) Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). (2) If I clicked on "Surface Area Configuration for Features, I got a different error "Computer localhost does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required computer has the required Windows Management Instrumentation components and then try again. (SQLSAC) Additional Information: An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo). Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) The operation could not be completed. (WinMgmt). Please help and tell me how I should do to get "Enabling CLR Integration" in my SQL Server Express done and how I can create SQLCLR Project in VB 2005 Express.

Thanks in advance,

Scott Chang

Normally you would enable the CLR using the Surface Area Configuration Tool, but it appears that something is wrong with SMO on your computer. I don't know much about how to fix SMO and SAC, so you should ask some questions about how to get these tools working over in the General Tools forum. Having SMO broken will impact other tools that you might want to use, so this is relatively important.

In the mean time, you can configure CLR manually using TSQL by running the following query in management studio or using SQLCmd:

Code Snippet

sp_configure 'clr enabled', 1

RECONFIGURE

Check out the topic on server configuration in BOL for more informaiton.

Mike

|||

I think the SMO problem is related to a WMI problem on your machine, it would be interesting if you could post the error messages to see if the message correlate with the assumptions that this is based on the WMI part of SMO.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Mike, Thanks for your responses.

I guess my SQL Express 9.0.1399 is too old to get the CLR Integration. I am thinking to delete this 2-year old SQL Express in my PC and download and install the Microsoft Advanced SQL Erxpress (with some SP) for "Enabling CLR Integration". Is it a right thing for me to do? Please respond.

Thanks,

Scott Chang

|||

Hi Scott,

I'm curious as to how you've come to this conclusion? Did you try running the command I suggested to enable the CLR?

All versions of SQL Express 2005, including the one you list, support integrated CLR. The T-SQL command I suggested should enable the CLR. I believe it is a best practice to always run the latest service pack, which is SP2 right now, but the SP-level has nothing to do with CLR support.

If Jen's suggestion about a WMI configuration problem causing your issues with SAC, upgrading or re-installing will not fix that problem either since WMI is a Windows component. If it's not configured correctly on your computer, SQL can't do anything about it, you have to fix WMI. I would suggest posting the error you are getting as Jen's suggested to see if ayone has heard of your specific message.

Mike

|||

Hi Mike and Jens, Thank you both for your responses.

I have tried to understand what you said for 10 days. I thought I already presented the error messages in my first posting. Sorry, I am new in WMI, SMO and CLRSAC and I am completely lost now. I want to tell you that my SQL Server Express is in my personal PC that is a stand-alone Windows XP Pro PC without any Network/LAN system. I think I did not know how to configure my SQL Server Express correctly when I installed it 2 years ago. Please tell me how I can get the specific error messages Jens asked for.

Thanks again,

Scott Chang

|||

Hi Scott,

First, I want to make sure that you understand tha WMI is not part of SQL Server, it is a Windows component that many different systems rely on and use. As I said, it seems that you are getting SMO errors when you try to run the Surface Area Configuration Tool; you should probably go and post this question in the General Tools forum as that is the group that deals with SAC.

If the problem is really related to WMI, it could be that your WMI Repository is corrupt, and you could try rebuilding it. When you start messing with the sub-systems of Windows, it's always possible that you could make things worse if you do stuff wrong. If you're concerned about this, you should look for help from the right sources, General Tools for SAC and the Windows Group for help with WMI.

If you're bold, you could give the repository rebuild a try, one set of instructions is posted at http://www.nucleustechnologies.com/Repair-WMI-Services.php.

Mike

How to enable Ah Hoc Query in SQL 2000

Hi guys

I have a program which imports data into sql server from csv files. This program uses the something like this

CREATE PROCEDURE MyImport

(

@.FileName AS VARCHAR(200)

)

AS

SET NOCOUNT ON

DECLARE @.SQL VARCHAR(4000)

SET @.SQL = ' SELECT *

FROM OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\;'', ''SELECT * FROM ' + QUOTENAME(@.FileName) + '' + ''') as t

'

EXEC (@.SQL)

when i excute this i get a permission error saying the account i specified in the web config doesn't have rights to run these type of scripts. I tried using the sa account which works. But its not recommended to setup applications to access the database using the sa account.

i did some research and i found out that yon can actually enable that by change a registry key. But i cant find the MSDASQL datasource under ms sql server providers in the registry.

Any other ways i can enable this?

Have a look at http://www.asp101.com/articles/john/connstring/default.asp|||

Excellent article. Will give it a go at it on monday.

Thanks

|||

Hi TATWORTH

The link you provided was good but not what im looking for. If you read my post carefully you will know. I have a stored procedure which will select from a file passed in as a parameter using theMSDASQL provider. NOW THIS IS ALL DONE IN SQL not in my app.

I was saying that the account i specified in the web.config does not have permissions to use the MSDASQL provider inside of SQL. The account i used only have rights to run stored procedures and that is it. But my script is written inside of a stored procedure. The reason for not being able to run the script inside of the procedure is because this procedure produces a dynamic script and executes that inside of the stored procedure.

i heard you can enable a registry key which will enable the use of that provider, but i cant find theMSDASQLprovider in the registry.

i was looking for a way to grant permissions to (web_user account) execute these type of queries.

Hope this cleared things up

Cheers

how to eliminate "items to synchronize" dialog?

Hi there:
When I log in and log out, I get a dialog prompting me to create a
subscription to synch Sql Server data. I have tried to google the
issue to find out how to eliminate the dialog, without finding
anything useful. Can someone point me to a resource?
Thanks;
Duncan
Have a look at windows synchronization manager. Go to Start, All Programs,
and click on Synchronize. Delete the unwanted items there.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Duncan A. McRae" <google.com@.mcrae.ca> wrote in message
news:d0b466c7-b0dd-4826-82ca-fe8cb924e746@.i12g2000prf.googlegroups.com...
> Hi there:
> When I log in and log out, I get a dialog prompting me to create a
> subscription to synch Sql Server data. I have tried to google the
> issue to find out how to eliminate the dialog, without finding
> anything useful. Can someone point me to a resource?
> Thanks;
> Duncan

How to effectively create dynamic queries?

Let's say I have a search screen in my application that allows users to
do various AND OR conditions to about 14 pieces of criteria. That is a
complicated query to build dynamically and will be super slow because
it is dynamic.
Is there an efficient way to do this type of quering?
Thanks,
BrettHi
You can search sp_executesql in BOL.
That sp may reuse Execute plan than EXEC.
Also, You can access .xp_execresultset in Master database stored procedures.
"brett"?? ??? ??:

> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>|||On 24 Apr 2006 16:02:36 -0700, "brett" <account@.cygen.com> wrote:
in <1145919756.043300.318930@.u72g2000cwu.googlegroups.com>

>Let's say I have a search screen in my application that allows users to
>do various AND OR conditions to about 14 pieces of criteria. That is a
>complicated query to build dynamically and will be super slow because
>it is dynamic.
>Is there an efficient way to do this type of quering?
>Thanks,
>Brett
Of course not. But you can always use a sophisticated CASE function
statement.
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL. Let's say you want to search for job
candidates based on their skills.
skill = Java AND (skill = Perl OR skill = PHP)
becomes the disjunctive canonical form:
(Java AND Perl) OR (Java AND PHP)
which we load into this table:
CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));
INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');
Assume we have a table of job candidates:
CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));
INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');
The query is simple now:
SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);
You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relational division.|||Celko wrote:

> I think what you want is the ability to load tables with criteria and
> not have to use dynamic SQL. Let's say you want to search for job
> candidates based on their skills.
Just out of interest, what's if you had multiple types of criteria to
search on. For example, let's say you wanted:
skill = Java AND (skill = Perl OR skill = PHP) AND
birth_date between '1 Jan 1960' and '31 Dec 1970' AND
exists at least 2 entries in CandidateJobHistory where (role =
'Programmer' or role = 'BusinessAnalyst')
Would you have multiple Query tables, or make the Query table handle
multiple types of crtieria?
Kris|||http://www.sommarskog.se/dyn-search.html
"brett" <account@.cygen.com> wrote in message
news:1145919756.043300.318930@.u72g2000cwu.googlegroups.com...
> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>|||I think that one has got to take the biscuit for an over complicated kludge
at keeping the solution within standard sql.
Not only will this not scale you have still to address optional parameters
at which point you will realise 14 tables all with left outer joins just
will not scale.
Dynamic SQL or a ridiculous number of IF ELSE statements are required,
unfortunetly you'll never use IF ELSE because its procedural so that puts
you into the COALESCE camp which will give a very general and very poor
query plan that will not scale.
Your answer only serves to demonstrate your lack of real programming
exposure, get out of the class room, these solutions will not work in the
real world.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145932636.689321.112960@.j33g2000cwa.googlegroups.com...
>I think what you want is the ability to load tables with criteria and
> not have to use dynamic SQL. Let's say you want to search for job
> candidates based on their skills.
> skill = Java AND (skill = Perl OR skill = PHP)
> becomes the disjunctive canonical form:
> (Java AND Perl) OR (Java AND PHP)
> which we load into this table:
> CREATE TABLE Query
> (and_grp INTEGER NOT NULL,
> skill CHAR(4) NOT NULL,
> PRIMARY KEY (and_grp, skill));
> INSERT INTO Query VALUES (1, 'Java');
> INSERT INTO Query VALUES (1, 'Perl');
> INSERT INTO Query VALUES (2, 'Java');
> INSERT INTO Query VALUES (2, 'PHP');
> Assume we have a table of job candidates:
> CREATE TABLE Candidates
> (candidate_name CHAR(15) NOT NULL,
> skill CHAR(4) NOT NULL,
> PRIMARY KEY (candidate_name, skill));
> INSERT INTO Candidates VALUES ('John', 'Java'); --winner
> INSERT INTO Candidates VALUES ('John', 'Perl');
> INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
> INSERT INTO Candidates VALUES ('Mary', 'PHP');
> INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
> INSERT INTO Candidates VALUES ('Larry', 'PHP');
> INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
> INSERT INTO Candidates VALUES ('Moe', 'PHP');
> INSERT INTO Candidates VALUES ('Moe', 'Java');
> INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
> INSERT INTO Candidates VALUES ('Celko', 'Algol');
> INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
> INSERT INTO Candidates VALUES ('Smith', 'Algol');
> The query is simple now:
> SELECT DISTINCT C1.candidate_name
> FROM Candidates AS C1, Query AS Q1
> WHERE C1.skill = Q1.skill
> GROUP BY Q1.and_grp, C1.candidate_name
> HAVING COUNT(C1.skill)
> = (SELECT COUNT(*)
> FROM Query AS Q2
> WHERE Q1.and_grp = Q2.and_grp);
> You can retain the COUNT() information to rank candidates. For example
> Moe meets both qualifications, while other candidates meet only one of
> the two. You can Google "canonical disjunctive form" for more details.
> This is a form of relational division.
>|||Tony Rogerson wrote:

> Your answer only serves to demonstrate your lack of real programming
> exposure, get out of the class room, these solutions will not work in the
> real world.
Actually we've used a similar approach for one of our products. It was
for a set of marketing plans for which the user selected a set of
customer demographic criteria (birth date, ethnicity, location, etc
etc). The reason we didn't just generate dynamic SQL was because these
marketing plans had to be saved and reusable, therefore we had to store
the actual criteria in a set of tables, similar to the Query table Joe
used except we stored all criteria types in that table.
Then when actually generating the customer list, we used a query
similar to Joe's and it works reasonably well. The main divantage is
that the optimizer can't use the best indexes, but since the act of
generating a customer list isn't time criticial it didn't matter.
I doubt this solution is suitable for the OP's problem (dynamic SQL is
probably better IMO), but in some cases it is useful and not just a
theoretical solution.
Kris|||Hi Kris,
I think you made the point that you get a general plan.
Also, consider 10 concurrent users doing this, suddenly you have shifted
your select centric access model to a transaction one so you'll face
increased write IO in the logs and disk, also there may well be a lot of
blocking problems which will cause significant scalability problems which
I'm suprised you have not noticed yet - perhaps there aren't many concurrent
users on your particular app using this functionality at the same time?
Dynamic SQL is the only way to go, I've done something similar in terms of
spec myself for a knowledge management system we developed, basically I
build the SQL dynamically and store the text, the query can be very easily
resubmitted without any work apart from a select from the table holding the
query, no constants used - all parameterised too and you get the best plan
for the job instead of a general one.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<kriskirk@.hotmail.com> wrote in message
news:1145947385.605291.160600@.y43g2000cwc.googlegroups.com...
> Tony Rogerson wrote:
>
> Actually we've used a similar approach for one of our products. It was
> for a set of marketing plans for which the user selected a set of
> customer demographic criteria (birth date, ethnicity, location, etc
> etc). The reason we didn't just generate dynamic SQL was because these
> marketing plans had to be saved and reusable, therefore we had to store
> the actual criteria in a set of tables, similar to the Query table Joe
> used except we stored all criteria types in that table.
> Then when actually generating the customer list, we used a query
> similar to Joe's and it works reasonably well. The main divantage is
> that the optimizer can't use the best indexes, but since the act of
> generating a customer list isn't time criticial it didn't matter.
> I doubt this solution is suitable for the OP's problem (dynamic SQL is
> probably better IMO), but in some cases it is useful and not just a
> theoretical solution.
> Kris
>|||Hi Brett,
SQL Server does a lot of auto-parameterisation so query plans will be
cached, obviously you might end up with 'x' different query plans but thats
good because they will be highly efficient based solely on what you are
trying to do instead of kludging the query to get round the lack of dynamics
within the SELECT statement.
The string concatenation won't even be measurable.
Build your query and parameterise it, Aaron links to a really good article
on dynamic SQL by Erland (http://www.sommarskog.se/dyn-search.html)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"brett" <account@.cygen.com> wrote in message
news:1145919756.043300.318930@.u72g2000cwu.googlegroups.com...
> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>

How to dynamically create Tables

I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:

public void CreateTypedLiterals(Type type)
{


String sql = String.Format(

"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",

// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()

);

...

As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:

[SqlProcedure]
public void CreateTypedLiterals(Type type)
{


DataTable TypedLiterals =

new DataTable(

String.Format("Literals_{0}", type.ToString()));

TypedLiterals.Columns.Add(

"ID", typeof(int), "DISTINCT NOT NULL");

TypedLiterals.Columns.Add("Value", type);

...

But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:

using(Microsoft.SqlServer.Server)
{


CurrentDatabase.Tables.Add(TypedLiterals);

}

But this is afaik not possible. Has anybody an idea how to solve this issure?Have you considere using the new XML datatype of SqlServer2005 ? As MS documentation says, it well fits into scenarios where you have sparse data.
You could use a single table that contains variable data and types in XML format.|||The ADO.NET datatable (which you are using) is not the same as the SQL Server table. YOu either will have to use your script approach or use SMO to create objects using the current server connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I think it might be better to use strings or at least tables for the most common types as defined in XSD and handling all others simply as strings. Using XML format is ineffective, because the main reason for using different Tables for each type is because I want to get more effective sorting.|||That the ADO.NET datatable is not the same than a SQL Server table was clear. Because I'm not a fan of the script Approach using String.Format to get SQL-Commands because of the fear of SQL-Injections, so I'll take a closer look at SMO. Thanks for the hint.

How to dynamically create Tables

I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:

public void CreateTypedLiterals(Type type)
{


String sql = String.Format(

"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",

// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()

);

...

As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:

[SqlProcedure]
public void CreateTypedLiterals(Type type)
{


DataTable TypedLiterals =

new DataTable(

String.Format("Literals_{0}", type.ToString()));

TypedLiterals.Columns.Add(

"ID", typeof(int), "DISTINCT NOT NULL");

TypedLiterals.Columns.Add("Value", type);

...

But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:

using(Microsoft.SqlServer.Server)
{


CurrentDatabase.Tables.Add(TypedLiterals);

}

But this is afaik not possible. Has anybody an idea how to solve this issure?Have you considere using the new XML datatype of SqlServer2005 ? As MS documentation says, it well fits into scenarios where you have sparse data.
You could use a single table that contains variable data and types in XML format.|||The ADO.NET datatable (which you are using) is not the same as the SQL Server table. YOu either will have to use your script approach or use SMO to create objects using the current server connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I think it might be better to use strings or at least tables for the most common types as defined in XSD and handling all others simply as strings. Using XML format is ineffective, because the main reason for using different Tables for each type is because I want to get more effective sorting.|||That the ADO.NET datatable is not the same than a SQL Server table was clear. Because I'm not a fan of the script Approach using String.Format to get SQL-Commands because of the fear of SQL-Injections, so I'll take a closer look at SMO. Thanks for the hint.

How to dynamically create SQL inside a stored procedure?

I am having problem with 'TOP @.pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @.pageSize here so that it dynamically determines the 'n' of 'TOP n' ?

ALTER PROCEDURE dbo.spGetNextPageRecords
(
@.pageSize int,
@.previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @.pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @.previousMaxId) order by ProductId
RETURN

Try aSET @.@.ROWCOUNT instead.

Terri|||So is there a way of dynamically forming a SQL statement inside a stored procedure and then executing it inside the same stored procedure?

I guess, from what you suggested, I should remove the 'TOP @.pageSize' clause from the query, and then use just before the query the following clause - 'SET @.@.ROWCOUNT @.pageSize'.|||The rowcount suggestion works beautifully. I can always get the next 'n' records for the next page.

With this stored procedure, one can always get a fixed number of records for all pages except the last page, while implementing custom paging in a datagrid. Most books will mention an example of custom paging in a datagrid, but will also say that each page might contain a different number of records, if there are missing values of the identity column. But here, even if identity column values are missing ( example: id's = 1,2, 5 , 9, 11 where id's = 3,4,6,7,8,10 are missing ) we can still get a constant number of records per page in custom paging.

I am assuming that the query in this stored procedure is not going to create any inefficiencies on SQL Server side. If you think it will, then please give your feedback.|||SET @.@.ROWCOUNT can have some unexpected side effects if you've got a number of batches in your proc because it restricts the rows for all batches not just the one you want. So just be careful of that.

You could use sp_execute run the dynamic top N (or even wait for Yukon), but unless you get into the problem areas I'd stick with ROWCOUNT.

how to dynamically create report in web app?

Hello,
I am rather new to report service. What I want to achieve is prvide a web
page and let users to choose what fields they want to see, what table they
want to query and what formats they want to apply.
Is it something achievable? Would someone give me some tutorial or hints?
Many Thanks
--
hello, please helpAlthough possible it is not trivial. You need to know the RDL specification
(go to MSDN.microsoft.com and search on RDL specification, there are several
articles). Then there is the issue that this is a server based product, you
cannot change the RDL on the fly. You have to publish the RDL. With RS 2005
you can use the new controls and give the control the RDL and the dataset
(in local mode you do not even need the server). These controls come with VS
2005 (Not with SQL Server). So, check out the spec and see if this is really
something you want to tackle. Depending on the complexity you might be
better off to use XML and XSL instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jerry.xuddd" <jerryxuddd@.discussions.microsoft.com> wrote in message
news:78300BE1-38D8-4491-997D-2C4C0EC98AFE@.microsoft.com...
> Hello,
> I am rather new to report service. What I want to achieve is prvide a web
> page and let users to choose what fields they want to see, what table they
> want to query and what formats they want to apply.
> Is it something achievable? Would someone give me some tutorial or hints?
> Many Thanks
> --
> hello, please help

How to duplicate table structure in Transactional replication?

Dear all:
After I create a new publication and its subscription by Transactional
replication in SQL server 2000,I want to add a column or alter a column
length in publication table and hope that the column can be duplicated to
subscription table.However,I find that Transactional replication in SQL
server 2000 can only replicate data and can not replicate table structure.
The replication type I use is "Transactional replication".
My problem is:
How to duplicate table structure in Transactional replication of SQL server
2000?
On 8 Jun, 04:45, gzwangyang <gzwangy...@.discussions.microsoft.com>
wrote:
> Dear all:
> After I create a new publication and its subscription by Transactional
> replication in SQL server 2000,I want to add a column or alter a column
> length in publication table and hope that the column can be duplicated to
> subscription table.However,I find that Transactional replication in SQL
> server 2000 can only replicate data and can not replicate table structure.
> The replication type I use is "Transactional replication".
> My problem is:
> How to duplicate table structure in Transactional replication of SQL server
> 2000?
What you're trying to do isn't strictly possible. You can't alter a
table that is published for replication.
You would need to drop replication, change the table and then re-
enable the replication with snapshot enabled. Provided your articles
snapshot attributes are correct it will create the table at the
subscriber.
Thanks
James
|||This is not correct. You can use sp_repladdcolumn and sp_repldropcolumn.
Altering a column is not straightforward but is achievable
(http://www.replicationanswers.com/AddColumn.asp).
Cheers,
Paul Ibison

How to duplicate Identity Column in SQL Server 2000?

Dear All:
The table for subscription is the same as the table for publication.
create table zt_company(company_id int identity(1,1) not for replication
primary key,companyname varchar(200),create_date datetime,modify_date
datetime)
If I use "not for replication" option when creating table,publication and
subscription is successful.However,company_id column of subscription table
loses identity attribute and primary key.
If I do not use "not for replication" option when creating table,publication
and subscription fails.
I want to keep "identity attribute and primary key" of identity column in
subscription table ,what should I do?
I use Enterprise manager for publication and subscription.
thanks.
You didn't mention what type of replication you're using, but presumably
you're talking about transactional? If so, you can set up queued updating
subscribers to maintain the identity attribute and have it set up ready for
failover - is that what you want?
Cheers,
Paul Ibison
|||Dear Paul Ibison:
Thanks for your help."queued updating subscribers" sucessfully solved my
problem and is what I want.
wangyang.
"Paul Ibison" wrote:

> You didn't mention what type of replication you're using, but presumably
> you're talking about transactional? If so, you can set up queued updating
> subscribers to maintain the identity attribute and have it set up ready for
> failover - is that what you want?
> Cheers,
> Paul Ibison
>

How to drop db and create new table

Hi all,

I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?

I know these are basic questions but since I am new to MSDE I hope the forum would bear me.

With kind regards,
Ashfaque1) DROP DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_82lh.asp)
2) CREATE TABLE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_8g9x.asp)

Note that there are other ways using the MS-Access GUI based tools. These are pure SQL Server, and as such are more portable than other ways.

-PatP|||Thanks Pat,

No doubt the article is useful to use with SQL Server. It may also be useful for MSDE2000A server. But the problem is from where I should enter in SQL area to deal with these syntaxes. Like in Oracle/SQL you can reach to SQL prompt and put "Grant Connection, Resource, DBA To xyz indentified by xyz" something like.

I need to know in MSDE server how can I do this while I am handeling Access Database Project file?

With kind regards,
Ashfaque|||Hi

After you open your access project press CTRL+G and go to Immediate window
you can run your command immediatly from there like this

docmd.runsql "create table somthing(empid varchar(5))"|||Thanks Feras,

I got the idea to start up at least. Bcz I am new to db Project. I know only sql of mdb files.
I used followings with docmds in immediate window
Drop table, Create table, Instert, Update

They can be written in following fashion

docmd.RunSQL "insert into T_dob values(772,'Waheed');

It is ok. What about if I need to delete the record set. Bcz I tried in the same way like:

Docmd.RunSQL "Delete * From T_dob Where Empname='Waheed'';

But it show syntax error. Where might have gone wrong?

Thanks again.

Regards,
Ashfaque|||I got it again.

Thanks.
Ashfaque

how to drop and create Triggers from an other DB?

Hi,
I am running a stored procedure on the database DB01,
that SP got some statement looking like:
use DB02
DROP Trigger DB02_USER.BOOK_DELETE
It does not run and says:
a USE database statement is not allowed in a procedure or trigger.
If I replace the code by
DROP Trigger DB02.DB02_USER.BOOK_DELETE
then it says:
'DROP TRIGGER' does not allow specifying the database name as a prefix to
the object name.
Then how is it possible to drop a trigger in an other DB than the one the SP
is stored in?
Best regards,
Francois Malgreve.> Then how is it possible to drop a trigger in an other DB than the one the
> SP
> is stored in?
EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
A|||Thanks a lot for the information.
It works very well.
But I am still faced with a related problem. Creating triggers from an SP.
When I try to execute:
EXEC('USE DB02; CREATE Trigger CSDB_TR_SELLRATE_DELETE
On DB02_USER.BOOK_DELETE
For Delete As
Begin
--code goes here
End
')
I have an error saying:
'CREATE TRIGGER' must be the first statement in a query batch.
Do you have any trick to go around this one?
Thanks,
Francois
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPFJxy36FHA.2524@.TK2MSFTNGP10.phx.gbl...
the
> EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
> A
>