Comparing text in WHERE

Hi everyone,

I am compare to text fields like this:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value=@.Value

END

But the environment (I use VS2005) says that the data type is incompatible with the equal operator. Then I tried:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value IN '('+@.Value+')'

END

But nada (nothing). Any ideas? I can't change the data type since this field will hold values of different sizes.

Thank you for your input in advance!

hi,

text/ntext datatype does not support this kind of operation/comparison... you can find the "available" methods against these datatypes in http://msdn2.microsoft.com/en-us/library/ms187993.aspx..

these datatype are not good candidates for "filtering" operations as well.. consider that text can hold up to 2gb of data, and it's not worth the problem to support such heavy features..

so you can end up with "workarounds" if they fit your needs, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Value text NULL ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 'some text' ); INSERT INTO dbo.TestTB VALUES ( 2 , 'some other text' ); GO DECLARE @.key varchar(10); SET @.key = 'some text'; SELECT * FROM dbo.TestTB WHERE SUBSTRING( Value, 1, DATALENGTH(@.key)) = @.key; GO DROP TABLE dbo.TestTB; --<- Id Value -- 1 some text

but consider the other methods as well..

if you are using SQL Server 2005, consider moving the text/ntext datatypes to varchar(MAX)/nvarchar(MAX), as text datatype has been deprecated and, more usefull, varchar(MAX) supports all the traditional "string" operations...

regards

sqlsql

Comparing DB's columns and create script

Hi,
I am looking for a solution that can generate a script on column difference
between two databases and its tables.
Basically, I have a one core db and one development db. The development db
has been revised several time and now I need to generate a script that just
adds all new table columns with their defaults to the old core db.
Any knowledge about this or some methods to use.
Thanks in advance
ChristianCheck out SQLCompare, from Red-GAte Software... It will compare the two
databases, and generate a script to do exactly what you need...
http://www.red-gate.com
"Christian Perthen" wrote:

> Hi,
> I am looking for a solution that can generate a script on column differenc
e
> between two databases and its tables.
> Basically, I have a one core db and one development db. The development db
> has been revised several time and now I need to generate a script that jus
t
> adds all new table columns with their defaults to the old core db.
> Any knowledge about this or some methods to use.
> Thanks in advance
> Christian
>
>

Comparing Dates in SQL

I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.

Can anyone help?

you could do a MAX(lastupdatedate) and then do the INNER JOIN.
SELECT
table1.column
FROM
table1
INNER JOIN table2
on table1.table1id = able2.table1id
WHERE
MAX(table1.lastupdatedate) = MAX(table2.lastupdatedate)|||I'm sorry I don't understand how this works. If the last update date is today in table2, I want to return that date. Otherwise I want to return the last update date in table1. Does this mak sense?|||SELECTCASE WHEN (table1.LastUpdate > table2.LastUpdate) THEN table1.LastUpdate ELSE table2.LastUpdate END AS MaxLastUpdate
FROM ... usual join statement|||

This is almost perfect! Thanks!

Followup: what if there is no instance of table2.table1id? Then I get nothing returned but in reality I still want table1.lastupdate returned.

Thanks again!

|||Ok I'm an idiot. I just switched the when statement and the then and else so the else displays the main from table1 and walaa. Thank you so much for this. I will get better at these case statements yet!|||

You have to use OUTER JOIN to make sure all rows are included. For example (using my own dummy master/detail tables):

SELECT A.ID_MASTER, CASE WHEN (A.DateEntered > ISNULL(B.DateEntered, '01-01-1900'))
THEN A.DateEntered ELSE b.DateEntered END AS MaxLastUpdate
FROM TestDate AS A LEFT OUTER JOIN
TestDate2 AS B ON A.ID_MASTER = B.ID_MASTER

ISNULL() function is used to make sure NULL date value (for the missing row in Detail) defaults to "01-01-1900", assuming that date will be small enough. Also, this query will return multiple rows if there are more than one Detail rows for a given Master row. Depending on your situation, you may or may not have to change this.

|||

SELECT keyid,MAX(DateEntered)
FROM (
SELECT keyid,DateEntered FROM table1
UNION
SELECT keyid,DateEntered FROM table2
) z
GROUP BY keyid

Would work as well, and only return a single result for each keyid.

Comparing Date to SQL Server Date

Hey everyone...I need to create a job which occurs once monthly that is based on a stored procedure that contains as a rough example:

CREATE PROCEDURE CompareDate
@.CurrentDate datetime

AS

SELECT * FROM Contracts WHERE EndDate LIKE (CURRENT SERVER DATE)
GO

How do I declare a parameter to automatically get the current server date in the stored procedure? Also, once it is declared how can I add Months to it? For example this job is executed monthly and is supposed to select contracts that expire 2 months from the current month. So once I get the current server date, how do I add 2 months to the date in the stored procedure? Thanks very much in advance!!!Use getdate() function to get the current server date.

Compare values

I have two tables both with first name and last name fields. I want to create
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanksselect ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
--
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||Great that works thanks!!!!! So that shows me the ones that are not there how
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:
> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if the
> values are NULL in both tables. You can add IS NULL criteria to the queries
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
> >I have two tables both with first name and last name fields. I want to
> >create
> > a query that pulls the names out of table ctct that are not in table
> > ctctws.
> > I tried this query but does not give me the expected reults
> >
> > select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> > ctctws.c_first_namews
> > from ctct, ctctws
> > where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> >
> > Can anyone suggest away to produce the required result.
> > I Really appreciate any help
> >
> > Thanks
>
>|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
> Great that works thanks!!!!! So that shows me the ones that are not there how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:
> > I suggest NOT EXISTS. Below is an untested example in which I removed the
> > ctctws data from the column list since these refer to non-existing rows:
> >
> > SELECT
> > ctct.c_last_name,
> > ctct.c_first_name
> > FROM ctct
> > WHERE NOT EXISTS
> > (
> > SELECT *
> > FROM ctctws
> > WHERE
> > ctct.c_last_name = ctctws.c_last_namews AND
> > ctct.c_first_name = ctctws.c_first_namews
> > )
> >
> > You could also use LEFT JOIN, but note that unlatching ctctws rows will
> > contain NULL values.
> >
> > SELECT
> > ctct.c_last_name,
> > ctct.c_first_name,
> > ctctws.c_last_namews,
> > c_first_namews
> > FROM ctct
> > LEFT JOIN ctctws ON
> > ctct.c_last_name = ctctws.c_last_namews AND
> > ctct.c_first_name = ctctws.c_first_namews
> > WHERE
> > ctctws.c_last_namews IS NOT NULL
> >
> > Both of these examples will consider NULL values as unmatching, even if the
> > values are NULL in both tables. You can add IS NULL criteria to the queries
> > if you need to consider NULLs as equal for comparison purposes.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Eric" <Eric@.discussions.microsoft.com> wrote in message
> > news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
> > >I have two tables both with first name and last name fields. I want to
> > >create
> > > a query that pulls the names out of table ctct that are not in table
> > > ctctws.
> > > I tried this query but does not give me the expected reults
> > >
> > > select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> > > ctctws.c_first_namews
> > > from ctct, ctctws
> > > where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> > >
> > > Can anyone suggest away to produce the required result.
> > > I Really appreciate any help
> > >
> > > Thanks
> >
> >
> >sqlsql

Compare Triggers Values

Hi.
How to create a trigger before insert that if a new row is giong to be
inserted in a table the trigger compares a value of a column from the
table with the value of a column in the record to be inserted...
Regards
Muhammad Bilal
<bilal_4x@.hotmail.com>
*** Sent via Developersdex http://www.examnotes.net ***Use of inserted table and deleted table in the trigger can help you.
According to BOL
two special tables are used in trigger statements: the deleted table
and the inserted table. SQL Server 2000 automatically creates and
manages these tables. You can use these temporary, memory-resident
tables to test the effects of certain data modifications and to set
conditions for trigger actions;
Look at books online for more details and samples.
Or
Post your table with DDL and data and describe your problem.
Regards
Amish

Compare timestamps then delete

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

Quote:

Originally Posted by JReneau35

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

delete ... where datediff(mm,datefield,getdate()) > 6|||

Quote:

Originally Posted by ck9663

delete ... where datediff(mm,datefield,getdate()) > 6

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.|||

Quote:

Originally Posted by JReneau35

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.

datediff() gets the difference between the start data and end date. the "mm" signifies you're trying to get the difference expressed in number of months. getdate() is a function that returns the system date.

essentially, you're deleting the record if the difference between the datefield (content of your field) and the system date is more then 6 months ... if you need to include 6 months and older, do a "=>" instead

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...
>

Compare Differences 2 Fields

I have an Id# as a PK in one table and I am trying to create a FK relationship on another table I need to compare the data because the new FK table has a few Id#'s that don't match up what is the best way to do this?-- this will list the ID in onetable but not in anothertable
select *
from onetable o left join anothertable a
on o.id = a.id
where a.id is null

do you have the other case ? ID in anothertable but not in onetable ?|||

Maybe something like:

select id#
from newTable a
where not exists
( select id#
from firstTable b
where a.id# = b.id#
)

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

Compact Edition not in Data Source List

After I installed the SQL Server Compact Edition, I start a new VS 2005 Basic project. When I try to create a new connection I do not see the Compact Edition in the list of data sources.

I have installed previously SQL Server CE, Everywhere, Mobile, 2005 Mobile but I uninstalled them all.

Can anyone give me an idea why I can't see the Compact Edition as a data source?

Thanks.

Hello

From what you'r saying maybe you need to re-install the SQL Server CE.
I have the same problem but it's because I'm using VB.NET Express Edition.

see ya

Communication between two service brokers

I am trying to setup an alert system for our new application. Idea is to create triggers on the tables that we need updates about. When a table changes trigger will be fired and that will send a change data message to a service on a

different sql server. That service will process the message and create an event in the notification services database. Notification service will later send an email or sms etc depeding on what is required.

Currently i got the message exchange working between two service in the same database. Now I am working on exchanging message between two SQL Servers with no luck. Can anyone please post an article on how to setup communications between two services on different servers.

I did try to expose service broker as an endpoint and create a route on the other server to call it? (This don't work)

Thanks,

Fahad

Here is what I have been doing and it works well once you figure it out:

http://blogs.msdn.com/remusrusanu/archive/2006/04/07/571066.aspx
|||Thanks, that worked for me.

Committing data to SQL from a Visual Basic Form

I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.

The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.

I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI…data entered manually into the SQL table is stored, just not data from the form.

Does anyone have any idea what is wrong?

Are you using the user instance feature in SQL Server express ? Then you will have to set the "Copy" property to "Never" for the in your solution included datafile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I am not clear on the instructions please clarify for the newbie.

Thanks.

|||If you are starting your program from inside visual basic the application is being rebuilt and the SQL database as created in visual basic is overwriting the database you created last time you ran your program. If you open your program from the bin\debug directory within your project folder, make changes to the data, exit the program, then go back into the program the same way, your database should have been updated with the changes.|||@.Bluebuddha: Did you find the setting ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Committing data to SQL from a Visual Basic Form

I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.

The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.

I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI…data entered manually into the SQL table is stored, just not data from the form.

Does anyone have any idea what is wrong?

Are you using the user instance feature in SQL Server express ? Then you will have to set the "Copy" property to "Never" for the in your solution included datafile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I am not clear on the instructions please clarify for the newbie.

Thanks.

|||If you are starting your program from inside visual basic the application is being rebuilt and the SQL database as created in visual basic is overwriting the database you created last time you ran your program. If you open your program from the bin\debug directory within your project folder, make changes to the data, exit the program, then go back into the program the same way, your database should have been updated with the changes.|||@.Bluebuddha: Did you find the setting ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Committing data to SQL from a Visual Basic Form

I have novice level visual basic knowledge using Visual Studio 2005 and have little knowledge of SQL using SQL express. I am attempting to create a SQL database which initially consists of a single table bound to a Visual Basic form for data entry. So far I have been able to bind a VB form to a SQL database creating a DataSet, DataAdapter, and utilizing the default binding navigator. I also seem to have functioning Stored Procedures.

The VB form is able to add data to the SQL table and I can toggle through the data while the VB app is running but when I terminate the app and load it again all newly added data is not available from the SQL table.

I have seen other strings on this subject and viewed many tutorials but they only bring me as far as I am. None of them address the concept of permanently committing data to SQL from the VB form. FYI…data entered manually into the SQL table is stored, just not data from the form.

Does anyone have any idea what is wrong?

Are you using the user instance feature in SQL Server express ? Then you will have to set the "Copy" property to "Never" for the in your solution included datafile.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

I am not clear on the instructions please clarify for the newbie.

Thanks.

|||If you are starting your program from inside visual basic the application is being rebuilt and the SQL database as created in visual basic is overwriting the database you created last time you ran your program. If you open your program from the bin\debug directory within your project folder, make changes to the data, exit the program, then go back into the program the same way, your database should have been updated with the changes.|||@.Bluebuddha: Did you find the setting ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

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.

Comma-separated field

Hi,
table (contact) has a field categories with values like
row 1:
bil;jack;john;don
row 2:
bil;zub;sam;tom;jack
Ideally I would like to create a new table (split_table) with the
following values in each row without any duplicate. The
item NoOfOccurence
-- --
bil 2
jack 2
john 1
don 1
zub 1
sam 1
tom 1
Does anyone has a simple code for a newbie using MS SQL 2000. I don't
mind using a stored procedure or cursor. I've seen solution but they
seem too complicated for me to even modify.
I appreciate any help and suggestion.
Thank you and kind regards
BilsCheck the article at
http://www.windowsitpro.com/SQLServ...678/25678.html.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Bils" <bjeewa@.advsol.com> wrote in message
news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
> Hi,
> table (contact) has a field categories with values like
> row 1:
> bil;jack;john;don
> row 2:
> bil;zub;sam;tom;jack
> Ideally I would like to create a new table (split_table) with the
> following values in each row without any duplicate. The
> item NoOfOccurence
> -- --
> bil 2
> jack 2
> john 1
> don 1
> zub 1
> sam 1
> tom 1
> Does anyone has a simple code for a newbie using MS SQL 2000. I don't
> mind using a stored procedure or cursor. I've seen solution but they
> seem too complicated for me to even modify.
> I appreciate any help and suggestion.
> Thank you and kind regards
> Bils
>|||My solution is:
select * into #t from source
select * into #t1 from source where 0 = 1
while exists(select * from #t where len(row) > 0)
begin
insert into #t1 (row)
select substring(row,1,charindex(';',row + ';') - 1)
from #t
update #t set row = substring(row,charindex(';',row + ';') + 1,len(row))
end
select row as Item,count(*) as NoOfOccurence
from #t1
where len(row) > 0
group by row
drop table #t1
drop table #t
No proc, no cursor - is that you want?
endorsed by signature
*** Serg Yury ***
"Bils" <bjeewa@.advsol.com> '?/'' ? '' '?:
news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
> Hi,
> table (contact) has a field categories with values like
> row 1:
> bil;jack;john;don
> row 2:
> bil;zub;sam;tom;jack
> Ideally I would like to create a new table (split_table) with the
> following values in each row without any duplicate. The
> item NoOfOccurence
> -- --
> bil 2
> jack 2
> john 1
> don 1
> zub 1
> sam 1
> tom 1
> Does anyone has a simple code for a newbie using MS SQL 2000. I don't
> mind using a stored procedure or cursor. I've seen solution but they
> seem too complicated for me to even modify.
> I appreciate any help and suggestion.
> Thank you and kind regards
> Bils
>|||simply brilliant. Thank you very much guys for your assistance
Kind regards
Bils|||IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
SELECT
Row
, Data
INTO
#tmp
FROM
(
SELECT TOP 0 NULL AS Row , NULL AS Data
UNION ALL SELECT 1 , 'bil;jack;john;don'
UNION ALL SELECT 2 , 'bil;zub;sam;tom;jack'
) Data
SELECT
Row
, NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
n ) - n ) , '' ) AS Item
INTO
#tmp2
FROM
#tmp
INNER JOIN
tblNumbers
ON
tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
AND
SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
SELECT
Item
, COUNT(*) AS NoOfOccurence
FROM
#tmp2
GROUP BY
Item
ORDER BY
NoOfOccurence DESC
, Item
This should be quicker than using a cursor/loop.
"Bils" <bjeewa@.advsol.com> wrote in message
news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
> Hi,
> table (contact) has a field categories with values like
> row 1:
> bil;jack;john;don
> row 2:
> bil;zub;sam;tom;jack
> Ideally I would like to create a new table (split_table) with the
> following values in each row without any duplicate. The
> item NoOfOccurence
> -- --
> bil 2
> jack 2
> john 1
> don 1
> zub 1
> sam 1
> tom 1
> Does anyone has a simple code for a newbie using MS SQL 2000. I don't
> mind using a stored procedure or cursor. I've seen solution but they
> seem too complicated for me to even modify.
> I appreciate any help and suggestion.
> Thank you and kind regards
> Bils
>|||Sure, no cursor - but if the data contains 50,000 records with 100 items per
record, you'll have 5,000,000 record updates to the tempdb and it will take
forever.
This is not a scalable solution.
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c815f$1_1@.isa.dvgd.mps...
> My solution is:
> select * into #t from source
> select * into #t1 from source where 0 = 1
> while exists(select * from #t where len(row) > 0)
> begin
> insert into #t1 (row)
> select substring(row,1,charindex(';',row + ';') - 1)
> from #t
> update #t set row = substring(row,charindex(';',row + ';') +
1,len(row))
> end
> select row as Item,count(*) as NoOfOccurence
> from #t1
> where len(row) > 0
> group by row
> drop table #t1
> drop table #t
> No proc, no cursor - is that you want?
>
> --
> endorsed by signature
> *** Serg Yury ***
> "Bils" <bjeewa@.advsol.com> '?/'' ? '' '?:
> news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
>|||You can also do without the temporary table by using a subquery.
... replace #tmp with your datasource
SELECT
Item
, COUNT(*) AS NoOfOccurence
FROM
(
SELECT
Row
, NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
n ) - n ) , '' ) AS Item
FROM
#tmp
INNER JOIN
tblNumbers
ON
tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
AND
SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
) Data
GROUP BY
Item
ORDER BY
NoOfOccurence DESC
, Item
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435ca9c0$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
> IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
> IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
> SELECT
> Row
> , Data
> INTO
> #tmp
> FROM
> (
> SELECT TOP 0 NULL AS Row , NULL AS Data
> UNION ALL SELECT 1 , 'bil;jack;john;don'
> UNION ALL SELECT 2 , 'bil;zub;sam;tom;jack'
> ) Data
>
> SELECT
> Row
> , NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
> n ) - n ) , '' ) AS Item
> INTO
> #tmp2
> FROM
> #tmp
> INNER JOIN
> tblNumbers
> ON
> tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
> AND
> SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
> SELECT
> Item
> , COUNT(*) AS NoOfOccurence
> FROM
> #tmp2
> GROUP BY
> Item
> ORDER BY
> NoOfOccurence DESC
> , Item
>
> This should be quicker than using a cursor/loop.
>
> "Bils" <bjeewa@.advsol.com> wrote in message
> news:1130132753.169888.314860@.g44g2000cwa.googlegroups.com...
>|||First of all: what is tblNumbers?
I suppose:
SELECT n INTO tblNumbers
FROM
(
SELECT TOP 0 NULL AS n
UNION ALL SELECT 1
UNION ALL SELECT 2
-- ...
-- ... to DATALENGTH(#tmp.Data) '
) Data
I agree - your solution more elegant, if not to take into account creation
of table tblNumbers...
endorsed by signature
*** Serg Yury ***
"Rebecca York" <rebecca.york {at} 2ndbyte.com> /
: news:435ca9c0$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
> IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
> IF OBJECT_ID('tempdb..#tmp2') IS NOT NULL DROP TABLE #tmp2
> SELECT
> Row
> , Data
> INTO
> #tmp
> FROM
> (
> SELECT TOP 0 NULL AS Row , NULL AS Data
> UNION ALL SELECT 1 , 'bil;jack;john;don'
> UNION ALL SELECT 2 , 'bil;zub;sam;tom;jack'
> ) Data
>
> SELECT
> Row
> , NULLIF( SUBSTRING( #tmp.Data , n , CHARINDEX( ';' , #tmp.Data + ';' ,
> n ) - n ) , '' ) AS Item
> INTO
> #tmp2
> FROM
> #tmp
> INNER JOIN
> tblNumbers
> ON
> tblNumbers.n BETWEEN 1 AND DATALENGTH( #tmp.Data )
> AND
> SUBSTRING( ';' + #tmp.Data , n , 1 ) = ';'
> SELECT
> Item
> , COUNT(*) AS NoOfOccurence
> FROM
> #tmp2
> GROUP BY
> Item
> ORDER BY
> NoOfOccurence DESC
> , Item
>
> This should be quicker than using a cursor/loop.
>|||Everyone needs tblNumbers :)
It contains numbers 1 to 100,000.
To populate it:-
USE UtilsDatabase
CREATE TABLE
dbo.tblNumbers
(
n INT
, CONSTRAINT PK_dbo_tblNumbers PRIMARY KEY CLUSTERED ( n ASC )
)
GRANT SELECT ON dbo.tblNumbers TO PUBLIC
IF NOT EXISTS( SELECT n FROM dbo.tblNumbers )
INSERT INTO dbo.tblNumbers ( N )
SELECT TOP 0 NULL AS n
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
DECLARE @.Loop TINYINT , @.HowManyZeros TINYINT , @.MAX_N INT
SELECT @.Loop = 1 , @.HowManyZeros = 5
WHILE @.Loop < @.HowManyZeros
BEGIN
SELECT @.MAX_N = MAX( n ) FROM dbo.tblNumbers
INSERT INTO
dbo.tblNumbers ( n )
SELECT
n + n2 * @.MAX_N AS N
FROM
dbo.tblNumbers
CROSS JOIN
( SELECT n AS n2 FROM dbo.tblNumbers WHERE n BETWEEN 1 AND 9 ) BaseTen
SELECT @.Loop = @.Loop + 1
END
DENY INSERT , UPDATE , DELETE ON dbo.tblNumbers TO PUBLIC
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435da744$1_1@.isa.dvgd.mps...
> First of all: what is tblNumbers?
> I suppose:
> SELECT n INTO tblNumbers
> FROM
> (
> SELECT TOP 0 NULL AS n
> UNION ALL SELECT 1
> UNION ALL SELECT 2
> -- ...
> -- ... to DATALENGTH(#tmp.Data) '
> ) Data
> I agree - your solution more elegant, if not to take into account creation
> of table tblNumbers...
> --
> endorsed by signature
> *** Serg Yury ***
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> /
> : news:435ca9c0$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
>
>

Command Prompt problems when installing MSDE

Hi,
I cannot seem to create a server instance when installing the MSDE.
step 1. I launch my cmd. It pops up with (C:\Documents and Settings\Billy
I type setup instancename="netsdk" sapwd="sa"
I immediately get a status message saying go to the control planel and
install and configure systems components. I am stuck here. If anyone can shed
some light with this problem I would appreciate it.
I am installing the MSDE onto my laptop(XP OS). I went to my control
panel->administrative tools->Local Security Policy-> but I don't what to look
for?
I am lost.
hi Billy,
Billy wrote:
> Hi,
> I cannot seem to create a server instance when installing the MSDE.
> step 1. I launch my cmd. It pops up with (C:\Documents and
> Settings\Billy
> I type setup instancename="netsdk" sapwd="sa"
> I immediately get a status message saying go to the control planel and
> install and configure systems components. I am stuck here. If anyone
> can shed some light with this problem I would appreciate it.
>
when opening the command prompt window, please navigate to the folder
containing the MSDE setup.exe boostrap installer, typically C:\MSDErelA\ or
C:\sql2kspX\MSDE\ (where X stands for the service pack level)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Forgive for not knowing, but how I do navigate to the root directory?
I tried the following commands you posted early,but cannot get to specified
location. The downloaded folder is under the C:\ dir. However, when my cmd is
invoked I cannot get to it from C:\Documents and Settings. One more time,
please. I even tried to copying the folder to the documents folder and still
no go.
Thanks
"Andrea Montanari" wrote:

> hi Billy,
> Billy wrote:
> when opening the command prompt window, please navigate to the folder
> containing the MSDE setup.exe boostrap installer, typically C:\MSDErelA\ or
> C:\sql2kspX\MSDE\ (where X stands for the service pack level)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Andrea,
Recopied it to the Billy folder and create an instance to the server,
automatically starting installing. Error on my end. Thanks for the help.
"Billy" wrote:
[vbcol=seagreen]
> Andrea,
> Forgive for not knowing, but how I do navigate to the root directory?
> I tried the following commands you posted early,but cannot get to specified
> location. The downloaded folder is under the C:\ dir. However, when my cmd is
> invoked I cannot get to it from C:\Documents and Settings. One more time,
> please. I even tried to copying the folder to the documents folder and still
> no go.
> Thanks
> "Andrea Montanari" wrote:
|||Billy wrote:
> Andrea,
> Recopied it to the Billy folder and create an instance to the server,
> automatically starting installing. Error on my end. Thanks for the
> help.
?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Command Line Install

I am trying to create an quiet install for SQL Server Express. The install is being run under a local administrators account. However, when a user, who is only in the local users group, accesses the application using ClickOnce deplolyment, it says SQL Server Express Edition is not installed.

-Dan

You may find these resources helpful:

SQL Server 2005 UnAttended Installations
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
http://www.devx.com/dbzone/Article/31648

command line

Using Query Analyzer, I can right click on an object and select "script
object to new window as create" and I get the text of the object's
definition (schema). Can I get same result from command line, i.e., from
osql, I can get text output for the definition of the object (something like
defncopy under Sybase)? If so, what is the command or store procedure name?

Thanks in advance.Unfortunately not. You can look at scptxfr.exe (it's installed with
MSSQL server - run it with /? to see the options), which is a
command-line tool used during an upgrade process. But it's not
documented, and it doesn't give you the same flexibility as the QA/EM
scripting fucntionality.

The best general solution is probably to write your own scripts using
the SQLDMO COM interface, which exposes .Script methods for all MSSQL
objects - the details are in Books Online. Or there are plenty of
third-party tools to do this as well.

Simon|||Hi Dav,

Take a look at this sample. I guess this helps you. It is really good.

http://www.databasejournal.com/feat...10894_3401081_1

Thank you
Raju