field used to be comparing varchar to char such as this and it worked ok:
WHERE firstname = username
now the field is comparing long varchar vs. char:
WHERE thefullname = username
and it's returning this error:
Improper use of a string column, host variable, constant, or
function "". SQLSTATE=42907
Is there any way I can still compare these two fields ? It must be these fields so maybe a function on either of them would work ?
ThanksI've never tried it, but could you use Cast(myLongChar AS VARCHAR(1000)) or something like that?
-PatP|||thanks but the cast function created the following error:
SQL0604N The length, precision, or scale attribute for column, distinct type,
structured type, attribute of structured type, function, or type mapping "" is
not valid. SQLSTATE=42611|||Ok, just for the jolly factor, lets assume that you are running an unpatched (or at least not current) version of DB2. As Lewis Grizzard used to say: Shoot low boys, they're riding Shetland Ponies!
Try to use Cast(myLongChar AS VARCHAR(250)) and see if that works better.
-PatP
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Comparing dates in Case statement
hi
I am having problem in converting dates either to varchar or integer
I need to compare (just month and date part , not the year)
If
mm/dd (of current date) i.e 07/12 > 06/30
i.e if today's month and date is greater than june 30th then perform task A
if today's month and date is less than july 1st perform task B
i.e
07/12 < 07/01
please help
Thanks
Code Snippet
select case when (month(getdate())* 100 + day(getdate())) > 0630
then ... -- task a
else ... --task b
end
from ....
|||Thank you very much , that helped
But I have a new problem
I am trying to execute task A
as below
SELECT
case
when (month(getdate())* 100 + day(getdate())) > 0630
then
(select * from dbo.V_FUN_SCOPES_LEVELS0506)
end
It gives error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
|||You need to use IF syntax instead of CASE syntax; give a look to the related article in books online. That should look something like:
Code Snippet
if (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
Also, beware of using "SELECT *" syntax from within a stored procedure or function.
|||yea, it won't work in that manner.
Can you elaborate a little more on what you're trying to do and how it is going to be used.
From this is looks like you'll need a stored procedure or maybe a function.
|||I think in you're probably just looking at some standard IF...ELSE control of flow.
IF (SELECT (month(getdate())* 100 + day(getdate()))) > 0630
PRINT 'do this'
ELSE
PRINT 'do that'
|||Hi
the IF syntax works independently but If i try to create a store proc as below it gives error
CREATE PROCEDURE [dbo].[P_Latest]
AS
IF (month(getdate())* 100 + day(getdate())) > 0630
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506
END
ELSE
IF (month(getdate())* 100 + day(getdate())) < 0701
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506
END
G0
I need to display select * from PROCEDURE [dbo].[P_Latest]
i.e Exec PROCEDURE [dbo].[P_Latest] from SQL reporting services
P.S. Here the view dbo.V_FUN_SCOPES_LEVELS0506 is as below
I need the subquery for Display purpose
(select
a.student_id,
a.at_sss_read_score as Score_06,
b.at_sss_read_score as Score_07,
a.at_test_month + '/' + '20' + a.fcat_test_year as Date_06,
b.at_test_month + '/' + '20' + b.fcat_test_year as Date_07,
a.at_test_month as Month_06,
b.at_test_month as Month_07,
a.at_test_year as Year_06,
b.at_test_year as Year_07,
a.at_sss_read_level as Level_06,
b.at_sss_read_level as Level_07
from
(
(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate())-1 , 2)) a
--AND STUDENT_ID IN ('0011307')) a
full join
(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate()),2 )) b
--AND STUDENT_ID IN ('0011307')) b
on a.student_id = b.student_id
)
where
a.at_test_year is not null
and b.at_test_year is not null
)
This might work:
Code Snippet
CREATE PROCEDURE [dbo].[P_Latest]
AS
IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506
G0
But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.
I feel like I am going wrong with this. Anyone? Help?
|||In your Reporting Services dataset, just set the command type to StoredProcedure and the QueryString to [dbo].[P_Latest].
That is the equivalent of select * from ...
|||I chose to believe that that was just test code 
Kent Waldrop Jl07 wrote:
This might work:
Code Snippet
CREATE PROCEDURE [dbo].[P_Latest]
ASIF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506G0
But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.
I feel like I am going wrong with this. Anyone? Help?
I've always heard this... "...SELECT * from inside a procedure is a bad idea.." without really understanding why. I apologize if i'm deviating from the point but...
when you say compile time and not run time, do you mean if a store procedure is compiled with select * then subsequently, the table changes (ie. column added) the sp will return everything without the extra column?
|||Yes, that is exactly what I mean; it is for that reason that I will sometimes say that use of "SELECT *" leaves "land mines" that blow up sometime later.Tuesday, March 20, 2012
compare varchar column values case-sensitively?
Is it possible to case-sensitively compare 2 varchar column values
without changing the collation on the column to case-sensitive? For
example, I have column called Note on a table called Operation, and I'd
like to select distinct note, case-sensitively, without changing the
collation.
Possible?
-KJ<n_o_s_p_a__m@.mail.com> wrote in message
news:1135287389.111343.321820@.g47g2000cwa.googlegroups.com...
> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
> -KJ
>
SELECT columnlist
FROM tablename
JOIN tablename
WHERE col1 COLLATE <collation name> = col2 COLLATE <collation name>
Rick Sawtell|||you can specify a collation on the select
e.g. [using pubs.authors out of the box]
use pubs
update authors
set au_lname = 'ringer' -- only duplicated last name
where au_id='899-46-2035'
select distinct au_lname collate SQL_Latin1_General_CP1_CS_AS
from authors
-- 23 rows return
select distinct au_lname
from authors
-- 22 rows return
n_o_s_p_a__m@.mail.com wrote:
> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
> -KJ
>|||Thanks, Rick and Trey, for the quick and accurate replies!
-KJ
without changing the collation on the column to case-sensitive? For
example, I have column called Note on a table called Operation, and I'd
like to select distinct note, case-sensitively, without changing the
collation.
Possible?
-KJ<n_o_s_p_a__m@.mail.com> wrote in message
news:1135287389.111343.321820@.g47g2000cwa.googlegroups.com...
> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
> -KJ
>
SELECT columnlist
FROM tablename
JOIN tablename
WHERE col1 COLLATE <collation name> = col2 COLLATE <collation name>
Rick Sawtell|||you can specify a collation on the select
e.g. [using pubs.authors out of the box]
use pubs
update authors
set au_lname = 'ringer' -- only duplicated last name
where au_id='899-46-2035'
select distinct au_lname collate SQL_Latin1_General_CP1_CS_AS
from authors
-- 23 rows return
select distinct au_lname
from authors
-- 22 rows return
n_o_s_p_a__m@.mail.com wrote:
> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
> -KJ
>|||Thanks, Rick and Trey, for the quick and accurate replies!
-KJ
Monday, March 19, 2012
Compare tables
can any one suggested me a better way to compare two tables.
I tried following,
create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
insert into t1(col1,col2) values('c11','c12')
insert into t1(col1,col2) values('c21','c22')
insert into t1(col1,col2) values('c31','c32')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t2(col1,col2) values('c11','c12')
insert into t2(col1,col2) values('c21','c22')
insert into t2(col1,col2) values('c31','c32')
insert into t2(col1,col2) values('c41','c42')
select col1 from
(
select col1,col2 from
t1
union all
select col1,col2 from
t2
) a
group by col1,col2 having count(*)<>2skg,
"What" is it that you want to find out?
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
> can any one suggested me a better way to compare two tables.
> I tried following,
> create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> insert into t1(col1,col2) values('c11','c12')
> insert into t1(col1,col2) values('c21','c22')
> insert into t1(col1,col2) values('c31','c32')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
>
> insert into t2(col1,col2) values('c11','c12')
> insert into t2(col1,col2) values('c21','c22')
> insert into t2(col1,col2) values('c31','c32')
> insert into t2(col1,col2) values('c41','c42')
>
> select col1 from
> (
> select col1,col2 from
> t1
> union all
> select col1,col2 from
> t2
> ) a
> group by col1,col2 having count(*)<>2
>|||Since you do not have any keys and the columns are NULL-able, this is
probably as good as anything else.|||Thanks!!. I want to to know if both the tables have same rows of data. i.e
both tables are same.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
> skg,
> "What" is it that you want to find out?
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||skg,
You can start with something like this:
SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
and work it with IS NOT NULL etc.. depending on your needs and requirements.
A more flexible and robust solution would be to use a third-party software
package to make the changes if required. See (as an example):
http://www.red-gate.com/products/SQ...mpare/index.htm
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
> Thanks!!. I want to to know if both the tables have same rows of data. i.e
> both tables are same.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||Thanks!!! Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
> skg,
> You can start with something like this:
> SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
> and work it with IS NOT NULL etc.. depending on your needs and
> requirements.
> A more flexible and robust solution would be to use a third-party software
> package to make the changes if required. See (as an example):
> http://www.red-gate.com/products/SQ...mpare/index.htm
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
>|||skg
SELECT a.col1,a.col2,b.col1,b.col2
From (Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t1 ) a
Inner Join (
Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t2 ) b
On a.col1 = b.col1 and a.col2 = b.col2
Where a.CheckSum = b.CheckSum
"skg" <skg@.yahoo.com> wrote in message
news:umyMn5dzFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks!!! Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
>
I tried following,
create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
insert into t1(col1,col2) values('c11','c12')
insert into t1(col1,col2) values('c21','c22')
insert into t1(col1,col2) values('c31','c32')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t1(col1,col2) values('c41','c42')
insert into t2(col1,col2) values('c11','c12')
insert into t2(col1,col2) values('c21','c22')
insert into t2(col1,col2) values('c31','c32')
insert into t2(col1,col2) values('c41','c42')
select col1 from
(
select col1,col2 from
t1
union all
select col1,col2 from
t2
) a
group by col1,col2 having count(*)<>2skg,
"What" is it that you want to find out?
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
> can any one suggested me a better way to compare two tables.
> I tried following,
> create table t1(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> create table t2(tid int identity(1,1), col1 varchar(10),col2 varchar(10))
> insert into t1(col1,col2) values('c11','c12')
> insert into t1(col1,col2) values('c21','c22')
> insert into t1(col1,col2) values('c31','c32')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
> insert into t1(col1,col2) values('c41','c42')
>
> insert into t2(col1,col2) values('c11','c12')
> insert into t2(col1,col2) values('c21','c22')
> insert into t2(col1,col2) values('c31','c32')
> insert into t2(col1,col2) values('c41','c42')
>
> select col1 from
> (
> select col1,col2 from
> t1
> union all
> select col1,col2 from
> t2
> ) a
> group by col1,col2 having count(*)<>2
>|||Since you do not have any keys and the columns are NULL-able, this is
probably as good as anything else.|||Thanks!!. I want to to know if both the tables have same rows of data. i.e
both tables are same.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
> skg,
> "What" is it that you want to find out?
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:OkmKx4czFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||skg,
You can start with something like this:
SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
and work it with IS NOT NULL etc.. depending on your needs and requirements.
A more flexible and robust solution would be to use a third-party software
package to make the changes if required. See (as an example):
http://www.red-gate.com/products/SQ...mpare/index.htm
HTH
Jerry
"skg" <skg@.yahoo.com> wrote in message
news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
> Thanks!!. I want to to know if both the tables have same rows of data. i.e
> both tables are same.
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:uLek66czFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||Thanks!!! Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
> skg,
> You can start with something like this:
> SELECT * FROM T1 FULL JOIN T2 ON T1.TID = T2.TID
> and work it with IS NOT NULL etc.. depending on your needs and
> requirements.
> A more flexible and robust solution would be to use a third-party software
> package to make the changes if required. See (as an example):
> http://www.red-gate.com/products/SQ...mpare/index.htm
> HTH
> Jerry
> "skg" <skg@.yahoo.com> wrote in message
> news:uLQ$XcdzFHA.156@.tk2msftngp13.phx.gbl...
>|||skg
SELECT a.col1,a.col2,b.col1,b.col2
From (Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t1 ) a
Inner Join (
Select col1,col2, BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.t2 ) b
On a.col1 = b.col1 and a.col2 = b.col2
Where a.CheckSum = b.CheckSum
"skg" <skg@.yahoo.com> wrote in message
news:umyMn5dzFHA.720@.TK2MSFTNGP15.phx.gbl...
> Thanks!!! Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OoI4R3dzFHA.1040@.TK2MSFTNGP14.phx.gbl...
>
Sunday, March 11, 2012
Compare letters, v-w, s-z...
Hello,
I have SQL2000.
If I search in a varchar after 'Eva', I get both 'Eva' and 'Ewa' (v-w), and
that is really great.
Is there a way to administrate this? Can I add more "compares", so if I
search for 'Has' I get 'Has' and 'Haz' (s-z)?
Regards,
/Mikael SörenssonThe reason is that you are using a collation where the letters W and V are
treated as the same letter. In Sweden, AFAIK, there is no W formally, hence
this treatment. You would have to find a collation which treats S and Z in a
similar way, but then the collation might not handle V and W anymore. There
is no setting except the collation setting, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:%23W%23LUirqDHA.2000@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have SQL2000.
> If I search in a varchar after 'Eva', I get both 'Eva' and 'Ewa' (v-w),
and
> that is really great.
> Is there a way to administrate this? Can I add more "compares", so if I
> search for 'Has' I get 'Has' and 'Haz' (s-z)?
> Regards,
> /Mikael Sörensson
>
I have SQL2000.
If I search in a varchar after 'Eva', I get both 'Eva' and 'Ewa' (v-w), and
that is really great.
Is there a way to administrate this? Can I add more "compares", so if I
search for 'Has' I get 'Has' and 'Haz' (s-z)?
Regards,
/Mikael SörenssonThe reason is that you are using a collation where the letters W and V are
treated as the same letter. In Sweden, AFAIK, there is no W formally, hence
this treatment. You would have to find a collation which treats S and Z in a
similar way, but then the collation might not handle V and W anymore. There
is no setting except the collation setting, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mikael" <mikael@.DELETETHISslk.nu> wrote in message
news:%23W%23LUirqDHA.2000@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have SQL2000.
> If I search in a varchar after 'Eva', I get both 'Eva' and 'Ewa' (v-w),
and
> that is really great.
> Is there a way to administrate this? Can I add more "compares", so if I
> search for 'Has' I get 'Has' and 'Haz' (s-z)?
> Regards,
> /Mikael Sörensson
>
Wednesday, March 7, 2012
compare 2 strings
Hi,
I need to compare 2 strings from 2 different tables but there is no common
column for a join.
CREATE TABLE T1 (FirstName varchar(20), LastName varchar(20))
CREATE TABLE T2 (FirstName varchar(20), LastName varchar(20))
I created 2 temp tables (#T1 & #T2) populated with binary data for the
comparasion but not realy sure how to proceed from there...
CAST(lower(FirstName + LastName) AS VARBINARY) AS FullNameYan
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where
charindex(cast('H' as varbinary(20)),cast(description as varbinary(20)))> 0
"Yan" <yanive@.rediffmail.com> wrote in message
news:OU4JgyrQGHA.4920@.tk2msftngp13.phx.gbl...
> Hi,
> I need to compare 2 strings from 2 different tables but there is no common
> column for a join.
> CREATE TABLE T1 (FirstName varchar(20), LastName varchar(20))
> CREATE TABLE T2 (FirstName varchar(20), LastName varchar(20))
> I created 2 temp tables (#T1 & #T2) populated with binary data for the
> comparasion but not realy sure how to proceed from there...
> CAST(lower(FirstName + LastName) AS VARBINARY) AS FullName
>|||Sorry, I do not understand.
I need to find if any record from #T1 exists in #T2 in order to know if any
name wich exists in #T1 also exists in #T2.
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6G0u3rQGHA.4312@.TK2MSFTNGP12.phx.gbl...
> Yan
> create table ABCD
> (
> courceid smallint not null,
> description varchar(20) null
> )
> insert into ABCD(courceid,description)values (1,'DFh2AcZ')
> insert into ABCD(courceid,description)values (2,'dHZ3')
> )
> SELECT description FROM ABCD where
> charindex(cast('H' as varbinary(20)),cast(description as varbinary(20)))>
> 0
>
>
> "Yan" <yanive@.rediffmail.com> wrote in message
> news:OU4JgyrQGHA.4920@.tk2msftngp13.phx.gbl...
>|||Yan
CREATE TABLE #T1 ( col1 VARCHAR(10)NOT NULL)
CREATE TABLE #T2 ( col1 VARCHAR(10)NOT NULL)
INSERT INTO #T1 VALUES ('Clinton')
INSERT INTO #T1 VALUES ('Bush')
INSERT INTO #T1 VALUES ('Lenin')
INSERT INTO #T1 VALUES ('Putin')
INSERT INTO #T2 VALUES ('Stalin')
INSERT INTO #T2 VALUES ('Ford')
INSERT INTO #T2 VALUES ('Lenin')
INSERT INTO #T2 VALUES ('Putin')
--SQL Server 2000
SELECT * FROM #T1
WHERE col1 NOT IN (SELECT col1 FROM #T2)
--SQL Server 2005
SELECT * FROM #T1 EXCEPT SELECT * FROM #T2;
"Yan" <yanive@.rediffmail.com> wrote in message
news:u5p1$DsQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Sorry, I do not understand.
> I need to find if any record from #T1 exists in #T2 in order to know if
> any name wich exists in #T1 also exists in #T2.
> --
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6G0u3rQGHA.4312@.TK2MSFTNGP12.phx.gbl...
>|||Thank you.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2361Y8LsQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Yan
> CREATE TABLE #T1 ( col1 VARCHAR(10)NOT NULL)
> CREATE TABLE #T2 ( col1 VARCHAR(10)NOT NULL)
> INSERT INTO #T1 VALUES ('Clinton')
> INSERT INTO #T1 VALUES ('Bush')
> INSERT INTO #T1 VALUES ('Lenin')
> INSERT INTO #T1 VALUES ('Putin')
> INSERT INTO #T2 VALUES ('Stalin')
> INSERT INTO #T2 VALUES ('Ford')
> INSERT INTO #T2 VALUES ('Lenin')
> INSERT INTO #T2 VALUES ('Putin')
> --SQL Server 2000
> SELECT * FROM #T1
> WHERE col1 NOT IN (SELECT col1 FROM #T2)
> --SQL Server 2005
> SELECT * FROM #T1 EXCEPT SELECT * FROM #T2;
>
>
>
>
>
> "Yan" <yanive@.rediffmail.com> wrote in message
> news:u5p1$DsQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>|||On Wed, 8 Mar 2006 17:06:03 +0200, "Yan" <yanive@.rediffmail.com>
wrote:
>I need to find if any record from #T1 exists in #T2 in order to know if any
>name wich exists in #T1 also exists in #T2.
SELECT *
FROM T1
WHERE EXISTS
(select * from T2
where T1.FirstName = T2.Firstname
and T1.LastName = T2.LastName)
Using IN is fine when you only have one column to test. When you have
two columns to test EXISTS is much preferred. You really do NOT want
to concatenate strings together for testing unless there is very
special reason to, such as strange data.
Roy Harvey
Beacon Falls, CT
I need to compare 2 strings from 2 different tables but there is no common
column for a join.
CREATE TABLE T1 (FirstName varchar(20), LastName varchar(20))
CREATE TABLE T2 (FirstName varchar(20), LastName varchar(20))
I created 2 temp tables (#T1 & #T2) populated with binary data for the
comparasion but not realy sure how to proceed from there...
CAST(lower(FirstName + LastName) AS VARBINARY) AS FullNameYan
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where
charindex(cast('H' as varbinary(20)),cast(description as varbinary(20)))> 0
"Yan" <yanive@.rediffmail.com> wrote in message
news:OU4JgyrQGHA.4920@.tk2msftngp13.phx.gbl...
> Hi,
> I need to compare 2 strings from 2 different tables but there is no common
> column for a join.
> CREATE TABLE T1 (FirstName varchar(20), LastName varchar(20))
> CREATE TABLE T2 (FirstName varchar(20), LastName varchar(20))
> I created 2 temp tables (#T1 & #T2) populated with binary data for the
> comparasion but not realy sure how to proceed from there...
> CAST(lower(FirstName + LastName) AS VARBINARY) AS FullName
>|||Sorry, I do not understand.
I need to find if any record from #T1 exists in #T2 in order to know if any
name wich exists in #T1 also exists in #T2.
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u6G0u3rQGHA.4312@.TK2MSFTNGP12.phx.gbl...
> Yan
> create table ABCD
> (
> courceid smallint not null,
> description varchar(20) null
> )
> insert into ABCD(courceid,description)values (1,'DFh2AcZ')
> insert into ABCD(courceid,description)values (2,'dHZ3')
> )
> SELECT description FROM ABCD where
> charindex(cast('H' as varbinary(20)),cast(description as varbinary(20)))>
> 0
>
>
> "Yan" <yanive@.rediffmail.com> wrote in message
> news:OU4JgyrQGHA.4920@.tk2msftngp13.phx.gbl...
>|||Yan
CREATE TABLE #T1 ( col1 VARCHAR(10)NOT NULL)
CREATE TABLE #T2 ( col1 VARCHAR(10)NOT NULL)
INSERT INTO #T1 VALUES ('Clinton')
INSERT INTO #T1 VALUES ('Bush')
INSERT INTO #T1 VALUES ('Lenin')
INSERT INTO #T1 VALUES ('Putin')
INSERT INTO #T2 VALUES ('Stalin')
INSERT INTO #T2 VALUES ('Ford')
INSERT INTO #T2 VALUES ('Lenin')
INSERT INTO #T2 VALUES ('Putin')
--SQL Server 2000
SELECT * FROM #T1
WHERE col1 NOT IN (SELECT col1 FROM #T2)
--SQL Server 2005
SELECT * FROM #T1 EXCEPT SELECT * FROM #T2;
"Yan" <yanive@.rediffmail.com> wrote in message
news:u5p1$DsQGHA.5092@.TK2MSFTNGP11.phx.gbl...
> Sorry, I do not understand.
> I need to find if any record from #T1 exists in #T2 in order to know if
> any name wich exists in #T1 also exists in #T2.
> --
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u6G0u3rQGHA.4312@.TK2MSFTNGP12.phx.gbl...
>|||Thank you.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2361Y8LsQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Yan
> CREATE TABLE #T1 ( col1 VARCHAR(10)NOT NULL)
> CREATE TABLE #T2 ( col1 VARCHAR(10)NOT NULL)
> INSERT INTO #T1 VALUES ('Clinton')
> INSERT INTO #T1 VALUES ('Bush')
> INSERT INTO #T1 VALUES ('Lenin')
> INSERT INTO #T1 VALUES ('Putin')
> INSERT INTO #T2 VALUES ('Stalin')
> INSERT INTO #T2 VALUES ('Ford')
> INSERT INTO #T2 VALUES ('Lenin')
> INSERT INTO #T2 VALUES ('Putin')
> --SQL Server 2000
> SELECT * FROM #T1
> WHERE col1 NOT IN (SELECT col1 FROM #T2)
> --SQL Server 2005
> SELECT * FROM #T1 EXCEPT SELECT * FROM #T2;
>
>
>
>
>
> "Yan" <yanive@.rediffmail.com> wrote in message
> news:u5p1$DsQGHA.5092@.TK2MSFTNGP11.phx.gbl...
>|||On Wed, 8 Mar 2006 17:06:03 +0200, "Yan" <yanive@.rediffmail.com>
wrote:
>I need to find if any record from #T1 exists in #T2 in order to know if any
>name wich exists in #T1 also exists in #T2.
SELECT *
FROM T1
WHERE EXISTS
(select * from T2
where T1.FirstName = T2.Firstname
and T1.LastName = T2.LastName)
Using IN is fine when you only have one column to test. When you have
two columns to test EXISTS is much preferred. You really do NOT want
to concatenate strings together for testing unless there is very
special reason to, such as strange data.
Roy Harvey
Beacon Falls, CT
Friday, February 24, 2012
Communicate XML between SP’s
Hi,
I have two databases and need to transfer data between them. The problem is
that I’m facing the 8000 characters limitation of a varchar and I cannot use
a text declaration within the trigger/SP itself. Therefore I had the
following idea:
SP 1:
CREATE PROCEDURE [DBO].[testXMLSelect] AS
declare @.idoc as int
--Load and parse the XML document in Memory
EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
for xml auto
exec testXMLInsert @.idoc
--now clear the XML document from memory
EXEC sp_xml_removedocument @.idoc
GO
SP 2
CREATE PROCEDURE [dbo].[testXMLInsert]
@.idoc as int
AS
insert into testtext (test)
select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
GO
My Idea was to parse the handle to the XML document in Memory from one to
the other SP, and later on to the other database (same server). Does anyone
have an idea how to solve this?
Regards,
Merijn
You cannot use a select statement as a parameter to a stored procedure...
assign the result to a variable and pass the variable.
Also, you may want to upgrade to SQL Server 2005 where you don't have the 8k
limit anylonger (and have XML data type and much more).
Best regards
Michael
"Merijn" <Merijn@.discussions.microsoft.com> wrote in message
news:482A7B95-4FC7-4C36-B4C9-BDFDAC76C31C@.microsoft.com...
> Hi,
> I have two databases and need to transfer data between them. The problem
> is
> that I'm facing the 8000 characters limitation of a varchar and I cannot
> use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does
> anyone
> have an idea how to solve this?
> Regards,
> Merijn
>
|||On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
> Hi,
> I have two databases and need to transfer data between them. The problem is
> that I'm facing the 8000 characters limitation of a varchar and I cannot use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does anyone
> have an idea how to solve this?
> Regards,
> Merijn
I have a requirment to share data between two databases and I'm hoping
you could outline to me the steps you have taken to get me started. I
won't run into the varchar limit in my application.
Thanks,
lq
|||On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
wrote:
> On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
>
>
> I have a requirment to share data between two databases and I'm hoping
> you could outline to me the steps you have taken to get me started. I
> won't run into the varchar limit in my application.
> Thanks,
> lq
Now that you mentioned the Server is same, but have 2 seperate DBs,
how about using DB Name of the 1st DB in your QUERY of 2nd DB;
something like this
In DB2, you have your query as
Create table #t (MyXMLColumn XML )
Insert INTO #t (MyXMLColumn)
Select <WhatEverXMLColumnNameYouWant> From
<DBName>.<ObjectOwner>.testtext where id=3
|||On Mar 4, 2:59 pm, prabh...@.gmail.com wrote:
> On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
> wrote:
>
>
>
>
>
> Now that you mentioned the Server is same, but have 2 seperate DBs,
> how about using DB Name of the 1st DB in your QUERY of 2nd DB;
> something like this
> In DB2, you have your query as
> Create table #t (MyXMLColumn XML )
> Insert INTO #t (MyXMLColumn)
> Select <WhatEverXMLColumnNameYouWant> From
> <DBName>.<ObjectOwner>.testtext where id=3- Hide quoted text -
> - Show quoted text -
In my case it's two different servers I cave to communicate using SOAP/
XML to send data from the SQL Server 2000 database to the remote
server.
I have two databases and need to transfer data between them. The problem is
that I’m facing the 8000 characters limitation of a varchar and I cannot use
a text declaration within the trigger/SP itself. Therefore I had the
following idea:
SP 1:
CREATE PROCEDURE [DBO].[testXMLSelect] AS
declare @.idoc as int
--Load and parse the XML document in Memory
EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
for xml auto
exec testXMLInsert @.idoc
--now clear the XML document from memory
EXEC sp_xml_removedocument @.idoc
GO
SP 2
CREATE PROCEDURE [dbo].[testXMLInsert]
@.idoc as int
AS
insert into testtext (test)
select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
GO
My Idea was to parse the handle to the XML document in Memory from one to
the other SP, and later on to the other database (same server). Does anyone
have an idea how to solve this?
Regards,
Merijn
You cannot use a select statement as a parameter to a stored procedure...
assign the result to a variable and pass the variable.
Also, you may want to upgrade to SQL Server 2005 where you don't have the 8k
limit anylonger (and have XML data type and much more).
Best regards
Michael
"Merijn" <Merijn@.discussions.microsoft.com> wrote in message
news:482A7B95-4FC7-4C36-B4C9-BDFDAC76C31C@.microsoft.com...
> Hi,
> I have two databases and need to transfer data between them. The problem
> is
> that I'm facing the 8000 characters limitation of a varchar and I cannot
> use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does
> anyone
> have an idea how to solve this?
> Regards,
> Merijn
>
|||On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
> Hi,
> I have two databases and need to transfer data between them. The problem is
> that I'm facing the 8000 characters limitation of a varchar and I cannot use
> a text declaration within the trigger/SP itself. Therefore I had the
> following idea:
> SP 1:
> CREATE PROCEDURE [DBO].[testXMLSelect] AS
> declare @.idoc as int
> --Load and parse the XML document in Memory
> EXEC sp_xml_preparedocument @.idoc OUTPUT select * from testtext where id=3
> for xml auto
> exec testXMLInsert @.idoc
> --now clear the XML document from memory
> EXEC sp_xml_removedocument @.idoc
> GO
> SP 2
> CREATE PROCEDURE [dbo].[testXMLInsert]
> @.idoc as int
> AS
> insert into testtext (test)
> select test from openxml(@.idoc,'/testtext',0) WITH (id int, test text)
> GO
> My Idea was to parse the handle to the XML document in Memory from one to
> the other SP, and later on to the other database (same server). Does anyone
> have an idea how to solve this?
> Regards,
> Merijn
I have a requirment to share data between two databases and I'm hoping
you could outline to me the steps you have taken to get me started. I
won't run into the varchar limit in my application.
Thanks,
lq
|||On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
wrote:
> On Feb 23, 8:42 am, Merijn <Mer...@.discussions.microsoft.com> wrote:
>
>
> I have a requirment to share data between two databases and I'm hoping
> you could outline to me the steps you have taken to get me started. I
> won't run into the varchar limit in my application.
> Thanks,
> lq
Now that you mentioned the Server is same, but have 2 seperate DBs,
how about using DB Name of the 1st DB in your QUERY of 2nd DB;
something like this
In DB2, you have your query as
Create table #t (MyXMLColumn XML )
Insert INTO #t (MyXMLColumn)
Select <WhatEverXMLColumnNameYouWant> From
<DBName>.<ObjectOwner>.testtext where id=3
|||On Mar 4, 2:59 pm, prabh...@.gmail.com wrote:
> On Feb 25, 9:54 am, "Lauren Quantrell" <laurenquantr...@.hotmail.com>
> wrote:
>
>
>
>
>
> Now that you mentioned the Server is same, but have 2 seperate DBs,
> how about using DB Name of the 1st DB in your QUERY of 2nd DB;
> something like this
> In DB2, you have your query as
> Create table #t (MyXMLColumn XML )
> Insert INTO #t (MyXMLColumn)
> Select <WhatEverXMLColumnNameYouWant> From
> <DBName>.<ObjectOwner>.testtext where id=3- Hide quoted text -
> - Show quoted text -
In my case it's two different servers I cave to communicate using SOAP/
XML to send data from the SQL Server 2000 database to the remote
server.
Thursday, February 16, 2012
comment in column definition
Plesae tell me the MSSQL Server equivalent of the below MySQL query .
create table temp2(a varchar(23) comment 'male m');
What is the use of specifying a keyword 'comment' in the column definition. Will it make any differenceThat create table statement does not work in SQL Server. What RDBMS are you using?|||Sorry , the above SQL Query is in MySQL Syntax . Could you please tell me the equivalent in MSSQL Server.|||Is comment setting a default?|||Actually it looks more like SPSS value labels or the like. If so - no- you need to set up another table and establish a foreign key.|||I'm guessing that you are trying to add a comment to your column to indicate what it is for. Depending on your version of SQL Server, you will have to use a separate syntax:
SQL 2000
sp_addextendedproperty 'Caption', 'US Phone Number',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
SQL 2005:
EXEC sys.sp_addextendedproperty
@.name = N'MS_DescriptionExample',
@.value = N'Minimum inventory quantity.',
@.level0type = N'SCHEMA', @.level0name = Production,
@.level1type = N'TABLE', @.level1name = Product,
@.level2type = N'COLUMN', @.level2name = SafetyStockLevel;
GO
Regards,
hmscott|||I think the 2000 syntax works on 2005 as well actually, for backwards compatibility.|||I think the 2000 syntax works on 2005 as well actually, for backwards compatibility.
prolly, but no sense in making it easy for him...
:D
hmscott|||in that case you should have given him the 2007 syntax. ;)|||in that case you should have given him the 2007 syntax. ;)
That's the new "virtual" syntax. You just think about it and it appears...
:D
hmscott|||FYI:
http://www.dbforums.com/showthread.php?p=6270444#post6270444
Thanks for trying to find out krReddy|||I am creating a table in SQL Server using the below query.
CREATE TABLE temp(
type21 varchar(1) default NULL /*where the COMMENT will be saved'*/)
In the above query there is a comment line enclosed in /* and */ .
This query is executable .
Please tell me where will this comment line be saved in SQL Server database .
I have checked this in SYSCOMMENTS but i didn't found .
If it is saved some where in the database , Plz tell me the query to fetch that comment
Thanks|||Syscomments is something else entirly. You know that the stuff /* here */ is commented out (a different sort of meaning for comment) and as such does not get executed. It is not stored anywhere. As far as SQL Server is concerned it does not exist - it is for the coders use only.|||If it is saved some where in the database ...it isn't :)|||it isn't :)Sigh - if only I could be so concise.
Why use twenty words when two\ three will do.
Indeed - why write all that at all?|||sorry, pootsie, did not mean to trample your fine post
i was busy merging this guy's threads and so i missed your reply|||Nowt trampled & no worries.
create table temp2(a varchar(23) comment 'male m');
What is the use of specifying a keyword 'comment' in the column definition. Will it make any differenceThat create table statement does not work in SQL Server. What RDBMS are you using?|||Sorry , the above SQL Query is in MySQL Syntax . Could you please tell me the equivalent in MSSQL Server.|||Is comment setting a default?|||Actually it looks more like SPSS value labels or the like. If so - no- you need to set up another table and establish a foreign key.|||I'm guessing that you are trying to add a comment to your column to indicate what it is for. Depending on your version of SQL Server, you will have to use a separate syntax:
SQL 2000
sp_addextendedproperty 'Caption', 'US Phone Number',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
SQL 2005:
EXEC sys.sp_addextendedproperty
@.name = N'MS_DescriptionExample',
@.value = N'Minimum inventory quantity.',
@.level0type = N'SCHEMA', @.level0name = Production,
@.level1type = N'TABLE', @.level1name = Product,
@.level2type = N'COLUMN', @.level2name = SafetyStockLevel;
GO
Regards,
hmscott|||I think the 2000 syntax works on 2005 as well actually, for backwards compatibility.|||I think the 2000 syntax works on 2005 as well actually, for backwards compatibility.
prolly, but no sense in making it easy for him...
:D
hmscott|||in that case you should have given him the 2007 syntax. ;)|||in that case you should have given him the 2007 syntax. ;)
That's the new "virtual" syntax. You just think about it and it appears...
:D
hmscott|||FYI:
http://www.dbforums.com/showthread.php?p=6270444#post6270444
Thanks for trying to find out krReddy|||I am creating a table in SQL Server using the below query.
CREATE TABLE temp(
type21 varchar(1) default NULL /*where the COMMENT will be saved'*/)
In the above query there is a comment line enclosed in /* and */ .
This query is executable .
Please tell me where will this comment line be saved in SQL Server database .
I have checked this in SYSCOMMENTS but i didn't found .
If it is saved some where in the database , Plz tell me the query to fetch that comment
Thanks|||Syscomments is something else entirly. You know that the stuff /* here */ is commented out (a different sort of meaning for comment) and as such does not get executed. It is not stored anywhere. As far as SQL Server is concerned it does not exist - it is for the coders use only.|||If it is saved some where in the database ...it isn't :)|||it isn't :)Sigh - if only I could be so concise.
Why use twenty words when two\ three will do.
Indeed - why write all that at all?|||sorry, pootsie, did not mean to trample your fine post
i was busy merging this guy's threads and so i missed your reply|||Nowt trampled & no worries.
Tuesday, February 14, 2012
Command USE with variable
Why I can′t use the command USE with variable?
declare @.banco varchar(20)
select @.banco='xpto'
USE @.banco
I have try in other way without success
DECLARE @.banco varchar(50)
declare @.SQL varchar(50)
select @.banco='xpto'
print @.banco
select @.SQL='USE '+@.banco
print @.SQL
exec(@.SQL)
Help me, please.
Rodrigo
It is executed at both compile time and run time, so I presume that is the reason. Not all commands can use variables in place of constants. the TOP command, for instance, only takes constants until it was altered in SQL 2005. For USE sysntax and description:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp
|||I am developing a routine to desfragment
the indexes of all tables of the server... I′ve found one in the books online (DBCC SHOWCONTIG session), but I ′m trying to convert it to defrag all objetcs in all databases of my instance. So I have think in a "Cursor" that get the name of the databases in the "sysdatabases" system table. Than set each database to current and run the script. Fallow the script:
For this, I have think in set the database to run the script on the database context.
Can you help me?
/*Perform a 'USE <database name>' to select the database in which to run the script.*/ -- Declare variables SET NOCOUNT ON DECLARE @.tablename VARCHAR (128) DECLARE @.execstr VARCHAR (255) DECLARE @.objectid INT DECLARE @.indexid INT DECLARE @.frag DECIMAL DECLARE @.maxfrag DECIMAL -- Decide on the maximum fragmentation to allow SELECT @.maxfrag = 30.0 -- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- Create the table CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @.tablename WHILE @.@.FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @.tablename END -- Close and deallocate the cursor CLOSE tables DEALLOCATE tables -- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @.maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 -- Open the cursor OPEN indexes -- loop through the indexes FETCH NEXT FROM indexes INTO @.tablename, @.objectid, @.indexid, @.frag WHILE @.@.FETCH_STATUS = 0 BEGIN PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%' SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(@.indexid) + ')' EXEC (@.execstr) FETCH NEXT FROM indexes INTO @.tablename, @.objectid, @.indexid, @.frag END -- Close and deallocate the cursor CLOSE indexes DEALLOCATE indexes -- Delete the temporary table DROP TABLE #fraglist GO |||Here is one way with using sp_MSforeachdb and DBCC DBREINDEXALL
Both of these things are undocumented and you might not want to run it on a production server
EXEC sp_MSForEachDB 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
DBCC DBREINDEXALL (''?'') '
-- nobody can be connected to the db for the above statement to work
also you could do something like this
DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
where dbid > 6
PRINT @.SQL
EXEC (@.SQL)
However if you exceed 4000 characters this will fail
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||For EXECUTE you can use varchar(8000), or in 2005 you can use varchar(max).
|||Please don't use undocumented system stored procedures. They are not guaranteed to remain in every version of SQL Server. In fact we removed several system stored procedures or modified their behavior in SQL Server 2005.|||Set options or use statement executed within dynamic SQL is only valid within that scope. So in your case, you have to perform the USE and the DBCC statement within the same dynamic SQL execution like:
exec('use ' + @.somedb + '; dbcc index...')
Sunday, February 12, 2012
Comma seperated IDS in SELECT query with IN.
How can I use list of comma separated IDS in SELECT query with IN.
DECLARE @.TaskID varchar(200)
SET @.TaskID = '(30,32)'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE TaskID IN @.TaskIDI do not want to use Dynamic SQL|||Look at Dejan's example
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690585.836794.90030@.g14g2000cwa.googlegroups.com...
>I do not want to use Dynamic SQL
>|||Look at this function by Dejan Sarka:
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks Uri,
It solved my problem.|||http://www.aspfaq.com/2248
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690353.281661.41550@.v46g2000cwv.googlegroups.com...
> How can I use list of comma separated IDS in SELECT query with IN.
> DECLARE @.TaskID varchar(200)
> SET @.TaskID = '(30,32)'
> SELECT DISTINCT UserID
> FROM tbl_UserTask
> WHERE TaskID IN @.TaskID
>|||or
DECLARE @.TaskID varchar(200)
SET @.TaskID = '30,32'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE ','+@.TaskID+',' like '%,'+cast(TaskID as varchar)+',%'
Madhivanan
DECLARE @.TaskID varchar(200)
SET @.TaskID = '(30,32)'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE TaskID IN @.TaskIDI do not want to use Dynamic SQL|||Look at Dejan's example
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690585.836794.90030@.g14g2000cwa.googlegroups.com...
>I do not want to use Dynamic SQL
>|||Look at this function by Dejan Sarka:
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks Uri,
It solved my problem.|||http://www.aspfaq.com/2248
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690353.281661.41550@.v46g2000cwv.googlegroups.com...
> How can I use list of comma separated IDS in SELECT query with IN.
> DECLARE @.TaskID varchar(200)
> SET @.TaskID = '(30,32)'
> SELECT DISTINCT UserID
> FROM tbl_UserTask
> WHERE TaskID IN @.TaskID
>|||or
DECLARE @.TaskID varchar(200)
SET @.TaskID = '30,32'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE ','+@.TaskID+',' like '%,'+cast(TaskID as varchar)+',%'
Madhivanan
comma separated values to stored procedures
Hi All,
This does not work as you probably want. You get a selection criterium
with a single string @.iClientid. You could have written
If you need to pass in your id's the way you do, you can do it with
dynamic SQL, i.e. create a SQL statement and EXEC it.
i hv created a sp as
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
but this statement throws an conversion error ( int to char error).
is there any way to fetch records from a select statement using a
string?
Thanks in Advance.shark wrote:
Quote:
Originally Posted by
Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
This does not work as you probably want. You get a selection criterium
with a single string @.iClientid. You could have written
select * from clients where CONVERT(VACHAR(100),iClientid) = @.iclientid
Quote:
Originally Posted by
end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?
If you need to pass in your id's the way you do, you can do it with
dynamic SQL, i.e. create a SQL statement and EXEC it.
robert|||See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
--
Hope this helps.
Dan Guzman
SQL Server MVP
"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...
Quote:
Originally Posted by
Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?
>
Thanks in Advance.
>
Madhivanan
Dan Guzman wrote:
Quote:
Originally Posted by
See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...
Quote:
Originally Posted by
Hi All,
i hv created a sp as
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
but this statement throws an conversion error ( int to char error).
is there any way to fetch records from a select statement using a
string?
Thanks in Advance.
Friday, February 10, 2012
Comma delimited list of IDs to a recordset from 2 tables...
Have 2 tables in SQL Server 05 DB:
First one is MyList
user_id -> unique value
list -> comma-delimited list of user_ids
notes -> random varchar data
Second one is MyProfile
user_id -> unique value
name
address
email
phone
I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @.user_id for MyList then return all this data.
The format of the comma-delimited data is as such (all values are 10-digit alphanumerics):
d25ef46bp3,s46ji25tn9,p53fy76nc9
The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned).
Thank you!Is this assignment posted anywhere that we can read it as the teacher orignally wrote it?
-PatP|||This is for a web site project... Im stuck on this problem and need a solution. Thanks!|||Oh, Pat. I should HOPE this wasn't a homework assignment. I shudder to think that teachers would advise or condone storing data as comma-delimited strings...
L0Y4L1S3R, you can accomplish what you want joining with the LIKE() operator along with suitable wildcard characters on your string, but the result will be both inefficient and buggy. Complex coding is frequently required to make up for inadequecies in design, and ultimately you need to scrap the comma delimited strings and store that data in a subtable.|||Haha... definitely not school assignment... my first attempt at working with complex data in sql server 05.
Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.
Any way... if you can think for a stored proc that would work, please provide. Otherwise please recommend an alternate table structure.
thank you!|||Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.No no no no no no no. The correct "normalized" design is to have a subtable with up to 100 records per user_id.
Look, you can tell there is something fishy about your design because you have two table that each use user_id as a primary key. The proper design should probably be:
Table MyProfile
(user_id primary key,
name,
address,
email,
phone)
Table MyList
(user_id,
list_item,
notes)
In table MyList, user_id and list_time form a composite and unique primary key, and list_item stores one and only one item. This allows you to store as many list_items per user_id as you want, prevents a user_id from having duplicate list_items, and allows fast and easy querying.
First one is MyList
user_id -> unique value
list -> comma-delimited list of user_ids
notes -> random varchar data
Second one is MyProfile
user_id -> unique value
name
address
phone
I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @.user_id for MyList then return all this data.
The format of the comma-delimited data is as such (all values are 10-digit alphanumerics):
d25ef46bp3,s46ji25tn9,p53fy76nc9
The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned).
Thank you!Is this assignment posted anywhere that we can read it as the teacher orignally wrote it?
-PatP|||This is for a web site project... Im stuck on this problem and need a solution. Thanks!|||Oh, Pat. I should HOPE this wasn't a homework assignment. I shudder to think that teachers would advise or condone storing data as comma-delimited strings...
L0Y4L1S3R, you can accomplish what you want joining with the LIKE() operator along with suitable wildcard characters on your string, but the result will be both inefficient and buggy. Complex coding is frequently required to make up for inadequecies in design, and ultimately you need to scrap the comma delimited strings and store that data in a subtable.|||Haha... definitely not school assignment... my first attempt at working with complex data in sql server 05.
Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.
Any way... if you can think for a stored proc that would work, please provide. Otherwise please recommend an alternate table structure.
thank you!|||Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.No no no no no no no. The correct "normalized" design is to have a subtable with up to 100 records per user_id.
Look, you can tell there is something fishy about your design because you have two table that each use user_id as a primary key. The proper design should probably be:
Table MyProfile
(user_id primary key,
name,
address,
email,
phone)
Table MyList
(user_id,
list_item,
notes)
In table MyList, user_id and list_time form a composite and unique primary key, and list_item stores one and only one item. This allows you to store as many list_items per user_id as you want, prevents a user_id from having duplicate list_items, and allows fast and easy querying.
Subscribe to:
Posts (Atom)