Obtaining total of partial

Dear all,
I was wondering how do I for obtain the total for the field 'parcial' in the
same query, of course:
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
COUNT(*) AS partial
FROM ABS_DIARIOHISTO
WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(HORAESCRITU
RA)
ORDER BY MES,DIA
DDL:
CREATE TABLE [ABS_DiarioHisto] (
[DIA_CodigoApunte] [int] NOT NULL ,
[DIA_MaquinaFisica] [varchar] (36) COLLATE Traditional_Spanish_CI_AS NOT
NULL ,
[DIA_HoraEscritura] [datetime] NULL ,
[DIA_Sucursal] [int] NULL ,
[DIA_Puesto] [smallint] NULL ,
[DIA_Usuario] [char] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_FechaOper] [datetime] NULL ,
[DIA_HoraOpera] [varchar] (8) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_FechaConta] [datetime] NULL ,
[DIA_Comentarios] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CodOper] [varchar] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_NumCuen] [varchar] (10) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_Importe] [float] NULL ,
[DIA_Predisp] [smallint] NULL ,
[DIA_NumDocum] [varchar] (30) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_ClCajero] [smallint] NULL ,
[DIA_Vise] [varchar] (2) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_NumHost] [int] NULL ,
[DIA_CodError] [int] NULL ,
[DIA_Anulada] [smallint] NULL ,
[DIA_TipoOper] [int] NULL ,
[DIA_NumOper] [int] NULL ,
[DIA_FormatoTXS] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CodEntidad] [int] NULL ,
[DIA_CodOficina] [int] NULL ,
[DIA_CasoEuro] [tinyint] NULL ,
[DIA_ImporteB] [float] NULL ,
[DIA_CodISO] [varchar] (4) COLLATE Traditional_Spanish_CI_AS NULL ,
[DIA_CuentaContrapart] [varchar] (16) COLLATE Traditional_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
Thanks in advance and best regards,
Enricsorry, as usual, I haven't enough patience...
select sum(total) from (
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
count(*) as total
FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
) D
"Enric" wrote:

> Dear all,
> I was wondering how do I for obtain the total for the field 'parcial' in t
he
> same query, of course:
> SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
> COUNT(*) AS partial
> FROM ABS_DIARIOHISTO
> WHERE DIA_SUCURSAL = 81
> GROUP BY MONTH(DIA_HORAESCRITURA),DAY(HORAESCRITU
RA)
> ORDER BY MES,DIA
>
> DDL:
> CREATE TABLE [ABS_DiarioHisto] (
> [DIA_CodigoApunte] [int] NOT NULL ,
> [DIA_MaquinaFisica] [varchar] (36) COLLATE Traditional_Spanish_CI_AS NOT
> NULL ,
> [DIA_HoraEscritura] [datetime] NULL ,
> [DIA_Sucursal] [int] NULL ,
> [DIA_Puesto] [smallint] NULL ,
> [DIA_Usuario] [char] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_FechaOper] [datetime] NULL ,
> [DIA_HoraOpera] [varchar] (8) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_FechaConta] [datetime] NULL ,
> [DIA_Comentarios] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CodOper] [varchar] (6) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_NumCuen] [varchar] (10) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_Importe] [float] NULL ,
> [DIA_Predisp] [smallint] NULL ,
> [DIA_NumDocum] [varchar] (30) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_ClCajero] [smallint] NULL ,
> [DIA_Vise] [varchar] (2) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_NumHost] [int] NULL ,
> [DIA_CodError] [int] NULL ,
> [DIA_Anulada] [smallint] NULL ,
> [DIA_TipoOper] [int] NULL ,
> [DIA_NumOper] [int] NULL ,
> [DIA_FormatoTXS] [varchar] (15) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CodEntidad] [int] NULL ,
> [DIA_CodOficina] [int] NULL ,
> [DIA_CasoEuro] [tinyint] NULL ,
> [DIA_ImporteB] [float] NULL ,
> [DIA_CodISO] [varchar] (4) COLLATE Traditional_Spanish_CI_AS NULL ,
> [DIA_CuentaContrapart] [varchar] (16) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> Thanks in advance and best regards,
> Enric|||Hi
This should be no different to
SELECT COUNT(*)
FROM ABS_DIARIOHISTO
WHERE DIA_SUCURSAL = 81
SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
COUNT(*) as totalDay, ( SELECT COUNT(*) FROM ABS_DIARIOHISTO WHERE
DIA_SUCURSAL = 81 ) AS TotalCount
FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
John
"Enric" wrote:
> sorry, as usual, I haven't enough patience...
> select sum(total) from (
> SELECT MONTH(DIA_HORAESCRITURA) AS MES,DAY(DIA_HORAESCRITURA) AS DIA,
> count(*) as total
> FROM ABS_DIARIOHISTO WHERE DIA_SUCURSAL = 81
> GROUP BY MONTH(DIA_HORAESCRITURA),DAY(DIA_HORAESC
RITURA)
> ) D
>
> "Enric" wrote:
>

Obtaining timezone offset in T-SQL.

In MS SQL 2000, I would like to obtain the timezone offset from UTC for
a given date. For today's date, I can do
DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
date may not be the same as today because some countries go in Daylight
Saving mode. Can you suggest a way to obtain the timezone offset for
any given date?
Thanks
Yashhttp://www.aspfaq.com/show.asp?id=2218
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
<yashgt@.yahoo.com> wrote in message
news:1108654973.175225.138590@.g14g2000cwa.googlegroups.com...
> In MS SQL 2000, I would like to obtain the timezone offset from UTC for
> a given date. For today's date, I can do
> DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
> date may not be the same as today because some countries go in Daylight
> Saving mode. Can you suggest a way to obtain the timezone offset for
> any given date?
> Thanks
> Yash
>

Obtaining timezone offset in T-SQL.

In MS SQL 2000, I would like to obtain the timezone offset from UTC for
a given date. For today's date, I can do
DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
date may not be the same as today because some countries go in Daylight
Saving mode. Can you suggest a way to obtain the timezone offset for
any given date?

Thanks
Yash(yashgt@.yahoo.com) writes:
> In MS SQL 2000, I would like to obtain the timezone offset from UTC for
> a given date. For today's date, I can do
> DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future
> date may not be the same as today because some countries go in Daylight
> Saving mode. Can you suggest a way to obtain the timezone offset for
> any given date?

Since the datetime data type is not timezone-aware, I can't see that
this can be done by SQL Server itself.

What I can think of is:
1) Set up a table with all the worlds time-zone information and query that
table.
2) Write an extended stored procedure or a COM object that retrieves
the information from Windows.
3) A combination of 2 and 3: you talk to Windows when the server boots,
and then stores the information in a table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Feb 17 2005, 05:43 pm, Erland Sommarskog <esquel@.sommarskog.se> wrote
in news:Xns9600F106C231BYazorman@.127.0.0.1:

> 3) A combination of 2 and 3: you talk to Windows when the server
> boots,
> and then stores the information in a table.

This may not be a good idea if the server is started on first Saturday in
April, for example...

--
remove a 9 to reply by email|||Dimitri Furman (dfurman@.cloud99.net) writes:
> On Feb 17 2005, 05:43 pm, Erland Sommarskog <esquel@.sommarskog.se> wrote
> in news:Xns9600F106C231BYazorman@.127.0.0.1:
>> 3) A combination of 2 and 3: you talk to Windows when the server
>> boots,
>> and then stores the information in a table.
> This may not be a good idea if the server is started on first Saturday in
> April, for example...

What you would read is of course the static information about when the
transitions to/from DST occur.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Obtaining the time from datetime field

From this value: 2002-11-22 00:00:22.000
I want to obtain in string type "00:00:22.000" including miliseconds
I try this but returns: 0:0:22:0
select
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
from cargaprocesos
Can you help me?
Thanks a lot,Enric, try:
convert(varchar(12), dt, 114)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||You should go with that:
Select Convert(varchar(12), GETDATE(), 114)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||Thanks Itzik, I had tried this:
select substring(convert(varchar(50),duracionul
t,113),13,12),
duracionult
from cargaprocesos
But it was failing.
regards,
"Itzik Ben-Gan" wrote:
> Enric, try:
> convert(varchar(12), dt, 114)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Enric" wrote:
>|||A disign suggestion:
I would create a function for this logic.
Makes the code more readable and it makes the logic reusable aswell.
Hope this is helpfull.
Marcel van Eijkel
( www.vaneijkel.com )

Obtaining the time

dear all,
I'm looking for a better version of this stuff. I want to obtain the time of
a datetime field:
DuracionUlt is a datetime field.
SELECT DuracionUlt,
Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
+ ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
+ ':' +
Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
AND datediff(dd,duracionult,getdate()) = 1
This query returns:
2006-03-28 00:00:00.000 = 0:0:0:0
2006-03-28 00:00:42.000 = 0:0:42:0
Any comment/suggestion/thought/advice would be very appreciated,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)SELECT CONVERT(VARCHAR(30),GETDATE(),108)
select {fn extract(minute from getdate())}
Select {fn dayname( GetDate()) }
select {fn CURRENT_DATE()}
select {fn CURRENT_time()}
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
> dear all,
> I'm looking for a better version of this stuff. I want to obtain the time
> of
> a datetime field:
> DuracionUlt is a datetime field.
> SELECT DuracionUlt,
> Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
> + ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
> Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
> + ':' +
> Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
> FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
> AND datediff(dd,duracionult,getdate()) = 1
> This query returns:
> 2006-03-28 00:00:00.000 = 0:0:0:0
> 2006-03-28 00:00:42.000 = 0:0:42:0
> Any comment/suggestion/thought/advice would be very appreciated,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> SELECT CONVERT(VARCHAR(30),GETDATE(),108)
> select {fn extract(minute from getdate())}
> Select {fn dayname( GetDate()) }
> select {fn CURRENT_DATE()}
> select {fn CURRENT_time()}
>
>
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
>
>

Obtaining the number of records in a sqlDataReader

Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?You will need to read through the records to know how many rows, or alternately, return the rowcount as a column:

SELECT *,(SELECT COUNT(*) FROM Customer WHERE ID=7)
FROM Customer WHERE ID=7

If using stored procedures, perhaps you can do this more elegantly.

Why do you need the count?|||You can also check the @.@.rowcount system variable after the select

select ...
set @.Count = @.@.rowcount|||I need the count because the project I am working on needs to do specific tasks dependent on the no of records returned.

How can I access the @.@.rowcount from inside my code?|||Well, then I think the reality is you are going to have to (either in a seperate query or in a subquery) run the COUNT(*) query and send that as a column in the select statement. You could pass an OUTPUT parameter, but I do not believe the OUTPUT parameter will be readable until the DataReader has been read through.

Perhaps you should just use a DataSet, which has the count available.|||Perhaps a dataset is the better tool for the job then. Will it have any significant overhead compared to a datareader?|||Yes. Rather than reading records one at a time in firehose mode, it will read all rows into memory, and store them in memory (the dataSet is an in-memory representation). If you are processing a huge resultset, it could be a problem vs. the DataReader, but generally it is OK as well.|||You can also try using a stored procedure and you can return the count as either a seperate resultset or using an output paramater.

Obtaining TCP/IP Port and Pipe name

Hello All,

Does anyone know how to obtain the TCP/IP port and the pipe name for an instance?

I would have expected to see these properties in smo.server.configuration or smo.server.settings. What am I missing here?

Thanks

Rob

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ServerInstance. ServerProtocols property is a collection with information about each protocol settings.

WBR, Evergray
--
Words mean nothing...

|||

Thanks Evergray,

Unfortunatly, this will not pick-up any 2000 instances, only 2005 instances installed on the same machine.

Does anyone else know of any other method to obtain the pipe/port via standard smo, or? I know that i can issue a UDP broadcast via a socket and the response (with limited success) will include the information I require, so it can't be rocket science!

Thanks

Rob

|||You will need DMO for this (see Registry object), as the SMO only supports SQL Server 2005 instances.|||

Thanks for the reply Michiel. I have, however respectfully unmarked your post as an answer as the registry object does not expose the required information. I can, via WMI access a remote registry and reqad the NP and port info, but there is a massive overhead in doing this.

I'll rephrase my question in the hope someone knows how to achieve what I'm trying to do:

Via a udp broadcast on 1434, the reply from SQL Servers includes the pipe name and TCP/IP port number they are listening on. This is the information I am trying to obtain. I can, very easily obtain this once connected to an instance, but I need this information prior to connecting, as of course the pipe/port may be different depending upon the instance.

Does anyone know of anyway to do this (without using a socket/IP broadcast)?

Cheers

Rob

|||

Thanks for claryfing your answer. I am somewhat puzzled about what you are trying to accomplish. The normal way to approach this configure you server for the required network protocols, and then configure your clients so that it will try the protocols in a preconfigured order. On a SQL Server 2000 and above versions, the connection betwee your client and instance will be 'routed' for you (on SQL Server 2000 it was a function of one of the instance; in SQL Server 2005, the SQL Browser service is introduced).

Are you trying to connect without this facility? If so, then it makes more sense to preconfigure your servers to some known IP port or named pipe names instead of reinventing the SQL Browser service.

At the risk I get unmarked again, please mark if this helps or clarify further if this is not what you're looking for :-)

HTH

|||

Hi Michiel,

Thanks for your help.

Basically, I'm attempting to audit all unauthorised MSSQL instances on our corporate network. This involves firstly listing all instances, then attempting to connect to the discovered instances to obtain further information. In order to connect, I need to know the pipe name and port number the instances are listening on (quite often the port is changed to be something other then 1433). So, I'm in effect trying to discover a way to to obtain the pipe/port of a particular instance without connecting to it first (chicken or the egg?). This could be done via a udp broadcast as the response from the instances would include the pipe/port they are listening on (as per isql -L). I just can't find a way to get this info without using a socket udp boradcast (or wmi for a remote registry interrogation , which is far too much overhead and is prone to security exceptions)...is there a way?

Thanks

Rob

|||

Makes sense. The UDP broadcast is however unreliable, and an instance could be configured to hide itself. There is really no silver bullet here. This is quite a common question however and it has the SQL Server's team attention (which will not help immediately).

If you are domain admin, you could enforce local scans and aggregate that data up to a central point, if you don't want to depend on WMI.

|||

Hi Michiel,

That confirms my suspicions.

Thanks for all your help regardless; I appreciate it.

Cheers

Rob

Obtaining Primary and Foreign Key information from a table

Hi,
I would like to query the system tables in order to obtain all the Primary
and Foreign keys for any particular table.
I have tried using the syscolumns.colstat field but it only outputs zero. I
understand it should output 1 if the column is a primary key.
Also I do not know I do not know how to obtain Foreign key info on a table.
All help is much appreciated.
Kind regards,
Polly AnnaWhat version of SQL Server are you using?
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> Hi,
> I would like to query the system tables in order to obtain all the Primary
> and Foreign keys for any particular table.
> I have tried using the syscolumns.colstat field but it only outputs zero.
> I
> understand it should output 1 if the column is a primary key.
> Also I do not know I do not know how to obtain Foreign key info on a
> table.
> All help is much appreciated.
> Kind regards,
> Polly Anna|||Hi Aaron,
I am using my query against both a SQL2000 and SQL2005 db.
I attach the query below.
Many thanks for your help.
Polly Anna
select distinct column_name as Column_Name,
data_type,
c.length,
CASE c.colStat
WHEN 1 THEN 'Y' ELSE ''
END AS PrimaryKey
from information_schema.columns as isc
left join sysobjects o
on isc.table_name = o.name
left join syscolumns c
on o.id = c.id and c.name = isc.column_name
where table_name = 'Dentist'
"Aaron Bertrand [SQL Server MVP]" wrote:
> What version of SQL Server are you using?
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> > Hi,
> >
> > I would like to query the system tables in order to obtain all the Primary
> > and Foreign keys for any particular table.
> >
> > I have tried using the syscolumns.colstat field but it only outputs zero.
> > I
> > understand it should output 1 if the column is a primary key.
> >
> > Also I do not know I do not know how to obtain Foreign key info on a
> > table.
> >
> > All help is much appreciated.
> >
> > Kind regards,
> >
> > Polly Anna
>
>|||Polly
SS2005
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
ORDINAL_POSITION;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> Hi Aaron,
> I am using my query against both a SQL2000 and SQL2005 db.
> I attach the query below.
> Many thanks for your help.
> Polly Anna
> select distinct column_name as Column_Name,
> data_type,
> c.length,
> CASE c.colStat
> WHEN 1 THEN 'Y' ELSE ''
> END AS PrimaryKey
> from information_schema.columns as isc
> left join sysobjects o
> on isc.table_name = o.name
> left join syscolumns c
> on o.id = c.id and c.name = isc.column_name
> where table_name = 'Dentist'
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> What version of SQL Server are you using?
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> > Hi,
>> >
>> > I would like to query the system tables in order to obtain all the
>> > Primary
>> > and Foreign keys for any particular table.
>> >
>> > I have tried using the syscolumns.colstat field but it only outputs
>> > zero.
>> > I
>> > understand it should output 1 if the column is a primary key.
>> >
>> > Also I do not know I do not know how to obtain Foreign key info on a
>> > table.
>> >
>> > All help is much appreciated.
>> >
>> > Kind regards,
>> >
>> > Polly Anna
>>|||Hi Uri,
yes thank you, that is brilliant. It works in SS2005. You don't happen to
have the script for SQL2000?
Thank you very much indeed.
Kind regards,
Polly Anna
"Uri Dimant" wrote:
> Polly
> SS2005
> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> from sys.key_constraints as k
> join sys.tables as t
> on t.object_id = k.parent_object_id
> join sys.schemas as s
> on s.schema_id = t.schema_id
> join sys.index_columns as ic
> on ic.object_id = t.object_id
> and ic.index_id = k.unique_index_id
> join sys.columns as c
> on c.object_id = t.object_id
> and c.column_id = ic.column_id
> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> ORDINAL_POSITION;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> > Hi Aaron,
> >
> > I am using my query against both a SQL2000 and SQL2005 db.
> >
> > I attach the query below.
> >
> > Many thanks for your help.
> >
> > Polly Anna
> >
> > select distinct column_name as Column_Name,
> > data_type,
> > c.length,
> > CASE c.colStat
> > WHEN 1 THEN 'Y' ELSE ''
> > END AS PrimaryKey
> >
> > from information_schema.columns as isc
> > left join sysobjects o
> > on isc.table_name = o.name
> > left join syscolumns c
> > on o.id = c.id and c.name = isc.column_name
> > where table_name = 'Dentist'
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> What version of SQL Server are you using?
> >>
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> > Hi,
> >> >
> >> > I would like to query the system tables in order to obtain all the
> >> > Primary
> >> > and Foreign keys for any particular table.
> >> >
> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> > zero.
> >> > I
> >> > understand it should output 1 if the column is a primary key.
> >> >
> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> > table.
> >> >
> >> > All help is much appreciated.
> >> >
> >> > Kind regards,
> >> >
> >> > Polly Anna
> >>
> >>
> >>
>
>|||SELECT t.TABLE_SCHEMA,
t.TABLE_NAME, c.CONSTRAINT_NAME,
c.CONSTRAINT_TYPE, k.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
AND c.TABLE_NAME = k.TABLE_NAME
WHERE
c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY 1,2,4 DESC,3;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> Hi Uri,
> yes thank you, that is brilliant. It works in SS2005. You don't happen to
> have the script for SQL2000?
> Thank you very much indeed.
> Kind regards,
> Polly Anna
> "Uri Dimant" wrote:
>> Polly
>> SS2005
>> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
>> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
>> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
>> from sys.key_constraints as k
>> join sys.tables as t
>> on t.object_id = k.parent_object_id
>> join sys.schemas as s
>> on s.schema_id = t.schema_id
>> join sys.index_columns as ic
>> on ic.object_id = t.object_id
>> and ic.index_id = k.unique_index_id
>> join sys.columns as c
>> on c.object_id = t.object_id
>> and c.column_id = ic.column_id
>> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
>> ORDINAL_POSITION;
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
>> > Hi Aaron,
>> >
>> > I am using my query against both a SQL2000 and SQL2005 db.
>> >
>> > I attach the query below.
>> >
>> > Many thanks for your help.
>> >
>> > Polly Anna
>> >
>> > select distinct column_name as Column_Name,
>> > data_type,
>> > c.length,
>> > CASE c.colStat
>> > WHEN 1 THEN 'Y' ELSE ''
>> > END AS PrimaryKey
>> >
>> > from information_schema.columns as isc
>> > left join sysobjects o
>> > on isc.table_name = o.name
>> > left join syscolumns c
>> > on o.id = c.id and c.name = isc.column_name
>> > where table_name = 'Dentist'
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> >> What version of SQL Server are you using?
>> >>
>> >>
>> >>
>> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I would like to query the system tables in order to obtain all the
>> >> > Primary
>> >> > and Foreign keys for any particular table.
>> >> >
>> >> > I have tried using the syscolumns.colstat field but it only outputs
>> >> > zero.
>> >> > I
>> >> > understand it should output 1 if the column is a primary key.
>> >> >
>> >> > Also I do not know I do not know how to obtain Foreign key info on a
>> >> > table.
>> >> >
>> >> > All help is much appreciated.
>> >> >
>> >> > Kind regards,
>> >> >
>> >> > Polly Anna
>> >>
>> >>
>> >>
>>|||Hi Aaron,
it works like a charm. Thank you so much for your help.
Kind regards,
Polly Anna
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT t.TABLE_SCHEMA,
> t.TABLE_NAME, c.CONSTRAINT_NAME,
> c.CONSTRAINT_TYPE, k.COLUMN_NAME
> FROM INFORMATION_SCHEMA.TABLES t
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
> ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
> AND t.TABLE_NAME = c.TABLE_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
> ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
> AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
> AND c.TABLE_NAME = k.TABLE_NAME
> WHERE
> c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
> ORDER BY 1,2,4 DESC,3;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> > Hi Uri,
> >
> > yes thank you, that is brilliant. It works in SS2005. You don't happen to
> > have the script for SQL2000?
> >
> > Thank you very much indeed.
> >
> > Kind regards,
> >
> > Polly Anna
> >
> > "Uri Dimant" wrote:
> >
> >> Polly
> >> SS2005
> >> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> >> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> >> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> >> from sys.key_constraints as k
> >> join sys.tables as t
> >> on t.object_id = k.parent_object_id
> >> join sys.schemas as s
> >> on s.schema_id = t.schema_id
> >> join sys.index_columns as ic
> >> on ic.object_id = t.object_id
> >> and ic.index_id = k.unique_index_id
> >> join sys.columns as c
> >> on c.object_id = t.object_id
> >> and c.column_id = ic.column_id
> >> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> >> ORDINAL_POSITION;
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> >> > Hi Aaron,
> >> >
> >> > I am using my query against both a SQL2000 and SQL2005 db.
> >> >
> >> > I attach the query below.
> >> >
> >> > Many thanks for your help.
> >> >
> >> > Polly Anna
> >> >
> >> > select distinct column_name as Column_Name,
> >> > data_type,
> >> > c.length,
> >> > CASE c.colStat
> >> > WHEN 1 THEN 'Y' ELSE ''
> >> > END AS PrimaryKey
> >> >
> >> > from information_schema.columns as isc
> >> > left join sysobjects o
> >> > on isc.table_name = o.name
> >> > left join syscolumns c
> >> > on o.id = c.id and c.name = isc.column_name
> >> > where table_name = 'Dentist'
> >> >
> >> >
> >> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >> >
> >> >> What version of SQL Server are you using?
> >> >>
> >> >>
> >> >>
> >> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I would like to query the system tables in order to obtain all the
> >> >> > Primary
> >> >> > and Foreign keys for any particular table.
> >> >> >
> >> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> >> > zero.
> >> >> > I
> >> >> > understand it should output 1 if the column is a primary key.
> >> >> >
> >> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> >> > table.
> >> >> >
> >> >> > All help is much appreciated.
> >> >> >
> >> >> > Kind regards,
> >> >> >
> >> >> > Polly Anna
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>

Obtaining PATH to master DB

I need to obtain the path to the master DB in a Wise install script. I know that for SQL2K it is in the registry key:
HKLM\Software\Microsoft\MSSQLServer\Setup

It seems that a SQL2005 installation does not have the same default
directory.

Is there a way to query this from the server itself so I don't have to read the registry?With the usual caveat that it is not a 'best practice' to query the system tables, you can find the answer by executing:

SELECT filename
FROM sysfiles

while connected to the master database. You will get two records. One is the data file, one is the log file. You can narrow the query by adding:

WHERE name = 'master'

Regards,

hmscott

Edit to add: I did verify this against a SQL 2005 instance we are running in development, but it will also work in SQL2K.

Obtaining foreing keys dependences

I want to know how to obtain the relationships of foreing keys when they have diferent names in diferent tables using SQL querys from VB.net in SQL Server 2000.

Example:

Table Person
----
IDPerson
Name
Address

Table Customer
-----
IDCustomer
.
.
.

Table Employee
-----
IDEmployee
.
.
.

The dependencies are:

IDPerson-->IDCustomer
IDPerson-->IDEmployeeI think you need to query sysforiegnkeys, syscolumns and sysobjects|||Thanks, it wasnt easy but it's done.

Rodrigo

obtaining disk space

Hi,
I'm new to these forums (and to SQL Server), so please be gentle with me.

I am developing a process to obtain information on all our remote servers/databases, and store it in a single local database.
I'm after things like db size, last backup date, free drive space etc...the usual weekly statistics.

I've linked the remote servers to my local one, and have written a few simple procedures (which exist on the local server) to grab backup and file size information from the remote tables. The output is stored locally in tables which we can then query as necessary.

I am having difficulty obtaining the free drive space details.
I'm using :-
'exec <remote_server>.master.dbo.xp_fixeddrives' to get the info, but I cannot store the output in a table on the local server. (remote_server_name, date, drive_letter, space_mb)

I wish to avoid creating any objects on the remote servers if at all possible.
I really want to pass the remote server name into the procedure, and the output to be inserted into the table.

Many thanksset nocount on

create table #FreeSpace(Drive char(1), MB_Free int)

insert into #FreeSpace exec master..xp_fixeddrives

drop table #FreeSpace
go|||Thanks for the reply Blindman, I think the issue is that the insert is being turned into a distributed transaction when the "xp_fixeddisks" function is being called eg...

"insert into my_local_database.dbo.disk_space (drive,mb_free)
exec the_remote_server.master.dbo.xp_fixeddrives"

returns the error "MSDTC on server 'my_local_server' is unavailable".
Whereas the sql to get the file size stats, uses pure sql rather than the exec and works :-

"insert into my_local_database.dbo.db_growth select 'the_remote_server','northwind', getdate() as timestamps, *
from the_remote_server.northwind.dbo.sysfiles"

If it is run locally, the insert works :-
"insert into my_local_database.dbo.disk_space (drive,mb_free) exec master.dbo.xp_fixeddrives"

it is the "exec the_remote_server......" bit that is giving me problems.

I could get it to work by using two processes - (1) the remote server runs the function and inserts into a table of it's own, then (2) my_local_server grabs that data and inserts into the central table, but I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.

Thanks.|||I'd like to avoid creating tables or making too many config changes on the remote servers if at all possible.

Thanks.
No changes on remote server.

create table FreeSpace
(
Drive nvarchar(5),
MB_Free nvarchar(20)
)
go
create Procedure remoteServerDiskSpace_sp (
@.servername nvarchar(4000),
@.FileName varchar(50)
)
AS
declare @.bulksql varchar(200)
Begin


SET @.servername = 'osql -Slocalhost -E -q"SET NOCOUNT ON; exec ' + @.servername +' .master.dbo.xp_fixeddrives" -s"," -n -w8000 -o"' + @.FileName +'"'

Exec master..xp_cmdshell @.servername, NO_OUTPUT
set @.bulksql='BULK INSERT FreeSpace FROM '''+@.FileName+''' WITH (FIELDTERMINATOR = '','',FirstRow=3)'
exec(@.bulksql)
END
GO
-- execute query
exec remoteServerDiskSpace_sp 'remoteserverName','c:\DiskSpace.csv'
--select query
select * from FreeSpace|||Thanks Mallier,
I've now done something in a similar vein to your routine, and it works a treat.

Obtaining all the dates

Dear all,
According to a date introduce I would need to obtain all the periodDesc of
the following table:
CREATE TABLE [dbo].[tbl_Periods] (
[sinStudyID] [smallint] NOT NULL ,
[strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[boldone] [bit] NOT NULL ,
[sinPeriodID] [smallint] NOT NULL ,
[strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[datPeriodBegin] [datetime] NULL ,
[datPeriodEnd] [datetime] NULL ,
)
If I have got as input '2005-01-02' I need obtain this set of rows:
begin end
4 COW 0 203 W2005006 2005-01-30 2005-01-31
4 COW 0 202 W2005005 2005-01-23 2005-01-29
4 COW 0 201 W2005004 2005-01-16 2005-01-22
4 COW 0 196 W2005003 2005-01-09 2005-01-15
4 COW 0 195 W2005002 2005-01-02 2005-01-08
4 COW 0 194 W2005001 2005-01-01 2005-01-01
Any advice will be well received.
Regards,SELECT <column lists> FROM Table
WHERE datPeriodBegin >=@.dt AND datPeriodEnd < dateadd(day,1,@.dt)
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||And the date you provide is related to the datPeriodBegin and datPeriodEnd
in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
both?
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
> Dear all,
> According to a date introduce I would need to obtain all the periodDesc of
> the following table:
>
> CREATE TABLE [dbo].[tbl_Periods] (
> [sinStudyID] [smallint] NOT NULL ,
> [strStudy] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [boldone] [bit] NOT NULL ,
> [sinPeriodID] [smallint] NOT NULL ,
> [strPeriodDesc] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [datPeriodBegin] [datetime] NULL ,
> [datPeriodEnd] [datetime] NULL ,
> )
> If I have got as input '2005-01-02' I need obtain this set of rows:
> begin end
> 4 COW 0 203 W2005006 2005-01-30 2005-01-31
> 4 COW 0 202 W2005005 2005-01-23 2005-01-29
> 4 COW 0 201 W2005004 2005-01-16 2005-01-22
> 4 COW 0 196 W2005003 2005-01-09 2005-01-15
> 4 COW 0 195 W2005002 2005-01-02 2005-01-08
> 4 COW 0 194 W2005001 2005-01-01 2005-01-01
> Any advice will be well received.
> Regards,|||Hi,
Only could be this, nothing else:
2005-01-30
2005-01-23
2005-01-16
2005-01-09
2005-01-02
2005-01-01
Thanks,
"Jacco Schalkwijk" wrote:

> And the date you provide is related to the datPeriodBegin and datPeriodEnd
> in which way? Is it related to datPeriodBegin only, datPeriodEnd only or
> both?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:BD321F1D-7679-4CFF-AFAB-137307FDCC84@.microsoft.com...
>
>|||Sorry, these former values are efectively just for datPeriodBegin
Best wishes,
"Enric" wrote:
> Hi,
>
> Only could be this, nothing else:
> 2005-01-30
> 2005-01-23
> 2005-01-16
> 2005-01-09
> 2005-01-02
> 2005-01-01
>
> Thanks,
> "Jacco Schalkwijk" wrote:
>|||And how does that list of dates relate to 2005-01-02? All in the same month?
In that case:
DECLARE @.date DATETIME
SET @.date = '20050102'
SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
[strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
FROM [tbl_Periods]
WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
Jacco Schalkwijk
SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
> Sorry, these former values are efectively just for datPeriodBegin
> Best wishes,
> "Enric" wrote:
>|||It works amazingly.
Thanks a lot man,
"Jacco Schalkwijk" wrote:

> And how does that list of dates relate to 2005-01-02? All in the same mont
h?
> In that case:
> DECLARE @.date DATETIME
> SET @.date = '20050102'
> SELECT [sinStudyID] , [strStudy], [boldone], [sinPeriodID],
> [strPeriodDesc], [datPeriodBegin], [datPeriodEnd]
> FROM [tbl_Periods]
> WHERE datPeriodBegin >= DATEADD(dd, 1 - DAY(@.dt), @.dt)
> AND datPeriodBegin < DATEADD(mm, 1, DATEADD(dd, 1 - DAY(@.dt), @.dt))
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:9540B0BB-7599-4C48-A6B4-EE78E9585884@.microsoft.com...
>
>

Obtaining a value from Openquery?

Hi all,

I have an Informix Dynamic Server linked within my MS SQL 7 server. What I want to achieve is to be able to obtain a value from the informix table and then to use this value to update the MS SQL server table. I am doing this within a trigger on SQL Server. I am not doing this from infromix as I cant get informix to see the SQL Server.

My problem is that I dont know how to assign the query result to a variable so I can use it in my Update. Can anyone help me with my syntax?? Below is my variable settings and query within the Insert trigger...(Not sure if its correct)

DECLARE @.TSQL VARCHAR(100)
DECLARE @.NAMEID VARCHAR(10)

SET @.NAMEID = (Select Inserted.NameID from Inserted)

SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

EXEC (@.TSQL)

How do I set a variable with the nar_num value that I get back from the informix server. Any Help would be great.

Thanks
Anthonyyou would need to fill in the data type for nar_num but give this a try:

DECLARE @.TSQL VARCHAR(100)
, @.NAMEID VARCHAR(10)

create table #tmp(nar_num <data type>)

select @.NAMEID = min(NameID) from Inserted
while (@.NAMEID is not null) begin
SET @.TSQL = 'SELECT * FROM OPENQUERY(AUTHTEST, ''Select nar_num from aunrmast where dpid = '' + @.NAMEID + '')'

truncate table #tmp
insert into #tmp
EXEC (@.TSQL)

select @.NAMEID = min(NameID) from Inserted where nameid > @.NAMEID
end

Please note that I changed things a bit to handle more than one one record.

Obtain XML from URL with SQL Server 2005?

Hi guys,
With SQL Server 2000 I used to create a virtual directory and obtain a XML
from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
XML RAW &root=root"
Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
know how to do this (neither the guy who is in charge of the server, lol).
I've been searching but I dind't find a thing. Would anybody be soooo kind
to shed some light over this for me?
=)
Thanks a lot!
You have to go to msdn.microsoft.com and download SQLXML 3.0
SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
extension any longer that provided VDIR access.
If you miss this, please go to http://connect.microsoft.com/sqlserver and
file a request ;-)
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> With SQL Server 2000 I used to create a virtual directory and obtain a XML
> from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
> XML RAW &root=root"
> Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
> know how to do this (neither the guy who is in charge of the server, lol).
> I've been searching but I dind't find a thing. Would anybody be soooo kind
> to shed some light over this for me?
> =)
> Thanks a lot!
|||Hehehehe
Thanks a lot Michael!
Yes... I found it in the weekend. What a @.$#)@.( =P Specially since they
don't mention "officially" in the changes and compatibility issues...
Luckily, the old SQLXML is compatible.
Now I'm trying to figure out how the SQLXML works, since I don't fully
understand how I have to work from .NET and how the SQL works... But well,
in a few weeks I'll have some time to work on it. =/
Thanks again!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> You have to go to msdn.microsoft.com and download SQLXML 3.0
> SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
> extension any longer that provided VDIR access.
> If you miss this, please go to http://connect.microsoft.com/sqlserver and
> file a request ;-)
> Best regards
> Michael
> "Carlos Sosa Albert" <betun (at) hotmail> wrote in message
> news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Carlos. you may also look at the new Astoria project
http://astoria.mslivelabs.com that provides the REST like APIs similar to
the SQLXML component.
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:%23IQFSjqpHHA.4872@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Hehehehe
> Thanks a lot Michael!
> Yes... I found it in the weekend. What a @.$#)@.( =P Specially since they
> don't mention "officially" in the changes and compatibility issues...
> Luckily, the old SQLXML is compatible.
> Now I'm trying to figure out how the SQLXML works, since I don't fully
> understand how I have to work from .NET and how the SQL works... But well,
> in a few weeks I'll have some time to work on it. =/
> Thanks again!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...

Obtain XML from URL with SQL Server 2005?

Hi guys,
With SQL Server 2000 I used to create a virtual directory and obtain a XML
from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
XML RAW &root=root"
Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
know how to do this (neither the guy who is in charge of the server, lol).
I've been searching but I dind't find a thing. Would anybody be soooo kind
to shed some light over this for me?
=)
Thanks a lot!You have to go to msdn.microsoft.com and download SQLXML 3.0
SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
extension any longer that provided VDIR access.
If you miss this, please go to http://connect.microsoft.com/sqlserver and
file a request ;-)
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
> Hi guys,
> With SQL Server 2000 I used to create a virtual directory and obtain a XML
> from a URL using "http://server/myVirtualDir?sql=select * from myTable FOR
> XML RAW &root=root"
> Recently we installed SQL Server 2005 (not my fault, lol) and I just don't
> know how to do this (neither the guy who is in charge of the server, lol).
> I've been searching but I dind't find a thing. Would anybody be soooo kind
> to shed some light over this for me?
> =)
> Thanks a lot!|||Hehehehe
Thanks a lot Michael!
Yes... I found it in the wend. What a @.$#)@.( =P Specially since they
don't mention "officially" in the changes and compatibility issues...
Luckily, the old SQLXML is compatible.
Now I'm trying to figure out how the SQLXML works, since I don't fully
understand how I have to work from .NET and how the SQL works... But well,
in a few ws I'll have some time to work on it. =/
Thanks again!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...
> You have to go to msdn.microsoft.com and download SQLXML 3.0
> SQLXML 4.0 that comes with SQL Server 2005 does not support the ISAPI
> extension any longer that provided VDIR access.
> If you miss this, please go to http://connect.microsoft.com/sqlserver and
> file a request ;-)
> Best regards
> Michael
> "Carlos Sosa Albert" <betun (at) hotmail> wrote in message
> news:uCVYqZHpHHA.588@.TK2MSFTNGP06.phx.gbl...
>|||Hi Carlos. you may also look at the new Astoria project
http://astoria.mslivelabs.com that provides the REST like APIs similar to
the SQLXML component.
Best regards
Michael
"Carlos Sosa Albert" <betun (at) hotmail> wrote in message
news:%23IQFSjqpHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hehehehe
> Thanks a lot Michael!
> Yes... I found it in the wend. What a @.$#)@.( =P Specially since they
> don't mention "officially" in the changes and compatibility issues...
> Luckily, the old SQLXML is compatible.
> Now I'm trying to figure out how the SQLXML works, since I don't fully
> understand how I have to work from .NET and how the SQL works... But well,
> in a few ws I'll have some time to work on it. =/
> Thanks again!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:ekrva6MpHHA.3736@.TK2MSFTNGP03.phx.gbl...

Obtain values from different tables

Table processes_user
id_user | id_proc
--
35 | 17001
100 | 1089
35 | 17002
Table processes_flow
(example:the process 17001 is with the user 35 and the deadline of the
flow is 2006-07-30! As you can see there are 2 entries on the table
below, the first with a lower id references an old flow, but I want to
get the deadline of the last flow of the process)
--
id | id_proc | deadline
--
10| 17001 | null
12| 1089 | 2006-05-12
15| 17001 | 2006-07-30
20| 17002 | null
--
I would like to get for the user 35 the following info:
17001 | 2006-07-30
17002 | null
How can I do this with a sql command? I would like you to share some
ideas because I'm stuck with this.
Regards,
Hugo SantosSELECT a.id_user, a.id_proc, b.deadline
FROM processes_user a
LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
FROM processes_flow
GROUP BY id_proc) b ON a.id_proc = b.id_proc
WHERE a.id_user= 35
Untested.
Stu
hugonsan...@.gmail.com wrote:
> Table processes_user
> id_user | id_proc
> --
> 35 | 17001
> 100 | 1089
> 35 | 17002
>
> Table processes_flow
> (example:the process 17001 is with the user 35 and the deadline of the
> flow is 2006-07-30! As you can see there are 2 entries on the table
> below, the first with a lower id references an old flow, but I want to
> get the deadline of the last flow of the process)
> --
> id | id_proc | deadline
> --
> 10| 17001 | null
> 12| 1089 | 2006-05-12
> 15| 17001 | 2006-07-30
> 20| 17002 | null
> --
> I would like to get for the user 35 the following info:
> 17001 | 2006-07-30
> 17002 | null
> How can I do this with a sql command? I would like you to share some
> ideas because I'm stuck with this.
> Regards,
> Hugo Santos|||Stu wrote:
> SELECT a.id_user, a.id_proc, b.deadline
> FROM processes_user a
> LEFT JOIN (SELECT id_proc, MAX(deadline) as deadline
> FROM processes_flow
> GROUP BY id_proc) b ON a.id_proc = b.id_proc
> WHERE a.id_user= 35
>
> Untested.
> Stu
>
Thanks for your reply Stu, but with that aren't you going to get the
max deadline only? I want to show the deadline from the last flow.
For example.. on the first flow you may have a deadline and on the
second the deadline is null. And I want to get the deadline from the
last flow... which is null.
Can you clear this out?|||I'm sorry, I made an assumption that may or may not be true; I'm
assuming that NULL comes before a deadline. In that case, the subquery
will only return rows that have a deadline associated with them; doing
a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
there is not.
hugos wrote:
> Stu wrote:
> Thanks for your reply Stu, but with that aren't you going to get the
> max deadline only? I want to show the deadline from the last flow.
> For example.. on the first flow you may have a deadline and on the
> second the deadline is null. And I want to get the deadline from the
> last flow... which is null.
> Can you clear this out?|||Thanks.
I already found a different way! I think it's not optimized but it will
work for now ;)
Stu escreveu:
> I'm sorry, I made an assumption that may or may not be true; I'm
> assuming that NULL comes before a deadline. In that case, the subquery
> will only return rows that have a deadline associated with them; doing
> a LEFT JOIN will return the MAX(deadline) if there is one, and NULL if
> there is not.

Obtain unit percent with unit count divided by total count in query

The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.

Here is a test code snippet:

--Test Count/Count subquery

declare @.Date datetime

set @.date = '8/15/2007'

select

-- count returns unit data

Count(substring(m.PTNumber,3,3)) as PTCnt,

-- count returns total for all units

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date) as TotalCnt,

-- attempting to calculate the percent by PTCnt/TotalCnt returns 0

(Count(substring(m.PTNumber,3,3)) /

(select Count(substring(m1.PTNumber,3,3))

from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) as AUPct

-- main select

from tblVGD1_Master m

left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID

Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9

and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0

and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)

and v.[Date] between DateAdd(dd,-90,@.Date) and @.Date

group by substring(m.PTNumber, 3,3)

order by AUPct Desc

Thanks. Dan

I figured out my solution - The top integer needs to be cast as a decimal:

(CAST(Count(substring(m.PTNumber,3,3)) as Decimal(15,5))/

(select Count(substring(m1.PTNumber,3,3)) from tblVGD1_Master m1

left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID

Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9

and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0

and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)

and v1.[Date] between DateAdd(dd,-90,@.Date) and @.Date)) * 100 as AUPct

Obtain the sql server properties by code...

Hi,
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can′t
.
thanks in advance...
ChevyThose settings are in the registry. You will have to get then with
xp_regread.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/.../1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can′t.
> thanks in advance...
> --
> Chevy|||Chevy
http://dimantdatabasesolutions.blog...er.ht
ml
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> cant.
> thanks in advance...
> --
> Chevy

Obtain the sql server properties by code...

Hi,
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can′t.
thanks in advance...
Chevy
Those settings are in the registry. You will have to get then with
xp_regread.
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can′t.
> thanks in advance...
> --
> Chevy
|||Chevy
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> cant.
> thanks in advance...
> --
> Chevy

Obtain the sql server properties by code...

Hi,
a need to make a report wich contains the most of properties o an instance o
SQL Server 2000. The same information that appears on the SQL Server
Properties in the Enterprise Manager.
I have 25 servers and I wonder if I can get that information by a
Transact.SQL Server script. I try to do that and I got some information but
other like autentication mode, startup service account, audit level I can´t.
thanks in advance...
--
ChevyThose settings are in the registry. You will have to get then with
xp_regread.
--
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can´t.
> thanks in advance...
> --
> Chevy|||Chevy
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
"Chevy" <Chevy@.discussions.microsoft.com> wrote in message
news:230B88E2-13EE-4E33-84D5-D8FD01348B45@.microsoft.com...
> Hi,
> a need to make a report wich contains the most of properties o an instance
> o
> SQL Server 2000. The same information that appears on the SQL Server
> Properties in the Enterprise Manager.
> I have 25 servers and I wonder if I can get that information by a
> Transact.SQL Server script. I try to do that and I got some information
> but
> other like autentication mode, startup service account, audit level I
> can´t.
> thanks in advance...
> --
> Chevy