Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
SenthilkumarCheck out CONVERT() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:%234%232fJmJGHA.1388@.TK2MSFTNGP11.phx.gbl...
Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
Senthilkumar|||To add to Tom's reply, you might want to consider leaving off the
formatting in the view and just return the raw data. Let the client
format the result set. Any time you format the data on the server, the
formatting functions you use have to operate on every single row, one
at a time.
--Mary
On Tue, 31 Jan 2006 18:09:30 +0530, "Senthilkumar"
<kesk32@.yahoo.co.in> wrote:
>Hi,
>I have used an access table to create a chart report to show how many calls
>were handled in a month. How do i conver the Access sql to MS sql server
>view. Here is the access sql:
>SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
>FROM [Complaints]
>GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
> (Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
>ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
>In the MS Sql, the word FORMAT is not recognised. I have tried several ways
>like this w/o any luck. Can anyone tell me the correct syntax for the above
>sql querry.
>Thanks
>Senthilkumar
>
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Conversion from float to varchar
--SCRIPT :
CREATE TABLE [t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [t1] VALUES(1.0 , null )
INSERT INTO [t1] VALUES(3.1099999999999999 , null )
INSERT INTO [t1] VALUES(2.1000000000000001 , null )
What is required that copying data from column [id] to column [charid] with
all trailing decimal values.
-KhurramCREATE TABLE [#t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [#t1] VALUES(1.0 , null )
INSERT INTO [#t1] VALUES(3.1099999999999999 , null )
INSERT INTO [#t1] VALUES(2.1000000000000001 , null )
UPDATE #t1
SET charid =FLOOR(id)
Select * from #t1
HTH, Jens Suessmeyer.|||Look up the STR function in Books Online.
http://msdn.microsoft.com/library/d.../>
us_412q.asp
Plus some reading on data modeling might prove to be of great help.
MLsqlsql
CREATE TABLE [t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [t1] VALUES(1.0 , null )
INSERT INTO [t1] VALUES(3.1099999999999999 , null )
INSERT INTO [t1] VALUES(2.1000000000000001 , null )
What is required that copying data from column [id] to column [charid] with
all trailing decimal values.
-KhurramCREATE TABLE [#t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [#t1] VALUES(1.0 , null )
INSERT INTO [#t1] VALUES(3.1099999999999999 , null )
INSERT INTO [#t1] VALUES(2.1000000000000001 , null )
UPDATE #t1
SET charid =FLOOR(id)
Select * from #t1
HTH, Jens Suessmeyer.|||Look up the STR function in Books Online.
http://msdn.microsoft.com/library/d.../>
us_412q.asp
Plus some reading on data modeling might prove to be of great help.
MLsqlsql
Thursday, March 22, 2012
Conversion error
Hi all,
Basically I am trying to create a package that will
(A) Create a table with specified datatypes
(B) Use a text Source file for the data
(C) on Success \ Completion of the "Execute SQL" transform the data from the text into the table.
Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)
It all seems to work now but when I run the package I get the following error
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).
But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.
It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.
Any idea's or suggestions I could try ?
Im very new to SQL 2000 & DTS so dont rule out any very newbie errors :)
Thanks
I'm not sure what steps you created, so I'm uncertain as to why it would duplicate the table. I would create one step to read the text file and create the table, and another to fill it. Here's a broad reference with guidelines, and if you have further questions you can check out Books Online for SQL Server 2000 to read more:
http://support.microsoft.com/default.aspx/kb/242377
Buck Woody
Conversion error
Hi all,
Basically I am trying to create a package that will
(A) Create a table with specified datatypes
(B) Use a text Source file for the data
(C) on Success \ Completion of the "Execute SQL" transform the data from the text into the table.
Connect to DB <-- [TRANSFROM]-- Text (Source) <-- Execute SQL (Create Table)
It all seems to work now but when I run the package I get the following error
The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation_6'conversion error: Conversion invalid for datatypes on column on pair 1 (source column 'Col007' (DBTYPE_STR),destination column 'Rec_Amt' (DBTYPE_CY)).
But when I go into the TransformDataTask, under transformation and test that column it all works fine, infact I tested all the columns and they all seem to work fine.
It also seems to be creating the same table twice first in the " Execute SQL" task and then again for some reason in the "DataTransform" task. I dont know if that is realted to the problem or not though.
Any idea's or suggestions I could try ?
Im very new to SQL 2000 & DTS so dont rule out any very newbie errors :)
Thanks
I'm not sure what steps you created, so I'm uncertain as to why it would duplicate the table. I would create one step to read the text file and create the table, and another to fill it. Here's a broad reference with guidelines, and if you have further questions you can check out Books Online for SQL Server 2000 to read more:
http://support.microsoft.com/default.aspx/kb/242377
Buck Woody
Tuesday, March 20, 2012
conversation handle
hi all, i am having a hard time getting the conversation handle id to match
..
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. I
f
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>sqlsql
..
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. I
f
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>sqlsql
conversation handle
hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query?
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tin
The conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query?
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>
|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>
|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query?
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tin
The conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query?
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>
|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>
>
|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...[vbcol=seagreen]
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/ar...12/502942.aspx
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
>
>
conversation handle
hi all, i am having a hard time getting the conversation handle id to match ...
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
--
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> > hi all, i am having a hard time getting the conversation handle id to
> > match ...
> >
> > scripts that create the relevant objects ...
> >
> > create message type QueryMessage validation = none
> > create contract QueryContract (QueryMessage sent by initiator)
> > create queue QueueSender
> > create queue QueueReceiver
> > create service Sender on queue QueueSender
> > create service Receiver on queue QueueReceiver (QueryContract)
> >
> > send with conversation handle id ...
> >
> > begin transaction
> > declare @.conversationhandle uniqueidentifier;
> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> > begin dialog @.conversationhandle
> > from service [Sender]
> > to service 'Receiver'
> > on contract [QueryContract]
> > with encryption = off;
> > send on conversation @.conversationhandle
> > message type [QueryMessage] ('blah blah blah;');
> > commit transaction
> >
> > after i ran the above send query a few times, and do the below select ...
> >
> > select conversation_group_id, conversation_handle, cast(message_body as
> > varchar(1000)) from QueueReceiver
> >
> > i got all kinds of different conversation_group_id and
> > conversation_handle?
> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> > specified
> > in the send query'
> >
> > and when i do the below receive query, it says:
> > Msg 8426, Level 16, State 20, Line 1
> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> > found.
> >
> > receive top(1) convert(varchar(1000),message_body) as message
> > from QueueReceiver
> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >
> > please help! thanks in advance!
> >
> > - tin
> >
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
--
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>> The conversation handle is an output parameter for the BEGIN DIALOG
>> statement. Each time you run the statement, a new conversation will be
>> created and the @.conversationhandle will get a new value assigned to it.
>> If
>> the variable had a previous value, it will be overwriten.
>> So the conversation with the handle
>> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> was never created, hence the error on the RECEIVE statement.
>> --
>> 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
>>
>> "tin" <tin@.discussions.microsoft.com> wrote in message
>> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>> > hi all, i am having a hard time getting the conversation handle id to
>> > match ...
>> >
>> > scripts that create the relevant objects ...
>> >
>> > create message type QueryMessage validation = none
>> > create contract QueryContract (QueryMessage sent by initiator)
>> > create queue QueueSender
>> > create queue QueueReceiver
>> > create service Sender on queue QueueSender
>> > create service Receiver on queue QueueReceiver (QueryContract)
>> >
>> > send with conversation handle id ...
>> >
>> > begin transaction
>> > declare @.conversationhandle uniqueidentifier;
>> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> > begin dialog @.conversationhandle
>> > from service [Sender]
>> > to service 'Receiver'
>> > on contract [QueryContract]
>> > with encryption = off;
>> > send on conversation @.conversationhandle
>> > message type [QueryMessage] ('blah blah blah;');
>> > commit transaction
>> >
>> > after i ran the above send query a few times, and do the below select
>> > ...
>> >
>> > select conversation_group_id, conversation_handle, cast(message_body as
>> > varchar(1000)) from QueueReceiver
>> >
>> > i got all kinds of different conversation_group_id and
>> > conversation_handle?
>> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
>> > specified
>> > in the send query'
>> >
>> > and when i do the below receive query, it says:
>> > Msg 8426, Level 16, State 20, Line 1
>> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
>> > found.
>> >
>> > receive top(1) convert(varchar(1000),message_body) as message
>> > from QueueReceiver
>> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> >
>> > please help! thanks in advance!
>> >
>> > - tin
>> >
>>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> > ah, no wonder. another question:
> >
> > if i want an application to send messages to 2 applications, then i guess
> > i
> > will have to create two queues? i was trying to use one queue with 2
> > conversation handles.
> >
> > many thanks.
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> >> The conversation handle is an output parameter for the BEGIN DIALOG
> >> statement. Each time you run the statement, a new conversation will be
> >> created and the @.conversationhandle will get a new value assigned to it.
> >> If
> >> the variable had a previous value, it will be overwriten.
> >>
> >> So the conversation with the handle
> >> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> was never created, hence the error on the RECEIVE statement.
> >>
> >> --
> >> 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
> >>
> >>
> >> "tin" <tin@.discussions.microsoft.com> wrote in message
> >> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> >> > hi all, i am having a hard time getting the conversation handle id to
> >> > match ...
> >> >
> >> > scripts that create the relevant objects ...
> >> >
> >> > create message type QueryMessage validation = none
> >> > create contract QueryContract (QueryMessage sent by initiator)
> >> > create queue QueueSender
> >> > create queue QueueReceiver
> >> > create service Sender on queue QueueSender
> >> > create service Receiver on queue QueueReceiver (QueryContract)
> >> >
> >> > send with conversation handle id ...
> >> >
> >> > begin transaction
> >> > declare @.conversationhandle uniqueidentifier;
> >> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> > begin dialog @.conversationhandle
> >> > from service [Sender]
> >> > to service 'Receiver'
> >> > on contract [QueryContract]
> >> > with encryption = off;
> >> > send on conversation @.conversationhandle
> >> > message type [QueryMessage] ('blah blah blah;');
> >> > commit transaction
> >> >
> >> > after i ran the above send query a few times, and do the below select
> >> > ...
> >> >
> >> > select conversation_group_id, conversation_handle, cast(message_body as
> >> > varchar(1000)) from QueueReceiver
> >> >
> >> > i got all kinds of different conversation_group_id and
> >> > conversation_handle?
> >> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> >> > specified
> >> > in the send query'
> >> >
> >> > and when i do the below receive query, it says:
> >> > Msg 8426, Level 16, State 20, Line 1
> >> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> >> > found.
> >> >
> >> > receive top(1) convert(varchar(1000),message_body) as message
> >> > from QueueReceiver
> >> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> >
> >> > please help! thanks in advance!
> >> >
> >> > - tin
> >> >
> >>
> >>
> >>
>
>
scripts that create the relevant objects ...
create message type QueryMessage validation = none
create contract QueryContract (QueryMessage sent by initiator)
create queue QueueSender
create queue QueueReceiver
create service Sender on queue QueueSender
create service Receiver on queue QueueReceiver (QueryContract)
send with conversation handle id ...
begin transaction
declare @.conversationhandle uniqueidentifier;
select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
begin dialog @.conversationhandle
from service [Sender]
to service 'Receiver'
on contract [QueryContract]
with encryption = off;
send on conversation @.conversationhandle
message type [QueryMessage] ('blah blah blah;');
commit transaction
after i ran the above send query a few times, and do the below select ...
select conversation_group_id, conversation_handle, cast(message_body as
varchar(1000)) from QueueReceiver
i got all kinds of different conversation_group_id and conversation_handle?
should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i specified
in the send query'
and when i do the below receive query, it says:
Msg 8426, Level 16, State 20, Line 1
The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not found.
receive top(1) convert(varchar(1000),message_body) as message
from QueueReceiver
where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
please help! thanks in advance!
- tinThe conversation handle is an output parameter for the BEGIN DIALOG
statement. Each time you run the statement, a new conversation will be
created and the @.conversationhandle will get a new value assigned to it. If
the variable had a previous value, it will be overwriten.
So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
was never created, hence the error on the RECEIVE statement.
--
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> hi all, i am having a hard time getting the conversation handle id to
> match ...
> scripts that create the relevant objects ...
> create message type QueryMessage validation = none
> create contract QueryContract (QueryMessage sent by initiator)
> create queue QueueSender
> create queue QueueReceiver
> create service Sender on queue QueueSender
> create service Receiver on queue QueueReceiver (QueryContract)
> send with conversation handle id ...
> begin transaction
> declare @.conversationhandle uniqueidentifier;
> select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> begin dialog @.conversationhandle
> from service [Sender]
> to service 'Receiver'
> on contract [QueryContract]
> with encryption = off;
> send on conversation @.conversationhandle
> message type [QueryMessage] ('blah blah blah;');
> commit transaction
> after i ran the above send query a few times, and do the below select ...
> select conversation_group_id, conversation_handle, cast(message_body as
> varchar(1000)) from QueueReceiver
> i got all kinds of different conversation_group_id and
> conversation_handle?
> should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> specified
> in the send query'
> and when i do the below receive query, it says:
> Msg 8426, Level 16, State 20, Line 1
> The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> found.
> receive top(1) convert(varchar(1000),message_body) as message
> from QueueReceiver
> where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> please help! thanks in advance!
> - tin
>|||ah, no wonder. another question:
if i want an application to send messages to 2 applications, then i guess i
will have to create two queues? i was trying to use one queue with 2
conversation handles.
many thanks.
"Remus Rusanu [MSFT]" wrote:
> The conversation handle is an output parameter for the BEGIN DIALOG
> statement. Each time you run the statement, a new conversation will be
> created and the @.conversationhandle will get a new value assigned to it. If
> the variable had a previous value, it will be overwriten.
> So the conversation with the handle 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> was never created, hence the error on the RECEIVE statement.
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> > hi all, i am having a hard time getting the conversation handle id to
> > match ...
> >
> > scripts that create the relevant objects ...
> >
> > create message type QueryMessage validation = none
> > create contract QueryContract (QueryMessage sent by initiator)
> > create queue QueueSender
> > create queue QueueReceiver
> > create service Sender on queue QueueSender
> > create service Receiver on queue QueueReceiver (QueryContract)
> >
> > send with conversation handle id ...
> >
> > begin transaction
> > declare @.conversationhandle uniqueidentifier;
> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> > begin dialog @.conversationhandle
> > from service [Sender]
> > to service 'Receiver'
> > on contract [QueryContract]
> > with encryption = off;
> > send on conversation @.conversationhandle
> > message type [QueryMessage] ('blah blah blah;');
> > commit transaction
> >
> > after i ran the above send query a few times, and do the below select ...
> >
> > select conversation_group_id, conversation_handle, cast(message_body as
> > varchar(1000)) from QueueReceiver
> >
> > i got all kinds of different conversation_group_id and
> > conversation_handle?
> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> > specified
> > in the send query'
> >
> > and when i do the below receive query, it says:
> > Msg 8426, Level 16, State 20, Line 1
> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> > found.
> >
> > receive top(1) convert(varchar(1000),message_body) as message
> > from QueueReceiver
> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >
> > please help! thanks in advance!
> >
> > - tin
> >
>
>|||In general yes, each application should listen on it's own queue. When
sending a message to more than one application it usually conforms to a
publish-subscribe pattern. See if this article helps you at
http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
--
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
"tin" <tin@.discussions.microsoft.com> wrote in message
news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> ah, no wonder. another question:
> if i want an application to send messages to 2 applications, then i guess
> i
> will have to create two queues? i was trying to use one queue with 2
> conversation handles.
> many thanks.
> "Remus Rusanu [MSFT]" wrote:
>> The conversation handle is an output parameter for the BEGIN DIALOG
>> statement. Each time you run the statement, a new conversation will be
>> created and the @.conversationhandle will get a new value assigned to it.
>> If
>> the variable had a previous value, it will be overwriten.
>> So the conversation with the handle
>> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> was never created, hence the error on the RECEIVE statement.
>> --
>> 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
>>
>> "tin" <tin@.discussions.microsoft.com> wrote in message
>> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
>> > hi all, i am having a hard time getting the conversation handle id to
>> > match ...
>> >
>> > scripts that create the relevant objects ...
>> >
>> > create message type QueryMessage validation = none
>> > create contract QueryContract (QueryMessage sent by initiator)
>> > create queue QueueSender
>> > create queue QueueReceiver
>> > create service Sender on queue QueueSender
>> > create service Receiver on queue QueueReceiver (QueryContract)
>> >
>> > send with conversation handle id ...
>> >
>> > begin transaction
>> > declare @.conversationhandle uniqueidentifier;
>> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> > begin dialog @.conversationhandle
>> > from service [Sender]
>> > to service 'Receiver'
>> > on contract [QueryContract]
>> > with encryption = off;
>> > send on conversation @.conversationhandle
>> > message type [QueryMessage] ('blah blah blah;');
>> > commit transaction
>> >
>> > after i ran the above send query a few times, and do the below select
>> > ...
>> >
>> > select conversation_group_id, conversation_handle, cast(message_body as
>> > varchar(1000)) from QueueReceiver
>> >
>> > i got all kinds of different conversation_group_id and
>> > conversation_handle?
>> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
>> > specified
>> > in the send query'
>> >
>> > and when i do the below receive query, it says:
>> > Msg 8426, Level 16, State 20, Line 1
>> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
>> > found.
>> >
>> > receive top(1) convert(varchar(1000),message_body) as message
>> > from QueueReceiver
>> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
>> >
>> > please help! thanks in advance!
>> >
>> > - tin
>> >
>>|||thanks, that helped a lot!
"Remus Rusanu [MSFT]" wrote:
> In general yes, each application should listen on it's own queue. When
> sending a message to more than one application it usually conforms to a
> publish-subscribe pattern. See if this article helps you at
> http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx
> --
> 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
>
> "tin" <tin@.discussions.microsoft.com> wrote in message
> news:F9E9BD78-2518-4360-A0C1-6B02A62DFF10@.microsoft.com...
> > ah, no wonder. another question:
> >
> > if i want an application to send messages to 2 applications, then i guess
> > i
> > will have to create two queues? i was trying to use one queue with 2
> > conversation handles.
> >
> > many thanks.
> >
> > "Remus Rusanu [MSFT]" wrote:
> >
> >> The conversation handle is an output parameter for the BEGIN DIALOG
> >> statement. Each time you run the statement, a new conversation will be
> >> created and the @.conversationhandle will get a new value assigned to it.
> >> If
> >> the variable had a previous value, it will be overwriten.
> >>
> >> So the conversation with the handle
> >> 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> was never created, hence the error on the RECEIVE statement.
> >>
> >> --
> >> 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
> >>
> >>
> >> "tin" <tin@.discussions.microsoft.com> wrote in message
> >> news:0DC24D17-0167-4D80-8072-97817B6C7ABB@.microsoft.com...
> >> > hi all, i am having a hard time getting the conversation handle id to
> >> > match ...
> >> >
> >> > scripts that create the relevant objects ...
> >> >
> >> > create message type QueryMessage validation = none
> >> > create contract QueryContract (QueryMessage sent by initiator)
> >> > create queue QueueSender
> >> > create queue QueueReceiver
> >> > create service Sender on queue QueueSender
> >> > create service Receiver on queue QueueReceiver (QueryContract)
> >> >
> >> > send with conversation handle id ...
> >> >
> >> > begin transaction
> >> > declare @.conversationhandle uniqueidentifier;
> >> > select @.conversationhandle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> > begin dialog @.conversationhandle
> >> > from service [Sender]
> >> > to service 'Receiver'
> >> > on contract [QueryContract]
> >> > with encryption = off;
> >> > send on conversation @.conversationhandle
> >> > message type [QueryMessage] ('blah blah blah;');
> >> > commit transaction
> >> >
> >> > after i ran the above send query a few times, and do the below select
> >> > ...
> >> >
> >> > select conversation_group_id, conversation_handle, cast(message_body as
> >> > varchar(1000)) from QueueReceiver
> >> >
> >> > i got all kinds of different conversation_group_id and
> >> > conversation_handle?
> >> > should one of them be d27db2ac-08c5-405d-a53e-ec05635c7e5a which i
> >> > specified
> >> > in the send query'
> >> >
> >> > and when i do the below receive query, it says:
> >> > Msg 8426, Level 16, State 20, Line 1
> >> > The conversation handle "D27DB2AC-08C5-405D-A53E-EC05635C7E5A" is not
> >> > found.
> >> >
> >> > receive top(1) convert(varchar(1000),message_body) as message
> >> > from QueueReceiver
> >> > where conversation_handle = 'd27db2ac-08c5-405d-a53e-ec05635c7e5a'
> >> >
> >> > please help! thanks in advance!
> >> >
> >> > - tin
> >> >
> >>
> >>
> >>
>
>
Converion for VARCHAR to FLOAT
Hi-
I am trying the following example.
create table mytest (a float, b float(8))
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
The result is 0.40000000000000002.
Can some one tell me what am I doing wrong? Appreciate your time.
- SaratOriginally posted by sbaru
Hi-
I am trying the following example.
create table mytest (a float, b float(8))
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
The result is 0.40000000000000002.
Can some one tell me what am I doing wrong? Appreciate your time.
- Sarat
why do u need the table? what r u trying to do?
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
it prints '0.4' for me|||Originally posted by sbaru
Hi-
I am trying the following example.
create table mytest (a float, b float(8))
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
The result is 0.40000000000000002.
Can some one tell me what am I doing wrong? Appreciate your time.
- Sarat
Yes, this is a common problem with all computers. When you declare float, the computer represents the numbers internally as approximations. The approximation is really really close to your number but often not exact.
To avoid this when working with monetary values, for example, I frequently will declare the field to be integer and just think of the value as pennies (for U.S. currency) and multiply the result by 100 to get dollars.
I am trying the following example.
create table mytest (a float, b float(8))
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
The result is 0.40000000000000002.
Can some one tell me what am I doing wrong? Appreciate your time.
- SaratOriginally posted by sbaru
Hi-
I am trying the following example.
create table mytest (a float, b float(8))
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
The result is 0.40000000000000002.
Can some one tell me what am I doing wrong? Appreciate your time.
- Sarat
why do u need the table? what r u trying to do?
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
it prints '0.4' for me|||Originally posted by sbaru
Hi-
I am trying the following example.
create table mytest (a float, b float(8))
declare @.a FLOAT
declare @.b varchar(10)
set @.b = '0.4'
set @.a = @.b
PRINT @.a
The result is 0.40000000000000002.
Can some one tell me what am I doing wrong? Appreciate your time.
- Sarat
Yes, this is a common problem with all computers. When you declare float, the computer represents the numbers internally as approximations. The approximation is really really close to your number but often not exact.
To avoid this when working with monetary values, for example, I frequently will declare the field to be integer and just think of the value as pennies (for U.S. currency) and multiply the result by 100 to get dollars.
Monday, March 19, 2012
controlling temdb size
We have a create table query against 2 large tables (about 12 gig) that
balloons our tempdb to about 17 gigs. Is there a way to constrain the growt
h
of tempdb by turning off logging of some operations. The query is fairly
clean and the join and group by clauses are fully covered by indexes.There is no way to turn off logging. Is it the log file that is ballooning
or the data file? If it's the data file (which I suspect is most of it)
then logging has nothing to do with it anyway. You can pretty much expect a
lot of activity in TempDB when you have that much data that you are joining
and especially grouping by. It has to keep the intermediate results
somewhere while it groups them and that is tempdb. Maybe if you post the DDL
for the tables involved and the actual query someone can suggest something.
Andrew J. Kelly SQL MVP
"Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
> We have a create table query against 2 large tables (about 12 gig) that
> balloons our tempdb to about 17 gigs. Is there a way to constrain the
> growth
> of tempdb by turning off logging of some operations. The query is fairly
> clean and the join and group by clauses are fully covered by indexes.|||Andrew,
Thanks for your reply. The problem is the tempdb file, not the data file.
Using table hints to force use of appropriate indexes and a 'merge' join hin
t
the query now has the same 1hr 50min time but only brought temdb to 9 gig
(rather than 17gig). Below is the DDL for the table and query. By the way
the accnt table is 1.1 million rows and the proft table is 25 million rows.
___
CREATE TABLE [dbo].[accnt] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BAL_OTHR] [money] NOT NULL ,
[CNT_OTHR] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[proft] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[HOUSE_REVENUE] [money] NOT NULL ,
[TRADING_REVENUE] [money] NOT NULL ,
[TOTAL_EXPENSE] [money] NOT NULL ,
[CLIENT_PROFITABILITY] [money] NOT NULL ,
[HasParent] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
[PRIMARY]
GO
CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
GO
CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON [PRIMARY]
GO
CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
GO
CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
GO
SELECT
accnt.BRANCHLABEL,
accnt.REP,
proft.DATABASE_DATE,
accnt.REGIONLABEL,
COUNT(*) AS ACCOUNTS,
SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
SUM(CAST(0 AS money)) AS OTHER_REVENUE
INTO RepMonth
FROM accnt WITH (INDEX (pk_firm_acct))
INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DAT
E
ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE, accnt.REGIONLABE
L
___
"Andrew J. Kelly" wrote:
> There is no way to turn off logging. Is it the log file that is balloonin
g
> or the data file? If it's the data file (which I suspect is most of it)
> then logging has nothing to do with it anyway. You can pretty much expect
a
> lot of activity in TempDB when you have that much data that you are joinin
g
> and especially grouping by. It has to keep the intermediate results
> somewhere while it groups them and that is tempdb. Maybe if you post the D
DL
> for the tables involved and the actual query someone can suggest something
.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
> message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
>
>|||OK well each database including tempdb has at least one data file and one
log file. The data file normally has a .mdf extension and the log file has
a .ldf extension. Which one of these for TempDB is growing to 17GB? A
couple of comments from what I see here. You don't have a primary Key
defined on the Proft table and you don't have a clustered index on that
table either. Both of which are very important. I suggest you drop the
nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in a
true merge fashion using the clustered indexes and should dramatically cut
down on the time to do this. You might even consider making the clustered
indexes on both these tables with ACCT_NO as the first column instead of
Firm_ID. The reason being that this is more selective but this depends a
lot on how you access these tables. Do you look for Acc_No or Firm_ID more?
In either case there is no need to have a non-clustered index on Firm_ID if
the clustered index already has Firm_ID as the first column in the index
expression. Also why does the Order By have the same columns as the group by
but in a different order? It may help to have them the same so the engine
does not potentially have to order twice.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
> Andrew,
> Thanks for your reply. The problem is the tempdb file, not the data file.
> Using table hints to force use of appropriate indexes and a 'merge' join
> hint
> the query now has the same 1hr 50min time but only brought temdb to 9 gig
> (rather than 17gig). Below is the DDL for the table and query. By the
> way
> the accnt table is 1.1 million rows and the proft table is 25 million
> rows.
> ___
> CREATE TABLE [dbo].[accnt] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BAL_OTHR] [money] NOT NULL ,
> [CNT_OTHR] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[proft] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [HOUSE_REVENUE] [money] NOT NULL ,
> [TRADING_REVENUE] [money] NOT NULL ,
> [TOTAL_EXPENSE] [money] NOT NULL ,
> [CLIENT_PROFITABILITY] [money] NOT NULL ,
> [HasParent] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
> CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
> ([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
> GO
> CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
> GO
> CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
> REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
> GO
> SELECT
> accnt.BRANCHLABEL,
> accnt.REP,
> proft.DATABASE_DATE,
> accnt.REGIONLABEL,
> COUNT(*) AS ACCOUNTS,
> SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
> SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
> SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
> SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
> SUM(CAST(0 AS money)) AS OTHER_REVENUE
> INTO RepMonth
> FROM accnt WITH (INDEX (pk_firm_acct))
> INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
> ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
> GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP,
> proft.DATABASE_DATE
> ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE,
> accnt.REGIONLABEL
> ___
> "Andrew J. Kelly" wrote:
>|||Andrew,
Thanks. Not quite sure why the order and group by statements have different
field order, but that's easy to fix.
The mdf for tempdb is the one growing.
We will try variations of your good suggestions. Firm_id is baggage, part
of the account number and in this implementation of the database always
containing the same value, so we will flip that around.
Monthly we use DTS to add 1 million records to proft and drop and replace
all rows in the accnt table. Because the proft is so large I drop and
rebuild all indexes (loading the data in between), so if I have a primary ke
y
I have to deal with all the dependencies, constraints, etc. Proft by the wa
y
is unique on acct_no, firm_id, database_date, so its primary key would need
all three but this field order should be ok.
With the foreign key constraint connecting proft into accnt I assumed the db
engine would recognize what is supposed to be happening.
"Andrew J. Kelly" wrote:
> OK well each database including tempdb has at least one data file and one
> log file. The data file normally has a .mdf extension and the log file ha
s
> a .ldf extension. Which one of these for TempDB is growing to 17GB? A
> couple of comments from what I see here. You don't have a primary Key
> defined on the Proft table and you don't have a clustered index on that
> table either. Both of which are very important. I suggest you drop the
> nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
> Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in
a
> true merge fashion using the clustered indexes and should dramatically cu
t
> down on the time to do this. You might even consider making the clustered
> indexes on both these tables with ACCT_NO as the first column instead of
> Firm_ID. The reason being that this is more selective but this depends a
> lot on how you access these tables. Do you look for Acc_No or Firm_ID mor
e?
> In either case there is no need to have a non-clustered index on Firm_ID i
f
> the clustered index already has Firm_ID as the first column in the index
> expression. Also why does the Order By have the same columns as the group
by
> but in a different order? It may help to have them the same so the engine
> does not potentially have to order twice.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
> message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
>
>|||If the data file keeps growing back to that size then you should leave it
there. Growing a data file is very expensive. There is no penalty for too
much free space but a big one for too little. If Firm_ID is always the same
value I would argue that you don't have it in the Clustered index at all.
The column(s) of the clustered index are appended to the end of all the
nonclustered indexes so you are propagating 4 bytes per row * every
nonclustered index. Let us know how it works out.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:36502EBA-6CFA-4F94-92B8-1402769CE178@.microsoft.com...
> Andrew,
> Thanks. Not quite sure why the order and group by statements have
> different
> field order, but that's easy to fix.
> The mdf for tempdb is the one growing.
> We will try variations of your good suggestions. Firm_id is baggage, part
> of the account number and in this implementation of the database always
> containing the same value, so we will flip that around.
> Monthly we use DTS to add 1 million records to proft and drop and replace
> all rows in the accnt table. Because the proft is so large I drop and
> rebuild all indexes (loading the data in between), so if I have a primary
> key
> I have to deal with all the dependencies, constraints, etc. Proft by the
> way
> is unique on acct_no, firm_id, database_date, so its primary key would
> need
> all three but this field order should be ok.
> With the foreign key constraint connecting proft into accnt I assumed the
> db
> engine would recognize what is supposed to be happening.
> "Andrew J. Kelly" wrote:
>
balloons our tempdb to about 17 gigs. Is there a way to constrain the growt
h
of tempdb by turning off logging of some operations. The query is fairly
clean and the join and group by clauses are fully covered by indexes.There is no way to turn off logging. Is it the log file that is ballooning
or the data file? If it's the data file (which I suspect is most of it)
then logging has nothing to do with it anyway. You can pretty much expect a
lot of activity in TempDB when you have that much data that you are joining
and especially grouping by. It has to keep the intermediate results
somewhere while it groups them and that is tempdb. Maybe if you post the DDL
for the tables involved and the actual query someone can suggest something.
Andrew J. Kelly SQL MVP
"Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
> We have a create table query against 2 large tables (about 12 gig) that
> balloons our tempdb to about 17 gigs. Is there a way to constrain the
> growth
> of tempdb by turning off logging of some operations. The query is fairly
> clean and the join and group by clauses are fully covered by indexes.|||Andrew,
Thanks for your reply. The problem is the tempdb file, not the data file.
Using table hints to force use of appropriate indexes and a 'merge' join hin
t
the query now has the same 1hr 50min time but only brought temdb to 9 gig
(rather than 17gig). Below is the DDL for the table and query. By the way
the accnt table is 1.1 million rows and the proft table is 25 million rows.
___
CREATE TABLE [dbo].[accnt] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BAL_OTHR] [money] NOT NULL ,
[CNT_OTHR] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[proft] (
[FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATABASE_DATE] [smalldatetime] NULL ,
[HOUSE_REVENUE] [money] NOT NULL ,
[TRADING_REVENUE] [money] NOT NULL ,
[TOTAL_EXPENSE] [money] NOT NULL ,
[CLIENT_PROFITABILITY] [money] NOT NULL ,
[HasParent] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
[PRIMARY]
GO
CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
GO
CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON [PRIMARY]
GO
CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
GO
CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
GO
CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
GO
CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
GO
CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[proft] ADD
CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
GO
SELECT
accnt.BRANCHLABEL,
accnt.REP,
proft.DATABASE_DATE,
accnt.REGIONLABEL,
COUNT(*) AS ACCOUNTS,
SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
SUM(CAST(0 AS money)) AS OTHER_REVENUE
INTO RepMonth
FROM accnt WITH (INDEX (pk_firm_acct))
INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DAT
E
ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE, accnt.REGIONLABE
L
___
"Andrew J. Kelly" wrote:
> There is no way to turn off logging. Is it the log file that is balloonin
g
> or the data file? If it's the data file (which I suspect is most of it)
> then logging has nothing to do with it anyway. You can pretty much expect
a
> lot of activity in TempDB when you have that much data that you are joinin
g
> and especially grouping by. It has to keep the intermediate results
> somewhere while it groups them and that is tempdb. Maybe if you post the D
DL
> for the tables involved and the actual query someone can suggest something
.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <Consultant Mark@.discussions.microsoft.com> wrote in
> message news:85E3C12A-EB74-4489-916A-7E15FAB04AA4@.microsoft.com...
>
>|||OK well each database including tempdb has at least one data file and one
log file. The data file normally has a .mdf extension and the log file has
a .ldf extension. Which one of these for TempDB is growing to 17GB? A
couple of comments from what I see here. You don't have a primary Key
defined on the Proft table and you don't have a clustered index on that
table either. Both of which are very important. I suggest you drop the
nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in a
true merge fashion using the clustered indexes and should dramatically cut
down on the time to do this. You might even consider making the clustered
indexes on both these tables with ACCT_NO as the first column instead of
Firm_ID. The reason being that this is more selective but this depends a
lot on how you access these tables. Do you look for Acc_No or Firm_ID more?
In either case there is no need to have a non-clustered index on Firm_ID if
the clustered index already has Firm_ID as the first column in the index
expression. Also why does the Order By have the same columns as the group by
but in a different order? It may help to have them the same so the engine
does not potentially have to order twice.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
> Andrew,
> Thanks for your reply. The problem is the tempdb file, not the data file.
> Using table hints to force use of appropriate indexes and a 'merge' join
> hint
> the query now has the same 1hr 50min time but only brought temdb to 9 gig
> (rather than 17gig). Below is the DDL for the table and query. By the
> way
> the accnt table is 1.1 million rows and the proft table is 25 million
> rows.
> ___
> CREATE TABLE [dbo].[accnt] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [REP] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BRANCHLABEL] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [REGIONLABEL] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BAL_OTHR] [money] NOT NULL ,
> [CNT_OTHR] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[proft] (
> [FIRM_ID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ACCT_NO] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATABASE_DATE] [smalldatetime] NULL ,
> [HOUSE_REVENUE] [money] NOT NULL ,
> [TRADING_REVENUE] [money] NOT NULL ,
> [TOTAL_EXPENSE] [money] NOT NULL ,
> [CLIENT_PROFITABILITY] [money] NOT NULL ,
> [HasParent] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[accnt] WITH NOCHECK ADD
> CONSTRAINT [PK_firm_acct] PRIMARY KEY CLUSTERED
> ([FIRM_ID],[ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[accnt]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[accnt]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [DATABASE_DATE_ind] ON [dbo].[accnt]([DATABASE_DATE]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REP_ind] ON [dbo].[accnt]([REP]) ON [PRIMARY]
> GO
> CREATE INDEX [BRANCHLABEL_ind] ON [dbo].[accnt]([BRANCHLABEL]) ON
> [PRIMARY]
> GO
> CREATE INDEX [REGIONLABEL_ind] ON [dbo].[accnt]([REGIONLABEL]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [HasParentDefault] DEFAULT (0) FOR [HasParent]
> GO
> CREATE INDEX [HasParent_ind] ON [dbo].[proft]([HasParent]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRM_ID_ind] ON [dbo].[proft]([FIRM_ID]) ON [PRIMARY]
> GO
> CREATE INDEX [ACCT_NO_ind] ON [dbo].[proft]([ACCT_NO]) ON [PRIMARY]
> GO
> CREATE INDEX [FIRMACCT_ind] ON [dbo].[proft]([FIRM_ID], [ACCT_NO]) ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[proft] ADD
> CONSTRAINT [FK_proft_accnt] FOREIGN KEY ([FIRM_ID],[ACCT_NO])
> REFERENCES [dbo].[accnt] ([FIRM_ID],[ACCT_NO])
> GO
> SELECT
> accnt.BRANCHLABEL,
> accnt.REP,
> proft.DATABASE_DATE,
> accnt.REGIONLABEL,
> COUNT(*) AS ACCOUNTS,
> SUM(proft.HOUSE_REVENUE) AS HOUSE_REVENUE,
> SUM(proft.TRADING_REVENUE) AS TRADING_REVENUE,
> SUM(proft.TOTAL_EXPENSE) AS TOTAL_EXPENSE,
> SUM(proft.CLIENT_PROFITABILITY) AS CLIENT_PROFITABILITY,
> SUM(CAST(0 AS money)) AS OTHER_REVENUE
> INTO RepMonth
> FROM accnt WITH (INDEX (pk_firm_acct))
> INNER MERGE JOIN proft WITH (INDEX (FIRMACCT_ind))
> ON accnt.FIRM_ID = proft.FIRM_ID AND accnt.ACCT_NO = proft.ACCT_NO
> GROUP BY accnt.REGIONLABEL, accnt.BRANCHLABEL, accnt.REP,
> proft.DATABASE_DATE
> ORDER BY accnt.BRANCHLABEL, accnt.REP, proft.DATABASE_DATE,
> accnt.REGIONLABEL
> ___
> "Andrew J. Kelly" wrote:
>|||Andrew,
Thanks. Not quite sure why the order and group by statements have different
field order, but that's easy to fix.
The mdf for tempdb is the one growing.
We will try variations of your good suggestions. Firm_id is baggage, part
of the account number and in this implementation of the database always
containing the same value, so we will flip that around.
Monthly we use DTS to add 1 million records to proft and drop and replace
all rows in the accnt table. Because the proft is so large I drop and
rebuild all indexes (loading the data in between), so if I have a primary ke
y
I have to deal with all the dependencies, constraints, etc. Proft by the wa
y
is unique on acct_no, firm_id, database_date, so its primary key would need
all three but this field order should be ok.
With the foreign key constraint connecting proft into accnt I assumed the db
engine would recognize what is supposed to be happening.
"Andrew J. Kelly" wrote:
> OK well each database including tempdb has at least one data file and one
> log file. The data file normally has a .mdf extension and the log file ha
s
> a .ldf extension. Which one of these for TempDB is growing to 17GB? A
> couple of comments from what I see here. You don't have a primary Key
> defined on the Proft table and you don't have a clustered index on that
> table either. Both of which are very important. I suggest you drop the
> nonclustered index on Firm_ID, ACCT_NO and create a clustered index on
> Firm_ID, ACCT_NO instead. That will allow the two tables to be joined in
a
> true merge fashion using the clustered indexes and should dramatically cu
t
> down on the time to do this. You might even consider making the clustered
> indexes on both these tables with ACCT_NO as the first column instead of
> Firm_ID. The reason being that this is more selective but this depends a
> lot on how you access these tables. Do you look for Acc_No or Firm_ID mor
e?
> In either case there is no need to have a non-clustered index on Firm_ID i
f
> the clustered index already has Firm_ID as the first column in the index
> expression. Also why does the Order By have the same columns as the group
by
> but in a different order? It may help to have them the same so the engine
> does not potentially have to order twice.
> --
> Andrew J. Kelly SQL MVP
>
> "Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
> message news:95D8D214-FFA2-4C49-A6D9-64AE8CDDF645@.microsoft.com...
>
>|||If the data file keeps growing back to that size then you should leave it
there. Growing a data file is very expensive. There is no penalty for too
much free space but a big one for too little. If Firm_ID is always the same
value I would argue that you don't have it in the Clustered index at all.
The column(s) of the clustered index are appended to the end of all the
nonclustered indexes so you are propagating 4 bytes per row * every
nonclustered index. Let us know how it works out.
Andrew J. Kelly SQL MVP
"Consultant Mark" <ConsultantMark@.discussions.microsoft.com> wrote in
message news:36502EBA-6CFA-4F94-92B8-1402769CE178@.microsoft.com...
> Andrew,
> Thanks. Not quite sure why the order and group by statements have
> different
> field order, but that's easy to fix.
> The mdf for tempdb is the one growing.
> We will try variations of your good suggestions. Firm_id is baggage, part
> of the account number and in this implementation of the database always
> containing the same value, so we will flip that around.
> Monthly we use DTS to add 1 million records to proft and drop and replace
> all rows in the accnt table. Because the proft is so large I drop and
> rebuild all indexes (loading the data in between), so if I have a primary
> key
> I have to deal with all the dependencies, constraints, etc. Proft by the
> way
> is unique on acct_no, firm_id, database_date, so its primary key would
> need
> all three but this field order should be ok.
> With the foreign key constraint connecting proft into accnt I assumed the
> db
> engine would recognize what is supposed to be happening.
> "Andrew J. Kelly" wrote:
>
Sunday, March 11, 2012
Controlling create & drop proc, view privilege
Hi,
Is there a way to allow a user, who has access to a db say "DevDB" as
db_datareader, to only create & drop stored procs and views in DevDB. What
extra permissions does the user need ?
I tried playing with the "grant create proc to user" command. But it lets
the user create procs with him as owner. In the current case, the applicatio
n
needs all objects to be owned by dbo, so the user needs to be able to run
"create proc dbo.tempProc as ..."
In case there is a solution to the above, we might fall into the next trap.
since the user can create procedures with dbo as the owner, if the SP has a
drop table command, that would execute in the owners context and hence would
drop the table. Is that right ? I guess the question is when an SP is
executed does it use the permissions of the owner of the SP or the user
executing the SP
ManiMani
You can EXECUTION permission on the stored procedure for the user
Also ,you can remove him/her from sysadmin fixed server role but he/she
should be member db_owner fixed database and must qualified User.sp
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:0C3FBF63-843E-465E-98C0-4BE9152BF08F@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
trap.
> since the user can create procedures with dbo as the owner, if the SP has
a
> drop table command, that would execute in the owners context and hence
would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani|||1. A user needs to be a member of db_owner or db_ddladmin
roles (or sysadmin) to create a objects owned by dbo.
Members of db_owner and db_ddladmin need to qualify the
owner as dbo.object when they create the objects to be owned
by dbo.
2. It depends first on ownership the ownership chain. If the
ownership chains are intact, the secuirty is checked for
permissions to execute the stored procedure only. If the
ownership chain is broken, permissions are checked on each
branch where the owner of the object is different. You can
find more information in books online under ownership chains
-Sue
On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
<Mani@.discussions.microsoft.com> wrote:
>Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
>db_datareader, to only create & drop stored procs and views in DevDB. What
>extra permissions does the user need ?
>I tried playing with the "grant create proc to user" command. But it lets
>the user create procs with him as owner. In the current case, the applicati
on
>needs all objects to be owned by dbo, so the user needs to be able to run
>"create proc dbo.tempProc as ..."
>In case there is a solution to the above, we might fall into the next trap.
>since the user can create procedures with dbo as the owner, if the SP has a
>drop table command, that would execute in the owners context and hence woul
d
>drop the table. Is that right ? I guess the question is when an SP is
>executed does it use the permissions of the owner of the SP or the user
>executing the SP|||Thanks Uri and Sue for your responses.
"Sue Hoegemeier" wrote:
> 1. A user needs to be a member of db_owner or db_ddladmin
> roles (or sysadmin) to create a objects owned by dbo.
> Members of db_owner and db_ddladmin need to qualify the
> owner as dbo.object when they create the objects to be owned
> by dbo.
> 2. It depends first on ownership the ownership chain. If the
> ownership chains are intact, the secuirty is checked for
> permissions to execute the stored procedure only. If the
> ownership chain is broken, permissions are checked on each
> branch where the owner of the object is different. You can
> find more information in books online under ownership chains
> -Sue
>
> On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
> <Mani@.discussions.microsoft.com> wrote:
>
>
Is there a way to allow a user, who has access to a db say "DevDB" as
db_datareader, to only create & drop stored procs and views in DevDB. What
extra permissions does the user need ?
I tried playing with the "grant create proc to user" command. But it lets
the user create procs with him as owner. In the current case, the applicatio
n
needs all objects to be owned by dbo, so the user needs to be able to run
"create proc dbo.tempProc as ..."
In case there is a solution to the above, we might fall into the next trap.
since the user can create procedures with dbo as the owner, if the SP has a
drop table command, that would execute in the owners context and hence would
drop the table. Is that right ? I guess the question is when an SP is
executed does it use the permissions of the owner of the SP or the user
executing the SP
ManiMani
You can EXECUTION permission on the stored procedure for the user
Also ,you can remove him/her from sysadmin fixed server role but he/she
should be member db_owner fixed database and must qualified User.sp
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:0C3FBF63-843E-465E-98C0-4BE9152BF08F@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
trap.
> since the user can create procedures with dbo as the owner, if the SP has
a
> drop table command, that would execute in the owners context and hence
would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani|||1. A user needs to be a member of db_owner or db_ddladmin
roles (or sysadmin) to create a objects owned by dbo.
Members of db_owner and db_ddladmin need to qualify the
owner as dbo.object when they create the objects to be owned
by dbo.
2. It depends first on ownership the ownership chain. If the
ownership chains are intact, the secuirty is checked for
permissions to execute the stored procedure only. If the
ownership chain is broken, permissions are checked on each
branch where the owner of the object is different. You can
find more information in books online under ownership chains
-Sue
On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
<Mani@.discussions.microsoft.com> wrote:
>Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
>db_datareader, to only create & drop stored procs and views in DevDB. What
>extra permissions does the user need ?
>I tried playing with the "grant create proc to user" command. But it lets
>the user create procs with him as owner. In the current case, the applicati
on
>needs all objects to be owned by dbo, so the user needs to be able to run
>"create proc dbo.tempProc as ..."
>In case there is a solution to the above, we might fall into the next trap.
>since the user can create procedures with dbo as the owner, if the SP has a
>drop table command, that would execute in the owners context and hence woul
d
>drop the table. Is that right ? I guess the question is when an SP is
>executed does it use the permissions of the owner of the SP or the user
>executing the SP|||Thanks Uri and Sue for your responses.
"Sue Hoegemeier" wrote:
> 1. A user needs to be a member of db_owner or db_ddladmin
> roles (or sysadmin) to create a objects owned by dbo.
> Members of db_owner and db_ddladmin need to qualify the
> owner as dbo.object when they create the objects to be owned
> by dbo.
> 2. It depends first on ownership the ownership chain. If the
> ownership chains are intact, the secuirty is checked for
> permissions to execute the stored procedure only. If the
> ownership chain is broken, permissions are checked on each
> branch where the owner of the object is different. You can
> find more information in books online under ownership chains
> -Sue
>
> On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
> <Mani@.discussions.microsoft.com> wrote:
>
>
Controling Names of Agents
Hello there
After i've created my replication I aslo create script for recreating it
again.
However, After i create the replication again the names of my Jobs are being
changed.
Is there a way to create constant name to the replication jobs?
Roy,
not as far as I know. However provided you know the name of the publication,
you can determine the name of the jobs after replication is set up and use
it in code.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This is to prevent two jobs having the same name. There is a "bug" where if
you create two publications with the same name in different database your
agents will disappear in the agents folders. You can still pass the names
you want in your script using the agent_names parameter.
Hilary Cotter
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OQocpByAGHA.324@.TK2MSFTNGP10.phx.gbl...
> Hello there
> After i've created my replication I aslo create script for recreating it
> again.
> However, After i create the replication again the names of my Jobs are
> being
> changed.
> Is there a way to create constant name to the replication jobs?
>
After i've created my replication I aslo create script for recreating it
again.
However, After i create the replication again the names of my Jobs are being
changed.
Is there a way to create constant name to the replication jobs?
Roy,
not as far as I know. However provided you know the name of the publication,
you can determine the name of the jobs after replication is set up and use
it in code.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This is to prevent two jobs having the same name. There is a "bug" where if
you create two publications with the same name in different database your
agents will disappear in the agents folders. You can still pass the names
you want in your script using the agent_names parameter.
Hilary Cotter
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
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OQocpByAGHA.324@.TK2MSFTNGP10.phx.gbl...
> Hello there
> After i've created my replication I aslo create script for recreating it
> again.
> However, After i create the replication again the names of my Jobs are
> being
> changed.
> Is there a way to create constant name to the replication jobs?
>
control where the backup.dat goes
Hey everyone
I have a question for you, I am creating a database as part of the application that I am currently working one. When I create the database the .mdf and .log files go to the Sql directory however the .backup.dat file goes in my application folder. Is there anyway to disable this or have those files be elsewhere programatically.
Thanks
Kenzie
Using the BACKUP statement, you may specifically direct a backup file location.
See Books Online, Topic: BACKUP
You would issue a command similar to this:
BACKUP DATABASE MyDatabase TO DISK = 'D:\MyDataBackups\MyDatabase.bak'Thursday, March 8, 2012
Control over creation of procs & views owned by dbo
Hi,
Is there a way to allow a user, who has access to a db say "DevDB" as
db_datareader, to only create & drop stored procs and views in DevDB. What
extra permissions does the user need ?
I tried playing with the "grant create proc to user" command. But it lets
the user create procs with him as owner. In the current case, the applicatio
n
needs all objects to be owned by dbo, so the user needs to be able to run
"create proc dbo.tempProc as ..."
In case there is a solution to the above, we might fall into the next trap.
since the user can create procedures with dbo as the owner, if the SP has a
drop table command, that would execute in the owners context and hence would
drop the table. Is that right ? I guess the question is when an SP is
executed does it use the permissions of the owner of the SP or the user
executing the SP
ManiThey would have to be a member of the db_ddladmin or db_owner fixed database
roles to create objects in the dbo schema which would give them too many
rights (they would also be able to create tables etc).It's not possible to
give them just a subset of the rights if you want them to create objects in
the dbo schema
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
> application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
> trap.
> since the user can create procedures with dbo as the owner, if the SP has
> a
> drop table command, that would execute in the owners context and hence
> would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani|||Thanks Jasper.
"Jasper Smith" wrote:
> They would have to be a member of the db_ddladmin or db_owner fixed databa
se
> roles to create objects in the dbo schema which would give them too many
> rights (they would also be able to create tables etc).It's not possible to
> give them just a subset of the rights if you want them to create objects i
n
> the dbo schema
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Mani" <Mani@.discussions.microsoft.com> wrote in message
> news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
>
>|||To add on to Jasper's response, you could also change object ownership to
'dbo' with sp_changeobjectowner.
Regarding the second part of your question, stored procedures run in the
security context of the invoking user, not the object owner. Due to
ownership chains, permissions on indirectly referenced objects are not
checked as long as the objects involved have the same owner. Users only
need permissions on only directly referenced objects.
Note that ownership chains apply only to object permissions, not statement
permissions like CREATE. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
> application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
> trap.
> since the user can create procedures with dbo as the owner, if the SP has
> a
> drop table command, that would execute in the owners context and hence
> would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani
Is there a way to allow a user, who has access to a db say "DevDB" as
db_datareader, to only create & drop stored procs and views in DevDB. What
extra permissions does the user need ?
I tried playing with the "grant create proc to user" command. But it lets
the user create procs with him as owner. In the current case, the applicatio
n
needs all objects to be owned by dbo, so the user needs to be able to run
"create proc dbo.tempProc as ..."
In case there is a solution to the above, we might fall into the next trap.
since the user can create procedures with dbo as the owner, if the SP has a
drop table command, that would execute in the owners context and hence would
drop the table. Is that right ? I guess the question is when an SP is
executed does it use the permissions of the owner of the SP or the user
executing the SP
ManiThey would have to be a member of the db_ddladmin or db_owner fixed database
roles to create objects in the dbo schema which would give them too many
rights (they would also be able to create tables etc).It's not possible to
give them just a subset of the rights if you want them to create objects in
the dbo schema
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
> application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
> trap.
> since the user can create procedures with dbo as the owner, if the SP has
> a
> drop table command, that would execute in the owners context and hence
> would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani|||Thanks Jasper.
"Jasper Smith" wrote:
> They would have to be a member of the db_ddladmin or db_owner fixed databa
se
> roles to create objects in the dbo schema which would give them too many
> rights (they would also be able to create tables etc).It's not possible to
> give them just a subset of the rights if you want them to create objects i
n
> the dbo schema
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Mani" <Mani@.discussions.microsoft.com> wrote in message
> news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
>
>|||To add on to Jasper's response, you could also change object ownership to
'dbo' with sp_changeobjectowner.
Regarding the second part of your question, stored procedures run in the
security context of the invoking user, not the object owner. Due to
ownership chains, permissions on indirectly referenced objects are not
checked as long as the objects involved have the same owner. Users only
need permissions on only directly referenced objects.
Note that ownership chains apply only to object permissions, not statement
permissions like CREATE. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:17DE6BDF-E650-4002-8561-D28836F5F620@.microsoft.com...
> Hi,
> Is there a way to allow a user, who has access to a db say "DevDB" as
> db_datareader, to only create & drop stored procs and views in DevDB. What
> extra permissions does the user need ?
> I tried playing with the "grant create proc to user" command. But it lets
> the user create procs with him as owner. In the current case, the
> application
> needs all objects to be owned by dbo, so the user needs to be able to run
> "create proc dbo.tempProc as ..."
> In case there is a solution to the above, we might fall into the next
> trap.
> since the user can create procedures with dbo as the owner, if the SP has
> a
> drop table command, that would execute in the owners context and hence
> would
> drop the table. Is that right ? I guess the question is when an SP is
> executed does it use the permissions of the owner of the SP or the user
> executing the SP
> --
> Mani
Control of flow around "CREATE PROCEDURE"
Hi there.
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:
> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:
> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>
I am trying to write a single script to create some stored procedures. One
of the stored procedures however, refers to a database which may or may not
be present on the server. In the case of that database NOT being present, I
would like to create the stored procedure with different contents (as the
original contents cause script errors when the missing database os referred
to). However, I'm having trouble controlling the flow of execution in the
script around CREATE PROCEDURE as it needs to be the first instruction in a
batch.
Basically I'd like to do something like this:
Use SomeOtherDatabase
GO
IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
CREATE PROCEDURE p_MyStoredProc
AS
SELECT * FROM MyDatabaseName.dbo.SomeTable
GO
ELSE --The database doesn't exist
CREATE PROCEDURE p_MyStoredProc
AS
PRINT 'The Database doesnt exist on this server'
GO
The reason I want to take this approach is to avoid script errors when the
script is run on servers where that database is missing.
Any ideas how I should go about this?
Any help would be much appreciated!!Len,
Kinda questionable approach. Try something like this using Dynamic SQL:
IF DB_ID('MyDatabaseName') IS NOT NULL
EXEC('CREATE PROCEDURE ...')
ELSE
EXEC('CREATE PROCEDURE ...')
Also see Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"len" <len@.discussions.microsoft.com> wrote in message
news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
> Hi there.
> I am trying to write a single script to create some stored procedures. One
> of the stored procedures however, refers to a database which may or may
> not
> be present on the server. In the case of that database NOT being present,
> I
> would like to create the stored procedure with different contents (as the
> original contents cause script errors when the missing database os
> referred
> to). However, I'm having trouble controlling the flow of execution in the
> script around CREATE PROCEDURE as it needs to be the first instruction in
> a
> batch.
> Basically I'd like to do something like this:
> Use SomeOtherDatabase
> GO
> IF( DB_ID('MyDatabaseName') is not NULL ) --if the database exists
> CREATE PROCEDURE p_MyStoredProc
> AS
> SELECT * FROM MyDatabaseName.dbo.SomeTable
> GO
> ELSE --The database doesn't exist
> CREATE PROCEDURE p_MyStoredProc
> AS
> PRINT 'The Database doesnt exist on this server'
> GO
> The reason I want to take this approach is to avoid script errors when the
> script is run on servers where that database is missing.
> Any ideas how I should go about this?
> Any help would be much appreciated!!|||Why not just fix the code that is calling the wrong proc? Seems like an
unusual architecture if neither your client code or your procs will
know whether a database exists or not.
Where possible I find it better to reference other databases only in
views and then write procs against the views. That way views act as
your database indirection and the database names are hard-coded in as
few places as possible.
David Portas
SQL Server MVP
--|||Perfect - thanks! - I had tried dynamic SQL but got stuck on sp_executesql a
s
my stored proc was over 4000 chars long - Erland's article covers this thoug
h
"Jerry Spivey" wrote:
> Len,
> Kinda questionable approach. Try something like this using Dynamic SQL:
> IF DB_ID('MyDatabaseName') IS NOT NULL
> EXEC('CREATE PROCEDURE ...')
> ELSE
> EXEC('CREATE PROCEDURE ...')
> Also see Erland's article:
> http://www.sommarskog.se/dynamic_sql.html
> HTH
> Jerry
> "len" <len@.discussions.microsoft.com> wrote in message
> news:7F82FDC3-CE0F-427E-8BBB-50DD3798E4F8@.microsoft.com...
>
>|||I'm not too happy with the architecture myself! Unfortunately it's a legacy
thing whereby my principal aim was just to minimize the number of scripts
needed to install some additional stored procs.
"David Portas" wrote:
> Why not just fix the code that is calling the wrong proc? Seems like an
> unusual architecture if neither your client code or your procs will
> know whether a database exists or not.
> Where possible I find it better to reference other databases only in
> views and then write procs against the views. That way views act as
> your database indirection and the database names are hard-coded in as
> few places as possible.
> --
> David Portas
> SQL Server MVP
> --
>
Control if a SQL database exists before its creation or deletion
Hi,
I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors:
Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist".
Before creating or dropping a database, I should control if it exists or not...
Is there a method to do that?
I found that such control for a table is the following one (in this case, I drop the table only if it exists):
I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors:
Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist".
Before creating or dropping a database, I should control if it exists or not...
Is there a method to do that?
I found that such control for a table is the following one (in this case, I drop the table only if it exists):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
I tried to adapt the statement to the database case, modifying it as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[Database1]') and OBJECTPROPERTY(id, N'IsDatabase') = 1)
drop database [Database1]
but it didn't function (it was a blind attempt).
Can you suggest me a statement to do that?
Thank you very much
You can try:
if db_id('myDb') is not null print 'DB exists'
Alter NULL, NOT NULL check as appropriate for what you want to check
/Kenneth
|||Ok it functions correctly, thank you KeWinControl Flow Diverging then Converging Back
I am somewhat new to SSIS and have a question on branching/control flow. We have 3 manufacturing facilities on AS400. Requirement were to create independent SSIS packages in case certain facilities were down, to not interrupt or fail other facilities. to be honest, I didn't want to maintain 3 * (each plant package) packages and decided to create one that takes in a command line parameter and will create seperate batch files (requirements for our archaic scheduling system) that pass in the plant to be processed.
Everything works fine and the packages analyze the passed parameter with no problem and evaluate it when calling stored procedures int a SQL Task. I am required (I think - let me know if I'm wrong here) to have 3 seperate Data Flow tasks depending on which plant is being processed because they are different connections (same server, different DBs - AS400). Which Data Flow Taks run is based on a precedence constraint that analyzes the passed in plant number.
After the Data Flow Tasks are complete, I'd like to merge back into one path again (even though1 out of N will only run at a time) because they are all simply stored proc calls with different parameters. It seemed silly to me to have the same tasks repeated 3 different times. I looked into started up tasks after events, but I don't know a lot about it and it seems as though I would still be maintaining 3 different paths anyway.
Can anyone point me in the right direction?
P.S. (Even if the sollution is to have 1 Data Flow Task I'm still interested in knowing how to converge paths back together)
You can take an upstream control flow item and hook it to three data flows. Then coming out of each data flow you can hook them up to one downstream control flow item. Then you'll just have to work with your precedence constraints to ensure that if one doesn't run the whole package doesn't fail.|||If I'm undertanding you correctly, that's exactly what I tried. I should have mentioned that. The problem is that the package stops (with success) after the data flow is complete. I assumed it was because all but one of the paths coming to the post-data-flow task did not even execute (which is what I want) so they did not pass a complete or success flag tp the first post-data-flow task. So in short, everything runs great, it just stops as soon as the data flow task is complete.
Is there some other condition that I can put on the post-data-flow constraints? Or another toolbox item that I can use to connect them?
BTW. I appreciate the quick response!
|||You can change the precedence of those connectors. Double click on the connector line and you'll see the options available to you. Look at using "OR" in the multiple conditions.|||Yahtzee!!
Totally my bad. I had THOUGHT I set them all to "OR", but your suggestion made me check again. One was set to "AND".
Much appreciated!
Contraints
Hi,
I have this (syntax not verified):
CREATE TABLE Colors (
Id INT PRIMARY KEY,
Color VARCHAR(20) NOT NULL
)
GO
INSERT INTO Colors VALUES (1, 'red')
GO
INSERT INTO Colors VALUES (2, 'green')
GO
INSERT INTO Colors VALUES (3, 'blue')
GO
CREATE TABLE Candy (
Id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
ColorId INT REFERENCES Colors(Id)
)
GO
INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
GO
INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
GO
INSERT INTO Candy VALUES (3, 'SweetMama', 2)
GO
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT REFERENCES Candy(Id)
)
Now, you can put all kinds of candy in a pack:
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 3)
GO
This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag 1.
Now, I want to make sure that no bag contains more than 2 candies of the
same kind and same color.
So the following should return ZERO records:
SELECT * FROM
CandyPacks P1, Candy C1, Colors CLR1,
CandyPacks P2, Candy C2, Colors CLR2
WHERE
C1.Id = P1.CandyId
AND CLR1.Id = C1.ColorId
AND C2.Id = P2.CandyId
AND CLR2.Id = C2.ColorId
AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
Now, perhaps this query sucks.. please correct, but my point should be
clear.
This is only an example for similar situations. Perhaps I could use primary
key constraints or something, but in other situations I have some 'complex
query' which should not yield results..
How can I turn a query like the above into a constraint on the CandyPacks
table?
LisaHi Lisa
Please consider using INSTEAD OF INSERT Trigger on the Table.
If the condition is satisified, insert the data
hope the problem is solved? If there are any more issues, do not hesitate to
revert back
thanks and regards
Chandra
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||I would think that the best way to accomplish this would be using a trigger.
I would also consider changing the structure of the tables such that a
CandyPacks record contians the packid, a colorid, and a candyid. You may
find it easier to enforce the constraint with the data structured this way.
If you leave it as you have it you will have to write a more complex query i
n
the trigger.
"Chandra" wrote:
> Hi Lisa
> Please consider using INSTEAD OF INSERT Trigger on the Table.
> If the condition is satisified, insert the data
> hope the problem is solved? If there are any more issues, do not hesitate
to
> revert back
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||Try,
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT not null REFERENCES Candy(Id),
quantity int not null default(1) check(quantity = 1 or quantity = 2),
constraint pk_CandyPacks primary key (PackId, CandyId)
)
AMB
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||You could use a check constraint to see wheter how many candy are in the
package:
CHECK(Your Select statement)
Further help, just raise a hand.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
> 1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use
> primary key constraints or something, but in other situations I have some
> 'complex query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>|||*raises hand*
My actual table is:
CREATE TABLE MatchResults
(
ParentId INT NOT NULL REFERENCES Bedrijven(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES Bedrijven(Id),
PRIMARY KEY(ParentId,DatMatch,Id),
Updated DATETIME DEFAULT GETDATE(),
Deleted BIT DEFAULT 0
)
And no data may be added to this table that would return any records in this
query:
SELECT COUNT(*)
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
So the above query should always yield 0.
So I want to add a contraint to the above table so that the query below
always is 0.
I don't want to use INSTEAD OF INSERT trigger...
How do I turn it into a table constraint?
Thanks,
Lisa
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ePt3i5%23SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> You could use a check constraint to see wheter how many candy are in the
> package:
> CHECK(Your Select statement)
> Further help, just raise a hand.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
> news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
>|||On Wed, 4 May 2005 02:50:10 +0200, Lisa Pearlson wrote:
>*raises hand*
>My actual table is:
>CREATE TABLE MatchResults
>(
>ParentId INT NOT NULL REFERENCES Bedrijven(Id),
>DatMatch DATETIME NOT NULL,
>Id INT NOT NULL REFERENCES Bedrijven(Id),
> PRIMARY KEY(ParentId,DatMatch,Id),
>Updated DATETIME DEFAULT GETDATE(),
>Deleted BIT DEFAULT 0
> )
>
>And no data may be added to this table that would return any records in thi
s
>query:
>SELECT COUNT(*)
>FROM MatchResults M, Bedrijven B1, Bedrijven B2
>WHERE M.Deleted!=1
>AND B1.Id = M.ParentId
>AND B2.Id = M.Id
>AND (B1.ProfielId!=3 OR B2.ProfielId=3)
>So the above query should always yield 0.
>So I want to add a contraint to the above table so that the query below
>always is 0.
>I don't want to use INSTEAD OF INSERT trigger...
>How do I turn it into a table constraint?
>Thanks,
>Lisa
Hi Lisa,
Impossible with your current design, since a CHECK constraint can't use
any other data than the data in the same row.
The workaround is to replace the Bedrijven table with two tables: one
for the bedrijven with profiel equal to 3 and one for the bedrijven with
profiel unequal to 3:
CREATE TABLE BedrijvenType3
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId = 3)
)
CREATE TABLE BedrijvenOverig
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId <> 3)
)
CREATE TABLE MatchResults
(ParentId INT NOT NULL REFERENCES BedrijvenType3(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES BedrijvenOverig(Id),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0
)
Note that I also included a Deleted column in the two bedrijven tables,
because the referential integrity can't be made dependant of the Deleted
column in the MatchResults. If you do want to really remove rows from
the bedrijven tables (or if I somehow misunderstood your requirements),
you'll have to use a different technique: make a redundant copy of the
ProfielId for both Id and ParentId (and possibly add a redundant UNIQUE
constraint to the Bedrijven table, so that the data stays synch'ed):
ALTER TABLE Bedrijven
ADD UNIQUE (Id, ProfielId)
CREATE TABLE MatchResults
(ParentId INT NOT NULL,
ParentIdProfiel INT NOT NULL,
FOREIGN KEY (ParentId, ParentIdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL,
IdProfiel INT NOT NULL,
FOREIGN KEY (Id, IdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0,
CHECK (Deleted = 1 OR (ParentIdProfiel = 3 AND IdProfiel <> 3)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why do you believe that every table has magical, universal column
called "id"? In an RDBMS, there are lots of kinds of identifiers,
not like a 1950's file system record number. And have you ever
researched for industry standards, like the Land color number or
Pantone numbers? Let's clean up the sample DDL:
CREATE TABLE Colors
(pantone_nbr INTEGER PRIMARY KEY,
pantone_description VARCHAR(20) NOT NULL);
CREATE TABLE Candies
(upc CHAR(13) NOT NULL PRIMARY KEY,
candy_name VARCHAR(20) NOT NULL,
pantone_nbr INTEGER REFERENCES Colors(pantone_nbr));
CREATE TABLE CandyPacks
(pack_upc CHAR(13) NOT NULL,
candy_upc CHAR(13) NOT NULL
REFERENCES Candies(upc),
PRIMARY KEY (pack_upc, candy_upc)
);
same kind AND same color. <<
The first condition is enforced by a PRIMARY KEY. In full SQL-92 you
can put this into a CHECK();
CHECK(1 = ALL (SELECT COUNT(C1.pantone_nbr)
FROM Candies AS C1
WHERE C1.upc = CandyPacks.candyupc))
In SQL server, you will need to use a trigger on the packages table.
I have this (syntax not verified):
CREATE TABLE Colors (
Id INT PRIMARY KEY,
Color VARCHAR(20) NOT NULL
)
GO
INSERT INTO Colors VALUES (1, 'red')
GO
INSERT INTO Colors VALUES (2, 'green')
GO
INSERT INTO Colors VALUES (3, 'blue')
GO
CREATE TABLE Candy (
Id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
ColorId INT REFERENCES Colors(Id)
)
GO
INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
GO
INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
GO
INSERT INTO Candy VALUES (3, 'SweetMama', 2)
GO
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT REFERENCES Candy(Id)
)
Now, you can put all kinds of candy in a pack:
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 3)
GO
This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag 1.
Now, I want to make sure that no bag contains more than 2 candies of the
same kind and same color.
So the following should return ZERO records:
SELECT * FROM
CandyPacks P1, Candy C1, Colors CLR1,
CandyPacks P2, Candy C2, Colors CLR2
WHERE
C1.Id = P1.CandyId
AND CLR1.Id = C1.ColorId
AND C2.Id = P2.CandyId
AND CLR2.Id = C2.ColorId
AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
Now, perhaps this query sucks.. please correct, but my point should be
clear.
This is only an example for similar situations. Perhaps I could use primary
key constraints or something, but in other situations I have some 'complex
query' which should not yield results..
How can I turn a query like the above into a constraint on the CandyPacks
table?
LisaHi Lisa
Please consider using INSTEAD OF INSERT Trigger on the Table.
If the condition is satisified, insert the data
hope the problem is solved? If there are any more issues, do not hesitate to
revert back
thanks and regards
Chandra
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||I would think that the best way to accomplish this would be using a trigger.
I would also consider changing the structure of the tables such that a
CandyPacks record contians the packid, a colorid, and a candyid. You may
find it easier to enforce the constraint with the data structured this way.
If you leave it as you have it you will have to write a more complex query i
n
the trigger.
"Chandra" wrote:
> Hi Lisa
> Please consider using INSTEAD OF INSERT Trigger on the Table.
> If the condition is satisified, insert the data
> hope the problem is solved? If there are any more issues, do not hesitate
to
> revert back
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||Try,
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT not null REFERENCES Candy(Id),
quantity int not null default(1) check(quantity = 1 or quantity = 2),
constraint pk_CandyPacks primary key (PackId, CandyId)
)
AMB
"Lisa Pearlson" wrote:
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||You could use a check constraint to see wheter how many candy are in the
package:
CHECK(Your Select statement)
Further help, just raise a hand.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
> 1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use
> primary key constraints or something, but in other situations I have some
> 'complex query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>|||*raises hand*
My actual table is:
CREATE TABLE MatchResults
(
ParentId INT NOT NULL REFERENCES Bedrijven(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES Bedrijven(Id),
PRIMARY KEY(ParentId,DatMatch,Id),
Updated DATETIME DEFAULT GETDATE(),
Deleted BIT DEFAULT 0
)
And no data may be added to this table that would return any records in this
query:
SELECT COUNT(*)
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
So the above query should always yield 0.
So I want to add a contraint to the above table so that the query below
always is 0.
I don't want to use INSTEAD OF INSERT trigger...
How do I turn it into a table constraint?
Thanks,
Lisa
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ePt3i5%23SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> You could use a check constraint to see wheter how many candy are in the
> package:
> CHECK(Your Select statement)
> Further help, just raise a hand.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
> news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
>|||On Wed, 4 May 2005 02:50:10 +0200, Lisa Pearlson wrote:
>*raises hand*
>My actual table is:
>CREATE TABLE MatchResults
>(
>ParentId INT NOT NULL REFERENCES Bedrijven(Id),
>DatMatch DATETIME NOT NULL,
>Id INT NOT NULL REFERENCES Bedrijven(Id),
> PRIMARY KEY(ParentId,DatMatch,Id),
>Updated DATETIME DEFAULT GETDATE(),
>Deleted BIT DEFAULT 0
> )
>
>And no data may be added to this table that would return any records in thi
s
>query:
>SELECT COUNT(*)
>FROM MatchResults M, Bedrijven B1, Bedrijven B2
>WHERE M.Deleted!=1
>AND B1.Id = M.ParentId
>AND B2.Id = M.Id
>AND (B1.ProfielId!=3 OR B2.ProfielId=3)
>So the above query should always yield 0.
>So I want to add a contraint to the above table so that the query below
>always is 0.
>I don't want to use INSTEAD OF INSERT trigger...
>How do I turn it into a table constraint?
>Thanks,
>Lisa
Hi Lisa,
Impossible with your current design, since a CHECK constraint can't use
any other data than the data in the same row.
The workaround is to replace the Bedrijven table with two tables: one
for the bedrijven with profiel equal to 3 and one for the bedrijven with
profiel unequal to 3:
CREATE TABLE BedrijvenType3
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId = 3)
)
CREATE TABLE BedrijvenOverig
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId <> 3)
)
CREATE TABLE MatchResults
(ParentId INT NOT NULL REFERENCES BedrijvenType3(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES BedrijvenOverig(Id),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0
)
Note that I also included a Deleted column in the two bedrijven tables,
because the referential integrity can't be made dependant of the Deleted
column in the MatchResults. If you do want to really remove rows from
the bedrijven tables (or if I somehow misunderstood your requirements),
you'll have to use a different technique: make a redundant copy of the
ProfielId for both Id and ParentId (and possibly add a redundant UNIQUE
constraint to the Bedrijven table, so that the data stays synch'ed):
ALTER TABLE Bedrijven
ADD UNIQUE (Id, ProfielId)
CREATE TABLE MatchResults
(ParentId INT NOT NULL,
ParentIdProfiel INT NOT NULL,
FOREIGN KEY (ParentId, ParentIdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL,
IdProfiel INT NOT NULL,
FOREIGN KEY (Id, IdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0,
CHECK (Deleted = 1 OR (ParentIdProfiel = 3 AND IdProfiel <> 3)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why do you believe that every table has magical, universal column
called "id"? In an RDBMS, there are lots of kinds of identifiers,
not like a 1950's file system record number. And have you ever
researched for industry standards, like the Land color number or
Pantone numbers? Let's clean up the sample DDL:
CREATE TABLE Colors
(pantone_nbr INTEGER PRIMARY KEY,
pantone_description VARCHAR(20) NOT NULL);
CREATE TABLE Candies
(upc CHAR(13) NOT NULL PRIMARY KEY,
candy_name VARCHAR(20) NOT NULL,
pantone_nbr INTEGER REFERENCES Colors(pantone_nbr));
CREATE TABLE CandyPacks
(pack_upc CHAR(13) NOT NULL,
candy_upc CHAR(13) NOT NULL
REFERENCES Candies(upc),
PRIMARY KEY (pack_upc, candy_upc)
);
same kind AND same color. <<
The first condition is enforced by a PRIMARY KEY. In full SQL-92 you
can put this into a CHECK();
CHECK(1 = ALL (SELECT COUNT(C1.pantone_nbr)
FROM Candies AS C1
WHERE C1.upc = CandyPacks.candyupc))
In SQL server, you will need to use a trigger on the packages table.
Wednesday, March 7, 2012
Continuos Form/Letter
I need to create data report that has repeating rows and it is composed of
field names. This report looks like a payment book. I don't know how to do
this, because the report show me the first record but not the next 5 records.
Also, it occurs with the letters. If I have 4 customers that would receive
letters, only the first one received it(show the print preview).
I would appreciate any help.Please specify your question, i can´t get through to your problem.
Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"mlugoc" <mlugoc@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E8094B21-0192-498C-8EE0-F2F243678C46@.microsoft.com...
>I need to create data report that has repeating rows and it is composed of
> field names. This report looks like a payment book. I don't know how to do
> this, because the report show me the first record but not the next 5
> records.
> Also, it occurs with the letters. If I have 4 customers that would receive
> letters, only the first one received it(show the print preview).
> I would appreciate any help.|||I want to create a payment book. Each sheet of the payment book is a fifth
part of one 8.5" x 14" (legal paper size). Each sheet has 5 coupons from
payment book. The payment book might have up to 15 coupons. My problem is
that only the first coupon of the first sheet is printed. How can I do to
print the rest of the payment book?
I examined the query and it return the correct rows.
"Jens Sü�meyer" wrote:
> Please specify your question, i can´t get through to your problem.
> Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "mlugoc" <mlugoc@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E8094B21-0192-498C-8EE0-F2F243678C46@.microsoft.com...
> >I need to create data report that has repeating rows and it is composed of
> > field names. This report looks like a payment book. I don't know how to do
> > this, because the report show me the first record but not the next 5
> > records.
> >
> > Also, it occurs with the letters. If I have 4 customers that would receive
> > letters, only the first one received it(show the print preview).
> >
> > I would appreciate any help.
>
>
field names. This report looks like a payment book. I don't know how to do
this, because the report show me the first record but not the next 5 records.
Also, it occurs with the letters. If I have 4 customers that would receive
letters, only the first one received it(show the print preview).
I would appreciate any help.Please specify your question, i can´t get through to your problem.
Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"mlugoc" <mlugoc@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E8094B21-0192-498C-8EE0-F2F243678C46@.microsoft.com...
>I need to create data report that has repeating rows and it is composed of
> field names. This report looks like a payment book. I don't know how to do
> this, because the report show me the first record but not the next 5
> records.
> Also, it occurs with the letters. If I have 4 customers that would receive
> letters, only the first one received it(show the print preview).
> I would appreciate any help.|||I want to create a payment book. Each sheet of the payment book is a fifth
part of one 8.5" x 14" (legal paper size). Each sheet has 5 coupons from
payment book. The payment book might have up to 15 coupons. My problem is
that only the first coupon of the first sheet is printed. How can I do to
print the rest of the payment book?
I examined the query and it return the correct rows.
"Jens Sü�meyer" wrote:
> Please specify your question, i can´t get through to your problem.
> Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "mlugoc" <mlugoc@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E8094B21-0192-498C-8EE0-F2F243678C46@.microsoft.com...
> >I need to create data report that has repeating rows and it is composed of
> > field names. This report looks like a payment book. I don't know how to do
> > this, because the report show me the first record but not the next 5
> > records.
> >
> > Also, it occurs with the letters. If I have 4 customers that would receive
> > letters, only the first one received it(show the print preview).
> >
> > I would appreciate any help.
>
>
Continuation of emailing an excel file
Hi Rafael,
I need to create a new excel file daily and then need to mail it.
The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.
Any idea why this is happening?So iam unable to schedule as a job...
Thanks,
Vani.
This is more of an integration services forum question than data access forum question. Moving this thread to SQL IS forums.|||You might try setting the DelayValidation property true on the data flow task and the connection manager. You might take a look at this post for more information on dynamically creating Excel files in SSIS.
http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html
Saturday, February 25, 2012
contatenation of table column values into xquery expressions
How could I create XPath expressions from the table column values
below so that I can use them in an XPath query.
RuleID PropertyToTest op value score
1 property1 > 500000 10
2 property1 < 500000 5
3 propertyboolean1 = Yes 10
4 propertyboolean1 = No 5
I will be applying the expressions created from the values above against
objects which have been serialized into an XML document, eg;
CustomerID 1
property1 10000
propertyboolean1 Yes
CustomerID2
property1 600000
propertyboolean1 No
If the expressions applied against the objects in the XML doc are true, the
score will be added to an accumulated score for each Customer.
Thank you very much for any help. -hazz
its ok, I got over this idea...pleading temporary insanity for even trying
to go there. -hazz
"hazz" <greghazzard@.nospamcomcast.net> wrote in message
news:%23dzIetWgFHA.3316@.TK2MSFTNGP14.phx.gbl...
> How could I create XPath expressions from the table column values
> below so that I can use them in an XPath query.
> RuleID PropertyToTest op value score
> 1 property1 > 500000 10
> 2 property1 < 500000 5
> 3 propertyboolean1 = Yes 10
> 4 propertyboolean1 = No 5
> I will be applying the expressions created from the values above against
> objects which have been serialized into an XML document, eg;
> CustomerID 1
> property1 10000
> propertyboolean1 Yes
> CustomerID2
> property1 600000
> propertyboolean1 No
> If the expressions applied against the objects in the XML doc are true,
> the
> score will be added to an accumulated score for each Customer.
> Thank you very much for any help. -hazz
>
>
below so that I can use them in an XPath query.
RuleID PropertyToTest op value score
1 property1 > 500000 10
2 property1 < 500000 5
3 propertyboolean1 = Yes 10
4 propertyboolean1 = No 5
I will be applying the expressions created from the values above against
objects which have been serialized into an XML document, eg;
CustomerID 1
property1 10000
propertyboolean1 Yes
CustomerID2
property1 600000
propertyboolean1 No
If the expressions applied against the objects in the XML doc are true, the
score will be added to an accumulated score for each Customer.
Thank you very much for any help. -hazz
its ok, I got over this idea...pleading temporary insanity for even trying
to go there. -hazz
"hazz" <greghazzard@.nospamcomcast.net> wrote in message
news:%23dzIetWgFHA.3316@.TK2MSFTNGP14.phx.gbl...
> How could I create XPath expressions from the table column values
> below so that I can use them in an XPath query.
> RuleID PropertyToTest op value score
> 1 property1 > 500000 10
> 2 property1 < 500000 5
> 3 propertyboolean1 = Yes 10
> 4 propertyboolean1 = No 5
> I will be applying the expressions created from the values above against
> objects which have been serialized into an XML document, eg;
> CustomerID 1
> property1 10000
> propertyboolean1 Yes
> CustomerID2
> property1 600000
> propertyboolean1 No
> If the expressions applied against the objects in the XML doc are true,
> the
> score will be added to an accumulated score for each Customer.
> Thank you very much for any help. -hazz
>
>
Subscribe to:
Posts (Atom)