Change how command runs

This should be an easy one. I want to change the command to use the
DBID number instead of the database name to run the process. The
current loop does not keep the order if you add or remove databases
beecuse i am using the min() function.
DECLARE @.sql varchar(4000)
DECLARE @.db varchar(64)
SET @.db=''
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
WHILE @.db is not null
BEGIN
SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
EXEC (@.sql)
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
END
Thanks
-Matt-not sure what are you exactly looking for.
but if you want to loop through the databases on the basis of DBID rather
than database name , then you can try following procedural code.
DECLARE @.sql varchar(4000)
DECLARE @.db int
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.db is not null
BEGIN
select @.sql='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
from master..sysdatabases where dbid =@.db
EXEC (@.sql)
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.db
END
"MKruer@.gmail.com" wrote:

> This should be an easy one. I want to change the command to use the
> DBID number instead of the database name to run the process. The
> current loop does not keep the order if you add or remove databases
> beecuse i am using the min() function.
> DECLARE @.sql varchar(4000)
> DECLARE @.db varchar(64)
> SET @.db=''
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> WHILE @.db is not null
> BEGIN
> SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
> EXEC (@.sql)
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> END
> Thanks
> -Matt-
>|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820c|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820csql

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.
Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>
|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:
> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> > SQL 2005
> >
> > I have a table with a number of nvarchar fields. One field in the midde
> > was
> > 1000 in length. I ran a query with:
> >
> > ALTER TableName
> > ALTER ColumnName nvarchar(2000).
> >
> > This apeared to increase the field size to 2000 (as seen in Server
> > Management Studio), but it will still not store any more than 1000
> > characters.
> >
> > Have I done it incorrectly.
> >
> > There appears to be still space in my record as I can add a field at the
> > end
> > 2000 long and that works correctly.
> >
> > Thanks,
> > Steve.
> >
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 character
s.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and
I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

change default port

We have a lot of applications dependent upon SQL at the moment. If i change
my default port number of 1433, is there something I need to add/modify to
the existing connection info from my clients or will it just work ?
Using SQL 2KHassan
Yes , you will have to add with connection string
server=MyServer,Portnumber;
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23JS5jIdTFHA.2128@.TK2MSFTNGP14.phx.gbl...
> We have a lot of applications dependent upon SQL at the moment. If i
change
> my default port number of 1433, is there something I need to add/modify to
> the existing connection info from my clients or will it just work ?
> Using SQL 2K
>|||... or us Client Network Utility on the client machines.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eKIJZChTFHA.2520@.TK2MSFTNGP09.phx.gbl.
.
> Hassan
> Yes , you will have to add with connection string
> server=MyServer,Portnumber;
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23JS5jIdTFHA.2128@.TK2MSFTNGP14.phx.gbl...
> change
>sql

change default port

We have a lot of applications dependent upon SQL at the moment. If i change
my default port number of 1433, is there something I need to add/modify to
the existing connection info from my clients or will it just work ?
Using SQL 2K
Hassan
Yes , you will have to add with connection string
server=MyServer,Portnumber;
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23JS5jIdTFHA.2128@.TK2MSFTNGP14.phx.gbl...
> We have a lot of applications dependent upon SQL at the moment. If i
change
> my default port number of 1433, is there something I need to add/modify to
> the existing connection info from my clients or will it just work ?
> Using SQL 2K
>
|||... or us Client Network Utility on the client machines.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eKIJZChTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hassan
> Yes , you will have to add with connection string
> server=MyServer,Portnumber;
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23JS5jIdTFHA.2128@.TK2MSFTNGP14.phx.gbl...
> change
>

change default port

We have a lot of applications dependent upon SQL at the moment. If i change
my default port number of 1433, is there something I need to add/modify to
the existing connection info from my clients or will it just work ?
Using SQL 2KHassan
Yes , you will have to add with connection string
server=MyServer,Portnumber;
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23JS5jIdTFHA.2128@.TK2MSFTNGP14.phx.gbl...
> We have a lot of applications dependent upon SQL at the moment. If i
change
> my default port number of 1433, is there something I need to add/modify to
> the existing connection info from my clients or will it just work ?
> Using SQL 2K
>|||... or us Client Network Utility on the client machines.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eKIJZChTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hassan
> Yes , you will have to add with connection string
> server=MyServer,Portnumber;
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23JS5jIdTFHA.2128@.TK2MSFTNGP14.phx.gbl...
>> We have a lot of applications dependent upon SQL at the moment. If i
> change
>> my default port number of 1433, is there something I need to add/modify to
>> the existing connection info from my clients or will it just work ?
>> Using SQL 2K
>>
>

Change condition if the first doesn't exists

Have two tables:

code:

CREATE TABLE [Table1]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Number] [varchar] (50) NOT NULL ,
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
DEFAULT (getdate()),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table2]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Table1Id] [int] NOT NULL ,
[LingoId] [int] NOT NULL ,
[Header] [nvarchar] (150) NOT NULL ,
[Description] [ntext] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
(
[Table1Id]
) REFERENCES [Table1] (
[Id]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

What I would like is that if LingoId = 2 in the query below doesn't exists
than it should fall thru and use the values where LingoId = 1. LingoId = 1
always exists for each Table2.Id. Is this doable?

code:

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = 2) --Something should happen here I quess.

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Header ELSE
T2_1.Header END) as Header,
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Description ELSE
T2_1.Description END) as Description
FROM
dbo.Table1
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 2) T2_2
ON dbo.Table1.Id = T2_2.Table1Id
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 1) T2_1
ON dbo.Table1.Id = T2_1.Table1Id
HTH, Jens Suessmeyer.|||Got an answer elsewhere that did the trick.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||First, you are filtering the result with values from table2. That will
effectively be an INNER JOIN. Did you mean it like that?
If you want to use only one query then something like this could be good
enough:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = case when exists (select table1.id from table1
inner join table2 on table1.id = table2.id where table2.LingoID = 2) then 2
else 1 end)
MC
"Senna" <
Senna@.discussions.microsoft.com>
wrote in message
news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||The solution, as I mention above, looked liked this:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
dbo.Table2.LingoId = 2
or (dbo.Table2.LingoId = 1
and not exists
(select *
from table2 cn
where cn.table1id = dbo.Table2.Table1Id
and cn.lingoid = 2))
ps. Thank for your time and answer Jens.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||Yes, it was an inner join. :)
See the other post with the solution I went with. Thanks anyway for your
time and effort.
"MC" wrote:

> First, you are filtering the result with values from table2. That will
> effectively be an INNER JOIN. Did you mean it like that?
> If you want to use only one query then something like this could be good
> enough:
> SELECT
> dbo.Table1.Id,
> dbo.Table1.Number,
> dbo.Table1.[TimeStamp],
> dbo.Table2.Header,
> dbo.Table2.Description
> FROM
> dbo.Table1
> LEFT OUTER JOIN
> dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
> WHERE
> (dbo.Table2.LingoId = case when exists (select table1.id from table1
> inner join table2 on table1.id = table2.id where table2.LingoID = 2) then
2
> else 1 end)
>
> MC
>
> "Senna" <Senna@.discussions.microsoft.com> wrote in message
> news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
>

Chalenge returning the first available number in a sequence

Hi all,

Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.

Given the client table below my query would return 4 because its the lowest number availeble.

Thanks.

client table

clnum

1

2

3

6

7

Writing a single sql query for this task is challenging. Though I suggest that you could write a stored procedure that loops through a cursor pointing to clnum and check which number isn't present in the column:

psuedo code

open a cusor on clnum

loop i =1 to max int

loop thru the cursor and check if i is present ; if not then return i

end of loop

close the cursor

For help on using loops and cursors,seehttp://www.databasejournal.com/features/mssql/article.php/3111031

Hope this helps.

QI.

|||

Create a temp table or table variable, insert your clientid's and query from it. Something like this:

Declare @.ttable( Idint identity, clientidint)insert into @.tselect 1unionallselect 2unionallselect 3unionallselect 6unionallselect 7select top 1 IDfrom @.tWhere Idnot in (Select clientidfrom @.t)order by id

|||

This would not work if we already had 4 and 5 in the column as well. For example:

Declare @.ttable( Idintidentity, clientidint)

insertinto @.t

select 1unionall

select 2unionall

select 3unionall

select 4unionall

select 5unionall

select 6unionall

select 7

selecttop 1 ID

from @.tWhere Idnotin(Select clientidfrom @.t)

orderby id

The above code wouldn't return anything. Even though it should return 8, which is the lowest available number. Also, you want to be able to select from a column which may have an unknown number of rows so I don't think you can keep on doing unions like this.

QI.

|||

(1) If the ID returned is NULL, you can always query back with MAX(Clientid) and increment by 1. If you write a function to do this, it should be very simple.

(2) There is no need to manually write the INSERTs. That was just for a sample.

INSERT INTO @.T

SELECT Clientid

FROM Clients

would insert all the clients into the table.

Certificate security and Dynamic Routing

I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.

To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?

Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network

Any help or pointers would be greatly appreciated.

PapaLee

this is exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-across-instances-and-servers-with-service-broker

Certificate security and Dynamic Routing

I've been reseaching on SSB and have read quite a number of posts on this forum that closely relate to what im trying to achieve. I have a solution im designing that ideally consists of a central server (SQLENTERPRISE) that will receive messages asynchronously from remote clients (SQLEXPRESS) spanning a wide geographical region over a GPRS virtual private network on a TCP/IP transport. This ideally is a star and spoke architecture and requirements dictate high level security, no loss of messages whatsover as well as high reliability and scalability.

To meet the security requirement in the context of the above scenario, i was thinking implementing both dialog and endpoint security using certificates would be ideal. I've downloaded some samples and have encountered problems simulating the above scenario on 3 machines (I Server & 2 Clients). Client1 sends messags successfully, the 2nd client doesnt possibly because the certificate on the server matches that to client1 since i ran that script first. How do i make it that the Server shares one certificate with all remote clients? Or is there a better way to configure SSB to work in this scenario?

Secondly i have hard coded the Server's IP Address in the Routes created on the Clients. Considering this is over a GPRS Virtual network, how can i make the clients dyamically "discover" the Server? The idea here is to make adding and setting up of new remote clients easy so that you can just plug them in to the existing network

Any help or pointers would be greatly appreciated.

PapaLee

this is exactly what you need:

http://www.sqlteam.com/article/centralized-asynchronous-auditing-across-instances-and-servers-with-service-broker

Century date conversions

Hi,
I'm trying to load date fields into SQLServer using DTS, but the
format of the raw data is the number of days since 1 Jan 1900. How do
I convert this to a useful format, is there a standard conversion
routine?

Thanks
Timtim.philbrook@.cazenove.com (Helsop) wrote in message news:<1609a822.0408180233.9fbed33@.posting.google.com>...
> Hi,
> I'm trying to load date fields into SQLServer using DTS, but the
> format of the raw data is the number of days since 1 Jan 1900. How do
> I convert this to a useful format, is there a standard conversion
> routine?
> Thanks
> Tim

There are (at least) two possible solutions. First, load the data into
a staging table and clean it up with TSQL, before an INSERT into the
final table:

insert into dbo.Destination
(col1, datetime_column, ...)
select col1, dateadd(dd, numdays_column, '19000101'), ...
from dbo.Staging

Alternatively, if you want to do the transformation in DTS, then you
could use the VBScript DateAdd() function in an ActiveX column
transformation to achieve the same thing.

Simon|||--> use DATEADD(dd,@.OffsetDays,'1 Jan 1900')

DECLARE @.OffsetDays BIGINT
SET @.OffSetDays = 38217
select DATEADD(dd,@.OffsetDays,'1 Jan 1900')

Tx

Categorise Bar Chart

How to create a bar chart by setting the static category myself?

I mean, for example, I have a dataset which record the number of coins different people have.

I would like to draw a bar chart which shows 3 bars with the following 3 different categories:

1) n < 5,

2) n >= 5 and n <10,

3) n>=10.

where n is the number of coins.

How can this be expressed in the categories grouping?

Thanks in advance.

Sorry, this is currently not directly supported through chart groupings. You would need to write the query (or add a calculated field on the dataset) so that you get this categorization in the data and then use those fields in the chart.

-- Robert

catchin errors occured in sql server with DELPHI

hi
i want to know how can I catch an error occured in sql server with DELPHI
when an error occurs in sql server the number of error and description of th
e
error is sent to any programming language.
for example I want to change the message in my application when the error
occures?
but I don't knoe how?
is there any espesial EVENT HANDLER?Are you asking how to do this in Delphi? If so, you are probably better off
posting this in a Delphi forum.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pooyan_pdm" <pooyanpdm@.discussions.microsoft.com> wrote in message
news:C64FB3F0-1327-4B22-AC22-7F367E9EEE33@.microsoft.com...
> hi
> i want to know how can I catch an error occured in sql server with DELPHI
> when an error occurs in sql server the number of error and description of
> the
> error is sent to any programming language.
> for example I want to change the message in my application when the error
> occures?
> but I don't knoe how?
> is there any espesial EVENT HANDLER?
>

Casting Int to Varchar

I want to cast an In to varchar with a specific number of decimal places. So 10 will come across as 10.00.
Is there an easy way of doing this?Would you be llooking for something like this?

Code:
------------------------------
select cast(cast(10 as money) as varchar(10))
select cast(cast(10 as numeric(12,4)) as varchar(10))
------------------------------|||I think this is what Paul was trying to show:

select cast(cast(id as decimal(5,2)) as varchar(10)) from table

Where id and table are defined by you. The decimal parameters would be determined by your maximum integer.

Casting decimal number

Hello,

When I declare a VB variable Dim s as Decimal,

I want to cast d like this :
1452,41
41,00
45,47
756544,04

Only with to digits after the ","

How can I perform this

Hi,

If my variable d=125,45111

How can I view d like 125,45 ?

|||you mean 2 digits after a "decimal" not a "comma" right?|||

You have two choices use place holder in strings and formatting or set precision and scale. Try the links below for details. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

http://support.microsoft.com/?kbid=892406

|||

Hi,

If you are asking how to mak this conversion using t-sql, you can use CAST function

You can run and test the result for CAST

DECLARE @.d FLOAT
SET @.d = 125.45111

SELECT @.d, CAST(@.d AS DECIMAL(10,2))

|||

Hi,

Thank I mean two digits after a decimal number ( 44,45 or 4,00 or 7888,01 )

I'm using VB.NET and my variable is declared like Dim x as decimal.

Thanks

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

PublicSub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

EndSub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

PublicClass ScriptMain

PublicSub Main()

Dim dtAsNew System.Data.DataTable

Dim oleadAsNew Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim drAs DataRow

ForEach drIn dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

EndSub

EndClass

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Casting an in-memory resultset to a DataSet for use in XML file generation

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C:\test.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

I worked around this issue by saving the transformed result set to a temporary SQL table.

Then I used a Script Task to connect to the table, pull out the data into a DataSet object using the ReadXml method and used the WriteXml method with the secondary parameter to write the data, with schema, out to a file.

Not elegant, but it gets the job done in time for my Wednesday deadline.

I would still like to know how to cast an in-memory result set to a DataSet object if anyone can help with this.

Thanks

|||

Jamie Thomson had a good post here: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

For a script task I've used the following adaption to quickly populate a variable that contained a multi-table SQL statement.

Add a reference to System.Xml

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

Public Sub Main()

Dim dt As New System.Data.DataTable

Dim olead As New Data.OleDb.OleDbDataAdapter

olead.Fill(dt, Dts.Variables("User::rs").Value)

Dim dr As DataRow

For Each dr In dt.Rows

MsgBox(dr.Item("Name").ToString)

Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

CAST/Convert and performance measurements

Has anyone any testdata on how long a cast/convert from varchar to other datatypes is taking. Is casting a large number of data a major problem for MSSQL 2000?This is what I know.

CAST is the old way that SQL used to change from one data type to another. CONVERT is the preferred method according Microsoft.

I've used CONVERT on tables with a couple hundred thousand rows and didn't really see a large degradation in performance.

Hope this helps.|||CAST has simpler syntax, CONVERT has more functionality. They both work fast, and if you look at the documentation for them you'll see that SQL Server will do many conversion implicitily, so that you don't even need to use CAST or CONVERT.

blindman