Question With SQLDATASOURCE and User.identity.name

is there a way that I can use the sqldatasource with a form view where my sqldatasource select statement is like this

select * from tblUsers wherevcUserName=@.vcUserName

<selectparameters>

<

asp:ParameterName="user.identity.name"Type="String/>

</selectParameters>

Hi,

The way you are trying will not work.

Here is a link from Peter with a solution to handle this case by using ExpressionBuilder:

http://peterkellner.net/2006/09/18/expressionbuilderidentity/

Another way, I think you can try to assign the user to the selectParameter from your code under SqlDataSource's selecting event programatically. Also, you can assign the login user to a session varable and access to it through SessionParameter

Question to linked server, wrong result

First the question and correct result in Oracle
SQL> select distinct anv_uppg, profilkod from ekop2.bav001
where anv_uppg like 'san%' ;
ANV_UPPG PRO
-- --
sanderss SAN
Linked server question from query analyzer
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where anv_uppg like 'san%'
sanderssGNO
sanderssSAN
I have never seen this before and it usually works fine.
Any idea what's wrong?
Are you sure you don't want
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where pro like 'san%'
becuase the query you showed return 2 records and that is correct for
the field anv_uppg (sanderss)
http://sqlservercode.blogspot.com/
|||Problem solved: no longer an issue
:::::::::::::::::::::::::::::::::::::::::::::::::: ::::::::::::
"ln54" wrote:

> First the question and correct result in Oracle
> SQL> select distinct anv_uppg, profilkod from ekop2.bav001
> where anv_uppg like 'san%' ;
> ANV_UPPG PRO
> -- --
> sanderss SAN
>
> ----
> Linked server question from query analyzer
> SELECT distinct ANV_UPPG, REG_NAMN
> FROM HACTAR..EKOP2.BAV001
> where anv_uppg like 'san%'
> --
> sanderssGNO
> sanderssSAN
> ----
> I have never seen this before and it usually works fine.
> Any idea what's wrong?
>
>
>

Question to linked server, wrong result

First the question and correct result in Oracle
SQL> select distinct anv_uppg, profilkod from ekop2.bav001
where anv_uppg like 'san%' ;
ANV_UPPG PRO
-- --
sanderss SAN
----
Linked server question from query analyzer
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where anv_uppg like 'san%'
--
sanderss GNO
sanderss SAN
----
I have never seen this before and it usually works fine.
Any idea what's wrong'Are you sure you don't want
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where pro like 'san%'
becuase the query you showed return 2 records and that is correct for
the field anv_uppg (sanderss)
http://sqlservercode.blogspot.com/|||Problem solved: no longer an issue
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
"ln54" wrote:
> First the question and correct result in Oracle
> SQL> select distinct anv_uppg, profilkod from ekop2.bav001
> where anv_uppg like 'san%' ;
> ANV_UPPG PRO
> -- --
> sanderss SAN
>
> ----
> Linked server question from query analyzer
> SELECT distinct ANV_UPPG, REG_NAMN
> FROM HACTAR..EKOP2.BAV001
> where anv_uppg like 'san%'
> --
> sanderss GNO
> sanderss SAN
> ----
> I have never seen this before and it usually works fine.
> Any idea what's wrong'
>
>
>sql

Question to linked server, wrong result

First the question and correct result in Oracle
SQL> select distinct anv_uppg, profilkod from ekop2.bav001
where anv_uppg like 'san%' ;
ANV_UPPG PRO
-- --
sanderss SAN
----
Linked server question from query analyzer
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where anv_uppg like 'san%'
sanderss GNO
sanderss SAN
----
I have never seen this before and it usually works fine.
Any idea what's wrong'Are you sure you don't want
SELECT distinct ANV_UPPG, REG_NAMN
FROM HACTAR..EKOP2.BAV001
where pro like 'san%'
becuase the query you showed return 2 records and that is correct for
the field anv_uppg (sanderss)
http://sqlservercode.blogspot.com/|||Problem solved: no longer an issue
::::::::::::::::::::::::::::::::::::::::
::::::::::::::::::::::
"ln54" wrote:

> First the question and correct result in Oracle
> SQL> select distinct anv_uppg, profilkod from ekop2.bav001
> where anv_uppg like 'san%' ;
> ANV_UPPG PRO
> -- --
> sanderss SAN
>
> ----
> Linked server question from query analyzer
> SELECT distinct ANV_UPPG, REG_NAMN
> FROM HACTAR..EKOP2.BAV001
> where anv_uppg like 'san%'
> --
> sanderss GNO
> sanderss SAN
> ----
> I have never seen this before and it usually works fine.
> Any idea what's wrong'
>
>
>

Question Regarding Stored Procedure??OUTPUTS

I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure....

CREATE procedure dbo.IDXAppt_Settings_NET
(
@.SQLADD nvarchar(15)Output,
@.SQLDatabase nvarchar(20)Output,
@.SQLLogin nvarchar(20)Output,
@.SQLPass nvarchar(20)Output
)
as
select
@.SQLADD=SQLAddress,
@.SQLDatabase=SQLDatabase,
@.SQLLogin=SQLLogin,
@.SQLPass=SQLPassword

from
Clinic_Settings

GO

Here is the Vb.Net Code......
To retrieve the elements that does not give me an error just gives me no data...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand

Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter

Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String

cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@.SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@.SQLData").Value

parmLogin = cmdsql.Parameters.Add("@.SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@.SQLLogin").Value

parmSqlPass = cmdsql.Parameters.Add("@.SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@.SQLPass").Value

parmSQLAddress = cmdsql.Parameters.Add("@.SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@.SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()

Label1.Text = strtest
End SubYou may get the output value after the stored procedure executed.|||Doesn't look like you want to use OUTPUTs here. You're query will get every row back from the table, so only the last (or is it the first - last I think) row will go into your output params.

Question on, usage of NOT IN

Hi All,
The following string comparision fails:
declare @.prodCode as nvarchar(50)
select @.prodCode = 'ABZC001'
if @.prodCode NOT IN ( 'ABZC001,CBQA03,FG0023')
BEGIN
...
...
END
The code within the BEGIN...END block is executed!
Any suggestions?
Thanks
kdIN / NOT IN takes a subquery or expression list as an argument. It won't
parse a string for you. I assume this is what you intend:
IF @.prodCode NOT IN ('ABZC001','CBQA03','FG0023')
If you want to search for substrings, use CHARINDEX or PATINDEX.
David Portas
SQL Server MVP
--

Question on SQL Server2000s order by clause

Hi,
I have problem of order by clase.
Must I specify the column in both select clause and order by clause
so as to get the correct result?

If I omit the order by column in select clause(for example:
select order.*, cl.ID from T_ORDER order, T_CLIENT cl where ... order by cl.code),
would MS SQL server 2000 still correctly or ignore order by clause completely?


It seems the latter actually happens.

BTW, how would other DBMS handle this case?

Regards,
Justinin SQL the ORDER BY clause is independent of the actual query is so far as you can sort by a column that is not listed in the select list

for example

select fname , lastname, middle
from t1
order by zipcode

on a side note you can also order by the ordinal number of the columns in the result set but this is confusing and not good form

ex
select fname , lastname, middle
from t1
order by 2

does this answer your question?sql

Question on SQL

Hello,
I am new to SQL Server. I am writing a stored procedure with a sql like this
:
SELECT
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
FROM TableX
WHERE
ID = '1234'
When I run the query, all the five column return same value and I am sure
the result is incorrect. Here is the function of fn_currency. It simply
convert foreign curreny amount to base currency amount. Is there anyone who
can tell me what's wrong with it'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER FUNCTION fn_Currency
(
@.Item_Curr Currency_Code,
@.AC_Curr Currency_Code,
@.In_Val Amount
)RETURNS Amount AS
BEGIN
DECLARE
@.Out_Val Amount,
@.Ex_Rate Ex_Rate,
@.Cal_Method Cal_Method
SELECT @.Out_Val = ISNULL(@.In_Val, 0)
IF @.Item_Curr <> @.AC_Curr
BEGIN
SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency = @.Item_Curr AND To_Currency = @.AC_Curr
SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
AND To_Currency = @.AC_Curr
SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
IF @.Cal_Method = 'M'
SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
ELSE
SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
END
Return @.Out_Val
END
Many thanks!
KennethHi
Without DDL for the UDT and tables it is hard to replicate your environment.
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to get this. You may
also want to post what version/service pack you are using.
Your procedure should return different values, have you tried it without the
SUMs?
John
"Kenneth" wrote:
> Hello,
> I am new to SQL Server. I am writing a stored procedure with a sql like this
> :
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
> When I run the query, all the five column return same value and I am sure
> the result is incorrect. Here is the function of fn_currency. It simply
> convert foreign curreny amount to base currency amount. Is there anyone who
> can tell me what's wrong with it'
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER FUNCTION fn_Currency
> (
> @.Item_Curr Currency_Code,
> @.AC_Curr Currency_Code,
> @.In_Val Amount
> )RETURNS Amount AS
> BEGIN
> DECLARE
> @.Out_Val Amount,
> @.Ex_Rate Ex_Rate,
> @.Cal_Method Cal_Method
> SELECT @.Out_Val = ISNULL(@.In_Val, 0)
> IF @.Item_Curr <> @.AC_Curr
> BEGIN
> SELECT @.Cal_Method = Cal_Method FROM ExRate_CalMethod WHERE From_Currency => @.Item_Curr AND To_Currency = @.AC_Curr
> SET @.Cal_Method = ISNULL(@.Cal_Method, 'M')
> SELECT @.Ex_Rate = Rate FROM Exchange_Rate WHERE From_Currency = @.Item_Curr
> AND To_Currency = @.AC_Curr
> SELECT @.Ex_Rate = ISNULL(@.Ex_Rate, 0)
> IF @.Cal_Method = 'M'
> SELECT @.Out_Val = ISNULL(@.In_Val * @.Ex_Rate, 0)
> ELSE
> SELECT @.Out_Val = ISNULL(@.In_Val / @.Ex_Rate, 0)
> END
> Return @.Out_Val
> END
> Many thanks!
> Kenneth
>|||On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
>Hello,
>I am new to SQL Server. I am writing a stored procedure with a sql like this
>:
> SELECT
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> FROM TableX
> WHERE
> ID = '1234'
>When I run the query, all the five column return same value and I am sure
>the result is incorrect. Here is the function of fn_currency. It simply
>convert foreign curreny amount to base currency amount. Is there anyone who
>can tell me what's wrong with it'
Hi Kenneth,
It looks like your server has not been upgraded with any service pack
yet. What you describe looks exactly like this problem, which was fixed
in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
--
Hugo Kornelis, SQL Server MVP|||I am using SQL server service pack 2 currently. However, I can still simulate
the error mentioned in the knowledge base. I am now trying to upgrade to
service pack 4 and test again. Thank you very much!
"Hugo Kornelis" wrote:
> On Tue, 4 Apr 2006 00:24:01 -0700, Kenneth wrote:
> >Hello,
> >
> >I am new to SQL Server. I am writing a stored procedure with a sql like this
> >:
> >
> > SELECT
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnA)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnB)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnC)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnD)) ,
> > sum(dbo.fn_Currency(Currency_Code, 'USD', ColumnE))
> > FROM TableX
> > WHERE
> > ID = '1234'
> >
> >When I run the query, all the five column return same value and I am sure
> >the result is incorrect. Here is the function of fn_currency. It simply
> >convert foreign curreny amount to base currency amount. Is there anyone who
> >can tell me what's wrong with it'
> Hi Kenneth,
> It looks like your server has not been upgraded with any service pack
> yet. What you describe looks exactly like this problem, which was fixed
> in service pack 1: http://support.microsoft.com/kb/288957/EN-US/
> --
> Hugo Kornelis, SQL Server MVP
>

question on SELECT 1 and Profiler

I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?
"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David
|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegr oups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David
|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas

"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...[vbcol=seagreen]
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably
>
|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegr oups.com...
> driver
> "ping"
> probably
|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas

"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegro ups.com...[vbcol=seagreen]
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the
>

question on SELECT 1 and Profiler

I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
driver[vbcol=seagreen]
"ping"[vbcol=seagreen]
probably[vbcol=seagreen]
>|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:[vbcol=seagreen]
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> driver
> "ping"
> probably|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegroups.com...
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
Only[vbcol=seagreen]
about,[vbcol=seagreen]
level[vbcol=seagreen]
sql[vbcol=seagreen]
the[vbcol=seagreen]
>

question on SELECT 1 and Profiler

I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
and I see a lot of SELECT 1 statements that are running longer in sql
2005 than in sql 2000. what are these select 1 statements?"Derek" <gepetto_2000@.yahoo.com> wrote in message
news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> and I see a lot of SELECT 1 statements that are running longer in sql
> 2005 than in sql 2000. what are these select 1 statements?
>
SELECT 1
Is a query that returns a single row and a single column containing the
value 1. It's extremely cheap, and probably being used as kind of "ping"
from some application. It's such a cheap query that its cost is probably
just background noise.
David|||How much longer? 1,000 times longer? SQL Server 2005's profiler can
track time in Miliseconds or Microseconds. I believe the default is
microseconds. If something took 1 ms (would not have shown that level
of detail, but just for an example.. In 2000 I believe the lowest level
of detail visible was about 10 or 12 ms, one of the reasons the
microseconds are used, to provide much more detail over the life of a
query and it's trending time) in 2005 profiler under microseconds it
would be 1,000 (still 1 milisecond, however).
David Browne wrote:
> "Derek" <gepetto_2000@.yahoo.com> wrote in message
> news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> >
> > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1 driver
> > and I see a lot of SELECT 1 statements that are running longer in sql
> > 2005 than in sql 2000. what are these select 1 statements?
> >
> SELECT 1
> Is a query that returns a single row and a single column containing the
> value 1. It's extremely cheap, and probably being used as kind of "ping"
> from some application. It's such a cheap query that its cost is probably
> just background noise.
> David|||First of all, the SELECT 1 query is used by Connection Pools to maintain
those connections as "live" so the pool manager does not close them. Only
the base connections should be executing them; otherwise, the pool would
never shrink.
Next, when you say 1,000 times longer, I'm not sure what you mean. On a
SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
execute the same script on SS2K5. What are the average per execution
duration and the overall duration for each of these installations?
Now, there can be environmental factors that you should be careful about,
and I would prefer to run these against each instance, but on the same
hardware, but they should still be roughly the same. There's just not a
whole lot of improvements a system could make on such a simple query.
Sincerely,
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> How much longer? 1,000 times longer? SQL Server 2005's profiler can
> track time in Miliseconds or Microseconds. I believe the default is
> microseconds. If something took 1 ms (would not have shown that level
> of detail, but just for an example.. In 2000 I believe the lowest level
> of detail visible was about 10 or 12 ms, one of the reasons the
> microseconds are used, to provide much more detail over the life of a
> query and it's trending time) in 2005 profiler under microseconds it
> would be 1,000 (still 1 milisecond, however).
>
> David Browne wrote:
> > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > >
> > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
driver
> > > and I see a lot of SELECT 1 statements that are running longer in sql
> > > 2005 than in sql 2000. what are these select 1 statements?
> > >
> >
> > SELECT 1
> >
> > Is a query that returns a single row and a single column containing the
> > value 1. It's extremely cheap, and probably being used as kind of
"ping"
> > from some application. It's such a cheap query that its cost is
probably
> > just background noise.
> >
> > David
>|||Anthony,
I am not the OP. I did not discuss what the SELECT 1 was. The original
poster indicated that when using profiler, a SELECT 1 query is taking a
lot (never indicated by what factor) longer to run. (Presumably when
looking at Duration in Profiler)
My reply indicated that in SQL Server 2005 you can view your Duration
event in Micro or Miliseconds. In SQL Server 2000 the duration shows
only in miliseconds. I was suggesting that the default behavior (which,
I believe is to view duration in MICROseconds) is probably what is
making the OP's SELECT 1 query appear to run a lot longer in SQL Server
2005. A microsecond is 1,000 times longer than a milisecond.
Sorry that wasn't clear.
Anthony Thomas wrote:
> First of all, the SELECT 1 query is used by Connection Pools to maintain
> those connections as "live" so the pool manager does not close them. Only
> the base connections should be executing them; otherwise, the pool would
> never shrink.
> Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> execute the same script on SS2K5. What are the average per execution
> duration and the overall duration for each of these installations?
> Now, there can be environmental factors that you should be careful about,
> and I would prefer to run these against each instance, but on the same
> hardware, but they should still be roughly the same. There's just not a
> whole lot of improvements a system could make on such a simple query.
> Sincerely,
>
> Anthony Thomas
>
> --
> "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> > How much longer? 1,000 times longer? SQL Server 2005's profiler can
> > track time in Miliseconds or Microseconds. I believe the default is
> > microseconds. If something took 1 ms (would not have shown that level
> > of detail, but just for an example.. In 2000 I believe the lowest level
> > of detail visible was about 10 or 12 ms, one of the reasons the
> > microseconds are used, to provide much more detail over the life of a
> > query and it's trending time) in 2005 profiler under microseconds it
> > would be 1,000 (still 1 milisecond, however).
> >
> >
> > David Browne wrote:
> > > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > > >
> > > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
> driver
> > > > and I see a lot of SELECT 1 statements that are running longer in sql
> > > > 2005 than in sql 2000. what are these select 1 statements?
> > > >
> > >
> > > SELECT 1
> > >
> > > Is a query that returns a single row and a single column containing the
> > > value 1. It's extremely cheap, and probably being used as kind of
> "ping"
> > > from some application. It's such a cheap query that its cost is
> probably
> > > just background noise.
> > >
> > > David
> >|||Yes, I think your analysis is correct. But, when in doubt, test it out. If
anything, just out of curiosity.
Anthony Thomas
"MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
news:1164129078.760070.81590@.j44g2000cwa.googlegroups.com...
> Anthony,
> I am not the OP. I did not discuss what the SELECT 1 was. The original
> poster indicated that when using profiler, a SELECT 1 query is taking a
> lot (never indicated by what factor) longer to run. (Presumably when
> looking at Duration in Profiler)
> My reply indicated that in SQL Server 2005 you can view your Duration
> event in Micro or Miliseconds. In SQL Server 2000 the duration shows
> only in miliseconds. I was suggesting that the default behavior (which,
> I believe is to view duration in MICROseconds) is probably what is
> making the OP's SELECT 1 query appear to run a lot longer in SQL Server
> 2005. A microsecond is 1,000 times longer than a milisecond.
> Sorry that wasn't clear.
>
> Anthony Thomas wrote:
> > First of all, the SELECT 1 query is used by Connection Pools to maintain
> > those connections as "live" so the pool manager does not close them.
Only
> > the base connections should be executing them; otherwise, the pool would
> > never shrink.
> >
> > Next, when you say 1,000 times longer, I'm not sure what you mean. On a
> > SS2K instance, run a script of a loop of SELECT 1 for 5,000 times. Now,
> > execute the same script on SS2K5. What are the average per execution
> > duration and the overall duration for each of these installations?
> >
> > Now, there can be environmental factors that you should be careful
about,
> > and I would prefer to run these against each instance, but on the same
> > hardware, but they should still be roughly the same. There's just not a
> > whole lot of improvements a system could make on such a simple query.
> >
> > Sincerely,
> >
> >
> > Anthony Thomas
> >
> >
> >
> > --
> >
> > "MikeWalsh" <mwalsh9815@.gmail.com> wrote in message
> > news:1164074119.932839.211510@.j44g2000cwa.googlegroups.com...
> > > How much longer? 1,000 times longer? SQL Server 2005's profiler can
> > > track time in Miliseconds or Microseconds. I believe the default is
> > > microseconds. If something took 1 ms (would not have shown that level
> > > of detail, but just for an example.. In 2000 I believe the lowest
level
> > > of detail visible was about 10 or 12 ms, one of the reasons the
> > > microseconds are used, to provide much more detail over the life of a
> > > query and it's trending time) in 2005 profiler under microseconds it
> > > would be 1,000 (still 1 milisecond, however).
> > >
> > >
> > > David Browne wrote:
> > > > "Derek" <gepetto_2000@.yahoo.com> wrote in message
> > > > news:1164034938.054630.206620@.k70g2000cwa.googlegroups.com...
> > > > >
> > > > > I am testing sql 2005 queries using the sql server 2005 jdbc 1.1
> > driver
> > > > > and I see a lot of SELECT 1 statements that are running longer in
sql
> > > > > 2005 than in sql 2000. what are these select 1 statements?
> > > > >
> > > >
> > > > SELECT 1
> > > >
> > > > Is a query that returns a single row and a single column containing
the
> > > > value 1. It's extremely cheap, and probably being used as kind of
> > "ping"
> > > > from some application. It's such a cheap query that its cost is
> > probably
> > > > just background noise.
> > > >
> > > > David
> > >
>

Question on SELECT * TOP 100 FROM TheTable

I have a question about how the TOP n clause works on the SELECT
statement.
Assume I have a table called TheTable that contains 10,000,000 rows
and no indexes.
Assume I execute the following SQL...
SELECT * TOP 100 FROM TheTable WHERE TheField = "12345"
Will SQL Server first do a TableScan to find ALL the rows that meet
the WHERE clause and THEN give me the TOP 100, or is it clever enough
to stop the TableScan when 100 matching rows have been found?
Thanks for any help.
RichardF> Assume I have a table called TheTable that contains 10,000,000 rows
> and no indexes.
Why would you ever have such a table?

> Will SQL Server first do a TableScan to find ALL the rows that meet
> the WHERE clause and THEN give me the TOP 100, or is it clever enough
> to stop the TableScan when 100 matching rows have been found?
Run your query, turn on execution plan, and check it out...|||Thanks for your 'help'.
It is a hypothetical table.
My question is about how SQL Server acts on the TOP n clause when one
of the fields involved in the WHERE clause is not indexed.
RichardF
On Thu, 10 Mar 2005 12:53:14 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Why would you ever have such a table?
>
>Run your query, turn on execution plan, and check it out...
>|||> Thanks for your 'help'.
I'm trying to teach you how to analyze these things yourself. Teach someone
to fish, rather than just handing them a fish, that kind of thing. Sorry if
that help isn't good enough for you.
If it is, then create a smaller table, select a smaller percentage, and the
direction the engine takes (which you can view in Query Analyzer if you turn
execution plan on) should be roughly equivalent.
Without more information, I think it is impossible for anyone to give you a
black and white answer. There are dozens of variables aside from index
existence that contribute to whether a scan is used and when the engine
knows to stop searching. So you are probably better off testing against the
kind of data you are working with anyway.|||If the query is as simple as this, then SQL-Server will start a table
scan. During the table scan the matching rows will be returned. If a
100th row is found, the table scan will stop.
You can test this by creating this hypothetical table, and filling it
with an ascending value for "TheField". Then you can measure the
performance difference between
SELECT TOP 1 * FROM TheTable WHERE TheField=1 -- Should be fast
and
SELECT TOP 1 * FROM TheTable WHERE TheField=10000000 -- Could be slow
Hope this helps,
Gert-Jan
RichardF wrote:
> I have a question about how the TOP n clause works on the SELECT
> statement.
> Assume I have a table called TheTable that contains 10,000,000 rows
> and no indexes.
> Assume I execute the following SQL...
> SELECT * TOP 100 FROM TheTable WHERE TheField = "12345"
> Will SQL Server first do a TableScan to find ALL the rows that meet
> the WHERE clause and THEN give me the TOP 100, or is it clever enough
> to stop the TableScan when 100 matching rows have been found?
> Thanks for any help.
> RichardF|||Like Gert-Jan and Aaron said: You have to experiment. From what I could
tell, SQL Server stops searching the table when it finds enough rows to
satisfy the TOP X requirement. If you leave off of TOP requirement (or
there aren't enough rows to fulfill the TOP X) then the entire table will be
scanned.
Of course if this was a production table that was being queried quite often,
you could want an index. :)
-- Create temp table
-- =================
CREATE TABLE #test (col int NOT NULL)
GO
-- This will take awhile... (5 million rows in my test database)
-- ========================
INSERT INTO #test
SELECT CHECKSUM(NEWID()) FROM sysobjects S1,sysobjects S2,sysobjects
S3,sysobjects S4,sysobjects S5
GO
-- Start experimenting. Turn on "Show Execution Plan' and "Show Server
Trace"
-- ========================================
==============================
SELECT TOP 0 col FROM #test WHERE col = '779668530'
SELECT TOP 1 col FROM #test WHERE col = '779668530'
SELECT TOP 2 col FROM #test WHERE col = '779668530'
SELECT col FROM #test WHERE col = '779668530'
-- Find out which values of "col" are repeated
-- For use in the previous "experimenting" calls
-- ========================================
=====
SELECT *, count(*) FROM #test
group by col having count(*) > 1
"RichardF" <noone@.nowhere.com> wrote in message
news:sm1131tr8v8muj14huhtrtvnjevdkkvirf@.
4ax.com...
>I have a question about how the TOP n clause works on the SELECT
> statement.
> Assume I have a table called TheTable that contains 10,000,000 rows
> and no indexes.
> Assume I execute the following SQL...
> SELECT * TOP 100 FROM TheTable WHERE TheField = "12345"
> Will SQL Server first do a TableScan to find ALL the rows that meet
> the WHERE clause and THEN give me the TOP 100, or is it clever enough
> to stop the TableScan when 100 matching rows have been found?
> Thanks for any help.
> RichardF

Question on repeated query

I have to select a number of fields for a report. Name Address, city,
state zip, etc. from a list of customers.
I have to select 2000 records from each of 29 states.
Is there a way to do this without repeating and repeating the code in
QA, or by changing the state manually and running it each time?
I have to select 2000 customers from WV, 2000 from VA, 2000 from FL,
2000 from TX and so on.
It's more of just a random selection of customers, with no other
requirements other than the name & address are a good one (i.e. non-null
last name, good zip code, etc).
Any help appreciated.Try,
select
Name Address, city, state, zip
from
t1 as a
where
customerid in (
select top 2000 customerid
from t1 as b
where b.state = a.state
)
go
If you run this query again, do not expect to pull same result because I am
not using "order by" clause.
AMB
"Blasting Cap" wrote:

> I have to select a number of fields for a report. Name Address, city,
> state zip, etc. from a list of customers.
> I have to select 2000 records from each of 29 states.
> Is there a way to do this without repeating and repeating the code in
> QA, or by changing the state manually and running it each time?
> I have to select 2000 customers from WV, 2000 from VA, 2000 from FL,
> 2000 from TX and so on.
> It's more of just a random selection of customers, with no other
> requirements other than the name & address are a good one (i.e. non-null
> last name, good zip code, etc).
> Any help appreciated.
>

question on locks

somebody helps ?

1.What kind of lockes are created on what resources when following
querys executes ?
select * from JOBQUEUE where ID='XXX'
update JOBQUEUE set columnA='YYY' where ID='XXX'

2.Under what kind of situation, share lock on a row or on a page or on a
table will convert to exclusive lock?

Thanks a lot.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!stan cai (caichuang@.digitalchina.com) writes:
> somebody helps ?
> 1.What kind of lockes are created on what resources when following
> querys executes ?
> select * from JOBQUEUE where ID='XXX'
> update JOBQUEUE set columnA='YYY' where ID='XXX'

Depends. If there is an index on ID, you should get a shared row lock
and a exclusive row lock. If there is no index on ID, both statement
will need a shared table lock. The UPDATE still needs an exclusive row
lock.

> 2.Under what kind of situation, share lock on a row or on a page or on a
> table will convert to exclusive lock?

When you update the row.

If you want to learn more about locking, it could be an idea to check
out Kalen Delaney's E-book, "Hands-On SQL Server 2000 : Troubleshooting
Locking and Blocking". See www.netimpress.com or directly:
http://www.shareit.com/product.html...¤cies=USD

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

question on date (or string after using convert function) comparis

select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
the result return is 'T' why? (2005 should be < 2006)
On Mon, 13 Feb 2006 19:05:26 -0800, kei wrote:

>select case when '02/12/2005' >= '01/02/2006' then 'T' else 'F' end
>the result return is 'T' why? (2005 should be < 2006)
Hi kei,
You are comparing two string constants. They may look like dates to you
and me (though probably not the same dates - I'm from the part of the
world that uses dd/mm/yyyy), but SQL Server doesn't try to interpret
what you write - it takes you literally.
You could try
SELECT CASE WHEN CAST('02/12/2005' AS datetime) >=
CAST('01/02/2006' AS datetime) THEN 'T' ELSE 'F' END
and pray that SQL Server interprets the ambiguous date format the same
way you do.
Or you could switch to a non-ambiguous date format:
SELECT CASE WHEN CAST('20051202' AS datetime) >=
CAST('20060201' AS datetime) THEN 'T' ELSE 'F' END
For more information, check Tibor Karaszi's article on SQL Server date
and time handling: http://www.karaszi.com/SQLServer/info_datetime.asp
Hugo Kornelis, SQL Server MVP

Question on case table and nested table

Hi, all here,

As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.

I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Selecting the same table as both case and nested table makes sense when the table actually has two keys and represents a one-to-many relationship. An example is a transaction table, which contains products purchased by each customer. It could look like below:
CustID ProductID
1 Beer
1 Milk
1 Coke
1 Chips
2 Oreo
2 Milk
...

As you see, there is a one-to-many relationship between CustID and ProductID.
Typical modeling with nested tables would require a separate table, with customer information (containing the distinct customers, their IDs and possibly other information) and a relationship from that table to this one, with CustID acting as a Foreign Key inside this transaction table.

However, when:
- no Customer table is present OR
- no additional useful information is available in the Customer table
it is helpful to use only the transaction table for mining.

Internally, the implementation issues two queries, one of them selecting all distinct customers.
Hope this clarifies the scenario and any benefits|||

Hi, Bogdan,

Thanks a lot for your very helpful advices.

With best regards,

Yours sincerely,

Question on attributes selection for un-supervised algorithms and supervised algorithms

Hi, all,

Thanks for your kind attention.

Just wonder is there any good idea for us to select attributes for training models? Both for non-supervised algorithms like Association Rules and Clustering etc. and supervised algorithms like decision tree etc.

It will be much interesting to hear from you for any best practices and popular methods of dealing with this issue.

I am looking forward to hearing from you and thanks for your advices.

With best regards,

Yours sincerely,

Hi,

I assume that you are trying to select a subset of all your attributes to train the models. SQL Server Data Mining Algorithms have built in feature selection methods. For example, the Microsoft Decision Trees support the following attribute scoring methods: Entropy, Bayesian with K2 Prior and Bayesian Dirichlet Equivalent with Uniform Prior (which is used by default). When feature selection is necessary, the algorithm calculates the scores for each attribute and only train trees with selected features (with top scores, of course). Other algorithms have similar feature selection mechanism.

Thanks,

Question on attributes selection for un-supervised algorithms and supervised algorithms

Hi, all,

Thanks for your kind attention.

Just wonder is there any good idea for us to select attributes for training models? Both for non-supervised algorithms like Association Rules and Clustering etc. and supervised algorithms like decision tree etc.

It will be much interesting to hear from you for any best practices and popular methods of dealing with this issue.

I am looking forward to hearing from you and thanks for your advices.

With best regards,

Yours sincerely,

Hi,

I assume that you are trying to select a subset of all your attributes to train the models. SQL Server Data Mining Algorithms have built in feature selection methods. For example, the Microsoft Decision Trees support the following attribute scoring methods: Entropy, Bayesian with K2 Prior and Bayesian Dirichlet Equivalent with Uniform Prior (which is used by default). When feature selection is necessary, the algorithm calculates the scores for each attribute and only train trees with selected features (with top scores, of course). Other algorithms have similar feature selection mechanism.

Thanks,

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
--
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> hi,
>> I have a t-sql script as follows,
>> Declare @.sql varchar(1000)
>> set @.sql = select count(*) from table1
>> exec (@.sql)
>> I want to know if recordcount = 0
>> then go line1
>> else go line2
>> How do I assign the return value(recordcount)
>> Thanks
>>
>

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>