problem with srever 2005 and Oracle

I am creating report model witch have connection to Oracle database. I was
created the connection and data source view, but then I want to create
Report model with wizard I am geting the error: ORA-02179: valid options:
ISOLATION LEVEL {SERIAZABLE | READ COMMITTED}.
how to change isolation level to valid oracle connection isolation level.It is my understanding that the model part of RS in 2005 is not yet
compatible with Oracle. I am tied to find where I read that on technet
but I can't. Maybe someone else can elaborate.
Thanks!|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

problem with SQLSTATE

create function
"DBO".sf_GetStateID( @.Abbr char(2))
returns integer
begin
declare @.StateID integer;
set @.Abbr=UPPER(@.Abbr);
if @.Abbr is null
set @.Abbr=''
set @.StateID=53;

select MIN(lngStateID) into StateID from "DBA".States where strAbbr=@.Abbr;

if @.StateID is null
insert into States(strAbbr,strName) values(@.Abbr,@.Abbr)

if SQLSTATE = '00000'

set @.StateID=@.@.IDENTITY
return(@.StateID)
end

In this function, I am getting error at SQLSTATE = '00000'. I saw in books online and it says that SQLSTATE is a keyword. It is not recognizing SQLSTATE as a keyword in the function.
Can you help in that?

2nd Error: It is not accepting the statement
select MIN(lngStateID) into StateID from "DBO".States where strAbbr=@.Abbr;

I don't see any problem in the above statement. Do help in solving these two problems.

Tks
K.The 2 problems is that sqlstate is used for odbc/embedded sql - not for udfs. Also, you are returning a scalar in the udf and not a table (for the 2nd error).sql

problem with SQL server creating new database diagram

when i trying to create a new database diagram
it shows :

An unexpected error happened during this operation
[Ms design tools] - Invalid class string

can any 1 explain for me?
thank youHere are a few things you can try:

1. Try reinstalling the latest service pack (SP3a) on your PC.
2. If still having issues, uninstall the sql client tools, install latest MDAC, reinstall client tools,apply latest service pack.sql

Problem with SQL Server and odbc, please help me!

I have Visual Studio Dot.Net on Win XP and use the MSDE to access to Sql
Server.
This morning I've attempt to create a new ODBC DSN for Sql Server so
I've executed the wizard for a new System DSN by the ODBC administrator
of Windows. But when it attempts to connect to the server (the third
step of wizard) it give me somewhat like:
Connection impossible.
SQLState: '01000'
SQL Server error: 10049
SQLState: '08001'
SQL Server error: 17
I've tried to go forward with the manual configuration (uncheck the item
"Connect to an SQL Server for the default settings.." and press the Next
button), but at the end, when I press "Verify the data source", I have
no connection. So I've canceled the wizard.
The problem is that after that moment I have no connection with the server.
The console of MSDE indicate that the server is normally running, but I
can't connect to it neither by the Server Explorer of VS nor by osql.
I have always a message like:
[DBNETLIB][ConnectionOpen(Connect()).]Server non found or access denied.
Can someone help me?
Thanks
Tonio Tanzi
P.s. the messages of the server are probably a little different by the
ones indicate by me because I've translated them from the italian version.Tonio,
Is this a development server? If so try stopping the SQL Server service and
restarting it.
HTH
Jerry
"Tonio Tanzi" <vi.tan.gr@.inwind.it> wrote in message
news:m8c%e.3633$EZ.98724@.twister1.libero.it...
>I have Visual Studio Dot.Net on Win XP and use the MSDE to access to Sql
>Server.
> This morning I've attempt to create a new ODBC DSN for Sql Server so I've
> executed the wizard for a new System DSN by the ODBC administrator of
> Windows. But when it attempts to connect to the server (the third step of
> wizard) it give me somewhat like:
> Connection impossible.
> SQLState: '01000'
> SQL Server error: 10049
> SQLState: '08001'
> SQL Server error: 17
> I've tried to go forward with the manual configuration (uncheck the item
> "Connect to an SQL Server for the default settings.." and press the Next
> button), but at the end, when I press "Verify the data source", I have no
> connection. So I've canceled the wizard.
> The problem is that after that moment I have no connection with the
> server.
> The console of MSDE indicate that the server is normally running, but I
> can't connect to it neither by the Server Explorer of VS nor by osql.
> I have always a message like:
> [DBNETLIB][ConnectionOpen(Connect()).]Server non found or access denied.
>
> Can someone help me?
> Thanks
> Tonio Tanzi
> P.s. the messages of the server are probably a little different by the
> ones indicate by me because I've translated them from the italian version.|||Jerry Spivey ha scritto:
> Tonio,
> Is this a development server? If so try stopping the SQL Server service a
nd
> restarting it.
Yes. I've just stopped and restarted both Sql Server and computer many
times widhout any result.
Other suggest?
Bye
Tonio Tanzi

problem with space

HI friends,
I've got one question. When I realize "log in" in my sql server, I am using
SP like this:
CREATE Procedure CustomerLogin
(
@.Email nvarchar(30),
@.Pwd nvarchar(10),
@.CustomerID int OUTPUT
)
AS
SELECT
@.CustomerID = CustomerID
FROM
Customers
WHERE
Email = @.Email COLLATE SQL_Latin1_General_CP1_CS_AS
AND
Pwd =@.Pwd COLLATE SQL_Latin1_General_CP1_CS_AS
IF @.@.Rowcount < 1
SELECT
@.CustomerID = 0
GO
The problem is that if the password is "qweqwe" and I try to log in with
"qweqwe " or even "qweqwe " (I mean with space after last symbol) it
succeed!!!
Interesting but if I try to log in with " qweqwe" (I mean with space in
beginning) it not succeed.
I don't know why. Could you please somebody help me?This is a multi-part message in MIME format.
--020209070906080502050607
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Post the DDL for your Customers table. Specifically, what's datatype of
the Pwd column?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mango wrote:
>HI friends,
>I've got one question. When I realize "log in" in my sql server, I am using
>SP like this:
>CREATE Procedure CustomerLogin
>(
> @.Email nvarchar(30),
> @.Pwd nvarchar(10),
> @.CustomerID int OUTPUT
>)
>AS
>
>SELECT
> @.CustomerID = CustomerID
>
>FROM
> Customers
>
>WHERE
> Email = @.Email COLLATE SQL_Latin1_General_CP1_CS_AS
> AND
> Pwd =@.Pwd COLLATE SQL_Latin1_General_CP1_CS_AS
>
>IF @.@.Rowcount < 1
>SELECT
> @.CustomerID = 0
>GO
>
>The problem is that if the password is "qweqwe" and I try to log in with
>"qweqwe " or even "qweqwe " (I mean with space after last symbol) it
>succeed!!!
>Interesting but if I try to log in with " qweqwe" (I mean with space in
>beginning) it not succeed.
>I don't know why. Could you please somebody help me?
>
>
--020209070906080502050607
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Post the DDL for your Customers table. Specifically, what's
datatype of the Pwd column?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Mango wrote:
<blockquote cite="miduqIMeBIGFHA.2180@.TK2MSFTNGP12.phx.gbl" type="cite">
<pre wrap="">HI friends,
I've got one question. When I realize "log in" in my sql server, I am using
SP like this:
CREATE Procedure CustomerLogin
(
@.Email nvarchar(30),
@.Pwd nvarchar(10),
@.CustomerID int OUTPUT
)
AS
SELECT
@.CustomerID = CustomerID
FROM
Customers
WHERE
Email = @.Email COLLATE SQL_Latin1_General_CP1_CS_AS
AND
Pwd =@.Pwd COLLATE SQL_Latin1_General_CP1_CS_AS
IF @.@.Rowcount < 1
SELECT
@.CustomerID = 0
GO
The problem is that if the password is "qweqwe" and I try to log in with
"qweqwe " or even "qweqwe " (I mean with space after last symbol) it
succeed!!!
Interesting but if I try to log in with " qweqwe" (I mean with space in
beginning) it not succeed.
I don't know why. Could you please somebody help me?
</pre>
</blockquote>
</body>
</html>
--020209070906080502050607--|||the datatype of Pwd is nvarchar. I found that is working corect if i write
it like this:
CREATE Procedure CustomerLogin
(
@.Email nvarchar(80),
@.Pwd nvarchar(50),
@.CustomerID int OUTPUT
)
AS
SELECT
@.CustomerID = CustomerID
FROM
Customers
WHERE
Email + '1' = @.Email COLLATE SQL_Latin1_General_CP1_CS_AS + '1'
AND
Pwd + '1'=@.Pwd COLLATE SQL_Latin1_General_CP1_CS_AS + '1'
IF @.@.Rowcount < 1
SELECT
@.CustomerID = 0
GO
But I am sure this is not the best approach
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:#t9fdRIGFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Post the DDL for your Customers table. Specifically, what's datatype of
> the Pwd column?
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
http://www.mallesons.com
>
> Mango wrote:
> >HI friends,
> >
> >I've got one question. When I realize "log in" in my sql server, I am
using
> >SP like this:
> >
> >CREATE Procedure CustomerLogin
> >
> >(
> >
> > @.Email nvarchar(30),
> >
> > @.Pwd nvarchar(10),
> >
> > @.CustomerID int OUTPUT
> >
> >)
> >
> >AS
> >
> >
> >
> >SELECT
> >
> > @.CustomerID = CustomerID
> >
> >
> >
> >FROM
> >
> > Customers
> >
> >
> >
> >WHERE
> >
> > Email = @.Email COLLATE SQL_Latin1_General_CP1_CS_AS
> >
> > AND
> >
> > Pwd =@.Pwd COLLATE SQL_Latin1_General_CP1_CS_AS
> >
> >
> >
> >IF @.@.Rowcount < 1
> >
> >SELECT
> >
> > @.CustomerID = 0
> >
> >GO
> >
> >
> >
> >The problem is that if the password is "qweqwe" and I try to log in with
> >"qweqwe " or even "qweqwe " (I mean with space after last symbol)
it
> >succeed!!!
> >
> >Interesting but if I try to log in with " qweqwe" (I mean with space in
> >beginning) it not succeed.
> >
> >I don't know why. Could you please somebody help me?
> >
> >
> >
> >
>

Problem with SP3 JDBC Driver for SQL Server

Hi,
I noticed an interesting behaviour. When the autoCommit is turned off, I am not able to use the same connection handle to create multiple Statement/PreparedStatement handles.It goes into Manual Transaction Mode and says cannot clone connections.
Is this a bug? Can someone give some pointers here.
thanks
Shreyas
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
You need to add a property to the code that's making your connection:
selectMethod=cursor
Joe Weinstein at BEA
shreyask@.gmail.com wrote:
> Hi,
> I noticed an interesting behaviour. When the autoCommit is turned off, I am not able to use the same connection handle to create multiple Statement/PreparedStatement handles.It goes into Manual Transaction Mode and says cannot clone connections.
> Is this a bug? Can someone give some pointers here.
> thanks
> Shreyas
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||You need to add a property to the code that's making your connection:
selectMethod=cursor
Joe Weinstein at BEA
shreyask@.gmail.com wrote:
> Hi,
> I noticed an interesting behaviour. When the autoCommit is turned off, I am not able to use the same connection handle to create multiple Statement/PreparedStatement handles.It goes into Manual Transaction Mode and says cannot clone connections.
> Is this a bug? Can someone give some pointers here.
> thanks
> Shreyas
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Shreyas Kaushik (shreyask@.gmail.com) wrote in message news:<eMtbpzUxEHA.2564@.TK2MSFTNGP12.phx.gbl>...
> Hi,
> I noticed an interesting behaviour. When the autoCommit is turned off, I am not able to use the same connection handle to create multiple Statement/PreparedStatement handles.It goes into Manual Transaction Mode and says cannot clone connections.
> Is this a bug? Can someone give some pointers here.
> thanks
> Shreyas
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
This is one of the number of unfortunate consequences of the fact that
the driver silently creates >1 connection to the database when you
create > 1 statement. This is an example of what I was referring to in
my post in this group on 10/27 comparing the free MS driver to
comercial drivers.
So, well, yes its free but you have to deal with issues like this
one.. I guess you get what you pay for..

Problem with sp_repldropcolumn propagation

Hi,
Im tyring to modify a column datatype in a replicated table using
sp_repladdcolumn and sp_repldropcolumn. First I create a new column
that is propagated to the subscriber, but when I drop a column with
sp_repldropcolumn the change is not propagated to the subscriber.
Does anyone has an idea of why this is happening?
Thanks,
Diego
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict237980.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=827285
Some columns cannot be dropped, i.e. columns which are part of PKs. Is this
the case? Can you drop it through EM? Right click on your publication,
select Properties, click on the Filter Columns tab and try to drop it there.
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
"Diego" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_827285_2d4aa9fc6f9f611260ea17bddcfcd7da@.dbf orumz.com...
> Hi,
> I'm tyring to modify a column datatype in a replicated table using
> sp_repladdcolumn and sp_repldropcolumn. First I create a new column
> that is propagated to the subscriber, but when I drop a column with
> sp_repldropcolumn the change is not propagated to the subscriber.
> Does anyone has an idea of why this is happening?
> Thanks,
> Diego
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Replication-...ict237980.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=827285
|||"" wrote:
> Some columns cannot be dropped, i.e. columns which are part of
> PKs. Is this
> the case? Can you drop it through EM? Right click on your
> publication,
> select Properties, click on the Filter Columns tab and try to
> drop it there.
> --
> 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
> "Diego" <UseLinkToEmail@.dbForumz.com> wrote in message
> news:4_827285_2d4aa9fc6f9f611260ea17bddcfcd7da@.dbf orumz.com...
> using
> column
> column with
> subscriber.
> standards
> http://www.dbforumz.com/Replication-...ict237980.html
> abuse:
> http://www.dbforumz.com/eform.php?p=827285
Hillary,
The column is not a PK. Ive also could drop the column with
sp_repldropcolumn in the distributor, the problem is that the change
is not propagated to the subscription.
This is not the case with the sp_repladdcolumn that is added in both
places. For example to replace the field data type (thats what im
trying to do) where you run a sp_repldropcolumn before a
sp_repladdcolumn after the second command is run ive get an error in
the distribution agent that says it cannot add a column with the same
name.
Thank you very much,
Diego

Problem with Setting a variable in SQL String

Hi,

I am having problems setting the value of a variable in a SQL String
that I have to create dynamically in my procedure. The code that I
currently have is as follows:

set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

exec(@.sqlStatement)

The error message that I get is as follows:

Must declare the variable '@.compare_string'.

Here @.compare_string has already been declared in the procedure and I
don't have a problem using the variable anywhere else but this SQL
Statement (when called using the EXEC function).

I am not sure why SQL Server can't see the variable declared when used
in a string in conjunction with EXEC. Is this a syntax issue? Any help
on this issue would be greatly appreciated!

Thanks in advance.You need a parms string and an exec string, like this:

SET @.Parms = `@.compare_string`

set @.sqlStatement='Set @.compare_string=' + '(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string

SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )

> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).
> I am not sure why SQL Server can't see the variable declared when used
> in a string in conjunction with EXEC. Is this a syntax issue? Any help
> on this issue would be greatly appreciated!
> Thanks in advance.|||Thanks for your reply. The sp_executesql procedure still doesn't give
the desired results. I am posting the updated piece of code and sample
output from the Query Analyzer.

------
set @.parameter_String=N'@.compare_string nvarchar(4000)'

set @.sqlStatement='Set @.compare_string=(Select ' +
@.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'

Print @.sqlStatement
EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string

Print @.compare_String
------

When I print the value of @.compare_String in the end its a NULL.
However, if I run the same query without the set @.compare_string
clause, it does work perfectly and returns the values of two columns
concatenated together. Any clues as to where I might be going wrong?

Thanks,

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> You need a parms string and an exec string, like this:
> SET @.Parms = `@.compare_string`
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > exec(@.sqlStatement)
> > The error message that I get is as follows:
> > Must declare the variable '@.compare_string'.
> > Here @.compare_string has already been declared in the procedure and I
> > don't have a problem using the variable anywhere else but this SQL
> > Statement (when called using the EXEC function).
> > I am not sure why SQL Server can't see the variable declared when used
> > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > on this issue would be greatly appreciated!
> > Thanks in advance.|||[posted and mailed, please reply in news]

Aamer Nazir (aamernazir_01@.hotmail.com) writes:
> I am having problems setting the value of a variable in a SQL String
> that I have to create dynamically in my procedure. The code that I
> currently have is as follows:
>
> set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> exec(@.sqlStatement)
> The error message that I get is as follows:
> Must declare the variable '@.compare_string'.
> Here @.compare_string has already been declared in the procedure and I
> don't have a problem using the variable anywhere else but this SQL
> Statement (when called using the EXEC function).

The EXEC() statement is another scope which is not part of your procedure.
Thus, @.compare_string is not defined in that example.

For better examples than the one posted, see
http://support.microsoft.com/?id=262499 and
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, that won't work. Sorry, I just focused on the parameter part.

You can just do this:

select @.compare_string = mytable.myfield FROM mytable where Identity_Column
= myvalue

or, in your specific case:

'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
'_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'

At least this is the syntax you should use in this case. Otherwise, you are
effectively trying to bind @.compare_string to a recordset result, which
doesn't work.

Make sure you add in the error checking afterwards!! :)

"Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> Thanks for your reply. The sp_executesql procedure still doesn't give
> the desired results. I am posting the updated piece of code and sample
> output from the Query Analyzer.
>
> ------
> set @.parameter_String=N'@.compare_string nvarchar(4000)'
> set @.sqlStatement='Set @.compare_string=(Select ' +
> @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> Print @.sqlStatement
> EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> Print @.compare_String
> ------
> When I print the value of @.compare_String in the end its a NULL.
> However, if I run the same query without the set @.compare_string
> clause, it does work perfectly and returns the values of two columns
> concatenated together. Any clues as to where I might be going wrong?
> Thanks,
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > You need a parms string and an exec string, like this:
> > SET @.Parms = `@.compare_string`
> > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > exec(@.sqlStatement)
> > > > The error message that I get is as follows:
> > > > Must declare the variable '@.compare_string'.
> > > > Here @.compare_string has already been declared in the procedure and I
> > > don't have a problem using the variable anywhere else but this SQL
> > > Statement (when called using the EXEC function).
> > > > I am not sure why SQL Server can't see the variable declared when used
> > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > on this issue would be greatly appreciated!
> > > > Thanks in advance.|||Thanks for pointing me to the right direction. The code works
perfectly fine now. The problem was with the syntax that Erland
Sommarskog mentioned in his posting. You have to specify the parameter
type (input or output) in the parameter specification string (the
second argument to sp_executesql).

Best Regards,

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bsboku$o7p$1$8300dec7@.news.demon.co.uk>...
> Yes, that won't work. Sorry, I just focused on the parameter part.
> You can just do this:
> select @.compare_string = mytable.myfield FROM mytable where Identity_Column
> = myvalue
> or, in your specific case:
> 'Select @.compare_string=' + @.group_column_list_mod + ' from ' + @.Tbl_Name +
> '_Sorted' + ' where Identity_Column=' + ltrim(rtrim(str @.loop_counter))'
> At least this is the syntax you should use in this case. Otherwise, you are
> effectively trying to bind @.compare_string to a recordset result, which
> doesn't work.
> Make sure you add in the error checking afterwards!! :)
> "Aamer Nazir" <aamernazir_01@.hotmail.com> wrote in message
> news:60b6d0a1.0312231058.14540a2c@.posting.google.c om...
> > Thanks for your reply. The sp_executesql procedure still doesn't give
> > the desired results. I am posting the updated piece of code and sample
> > output from the Query Analyzer.
> > ------
> > set @.parameter_String=N'@.compare_string nvarchar(4000)'
> > set @.sqlStatement='Set @.compare_string=(Select ' +
> > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > Print @.sqlStatement
> > EXECUTE sp_executesql @.sqlStatement,@.parameter_String,@.compare_string
> > Print @.compare_String
> > ------
> > When I print the value of @.compare_String in the end its a NULL.
> > However, if I run the same query without the set @.compare_string
> > clause, it does work perfectly and returns the values of two columns
> > concatenated together. Any clues as to where I might be going wrong?
> > Thanks,
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bs9mbk$jt0$1$8300dec7@.news.demon.co.uk>...
> > > You need a parms string and an exec string, like this:
> > > > SET @.Parms = `@.compare_string`
> > > > set @.sqlStatement='Set @.compare_string=' + '(Select ' +
> > > @.group_column_list_mod + ' from ' + @.Tbl_Name + '_Sorted' + ' where
> > > Identity_Column=' + ltrim(rtrim(str(@.loop_counter))) + ')'
> > > > EXECUTE sp_executesql @.sqlStatement, @.Parms, @.compare_string
> > > > SET @.Error = COALESCE ( NULLIF ( @.Error, 0 ), @.@.ERROR )
> > > > > > > exec(@.sqlStatement)
> > > > > > The error message that I get is as follows:
> > > > > > Must declare the variable '@.compare_string'.
> > > > > > Here @.compare_string has already been declared in the procedure and I
> > > > don't have a problem using the variable anywhere else but this SQL
> > > > Statement (when called using the EXEC function).
> > > > > > I am not sure why SQL Server can't see the variable declared when used
> > > > in a string in conjunction with EXEC. Is this a syntax issue? Any help
> > > > on this issue would be greatly appreciated!
> > > > > > Thanks in advance.

Problem with Service Broker and DBMail

Hi!
I try to use Service Broker and DBMail together, but have some trouble with that.
I need to create the queue with activation.
And the stored procedure activated on this queue must send e-mail using DBmail.
It's looks simple, but it doesn't work.
There is my script to create objects, but don't forget create dbmail profile before use it.
PS And replace my email by yours

Activated procedures are under the EXECUTE AS context, so they cannot dirrectly invoke procedures in another database (e.g. msdb DBmail procs). The issue is covered on this serios of articles:
http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx
http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The easiest woraround is to mark the database where activation occurs as trustworthy:

ALTER DATABASE [<dbname>] SET TRUSTWORTHY ON;

HTH,
~ Remus

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
Thanks
The first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/default...b;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:

> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my address
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address'.
> Mapi error code number -2147221233
> Thanks

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
ThanksThe first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/defaul...kb;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:

> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my addres
s
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address
'.
> Mapi error code number -2147221233
> Thanks

Problem with sending email

This is the first time I am setting up an operator so that I can get job
notifications to my email. When I create an operator any type in my address
and click test. It gives me the following error:
Problem occoured while attempting to resolve the address 'my email address'.
Mapi error code number -2147221233
ThanksThe first thing would be to check to ensure that SQL Mail has been setup
correctly as typically the MAPI errors occur as a result of an incorrect
configuration. The KB Article
http://support.microsoft.com/default.aspx?scid=kb;en-us;263556 describes how
to Configure SQL Mail and the KB article
http://support.microsoft.com/kb/315886/EN-US/ details Common SQL Mail
Problems.
- Peter Ward
WARDY IT Solutions
"Amit" wrote:
> This is the first time I am setting up an operator so that I can get job
> notifications to my email. When I create an operator any type in my address
> and click test. It gives me the following error:
> Problem occoured while attempting to resolve the address 'my email address'.
> Mapi error code number -2147221233
> Thanks

Problem with select into querry

I have two database on same server. I want write a querry,witch qering the data from 1st database, than create a new table on the 2nd database.

But the in clause after the into clause permanently generate error.

Here is the query’s little part:

Select …..

into Backup in ’Test.mdb’ from …

I try wrote other ways

in ’Test’

in ’dbo.Test’

in dbo.Test

but none of them work

its only work, when i not want pass for the second database

How can i pass the result for the 2nd Database

What database engine are you using? You're posting this in the MS SQL forum, yet I see the "Test.mdb" which looks like an Access database.

If it's MS SQL, use the syntax like:

SELECT ... INTO SERVER2.DBname.dbo.TableName

You will also need to add SERVER2 as a linked server to SERVER1 (read BOL on sp_addlinkedserver).

|||

May you post your complete SQL Statement? Perhaps you have an syntax mistake (I never user into to query a access-database).

ralph

|||

I use MS SQL 2005

here the query, sorry the column names is hungarian

the server name is sysnapsys and synapsys have the two database

select

t1a.azonosító,

t1a.nv,

t1a.lakcím_irsz,

t1a.lakcím_helysg,

t1a.lakcím_uhea,

t1a.szlacím_nv,

t1a.szlacím_irsz,

t1a.szlacím_helysg,

t1a.szlacím_uhea,

t3.telepíts_id?pontja,

t4.szerzodes_megszunt_idopont,

t9.befizetett_eloleg,

t9.havi_díj_fizetsi_mód

into SzerzWorth

from t0, t1a, t3, t4, t9

where t0.t1a_id = t1a.idn and t0.t4_id = t4.idn

and t0.t9_id = t9.idn and t0.t3_id = t3.idn

this querry is working. If i want add the in clause after the "into SzerzWorth", then the server throw me an error. I try this methods:

" into SzerzWorth in 'Teszt' "

" into SzerzWorth in 'dbo.Teszt' "

" into SzerzWorth in 'synapsys.Teszt' "

" into SzerzWorth in 'synapsys.db.Teszt' "

but permanently throw me the error:

"Msg 156, Level 15, State 1, Line 15

Incorrect syntax near the keyword 'in'. "

|||I posted above|||hi mr. saint,

a SELECT INTO <table> IN is not a correct syntax. The IN keyword expects a list(s) of values e.g: IN (1, 2, 3) and usually used in WHERE clauses to check for data w/c matches the lists in IN.i.e.: SELECT * FROM table WHERE field1 IN (n1, n2.. n2n).. the query should be:

SELECT <fields>
INTO SzerzWorth
FROM t0, t1a, t4, t9

INSERT INTO synapsys.db.Teszt (fields...)
SELECT fields..
FROM SzerzWorth

or. simply:
INSERT INTO sysnapsys.db.Teszt(fields..)
SELECT <fields>
FROM t0, t1a, t4, t9

HTH|||I don′t know if that is the problem, but as I never use umlauts or special characters in the object names you should put the names in brackets, because the syntax look quite ok:

select

t1a.[azonosító],

t1a.[nv],

t1a.[lakcím_irsz],

t1a.[lakcím_helysg],

t1a.lakcím_uhea,

t1a.[szlacím_nv],

t1a.[szlacím_irsz],

t1a.[szlacím_helysg],

t1a.[szlacím_uhea],

t3.[telepíts_id?pontja],

t4.[szerzodes_megszunt_idopont],

t9.[befizetett_eloleg],

t9.[havi_díj_fizetsi_mód]

into SzerzWorth

from t0, t1a, t3, t4, t9

where t0.t1a_id = t1a.idn and t0.t4_id = t4.idn

and t0.t9_id = t9.idn and t0.t3_id = t3.idn

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem with Search engine access?

Hi,
We're running a Windows 2000/2003 network.
On Servers running Windows 2003 with or without SP1 and SQL Server 2000 SP3a
or 4, we cannot create any full Text Search Catalogs.
Whatever login we try to use, if we try to create a new catalog, from
Enterprise Manager, we get a dialog box stating "The Microsoft Search Engine
cannot be administered under the present user account"
Running profiler we can see the creation process starting. It appears to run
alright until it gets to the "sp_fulltext_database DBCC CALLFULLTEXT
(7,@.dbid) -- FTDropAllCatalogs("@.dbid") and then generates an Exception
"Error: 7635, Severity: 16, State: 1"
Help!
John,
The error "The Microsoft Search Engine cannot be administered under the
present user account" typically indicates that the SQL login
BUILTIN\Administrator has been removed or altered. Have you or anyone else
removed or altered the SQL Server login BUILTIN\Administrator? If so, can
you add or alter it back with the same parameters? As the MSSearch service
needs all of the default parameters to login to SQL Server, if not you can
use the following SQL code:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
See "SQL Server 2000 Full-Text Search Resources and Links" for more SQL FTS
info & KB articles at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John S" <js162@.newsgroup.nospam> wrote in message
news:0C9748C6-AB94-4AC3-AEED-4F97C1FCC49B@.microsoft.com...
> Hi,
> We're running a Windows 2000/2003 network.
> On Servers running Windows 2003 with or without SP1 and SQL Server 2000
SP3a
> or 4, we cannot create any full Text Search Catalogs.
> Whatever login we try to use, if we try to create a new catalog, from
> Enterprise Manager, we get a dialog box stating "The Microsoft Search
Engine
> cannot be administered under the present user account"
> Running profiler we can see the creation process starting. It appears to
run
> alright until it gets to the "sp_fulltext_database DBCC CALLFULLTEXT
> (7,@.dbid) -- FTDropAllCatalogs("@.dbid") and then generates an Exception
> "Error: 7635, Severity: 16, State: 1"
> Help!

Problem with Reporting service toolbox!

Hi All!!!!

When I create new reporting service I have problem with toolbox as follows:

Please help me to solved problem. Thanks

Hi,

From the image you provided, it seems that the report items in toolbox is mixed up.

Since we don't know if you have any modification on the toolbox (especially for the control assembly), I just provide you some steps to diagnose the problem.

1. Try to right click on the report items in your toolbox, and choose "Reset ToolBox" button, to see if it can fix the issue.
2. Try to right click on the report items in your toolbox, and click on 'choose items' to open the "Choose Toolbox items" window, switch to the .NET Framework components tab, try to check if there are so many textboxes control with different edition.
3. Go to %SystemRoot%/Assembly/ folder, try to find if the textbox controls exists normally.(Is any other extra textbox controls?)
4. After those steps, if the problem still exists, try to re-install your Business Intelligence Projects components.

Thanks.

Problem with report DataSource

Hello,
I am deploying reports via the web service. I create a folder, then create
a data source. Once I create a report, I make a reference to the data source
created and change the reports invalid data source to the reference.
When I did this with a data source with credentials stored on the report
server, it worked with no problems. When I changed this to prompt for
crendentials, accessing the report via web services cannot find the data
source associated with the report (the one defined in the rdl does not exist
in the folder fyi). Is this because a reference has no name in the database?
It does work via the Report Manager.
Here are some code snippets:
this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
definition, null);
//change its datasource to the new one
DataSourceReference reference = new DataSourceReference();
reference.Reference = "/" + dbName + "/" + dbName;
DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
dbName + "/" + reportType.ToString() );
for(int i=0;i<dataSources.Length;i++)
{
if(dataSources[i].Item is InvalidDataSourceReference)
{
dataSources[i].Item = reference;
}
}
this.ReportService.SetReportDataSources("/" + dbName + "/" +
reportType.ToString() ,dataSources);
and for credentials:
DataSourceCredentials d = new DataSourceCredentials();
d.DataSourceName = dbName;
d.UserName = userName;
d.Password = password;
this.mDataSourceCredentials = new DataSourceCredentials[]{d};
any help would be greatly appreciated.It seems recently that my posts never get replied to - Im sure others must
share the same frustration. The solution I came up with was to read the .rdl
file in as xml, search for all data source nodes and replace the old name
(attribute) with the new name. I also created the data source as a data
source, not as a reference.
"comet61" wrote:
> Hello,
> I am deploying reports via the web service. I create a folder, then create
> a data source. Once I create a report, I make a reference to the data source
> created and change the reports invalid data source to the reference.
> When I did this with a data source with credentials stored on the report
> server, it worked with no problems. When I changed this to prompt for
> crendentials, accessing the report via web services cannot find the data
> source associated with the report (the one defined in the rdl does not exist
> in the folder fyi). Is this because a reference has no name in the database?
> It does work via the Report Manager.
> Here are some code snippets:
> this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
> definition, null);
> //change its datasource to the new one
> DataSourceReference reference = new DataSourceReference();
> reference.Reference = "/" + dbName + "/" + dbName;
> DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
> dbName + "/" + reportType.ToString() );
> for(int i=0;i<dataSources.Length;i++)
> {
> if(dataSources[i].Item is InvalidDataSourceReference)
> {
> dataSources[i].Item = reference;
> }
> }
> this.ReportService.SetReportDataSources("/" + dbName + "/" +
> reportType.ToString() ,dataSources);
> and for credentials:
> DataSourceCredentials d = new DataSourceCredentials();
> d.DataSourceName = dbName;
> d.UserName = userName;
> d.Password = password;
> this.mDataSourceCredentials = new DataSourceCredentials[]{d};
> any help would be greatly appreciated.
>

Problem with replication

Dear friends
I have following problem in replication kindly suggest
what can be the problem.
Last Command: create table #belong_agent_-2049795750
(tablenick int NOT NULL, rowguid uniqueidentifier NOT
NULL,generation int NULL, lineage varbinary(255) NULL,
colv varbinary(2048) NULL)
Error Code: Failed to enumerate changes in the filtered
articles.
Failed to enumerate changes in the filtered articles.
(Source: Merge Replication Provider (Agent); Error
number: -2147200925)
----
Line 1: Incorrect syntax near '-'.
(Source: BPOGEM (Data source); Error number: 170)
----
does this help?
http://support.microsoft.com/?id=814916
"Niitmalad" <niitmalad@.yahoo.co.in> wrote in message
news:061a01c4b6c3$ca7b4250$a401280a@.phx.gbl...
> Dear friends
> I have following problem in replication kindly suggest
> what can be the problem.
> Last Command: create table #belong_agent_-2049795750
> (tablenick int NOT NULL, rowguid uniqueidentifier NOT
> NULL,generation int NULL, lineage varbinary(255) NULL,
> colv varbinary(2048) NULL)
> Error Code: Failed to enumerate changes in the filtered
> articles.
> Failed to enumerate changes in the filtered articles.
> (Source: Merge Replication Provider (Agent); Error
> number: -2147200925)
> ----
> ----
> Line 1: Incorrect syntax near '-'.
> (Source: BPOGEM (Data source); Error number: 170)
> ----
> ----
>

Problem with referencing System.Data.SqlServerCE.dll

Hello

I have referrenced System.Data.SqlServerCE.dll to my web project in order to create a .sdf file. I haven't wrote any code,just referenced it. When I build the project I get this error:

Error 2 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' cannot be loaded.

It sounds like you dont have the client dlls installed.

If you already installed the SDK you need to install it from C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\Desktop

if you dont have the SDK, I suggest you to install it, another option would be to download directly another redist.

|||

I have had problems too, related to incorrect referencing. There is a version in the public assemblies folder which causes this error.
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\System.Data.SqlServerCe.dll

This specific file/version is needed by the data designer in visual studio so dont delete it, just change any references in your projects, for System.Data.SqlServerCe.dll, to point to the correct file.

Usually

C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\System.Data.SqlServerCe.dll

OR

C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\System.Data.SqlServerCe.dll

Cheers

Anton

Problem with query and date ranges

I'm trying to create a query that will tell me which requests
took longer than 10 days to move one from particular state to another
state. The query I've created returns the correct requests,
but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.

I'm sure I'm missing something easy, but I can't figure out what it
might be. Any help is appreciated! Thanks,
Myron
-- remove SPAM-KILL from address to reply by email --

DDL for table creation and data population:

CREATE TABLE [dbo].[ReqHistory] (
[Id] [int] NOT NULL ,
[ReqId] [int] NOT NULL ,
[ReqIDStateId] [tinyint] NOT NULL ,
[ActionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RequestStates] (
[ID] [tinyint] NOT NULL ,
[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Requests] (
[ID] [int] NOT NULL ,
[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StatusChangeDate] [datetime] NULL ,
[Status] [tinyint] NULL
) ON [PRIMARY]
GO

insert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)
insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)
insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)

insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))
insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))
insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))
insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))
insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))
insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))
insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))
insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))
insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))
insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))
insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))
insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))
insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))
insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))
insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))
insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))
insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))
insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))

insert into RequestStates values(1,'Awaiting CSMC')
insert into RequestStates values(2,'CSMC Review')
insert into RequestStates values(3,'Reject Awaiting CSMC')
insert into RequestStates values(10,'Awaiting MA Review')
insert into RequestStates values(12,'Awaiting FO Review')
insert into RequestStates values(13,'Awaiting IS Review')
insert into RequestStates values(20,'MA Review')
insert into RequestStates values(22,'FO Review')
insert into RequestStates values(23,'IS Review')
insert into RequestStates values(30,'Func Approval')
insert into RequestStates values(31,'Func Approval Complete')
insert into RequestStates values(96,'Resolved')
insert into RequestStates values(97,'Planning')
insert into RequestStates values(98,'Open')
insert into RequestStates values(99,'Closed')

The query that almost works:

select irh.ReqID, irh.MAactiondate, irh.reviewstate,
irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextState
from (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,
irh.ReqIDStateID As IRHState, irs.statetext as ReviewState
from ReqHistory IRH
join requeststates irs on irs.id = irh.ReqIDStateID
where irh.ReqIDStateID in (20, 23)
group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irh
join (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetext
from (select min(actiondate) as actiondate, ReqID,
min(ReqIDStateID) as IRH2State
from ReqHistory
--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,
--but I couldn't make it work
where ReqIDStateID > 23
group by ReqID) as irh2
join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqID
join requests ir on ir.id = irh.ReqID
where irh.MAactiondate + 10 < irh2.Nextactiondate
order by irh.ReqID

The data being returned is:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 96 Resolved

The data that should have been returned:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMCHoly subqueries batman!

Thanks for including the DDL... it made finding an answer to this much
easier. A couple quick notes though... you didn't include and foreign
or primary keys. That combined with the inconsistent naming standards
made it a little difficult to understand the code.

The following code returned what you were expecting. The code assumes
that no two actions can occur at the same exact time. If that
assumption is incorrect then the code will not work correctly in those
situations.

Also, instead of the NOT EXISTS you could of course opt to use a LEFT
OUTER JOIN along with checking for IS NULL on one of the PK columns for
the table (RH). That often gives better performance than NOT EXISTS in
my experience.

HTH,
-Tom.

SELECT MAH.ReqID, MAH.ActionDate, RS.StateText, NA.ActionDate,
NA.ReqIDStateID, NS.StateText
FROM ReqHistory MAH
INNER JOIN ReqHistory NA ON NA.ReqID = MAH.ReqID
AND NA.ActionDate > DATEADD(dy, 10,
MAH.ActionDate)
INNER JOIN RequestStates RS ON RS.ID = MAH.ReqIDStateID
INNER JOIN RequestStates NS ON NS.ID = NA.ReqIDStateID
WHERE MAH.ReqIDStateID IN (20, 23)
AND NOT EXISTS (SELECT *
FROM ReqHistory RH
WHERE RH.ReqID = MAH.ReqID
AND RH.ActionDate > MAH.ActionDate
AND RH.ActionDate < NA.ActionDate)|||"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote:
>Holy subqueries batman!
>Thanks for including the DDL... it made finding an answer to this much
>easier. A couple quick notes though... you didn't include and foreign
>or primary keys. That combined with the inconsistent naming standards
>made it a little difficult to understand the code.
<some snippage
Thanks for the speedy and accurate answer, Tom! Your query looks a
lot more elegant than my monster, and it found rows that my original
was dropping. :)
Myron|||Your basic design is wrong. Time comes in durations and not points --
look at everyone from Zeno to the ISO standards. Also, your data
element names make no sense -- "ReqIDStateId"? if something is a
state, then it is a value and not an indentifier. What is the vague
"ID": floating around? Surely you do not blindly use sequentail
numbering in an RDBMS to mimic a sequential file physical record
number!

CREATE TABLE RequestHistory
( request_nbr INTEGER NOT NULL ,
request_status INTEGER NOT NULL
REFERENCES RequestStatusCodes(request_status)
ON UPDATE CASCADE,
start_date DATETIME DEFAULT CURRENT TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
PRIMARY KEY (request_nbr, start_date ));

>>I'm trying to create a query that will tell me which requests took
longer than 10 days to move one from particular state to another state.
<<

Trival with the right design, isn't it?