problem with Log

Hello,

Could anybody let me know why is this happening while executing a stored procedure through asp.net

Log Entry string is too long. A string written to the event log cannot exceed 32766 characters.

general

The message sounds like you tried to write a very large string to you system event log.
Possibly as a result of throwing an exception when trying to call the stored proc.

Do you have an Exception logging mechanism in place that is configured to put exception messages into your system Event Log?

Can you step into the code to see any possible exception when you make the stored proc call, or turn off exception handling/logging for this call and let the exception go to the page.

sql

Problem with locking in Selects

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

Regards

Bjrn(bjornsuneandersen@.gmail.com) writes:

Quote:

Originally Posted by

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.
>
The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.
>
I have also checked if there are any locks holding the other SPs back
- there isn't any.
>
So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem with linked server stored proc in a maintenance plan

Hello there,

I have a scenario where I need a few stored procs to auto-execute on an hourly basis so I thought it would be nicely done in a maintenance plan job list. I have experience with this in sql 2000 but I am struggling with sql 2005.

I have been struggling with my maintenance plan to successfully run the 2 jobs that it has to complete:
1) execute a stored proc that creates/updates a client in the Client table on the local server
(This step works fine without hassles)
2) execute a stored proc that synchronizes this entry with a database on another server. This stored proc works fine outside the maintenance plan, but inside the maintenance plan job it gives me an error :
Executed as user: NT AUTHORITY\SYSTEM. Cannot roll back T1. No transaction or savepoint of that name was found. [SQLSTATE 25000] (Error 6401)

I have tried looking on the net and forum to see whether i can solve this but i am stuck. What do I have to keep in mind executing this stored proc as a maintenance plan? What am i missing.

Thanks for any advice
Mike
Doesn't anyone know what this could be?

I believe that it has something to do with permissions or security but don't know for sure.|||

Mike,

It looks like the error is being caused by embedded transactions. Then a rollback is occurring due to some event (possibly one of the insert/update further ahead failing and issuing a rollback, which tries to rollback ALL transactions.

I found this article, because I'm seeing the same thing, and looking at the code that's failing, I do have several nested transactions.

http://www.informit.com/articles/article.asp?p=26657&seqNum=5&rl=1

It sounds like we might be having the same problem.

Hope this helps.

Bill

sql

Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary
> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>
|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>
|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>
|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>

Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance this
>>sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
>> My example stored procedure and plain sql are totally equivilent - one
>> works the other does not.
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Dan,
>> you are not making sense to me. % means "any string of zero or more
>> characters". WHERE branch LIKE '%%' is equivilent to not having the
>> WHERE clause at all. % is not a "wild card character." My example
>> stored procedure and plain sql are totally equivilent - one works the
>> other does not.
>> Gary
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>>
>

Problem with LIKE %

I have a stored procedure:
CREATE PROCEDURE SearchHistoryClient
@.TextContain nvarchar(1000),
@.Email nvarchar(512),
@.Operator_URI nvarchar(512),
@.DateFrom DateTime,
@.DateTo DateTime,
@.Company_ID int
AS
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL = '
SELECT
tblTemp.ChatTime,
tblTemp.Client_ID,
tblTemp.Client_SIPURI,
tblOperator.Operator_ID,
tblOperator.Operator_SIPURI,
tblOperator.Operator_Alias,
tblTemp.ChatTranscript
FROM
(SELECT * FROM tblSupportClient INNER JOIN tblClient ON
(tblSupportClient.ClientID = tblClient.Client_ID))
AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
tblOperator.Operator_ID
WHERE
(tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
(tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
''' + cast(@.DateTo as nvarchar) + ''')
'
SELECT @.TextContain = '%'+ @.TextContain + '%';
-- Check if keyword is specified
IF (@.TextContain <> "")
BEGIN
SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
cast(@.TextContain as nvarchar) + ''') '
END
...
EXEC(@.SQL)
It always return 0 rows if @.TextContain contains more than a word.
e.g. if you call the procedure and the input string for @.TextContain is
"Hello"
then it returns some rows if they contains "Hello" string. But when you
specify "Hello John" it won't work correctly, though in your database the
string Hello John... exists.
Can anyone help me?Can you do a PRINT of @.SQL before executing, and see how the string is
formed? Btw, why are you using dynamic SQL?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John C#" <John C#@.discussions.microsoft.com> wrote in message
news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
> I have a stored procedure:
> CREATE PROCEDURE SearchHistoryClient
> @.TextContain nvarchar(1000),
> @.Email nvarchar(512),
> @.Operator_URI nvarchar(512),
> @.DateFrom DateTime,
> @.DateTo DateTime,
> @.Company_ID int
> AS
> DECLARE @.SQL nvarchar(4000)
> SELECT @.SQL = '
> SELECT
> tblTemp.ChatTime,
> tblTemp.Client_ID,
> tblTemp.Client_SIPURI,
> tblOperator.Operator_ID,
> tblOperator.Operator_SIPURI,
> tblOperator.Operator_Alias,
> tblTemp.ChatTranscript
> FROM
> (SELECT * FROM tblSupportClient INNER JOIN tblClient ON
> (tblSupportClient.ClientID = tblClient.Client_ID))
> AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
> tblOperator.Operator_ID
> WHERE
> (tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
> (tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
> ''' + cast(@.DateTo as nvarchar) + ''')
> '
> SELECT @.TextContain = '%'+ @.TextContain + '%';
> -- Check if keyword is specified
> IF (@.TextContain <> "")
> BEGIN
> SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
> cast(@.TextContain as nvarchar) + ''') '
> END
> ...
> EXEC(@.SQL)
> --
> It always return 0 rows if @.TextContain contains more than a word.
> e.g. if you call the procedure and the input string for @.TextContain is
> "Hello"
> then it returns some rows if they contains "Hello" string. But when you
> specify "Hello John" it won't work correctly, though in your database the
> string Hello John... exists.
> Can anyone help me?|||Hi,
I tried to create a new table with a field of ntext type and then I use SQL
command to select, it works. However, it still does not work with fields
marked <long text>. Is it about type error? What is the problem here?
"Narayana Vyas Kondreddi" wrote:

> Can you do a PRINT of @.SQL before executing, and see how the string is
> formed? Btw, why are you using dynamic SQL?
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John C#" <John C#@.discussions.microsoft.com> wrote in message
> news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
>
>

Problem with install if machine.config username and password are stored in registry

Hi,
For information.
It looks like the SQL Reporting Services install doesn't work properly
if you have followed Microsoft's advice and have stored the server's
.net credentials (in the machine.config <processModel> element) in the
registry using aspnet_setreg. (You would do this to remove the plain
text entries from the machine.config file).
I have tried this several times and each time, the install just reads
the username and password entries as text, and can't deal with the
fact that these entries are actually pointing to the registry for the
credentials.
When you get to the Service Account screen on the install wizard, the
section entitled "The Report Server Web service will run under the
following account:" shows the following:
registy:HKLM\SOFTWARE\...\ASPNET_SETREG,userName
(or whatever the location is to your encrypted machine.config
settings).
The install starts after you've completed all of the screens in the
wizard, but then can't start the ReportServer web service
(unsurprisingly!).
The only way that I can see to get round this is to change the
machine.config processModel section before the SQL Reporting Services
install so that the userName and password entries are present in
plaintext in the file. This isn't great - particularly if your
entries have been stored in the registry because it's impossible to
find out what the password is if you haven't got a note of it (and
maybe only the Security team were allowed to have the password, etc.).
It also means that there's a possibility that .net things will stop
working on your server while you're doing the install (ie. just
because you don't really want to have to change those entries, for
risk of typos, etc.).
I hope these comments are useful.
Regards,
Rich WFor info, the same is true for when you install SQL Reporting Services
SP1 - ie. you have to change the machine.config processModel username
and password attributes so that they are not being read from the
registry.
Regards,
Rich

problem with IN (@intParameter) in a stored procedure

I want to pass a list of integer values as a parameter for a stored procedure where the criteria column is an integer

I understand what the problem is but I cannot seem to find a solution, I can't pass it as a varchar because the column is an int and I can't pass it as an in because of the commas in the list

SELECTAppId, ApplicationName, NextPage, AppData, GrpLogo, DeptLogin
FROMdbo.TblApplication
WHERE(AppIdIN(@.intParameter))
ORDER BYSortOrder, ApplicationName

any help please

This question has been answered a few times, with sample code in the solution. Please search in these forums. I am sure you will find it.|||Search for the Split function.|||

thanks for that I found304221 Terri Morton's bit did the trick

|||

I did as you suggested and found 304221 from Terri Morton's did just what I wanted

Many Thanks

problem with images on the report

Hello All,
I am using Image control with database sourced images on my reports. The
images are stored in WMF format.
There is no problem when the report is viewed in web browser but when I
export it as a PDF or TIFF, the images are not shown and a cross (X) is
shown instead of the image.
Has anyone faced the same problem? Any help is solving this?
Regards,
AtifHave you tried converting your WMF files into one of the supported formats?
The supported formats are bmp, jpg, gif, png and jpeg.
Charles Kangai, MCT, MCDBA
"Abdus Salam Atif" wrote:
> Hello All,
> I am using Image control with database sourced images on my reports. The
> images are stored in WMF format.
> There is no problem when the report is viewed in web browser but when I
> export it as a PDF or TIFF, the images are not shown and a cross (X) is
> shown instead of the image.
> Has anyone faced the same problem? Any help is solving this?
> Regards,
> Atif
>
>|||I have also tried to convert the WMF files to PNG, which is the only
accepted format for my application and web, this gives me a different
behavior. The reports look fine in the preview and I am also able to
generate a correct PDF from the preview window but not showing images when I
open the report in the web browser.
Regards,
Atif
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:ABD7F597-8056-4BDA-B4B9-7D5E7A9DA017@.microsoft.com...
> Have you tried converting your WMF files into one of the supported
formats?
> The supported formats are bmp, jpg, gif, png and jpeg.
> Charles Kangai, MCT, MCDBA
> "Abdus Salam Atif" wrote:
> > Hello All,
> >
> > I am using Image control with database sourced images on my reports. The
> > images are stored in WMF format.
> >
> > There is no problem when the report is viewed in web browser but when I
> > export it as a PDF or TIFF, the images are not shown and a cross (X) is
> > shown instead of the image.
> >
> > Has anyone faced the same problem? Any help is solving this?
> >
> > Regards,
> > Atif
> >
> >
> >|||Just to explain how am I converting the images, images are still in WMF
format in the DB, I am using a C# assembly to convert the images using a
function at runtime. The function takes image field from DB as parameter and
returns the converted PNG image. This is written as an expression in the
value property of image control.
Regards,
Atif
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:ABD7F597-8056-4BDA-B4B9-7D5E7A9DA017@.microsoft.com...
> Have you tried converting your WMF files into one of the supported
formats?
> The supported formats are bmp, jpg, gif, png and jpeg.
> Charles Kangai, MCT, MCDBA
> "Abdus Salam Atif" wrote:
> > Hello All,
> >
> > I am using Image control with database sourced images on my reports. The
> > images are stored in WMF format.
> >
> > There is no problem when the report is viewed in web browser but when I
> > export it as a PDF or TIFF, the images are not shown and a cross (X) is
> > shown instead of the image.
> >
> > Has anyone faced the same problem? Any help is solving this?
> >
> > Regards,
> > Atif
> >
> >
> >|||I have a similar situation, but rather than convert the images on the fly, I
convert the images in a batch and put back into the database. In my case the
images show correctly (PNG) in preview but not in HTML. If I export to PDF
they show ok. There seem to be others with this problem as well. I hope
someone from MS will address this issue because it is stopping me from
deploying RS is several situations.
Thanks.
"Abdus Salam Atif" <asalam@.xavor.com> wrote in message
news:eMS0MA60EHA.1408@.TK2MSFTNGP10.phx.gbl...
>I have also tried to convert the WMF files to PNG, which is the only
> accepted format for my application and web, this gives me a different
> behavior. The reports look fine in the preview and I am also able to
> generate a correct PDF from the preview window but not showing images when
> I
> open the report in the web browser.
> Regards,
> Atif
> "Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in
> message
> news:ABD7F597-8056-4BDA-B4B9-7D5E7A9DA017@.microsoft.com...
>> Have you tried converting your WMF files into one of the supported
> formats?
>> The supported formats are bmp, jpg, gif, png and jpeg.
>> Charles Kangai, MCT, MCDBA
>> "Abdus Salam Atif" wrote:
>> > Hello All,
>> >
>> > I am using Image control with database sourced images on my reports.
>> > The
>> > images are stored in WMF format.
>> >
>> > There is no problem when the report is viewed in web browser but when I
>> > export it as a PDF or TIFF, the images are not shown and a cross (X) is
>> > shown instead of the image.
>> >
>> > Has anyone faced the same problem? Any help is solving this?
>> >
>> > Regards,
>> > Atif
>> >
>> >
>> >
>

Problem with handling dates

hi all
I have a stored procedure that gets date from a particular field using the DATEPART function. However, somewhere in between it has to do an update which is why it adds hours, days or months to the function. Here is the code
SELECT @.var_NewNextRunDate=(CAST(DATEPART(YYYY,@.DT) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,@.DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@.DT) AS CHAR(3)),2) + ' '
+ RIGHT(CAST(100+DATEPART(HH,@.DT) + @.var_Frequency AS CHAR(3)),2) + ':'
+ RIGHT(CAST(DATEPART(MI,@.DT) AS CHAR(2)),2) + ':'
+ RIGHT(CAST(DATEPART(SS,@.DT) AS CHAR(2)),2)+ ':'
+ RIGHT(CAST(DATEPART(MS,@.DT) AS CHAR(3)),3) )
Now the problem is that when there a change in day the hours get stuck because it simply adds the hours so if it is 23:40 and it adds 3 it becomes 26:40 which makes no sense. Can someone please help me find a solution to this? I have the same situation with days, and months as well.
Thank you.you should do all data/time manipulations on a date/time datatypes not chars.
if you are extracting a portion of an existing date/time which results in a char datatype, you should cast it back to date/time before you start manipulating it.|||Although this is an old thread I just thought I'd add what I did. I just used the dateadd function to resolve the issue. It worked just perfect.

Problem with GetUTCDate function in stored procedure

Why does this not work? I get the following error when trying to save the stored procedure. "Incorrect syntax near ')'."

SQL2005 SP1 developing with VS 2005 SP1

UPDATE mytable
SET modifyDate = getutcdate()
WHERE rowID = 1

I have also tried several variations all of which don't work.

DECLARE @.modifyDate datetime
Set @.modifyDate = getutcdate()

SELECT @.modifyDate getutcdate()

However if I use the getdate() instead any of the above work. ?

Larry

For some unknown reason the getutcdate() must be enclosed in () or the procedure ignores the WHERE clause portion of the procedure.

UPDATE mytable
SET modifyDate = (getutcdate())
WHERE rowID = 1

In my example in the first post the getutcdate() is not enclosed in () and the WHERE clause is not part of the query at least that is what VS 2005 determines.

As for the syntax error, that happened to be unrelated altogether except as a coding error. This error started popping up during a code review/update because of the law forcing businesses to save all email and IM correspondence. We determined that henceforth we must standardize our timebase and the logical time is UTC. So all our stored procedures that use a "modifyDate" field are being updated to now use UTC times. The syntax error appeared in just a few procedures that have "Administrative" functionallity that can modify the modifyDate from external code. We are now removing that capability from our system. It just so happens that in a few isolated cases the declaration of the modifyDate appeared last in the parameters section of the procedure. Thus when the line was commented out there was a trailing "," on the previous line that caused the syntax error.

Nevertheless VS2005 improperly detects the endpoint of the procedure when the getutcdate() is not enclosed in () itself.

Larry

|||UPDATE myTable
SET modifyDate = getutcdate()
WHERE id = 1

This one looks fine and works on my machine.
And this sp works too.
CREATE PROCEDURE [dbo].[tDate_sp]
AS
UPDATE myTable
SET modifyDate = getutcdate()
WHERE id = 1|||

I think that the issues may be with Visual Studio 2005 SP1.

If I create the stored procedure as shown above, what actually happens is the editor imporperly places the blue border that deliniates the SQL statement is such a way that the WHERE clause in this case is excluded. However SQL 2005 does accept the entire statement.

So in appearance the border outline in Visural Studio would have the following enclosed inside the statement:

UPDATE myTable
SET modifyDate = getutcdate()

This would NOT be inside the statement:

WHERE id = 1

The net effect is that it does work it is just imporperly indicated in the editor.

Larry

Problem with GETDATE in SQL Stored Procedure

hi all,

i am using a stored procedure where i am using GETDATE to give default value to a field ( @.effectivedate as Datetime = GETDATE)

i am making the SP call in my code .

Dim cmd As System.Data.Common.DbCommand = db.GetStoredProcCommand("sel_TemplateData")

db.AddInParameter(cmd, "@.TemplateID", DbType.Int32, Convert.ToInt32(_templateId))

db.AddInParameter(cmd, "@.State", DbType.String, mrmParams("State").ToString())

db.AddInParameter(cmd, "@.SectionCode", DbType.String, mrmParams("SectionCode").ToString())

Dim ds As DataSet = db.ExecuteDataSet(cmd)

should i need to pass this as a parameter along with other parameter as below ? will it be defaultly taken.

when i try to add this parameter an error is thrown " cannot convert string to datetime .

is the syntax for GETDATE correct.

thanks in Advance

Since Getdate() is the non-deterministic function you can’t assign this function as your default value of the SP parameter.

Try to use the following approach to pick up the current date when there is no explicit value passed for datetime valued parameters.

C

Code Snippet

raete Procedure TestDateParam

(

@.Date as datetime= '1900-01-01'

)

as

Begin

Set @.Date = Case When @.Date = '1900-01-01' Then Getdate() Else @.Date End;

Select @.Date Date

End

Go

Exec TestDateParam --it will use the current date

Exec TestDateParam '1/1/2007' --it will use the passed date value

|||

Could you instead use NULL as your default value? If that is possible then you would not need the set statement but could use the ISNULL or COALESCE function -- something like:

ISNULL(@.Date, getdate())

or

COALESCE(@.Date, getdate())

within the body of your stored procedure

|||

If you are wanting to make @.EffectiveDate an optional parameter for the procedure, I suggest using Kent's suggestion of setting the optional value equal to NULL. It is cleaner than presuming a redefined date means none supplied...

|||In addition to Kent and Arnie you could consider using the syntax:

Create procedure someproc
(
@.SomeDate DATETIME = NULL
)
AS

SELECT
(...Something)
WHERE YourColumn = @.SomeDate OR @.SomeDate IS NULL

But this highly depends on your needs in the logic of the stored procedure.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem with generating stored procedures from script

Hi,
I am generating sored procedures from my script. It contains about 90 of
them, but for few I get a warning like:
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'STP_Set'. The stored procedure will still be
created."
What does it mean and can it cause some problems?
How to eliminate it?
Thank you.
PrzemoThis warning message is issued when a stored procedure is compiled but the
compiler cannot find all of the dependent stored procedures it references.
For example, if SPa calls SPb but SPb is compiled before SPa, you will
receive the warning. To eliminate the message, you need to compile all of
your procedures in dependency order.
--Brian
(Please reply to the newsgroups only.)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still
> be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||The stored procedure refers to another stored procedure that doesn't exist.
It is only a warning. If
you don't want that warning, you need to create the procedures in the right
order.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still b
e
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||You referenced an object named 'STP_Set' which doesn′t exists at the
execution of the create procedure script. If the table is created later in
the script you can ignore this message, otherwise you have to create this
objects (Manually or via script).
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still b
e
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||you may like to try a database build and see if you have anything broken in
your database - try DB Ghost (http://www.dbghost.com) - it has a build
component that takes a set of scripts, figures out the order and builds a
database which will quickly show you if you have any breakages.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still b
e
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo

Problem with generating stored procedures from script

Hi,
I am generating sored procedures from my script. It contains about 90 of
them, but for few I get a warning like:
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'STP_Set'. The stored procedure will still be
created."
What does it mean and can it cause some problems?
How to eliminate it?
Thank you.
PrzemoThis warning message is issued when a stored procedure is compiled but the
compiler cannot find all of the dependent stored procedures it references.
For example, if SPa calls SPb but SPb is compiled before SPa, you will
receive the warning. To eliminate the message, you need to compile all of
your procedures in dependency order.
--
--Brian
(Please reply to the newsgroups only.)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still
> be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||The stored procedure refers to another stored procedure that doesn't exist. It is only a warning. If
you don't want that warning, you need to create the procedures in the right order.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||You referenced an object named 'STP_Set' which doesn´t exists at the
execution of the create procedure script. If the table is created later in
the script you can ignore this message, otherwise you have to create this
objects (Manually or via script).
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Przemo" wrote:
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||you may like to try a database build and see if you have anything broken in
your database - try DB Ghost (http://www.dbghost.com) - it has a build
component that takes a set of scripts, figures out the order and builds a
database which will quickly show you if you have any breakages.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Przemo" wrote:
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo

Problem with generating stored procedures from script

Hi,
I am generating sored procedures from my script. It contains about 90 of
them, but for few I get a warning like:
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'STP_Set'. The stored procedure will still be
created."
What does it mean and can it cause some problems?
How to eliminate it?
Thank you.
Przemo
This warning message is issued when a stored procedure is compiled but the
compiler cannot find all of the dependent stored procedures it references.
For example, if SPa calls SPb but SPb is compiled before SPa, you will
receive the warning. To eliminate the message, you need to compile all of
your procedures in dependency order.
--Brian
(Please reply to the newsgroups only.)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still
> be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo
|||The stored procedure refers to another stored procedure that doesn't exist. It is only a warning. If
you don't want that warning, you need to create the procedures in the right order.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo
|||You referenced an object named 'STP_Set' which doesn′t exists at the
execution of the create procedure script. If the table is created later in
the script you can ignore this message, otherwise you have to create this
objects (Manually or via script).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo
|||you may like to try a database build and see if you have anything broken in
your database - try DB Ghost (http://www.dbghost.com) - it has a build
component that takes a set of scripts, figures out the order and builds a
database which will quickly show you if you have any breakages.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that type
of case. For example:
__________________________________________
1st Degree Murder 1 0
3rd Degree Murder 1 2
___________________________________________
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID =
c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other case
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and the
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...
Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:

> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that type
> of case. For example:
> __________________________________________
> 1st Degree Murder 1 0
> 3rd Degree Murder 1 2
> ___________________________________________
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is.
> I am doing that as a subquery that looks something like this:
> ----
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER JOIN
> [Case] c ON f.CaseID =
> c.CaseID INNER JOIN
> zPASService ON c.ServiceId
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JOIN
> ----
>
> (ServiceDescription is the code description for the case type. Right now I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part comes
> out to something like this:
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [Case] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
>
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other case
> type. If so, I have found examples on how to do a FULL JOIN for two or more
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>
|||Will do, but first let me ask a more basic question. Can you use a FULL JOIN
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to do
a FULL JOIN with the results of a GROUP BY so I have counts for my case types
with another GROUP BY that will have counts of cases assigned. I just wanted
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Steve,
> It will be good if you also post some DDL, sample data and expected result.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname
|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:

>Will do, but first let me ask a more basic question. Can you use a FULL JOIN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks to all of you for helping me on this one. It is working now. Here is
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =
zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =
RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:

> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that typ
e
of case. For example:
________________________________________
__
1st degree Murder 1 0
3rd degree Murder 1 2
________________________________________
___
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
----
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID =
c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
----
-
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
---
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
---
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other cas
e
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and th
e
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:

> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that t
ype
> of case. For example:
> ________________________________________
__
> 1st degree Murder 1 0
> 3rd degree Murder 1 2
> ________________________________________
___
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is
.
> I am doing that as a subquery that looks something like this:
> ----
--
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER J
OIN
> [Case] c ON f.CaseID
=
> c.CaseID INNER JOIN
> zPASService ON c.Service
Id
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JO
IN
> ----
--
>
> (ServiceDescription is the code description for the case type. Right now
I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part come
s
> out to something like this:
> ---
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [C
ase] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
> ---
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other c
ase
> type. If so, I have found examples on how to do a FULL JOIN for two or mo
re
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked a
t
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and
the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>|||Will do, but first let me ask a more basic question. Can you use a FULL JOI
N
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to d
o
a FULL JOIN with the results of a GROUP BY so I have counts for my case type
s
with another GROUP BY that will have counts of cases assigned. I just wante
d
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Steve,
> It will be good if you also post some DDL, sample data and expected result
.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
>|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:

>Will do, but first let me ask a more basic question. Can you use a FULL JO
IN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all of you for helping me on this one. It is working now. Here i
s
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =
zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =
RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:

> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
>
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Problem with FULL JOIN

I need to write a stored proc for a report. Each line of the report will
have a description of the type of case and then the number of cases opened
during the time period for that type of case followed by the third column
which will be the number of cases closed during the time period for that type
of case. For example:
__________________________________________
1st Degree Murder 1 0
3rd Degree Murder 1 2
___________________________________________
To get at these data I need to got into our assignment table to find the
first date the case was assigned and then find out the type of cases it is.
I am doing that as a subquery that looks something like this:
----
SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [#FirstAssignedData] f INNER JOIN
[Case] c ON f.CaseID = c.CaseID INNER JOIN
zPASService ON c.ServiceId
= zPASService.ServiceId
WHERE FirstAssignedDt > '5 / 1 / 04'
GROUP BY zPASService.ServiceDescription
ORDER BY zPASService.ServiceDescription FULL JOIN
----
(ServiceDescription is the code description for the case type. Right now I
have hardcoded the search for assignments to be any greater that 5/1/04).
Next I have to look to see if there are any cases with disposition dates
within the time period. Again I have hardcoded that test. That part comes
out to something like this:
---
SELECT
zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
DispositionedCases, OpenedCases
FROM [Case] c
INNER JOIN
zPASService ON c.ServiceId = zPASService.ServiceId
WHERE
DispositionDt > '8 / 1 / 05'
GROUP BY
zPASService.ServiceDescription
---
My question is how to bring these two results together? I am thinking I
want to do a FULL JOIN since I can't be sure that the case type that is in
either the assigned results or the dispositioned results is in the other case
type. If so, I have found examples on how to do a FULL JOIN for two or more
tables, but can't see how to do it when dealing with results from two
queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
UNION but since my columns are not the same (the first query returns the
number of cases assigned the second the number of cases dispositioned and the
report needs to get those two numbers seperately) I thougth I needed
something else.
Thanks...
- Steve
Thanks...Steve,
It will be good if you also post some DDL, sample data and expected result.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve" wrote:
> I need to write a stored proc for a report. Each line of the report will
> have a description of the type of case and then the number of cases opened
> during the time period for that type of case followed by the third column
> which will be the number of cases closed during the time period for that type
> of case. For example:
> __________________________________________
> 1st Degree Murder 1 0
> 3rd Degree Murder 1 2
> ___________________________________________
> To get at these data I need to got into our assignment table to find the
> first date the case was assigned and then find out the type of cases it is.
> I am doing that as a subquery that looks something like this:
> ----
> SELECT zPASService.ServiceDescription,
> COUNT(zPASService.ServiceDescription) AS OpenedCases
> FROM [#FirstAssignedData] f INNER JOIN
> [Case] c ON f.CaseID => c.CaseID INNER JOIN
> zPASService ON c.ServiceId
> = zPASService.ServiceId
> WHERE FirstAssignedDt > '5 / 1 / 04'
> GROUP BY zPASService.ServiceDescription
> ORDER BY zPASService.ServiceDescription FULL JOIN
> ----
>
> (ServiceDescription is the code description for the case type. Right now I
> have hardcoded the search for assignments to be any greater that 5/1/04).
> Next I have to look to see if there are any cases with disposition dates
> within the time period. Again I have hardcoded that test. That part comes
> out to something like this:
> ---
> SELECT
> zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> DispositionedCases, OpenedCases
> FROM [Case] c
> INNER JOIN
> zPASService ON c.ServiceId = zPASService.ServiceId
> WHERE
> DispositionDt > '8 / 1 / 05'
> GROUP BY
> zPASService.ServiceDescription
> ---
> My question is how to bring these two results together? I am thinking I
> want to do a FULL JOIN since I can't be sure that the case type that is in
> either the assigned results or the dispositioned results is in the other case
> type. If so, I have found examples on how to do a FULL JOIN for two or more
> tables, but can't see how to do it when dealing with results from two
> queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> UNION but since my columns are not the same (the first query returns the
> number of cases assigned the second the number of cases dispositioned and the
> report needs to get those two numbers seperately) I thougth I needed
> something else.
> Thanks...
> - Steve
> Thanks...
>|||Will do, but first let me ask a more basic question. Can you use a FULL JOIN
with the result of a query or does the subject of the JOIN have to be a
table? What I am need to do (or at least what I think I need to do) is to do
a FULL JOIN with the results of a GROUP BY so I have counts for my case types
with another GROUP BY that will have counts of cases assigned. I just wanted
to make sure that I am walking down the correct path. Let me know if you
need the DDL and sample data before you can even answer the question in this
post.
Thanks...
"Alejandro Mesa" wrote:
> Steve,
> It will be good if you also post some DDL, sample data and expected result.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Steve" wrote:
> > I need to write a stored proc for a report. Each line of the report will
> > have a description of the type of case and then the number of cases opened
> > during the time period for that type of case followed by the third column
> > which will be the number of cases closed during the time period for that type
> > of case. For example:
> > __________________________________________
> > 1st Degree Murder 1 0
> > 3rd Degree Murder 1 2
> > ___________________________________________
> >
> > To get at these data I need to got into our assignment table to find the
> > first date the case was assigned and then find out the type of cases it is.
> > I am doing that as a subquery that looks something like this:
> > ----
> > SELECT zPASService.ServiceDescription,
> > COUNT(zPASService.ServiceDescription) AS OpenedCases
> > FROM [#FirstAssignedData] f INNER JOIN
> > [Case] c ON f.CaseID => > c.CaseID INNER JOIN
> > zPASService ON c.ServiceId
> > = zPASService.ServiceId
> > WHERE FirstAssignedDt > '5 / 1 / 04'
> > GROUP BY zPASService.ServiceDescription
> > ORDER BY zPASService.ServiceDescription FULL JOIN
> > ----
> >
> >
> > (ServiceDescription is the code description for the case type. Right now I
> > have hardcoded the search for assignments to be any greater that 5/1/04).
> >
> > Next I have to look to see if there are any cases with disposition dates
> > within the time period. Again I have hardcoded that test. That part comes
> > out to something like this:
> > ---
> > SELECT
> > zPASService.ServiceDescription, COUNT(zPASService.ServiceDescription) AS
> > DispositionedCases, OpenedCases
> > FROM [Case] c
> > INNER JOIN
> >
> > zPASService ON c.ServiceId = zPASService.ServiceId
> > WHERE
> > DispositionDt > '8 / 1 / 05'
> > GROUP BY
> > zPASService.ServiceDescription
> >
> > ---
> >
> > My question is how to bring these two results together? I am thinking I
> > want to do a FULL JOIN since I can't be sure that the case type that is in
> > either the assigned results or the dispositioned results is in the other case
> > type. If so, I have found examples on how to do a FULL JOIN for two or more
> > tables, but can't see how to do it when dealing with results from two
> > queries. Perhaps it is not a FULL JOIN I am looking for. I also looked at
> > UNION but since my columns are not the same (the first query returns the
> > number of cases assigned the second the number of cases dispositioned and the
> > report needs to get those two numbers seperately) I thougth I needed
> > something else.
> >
> > Thanks...
> > - Steve
> >
> > Thanks...
> >|||A full join is a type of join. The requirements for its usage do not differ
(substantially) from any other type of join. Whether you need to use a full
join cannot be determined without a better understanding of the problem and
your proposed solution. With only a brief review of your initial post, I
doubt that a full join will help solve your problem.
To put your problem into the proper perspective, you need to think in terms
of sets of information and the relationships between the data that is used
to generate these sets. Your goal is to generate a report containing a
certain set of information (all types of cases) along with some related data
about each type. If you look at it from this perspective, you need
something that contains this basic set of information (all possible types of
cases). Does this exist somewhere? Can't tell without knowing your schema.
If it doesn't, then that is the first problem you must overcome. If it
does, then that information drives the query. Select the rows and then
figure out how to generate the other information. Here is a hint - try an
outer join to the case information and use aggregate functions. It is
likely that you will need to use the case expression. Timeperiods factor
into this problem somehow, but that aspect is not clear. It is likely that
you may also need something that contains the timeperiods of interest; in
this case a cross join **might** be useful.
Note that there are many ways to accomplish your goal; this is but a single
suggestion. Perhaps the best way to approach this is to concentrate on the
data that you do have and create a query that generates the desired
information using only inner joins. Obviously that will only include those
types of cases that have supporting data. That basic query can often be
modified to then generate the missing bits. Below is an example from
Northwind that should give you some ideas.
-- For each period and customer, get all orders ordered or shipped
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
convert(char(12), ord.OrderDate, 102) as orddate,
convert(char(12), ord.ShippedDate, 102) as shipdate
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
order by periods.begindate, cust.CompanyName, ord.OrderDate
-- For each period and customer, count the number of orders ordered
select convert(char(12), periods.begindate, 102) as bdate,
convert(char(12), periods.enddate, 102) as edate,
cust.CustomerID, left(cust.CompanyName, 15) as cname,
sum(case when ord.OrderDate between periods.begindate and
periods.enddate
then 1 else 0 end) as ordercnt
from Customers as cust
inner join Orders as ord
on cust.CustomerID = ord.CustomerID
inner join (select cast('19970601' as datetime) as begindate,
cast('19970630 23:59:59.997' as datetime) as enddate
union all
select '19970701', '19970731 23:59:59.997' ) as periods
on ord.OrderDate between periods.begindate and periods.enddate
or ord.ShippedDate between periods.begindate and periods.enddate
group by convert(char(12), periods.begindate, 102),
convert(char(12), periods.enddate, 102),
cust.CustomerID, cust.CompanyName
order by bdate, cname|||On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
>Will do, but first let me ask a more basic question. Can you use a FULL JOIN
>with the result of a query or does the subject of the JOIN have to be a
>table?
(snip)
Hi Stevem
I didn't read all details in your post, so I don't know if it will help
in your case, but the answer to your basic question is that you can
always use a (non-corelated) subquery in place of a table. This is
called a derived table. Example of using tw derived tables with a FULL
OUTER JOIN:
SELECT d1.Col1, d1.Col2, d2.Col4
FROM (SELECT Col1, Col2, Col3
FROM Table1
WHERE Col4 = 4) AS d1
FULL OUTER JOIN
(SELECT Col3, Col4
FROM Table2
WHERE Col5 = 5) AS d2
ON d2.col3 = d1.col3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all of you for helping me on this one. It is working now. Here is
what I came up with:
SELECT CASE WHEN RptOpenedCases.ServiceDescription IS NULL
THEN RptClosedCases.ServiceDescription ELSE
RptOpenedCases.ServiceDescription END AS 'RptServiceDescription',
RptOpenedCases.OpenedCases, RptClosedCases.CasesClosed
FROM (SELECT zPASService.ServiceDescription,
COUNT(zPASService.ServiceDescription) AS OpenedCases
FROM [Case] AS c INNER JOIN
zPASService ON c.ServiceId =zPASService.ServiceId INNER JOIN
(SELECT CaseID,
MIN(StartDt) AS FirstAssignedDt
FROM AttyAssign
GROUP BY CaseID) AS
FirstAssignment ON c.CaseID = FirstAssignment.CaseID
WHERE (FirstAssignment.FirstAssignedDt > '5/1/05')
GROUP BY zPASService.ServiceDescription) AS
RptOpenedCases FULL OUTER JOIN
(SELECT zPASService_1.ServiceDescription,
COUNT(zPASService_1.ServiceDescription) AS CasesClosed
FROM [Case] AS c INNER JOIN
zPASService AS
zPASService_1 ON c.ServiceId = zPASService_1.ServiceId
WHERE (c.DispositionDt > '5/1/04')
GROUP BY zPASService_1.ServiceDescription) AS
RptClosedCases ON
RptOpenedCases.ServiceDescription =RptClosedCases.ServiceDescription
ORDER BY RptServiceDescription
"Hugo Kornelis" wrote:
> On Mon, 10 Oct 2005 14:40:02 -0700, Steve wrote:
> >Will do, but first let me ask a more basic question. Can you use a FULL JOIN
> >with the result of a query or does the subject of the JOIN have to be a
> >table?
> (snip)
> Hi Stevem
> I didn't read all details in your post, so I don't know if it will help
> in your case, but the answer to your basic question is that you can
> always use a (non-corelated) subquery in place of a table. This is
> called a derived table. Example of using tw derived tables with a FULL
> OUTER JOIN:
> SELECT d1.Col1, d1.Col2, d2.Col4
> FROM (SELECT Col1, Col2, Col3
> FROM Table1
> WHERE Col4 = 4) AS d1
> FULL OUTER JOIN
> (SELECT Col3, Col4
> FROM Table2
> WHERE Col5 = 5) AS d2
> ON d2.col3 = d1.col3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>