Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'Consider making a small DTS package with the stored proc as the SQL for the
Data Pump. Have SQL Server execute the package.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<varkey.mathew@.wipro.com> wrote in message
news:1147823437.443893.165480@.i39g2000cwa.googlegroups.com...
Dear all,
I wanted to create a text file to be passed as input to a Mainframe
system. I have created an SP with the query to create the data exactly
in the requisite format. I need to be creating the text file from
within SQL Server only. I have used osql utility to achieve this and it
works.. However, it adds a couple of extra new lines in the end of the
text file, which is not acceptable to Mainframe system.. Is there any
way I can get rid of the 2 additional lines in the end of the file? Or
is there another way to do this?
I would really appreciate a quick response..
Best regards,
Varkey
PS: I am using master..xp_cmdshell to execute the osql statement. I am
using SQL Server 2000
osql format is 'osql -Q"EXEC mysp" -o c:\test.txt -h-1 -s"" -Smyserver
-dmydb -E'
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Create a new system table
Is possible create a new table in master database with the
flag 'System'?
Thanks,
Rui OliveiraYes, if you work for Microsoft
For the rest of us NO
You should not mess with system tables and you should not be creating tables on the master database. You will not be able to do either when you move to Yukon so start developing good habits now
Regard
John|||Hi Rui
The following is the limit of what you can do:
create table mysystemtable ( ....)
go
sp_MS_marksystemobject mysystemtable
go
This table will show up as 'system' in Enterprise Manager, but NOT when you
use sp_help in Query Analzyer. You might be able to force it by making
directly updates to sysobjects, but I wouldn't do that on a production
machine.
Can you tell us why you need to do this?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rui Oliveira" <anonymous@.discussions.microsoft.com> wrote in message
news:553e01c40046$09f66570$a001280a@.phx.gbl...
> Is possible create a new table in master database with the
> flag 'System'?
> Thanks,
> Rui Oliveira
>|||Tks
>--Original Message--
>Hi Rui
>The following is the limit of what you can do:
>create table mysystemtable ( ....)
>go
>sp_MS_marksystemobject mysystemtable
>go
>This table will show up as 'system' in Enterprise
Manager, but NOT when you
>use sp_help in Query Analzyer. You might be able to force
it by making
>directly updates to sysobjects, but I wouldn't do that on
a production
>machine.
>Can you tell us why you need to do this?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rui Oliveira" <anonymous@.discussions.microsoft.com>
wrote in message
>news:553e01c40046$09f66570$a001280a@.phx.gbl...
>> Is possible create a new table in master database with
the
>> flag 'System'?
>> Thanks,
>> Rui Oliveira
>
>.
>
flag 'System'?
Thanks,
Rui OliveiraYes, if you work for Microsoft
For the rest of us NO
You should not mess with system tables and you should not be creating tables on the master database. You will not be able to do either when you move to Yukon so start developing good habits now
Regard
John|||Hi Rui
The following is the limit of what you can do:
create table mysystemtable ( ....)
go
sp_MS_marksystemobject mysystemtable
go
This table will show up as 'system' in Enterprise Manager, but NOT when you
use sp_help in Query Analzyer. You might be able to force it by making
directly updates to sysobjects, but I wouldn't do that on a production
machine.
Can you tell us why you need to do this?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rui Oliveira" <anonymous@.discussions.microsoft.com> wrote in message
news:553e01c40046$09f66570$a001280a@.phx.gbl...
> Is possible create a new table in master database with the
> flag 'System'?
> Thanks,
> Rui Oliveira
>|||Tks
>--Original Message--
>Hi Rui
>The following is the limit of what you can do:
>create table mysystemtable ( ....)
>go
>sp_MS_marksystemobject mysystemtable
>go
>This table will show up as 'system' in Enterprise
Manager, but NOT when you
>use sp_help in Query Analzyer. You might be able to force
it by making
>directly updates to sysobjects, but I wouldn't do that on
a production
>machine.
>Can you tell us why you need to do this?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rui Oliveira" <anonymous@.discussions.microsoft.com>
wrote in message
>news:553e01c40046$09f66570$a001280a@.phx.gbl...
>> Is possible create a new table in master database with
the
>> flag 'System'?
>> Thanks,
>> Rui Oliveira
>
>.
>
Create a new system table
Is possible create a new table in master database with the
flag 'System'?
Thanks,
Rui OliveiraYes, if you work for Microsoft.
For the rest of us NO.
You should not mess with system tables and you should not be creating tables
on the master database. You will not be able to do either when you move to
Yukon so start developing good habits now.
Regards
John|||Hi Rui
The following is the limit of what you can do:
create table mysystemtable ( ....)
go
sp_MS_marksystemobject mysystemtable
go
This table will show up as 'system' in Enterprise Manager, but NOT when you
use sp_help in Query Analzyer. You might be able to force it by making
directly updates to sysobjects, but I wouldn't do that on a production
machine.
Can you tell us why you need to do this?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rui Oliveira" <anonymous@.discussions.microsoft.com> wrote in message
news:553e01c40046$09f66570$a001280a@.phx.gbl...
> Is possible create a new table in master database with the
> flag 'System'?
> Thanks,
> Rui Oliveira
>|||Tks
>--Original Message--
>Hi Rui
>The following is the limit of what you can do:
>create table mysystemtable ( ....)
>go
>sp_MS_marksystemobject mysystemtable
>go
>This table will show up as 'system' in Enterprise
Manager, but NOT when you
>use sp_help in Query Analzyer. You might be able to force
it by making
>directly updates to sysobjects, but I wouldn't do that on
a production
>machine.
>Can you tell us why you need to do this?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rui Oliveira" <anonymous@.discussions.microsoft.com>
wrote in message
>news:553e01c40046$09f66570$a001280a@.phx.gbl...
the
>
>.
>
flag 'System'?
Thanks,
Rui OliveiraYes, if you work for Microsoft.
For the rest of us NO.
You should not mess with system tables and you should not be creating tables
on the master database. You will not be able to do either when you move to
Yukon so start developing good habits now.
Regards
John|||Hi Rui
The following is the limit of what you can do:
create table mysystemtable ( ....)
go
sp_MS_marksystemobject mysystemtable
go
This table will show up as 'system' in Enterprise Manager, but NOT when you
use sp_help in Query Analzyer. You might be able to force it by making
directly updates to sysobjects, but I wouldn't do that on a production
machine.
Can you tell us why you need to do this?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rui Oliveira" <anonymous@.discussions.microsoft.com> wrote in message
news:553e01c40046$09f66570$a001280a@.phx.gbl...
> Is possible create a new table in master database with the
> flag 'System'?
> Thanks,
> Rui Oliveira
>|||Tks
>--Original Message--
>Hi Rui
>The following is the limit of what you can do:
>create table mysystemtable ( ....)
>go
>sp_MS_marksystemobject mysystemtable
>go
>This table will show up as 'system' in Enterprise
Manager, but NOT when you
>use sp_help in Query Analzyer. You might be able to force
it by making
>directly updates to sysobjects, but I wouldn't do that on
a production
>machine.
>Can you tell us why you need to do this?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rui Oliveira" <anonymous@.discussions.microsoft.com>
wrote in message
>news:553e01c40046$09f66570$a001280a@.phx.gbl...
the
>
>.
>
Thursday, March 22, 2012
Create a DSN programmatically with network options
Hello,
I would like to create a brand new system DSN, programmatically,
targeting an MS-SQL Server 2000 database, and with networking options
(pipes,TCP/IP, etc.).
Until now the only solutions I managed to find on google.groups or
forums are:
1- The usual "Why don't you do it manually ?" and other classical
off-topic answers.
2- The "Dump the registry keys in HKLM\Software\ODBC etc." solution.
Relying on this single registry entry is not enough since it only
configures the DSN registration, not its advanced connection options (at
least for SQL Server). Particularly, networking options cannot be set
this way. With Sysinternals RegMon and some additional MSDN
documentation I finally understood that some stuff was written in
"HKLM\Software\Microsoft\MSSQLServer\...\ConnectTo". My problem is I
have not enough documentation on the value of these keys (plus the ones
in \TDS) to write a robust script.
3- I cannot find anyone making the odbcconf.exe thing to work. Anyway, I
think it would be useless for my purposes.
4- Solutions with ODBC API calls or Registry API calls which just
replicates one of the broken methods detailed above.
So my question is : is there any robust, efficient, professional
solution (I usually do not consider registry wizardry to fulfill these
criterions but I will take whatever you may propose) to export one SQL
Server DSN configuration from one machine to another ? Or is it hopeless
and someone in Microsoft definitely miss something here ?
By DSN configuration, I mean everything which can be set via the ODBC
administrator interface. And the export method should optimally not
require more knowledge (ie. parameters) than the one passed to the
administrator GUI.
I easily understand that some of these parameters are machine/domain
dependant, but there should be a way to edit them via scripts (perl
regexp or anything else) if the configuration files happened to be text
ones. Otherwise, the export would be meaningless.
I hope that what I am asking makes some sense. Maybe not, I might
overlook some SQL Server configuration issues here.
Thanks for any hints.
Patrick MzardThe following articles can provide a couple of different
methods as well as more information on creating DSNs
programmatically:
HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
HOWTO: Set Up ODBC Data Sources When Distributing Apps
http://support.microsoft.com/defaul...kb;EN-US;123008
-Sue
On Mon, 01 Mar 2004 20:20:47 +0100, Patrick Mzard
<patrick.mezard@.ifrance.com> wrote:
>Hello,
>I would like to create a brand new system DSN, programmatically,
>targeting an MS-SQL Server 2000 database, and with networking options
>(pipes,TCP/IP, etc.).
>Until now the only solutions I managed to find on google.groups or
>forums are:
>1- The usual "Why don't you do it manually ?" and other classical
>off-topic answers.
>2- The "Dump the registry keys in HKLM\Software\ODBC etc." solution.
>Relying on this single registry entry is not enough since it only
>configures the DSN registration, not its advanced connection options (at
>least for SQL Server). Particularly, networking options cannot be set
>this way. With Sysinternals RegMon and some additional MSDN
>documentation I finally understood that some stuff was written in
>"HKLM\Software\Microsoft\MSSQLServer\...\ConnectTo". My problem is I
>have not enough documentation on the value of these keys (plus the ones
>in \TDS) to write a robust script.
>3- I cannot find anyone making the odbcconf.exe thing to work. Anyway, I
>think it would be useless for my purposes.
>4- Solutions with ODBC API calls or Registry API calls which just
>replicates one of the broken methods detailed above.
>So my question is : is there any robust, efficient, professional
>solution (I usually do not consider registry wizardry to fulfill these
>criterions but I will take whatever you may propose) to export one SQL
>Server DSN configuration from one machine to another ? Or is it hopeless
>and someone in Microsoft definitely miss something here ?
>By DSN configuration, I mean everything which can be set via the ODBC
>administrator interface. And the export method should optimally not
>require more knowledge (ie. parameters) than the one passed to the
>administrator GUI.
>I easily understand that some of these parameters are machine/domain
>dependant, but there should be a way to edit them via scripts (perl
>regexp or anything else) if the configuration files happened to be text
>ones. Otherwise, the export would be meaningless.
>I hope that what I am asking makes some sense. Maybe not, I might
>overlook some SQL Server configuration issues here.
>Thanks for any hints.
>Patrick Mzard|||> The following articles can provide a couple of different
> methods as well as more information on creating DSNs
> programmatically:
> HOWTO: Create and Remove a DSN in Visual Basic
> http://support.microsoft.com/defaul...b;EN-US;q171146
> HOWTO: Programmatically Create a DSN for SQL Server with VB
> http://support.microsoft.com/defaul...b;EN-US;q184608
> HOWTO: Set Up ODBC Data Sources When Distributing Apps
> http://support.microsoft.com/defaul...kb;EN-US;123008
> -Sue
Thank you for the links but I have already read these articles and they fall
in the section 4 category described in my original post: API calls (via VB)
which do not provide more options than the basic registry tricks. Or maybe I
really missed something, could you configure the SQL Server network library
to use with these methods ?
Patrick Mzard|||Yes - It's one of the driver attributes you can supply, e.g.
in the first example, you can add the following to the
strAttributes:
Network=DBMSSOCN (tcp/ip)
or
Network=DBNMPNTW (named pipes)
You have to set the address accordingly.
-Sue
On Tue, 2 Mar 2004 06:51:46 +0100, "Patrick.Mzard"
<patrick.mezard@.ifrance.fr> wrote:
>Thank you for the links but I have already read these articles and they fal
l
>in the section 4 category described in my original post: API calls (via VB)
>which do not provide more options than the basic registry tricks. Or maybe
I
>really missed something, could you configure the SQL Server network library
>to use with these methods ?
>Patrick Mzard
>|||Sue Hoegemeier wrote:
> Yes - It's one of the driver attributes you can supply, e.g.
> in the first example, you can add the following to the
> strAttributes:
> Network=DBMSSOCN (tcp/ip)
> or
> Network=DBNMPNTW (named pipes)
> You have to set the address accordingly.
Great I finally got it.
Here is the solution (maybe it will help someone who knows...).
I was unaware that all the ODBC administrator options were actually DSN
connection string parameters. I found the SQL Server parameter list and
description in the SQL Server documentation (by looking for the network
string you gave me and stumbling onto the "SQLConfigDataSource" function ).
Then I managed to get the odbcconf.exe thing to work (the use of pipes
'|' as separator instead of semi-colons is really misleading) and
register a data source with it. I do not know if DSN can be removed via
odbcconf, but anyway I can rewrite a simple call to SQLConfigDataSource
with the correct options if needed.
Thank you very much for your help, I would have never found it, had not
you insisted your solution was a valid one.
Patrick Mzard|||Your quite welcome...and thanks for posting back your
results.
-Sue
On Tue, 02 Mar 2004 18:30:16 +0100, Patrick Mzard
<patrick.mezard@.ifrance.com> wrote:
>Sue Hoegemeier wrote:
>Great I finally got it.
>Here is the solution (maybe it will help someone who knows...).
>I was unaware that all the ODBC administrator options were actually DSN
>connection string parameters. I found the SQL Server parameter list and
>description in the SQL Server documentation (by looking for the network
>string you gave me and stumbling onto the "SQLConfigDataSource" function ).
>Then I managed to get the odbcconf.exe thing to work (the use of pipes
>'|' as separator instead of semi-colons is really misleading) and
>register a data source with it. I do not know if DSN can be removed via
>odbcconf, but anyway I can rewrite a simple call to SQLConfigDataSource
>with the correct options if needed.
>Thank you very much for your help, I would have never found it, had not
>you insisted your solution was a valid one.
>Patrick Mzard
I would like to create a brand new system DSN, programmatically,
targeting an MS-SQL Server 2000 database, and with networking options
(pipes,TCP/IP, etc.).
Until now the only solutions I managed to find on google.groups or
forums are:
1- The usual "Why don't you do it manually ?" and other classical
off-topic answers.
2- The "Dump the registry keys in HKLM\Software\ODBC etc." solution.
Relying on this single registry entry is not enough since it only
configures the DSN registration, not its advanced connection options (at
least for SQL Server). Particularly, networking options cannot be set
this way. With Sysinternals RegMon and some additional MSDN
documentation I finally understood that some stuff was written in
"HKLM\Software\Microsoft\MSSQLServer\...\ConnectTo". My problem is I
have not enough documentation on the value of these keys (plus the ones
in \TDS) to write a robust script.
3- I cannot find anyone making the odbcconf.exe thing to work. Anyway, I
think it would be useless for my purposes.
4- Solutions with ODBC API calls or Registry API calls which just
replicates one of the broken methods detailed above.
So my question is : is there any robust, efficient, professional
solution (I usually do not consider registry wizardry to fulfill these
criterions but I will take whatever you may propose) to export one SQL
Server DSN configuration from one machine to another ? Or is it hopeless
and someone in Microsoft definitely miss something here ?
By DSN configuration, I mean everything which can be set via the ODBC
administrator interface. And the export method should optimally not
require more knowledge (ie. parameters) than the one passed to the
administrator GUI.
I easily understand that some of these parameters are machine/domain
dependant, but there should be a way to edit them via scripts (perl
regexp or anything else) if the configuration files happened to be text
ones. Otherwise, the export would be meaningless.
I hope that what I am asking makes some sense. Maybe not, I might
overlook some SQL Server configuration issues here.
Thanks for any hints.
Patrick MzardThe following articles can provide a couple of different
methods as well as more information on creating DSNs
programmatically:
HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
HOWTO: Set Up ODBC Data Sources When Distributing Apps
http://support.microsoft.com/defaul...kb;EN-US;123008
-Sue
On Mon, 01 Mar 2004 20:20:47 +0100, Patrick Mzard
<patrick.mezard@.ifrance.com> wrote:
>Hello,
>I would like to create a brand new system DSN, programmatically,
>targeting an MS-SQL Server 2000 database, and with networking options
>(pipes,TCP/IP, etc.).
>Until now the only solutions I managed to find on google.groups or
>forums are:
>1- The usual "Why don't you do it manually ?" and other classical
>off-topic answers.
>2- The "Dump the registry keys in HKLM\Software\ODBC etc." solution.
>Relying on this single registry entry is not enough since it only
>configures the DSN registration, not its advanced connection options (at
>least for SQL Server). Particularly, networking options cannot be set
>this way. With Sysinternals RegMon and some additional MSDN
>documentation I finally understood that some stuff was written in
>"HKLM\Software\Microsoft\MSSQLServer\...\ConnectTo". My problem is I
>have not enough documentation on the value of these keys (plus the ones
>in \TDS) to write a robust script.
>3- I cannot find anyone making the odbcconf.exe thing to work. Anyway, I
>think it would be useless for my purposes.
>4- Solutions with ODBC API calls or Registry API calls which just
>replicates one of the broken methods detailed above.
>So my question is : is there any robust, efficient, professional
>solution (I usually do not consider registry wizardry to fulfill these
>criterions but I will take whatever you may propose) to export one SQL
>Server DSN configuration from one machine to another ? Or is it hopeless
>and someone in Microsoft definitely miss something here ?
>By DSN configuration, I mean everything which can be set via the ODBC
>administrator interface. And the export method should optimally not
>require more knowledge (ie. parameters) than the one passed to the
>administrator GUI.
>I easily understand that some of these parameters are machine/domain
>dependant, but there should be a way to edit them via scripts (perl
>regexp or anything else) if the configuration files happened to be text
>ones. Otherwise, the export would be meaningless.
>I hope that what I am asking makes some sense. Maybe not, I might
>overlook some SQL Server configuration issues here.
>Thanks for any hints.
>Patrick Mzard|||> The following articles can provide a couple of different
> methods as well as more information on creating DSNs
> programmatically:
> HOWTO: Create and Remove a DSN in Visual Basic
> http://support.microsoft.com/defaul...b;EN-US;q171146
> HOWTO: Programmatically Create a DSN for SQL Server with VB
> http://support.microsoft.com/defaul...b;EN-US;q184608
> HOWTO: Set Up ODBC Data Sources When Distributing Apps
> http://support.microsoft.com/defaul...kb;EN-US;123008
> -Sue
Thank you for the links but I have already read these articles and they fall
in the section 4 category described in my original post: API calls (via VB)
which do not provide more options than the basic registry tricks. Or maybe I
really missed something, could you configure the SQL Server network library
to use with these methods ?
Patrick Mzard|||Yes - It's one of the driver attributes you can supply, e.g.
in the first example, you can add the following to the
strAttributes:
Network=DBMSSOCN (tcp/ip)
or
Network=DBNMPNTW (named pipes)
You have to set the address accordingly.
-Sue
On Tue, 2 Mar 2004 06:51:46 +0100, "Patrick.Mzard"
<patrick.mezard@.ifrance.fr> wrote:
>Thank you for the links but I have already read these articles and they fal
l
>in the section 4 category described in my original post: API calls (via VB)
>which do not provide more options than the basic registry tricks. Or maybe
I
>really missed something, could you configure the SQL Server network library
>to use with these methods ?
>Patrick Mzard
>|||Sue Hoegemeier wrote:
> Yes - It's one of the driver attributes you can supply, e.g.
> in the first example, you can add the following to the
> strAttributes:
> Network=DBMSSOCN (tcp/ip)
> or
> Network=DBNMPNTW (named pipes)
> You have to set the address accordingly.
Great I finally got it.
Here is the solution (maybe it will help someone who knows...).
I was unaware that all the ODBC administrator options were actually DSN
connection string parameters. I found the SQL Server parameter list and
description in the SQL Server documentation (by looking for the network
string you gave me and stumbling onto the "SQLConfigDataSource" function ).
Then I managed to get the odbcconf.exe thing to work (the use of pipes
'|' as separator instead of semi-colons is really misleading) and
register a data source with it. I do not know if DSN can be removed via
odbcconf, but anyway I can rewrite a simple call to SQLConfigDataSource
with the correct options if needed.
Thank you very much for your help, I would have never found it, had not
you insisted your solution was a valid one.
Patrick Mzard|||Your quite welcome...and thanks for posting back your
results.
-Sue
On Tue, 02 Mar 2004 18:30:16 +0100, Patrick Mzard
<patrick.mezard@.ifrance.com> wrote:
>Sue Hoegemeier wrote:
>Great I finally got it.
>Here is the solution (maybe it will help someone who knows...).
>I was unaware that all the ODBC administrator options were actually DSN
>connection string parameters. I found the SQL Server parameter list and
>description in the SQL Server documentation (by looking for the network
>string you gave me and stumbling onto the "SQLConfigDataSource" function ).
>Then I managed to get the odbcconf.exe thing to work (the use of pipes
>'|' as separator instead of semi-colons is really misleading) and
>register a data source with it. I do not know if DSN can be removed via
>odbcconf, but anyway I can rewrite a simple call to SQLConfigDataSource
>with the correct options if needed.
>Thank you very much for your help, I would have never found it, had not
>you insisted your solution was a valid one.
>Patrick Mzard
Monday, March 19, 2012
Crazy Row Numbering Poblem
I'm using ms sql 2000.
First here is a sample of my XML input:
- <scan ID="18.0" Section="System Restore">
- <scanattributes>
<scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
Time">5/4/2006 11:42 AM</scanattribute>
<scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
Time">5/4/2006 11:48 AM</scanattribute>
<scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
Time">5/4/2006 12:05 PM</scanattribute>
<scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
Time">5/4/2006 12:06 PM</scanattribute>
<scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
Time">5/4/2006 12:15 PM</scanattribute>
<scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
Time">5/4/2006 12:36 PM</scanattribute>
<scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
Time">5/4/2006 12:57 PM</scanattribute>
<scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
Time">5/4/2006 12:59 PM</scanattribute>
<scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
Time">5/4/2006 1:04 PM</scanattribute>
<scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
Time">5/4/2006 1:11 PM</scanattribute>
</scanattributes>
</scan>
Here is what I have so far:
declare @.iTree int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
*/
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
INSERT INTO #dup
SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
The Results are as follows for tblTest3:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
As you can see I am recording each repeating instace of the second
column, however when the fourth column has a repeating value the method
does not work, this is what I want it to look like:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
1 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
9 152 18 Installed ClientScanServiceSetup
2 152 18 Removed ClientScanServiceSetup
8 152 18 Installed ClientScanServiceSetup
3 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
7 152 18 Removed ClientScanServiceSetup
6 152 18 Installed ClientScanServiceSetup
4 152 18 Removed ClientScanServiceSetup
IF anyone can help me out with this I would appreciate it greatly.I forgot to include the other tbl where I get the attid from. so here
it is.
151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0|||It's not your main question, but I thought I'd point out that
the ordering of datetimes is probably not what you want:
1:04 PM
1:11 PM
11:42 AM
11:48 AM
...
If you have more than one year, it will get worse.
Steve Kass
Drew University
rhaazy wrote:
>I'm using ms sql 2000.
>First here is a sample of my XML input:
>- <scan ID="18.0" Section="System Restore">
>- <scanattributes>
> <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
>Time">5/4/2006 11:42 AM</scanattribute>
> <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
>Time">5/4/2006 11:48 AM</scanattribute>
> <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
>Time">5/4/2006 12:05 PM</scanattribute>
> <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
>Time">5/4/2006 12:06 PM</scanattribute>
> <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
>Time">5/4/2006 12:15 PM</scanattribute>
> <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
>Time">5/4/2006 12:36 PM</scanattribute>
> <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
>Time">5/4/2006 12:57 PM</scanattribute>
> <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
>Time">5/4/2006 12:59 PM</scanattribute>
> <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
>Time">5/4/2006 1:04 PM</scanattribute>
> <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
>Time">5/4/2006 1:11 PM</scanattribute>
> </scanattributes>
> </scan>
>Here is what I have so far:
>declare @.iTree int
>create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
>nvarchar(50), scanattribute nvarchar(50))
>create table #dup (attid nvarchar(50), name nvarchar (50), ID
>nvarchar(50))
>EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
>*/
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
>'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> INSERT INTO #dup
> SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
>tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
>tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
>tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
>The Results are as follows for tblTest3:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
>As you can see I am recording each repeating instace of the second
>column, however when the fourth column has a repeating value the method
>does not work, this is what I want it to look like:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 1 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 9 152 18 Installed ClientScanServiceSetup
> 2 152 18 Removed ClientScanServiceSetup
> 8 152 18 Installed ClientScanServiceSetup
> 3 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 7 152 18 Removed ClientScanServiceSetup
> 6 152 18 Installed ClientScanServiceSetup
> 4 152 18 Removed ClientScanServiceSetup
>IF anyone can help me out with this I would appreciate it greatly.
>
>|||rhaazy,
we need another column to break the ties. Try adding an identity column to
the temporary table #temp.
create table #temp (
[ID] nvarchar(50),
ParentID nvarchar(50),
[Name] nvarchar(50),
scanattribute nvarchar(50),
pk int not null identity unique -- new column
)
INSERT INTO #temp ([ID], ParentID, [Name], scanattribute)
SELECT * FROM openxml(...
...
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk < #temp)
)
),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM
tblScanAttribute, #temp
WHERE
#temp.ID like '18.%'
AND tblScanAttribute.Name = #temp.Name
AND tblScanAttribute.ScanSectionID like '18'
AMB
"rhaazy" wrote:
> I forgot to include the other tbl where I get the attid from. so here
> it is.
> 151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
> 152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
>|||NSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk <
#temp)
)
),
Does this imply I also need to add the pk column to the #dup table?|||You should be able to use your ID column to resolve duplicates.
Try changing
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'
to
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'|||rhaazy,
Sure, but here it will not have identity property. I will be populated from
#temp.
AMB
"rhaazy" wrote:
> NSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT
> instance = (
> select count(*)
> from #dup
> where
> #dup.attid = tblScanAttribute.ScanAttributeID
> AND
> (
> #dup.[name] <= #temp.scanattribute
> or
> (#dup.[name] = #temp.scanattribute and #dup.puk <
> #temp)
> )
> ),
> Does this imply I also need to add the pk column to the #dup table?
>|||I don't know who you are or where you come from but you are the
greatest person who ever lived... I have spent all w
trying to
figure this out, I knew it needed an 'or' clause in there but I
couldn't quite get it right. I am an intern and I've only been
database programming for 2 ws and was pretty pleased I got as far as
I did. I just wanted to tell you how thankful I am for you helping me.
The fact you were able to make sense of all the crazy stuff I gave and
crank out exactly what I needed is truely amazing to me.
Thanks again.|||Good catch!!!
"markc600@.hotmail.com" wrote:
> You should be able to use your ID column to resolve duplicates.
> Try changing
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
> to
>
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND ((#dup.name<#temp.scanattribute)
> or (#dup.name=#temp.scanattribute) and
> (#dup.ID<=#temp.ID))),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID,
> #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
First here is a sample of my XML input:
- <scan ID="18.0" Section="System Restore">
- <scanattributes>
<scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
Time">5/4/2006 11:42 AM</scanattribute>
<scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
Time">5/4/2006 11:48 AM</scanattribute>
<scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
Time">5/4/2006 12:05 PM</scanattribute>
<scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
Time">5/4/2006 12:06 PM</scanattribute>
<scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
Time">5/4/2006 12:15 PM</scanattribute>
<scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
Time">5/4/2006 12:36 PM</scanattribute>
<scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
Time">5/4/2006 12:57 PM</scanattribute>
<scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
Time">5/4/2006 12:59 PM</scanattribute>
<scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
Time">5/4/2006 1:04 PM</scanattribute>
<scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
Time">5/4/2006 1:11 PM</scanattribute>
</scanattributes>
</scan>
Here is what I have so far:
declare @.iTree int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
*/
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
INSERT INTO #dup
SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
The Results are as follows for tblTest3:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
As you can see I am recording each repeating instace of the second
column, however when the fourth column has a repeating value the method
does not work, this is what I want it to look like:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
1 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
9 152 18 Installed ClientScanServiceSetup
2 152 18 Removed ClientScanServiceSetup
8 152 18 Installed ClientScanServiceSetup
3 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
7 152 18 Removed ClientScanServiceSetup
6 152 18 Installed ClientScanServiceSetup
4 152 18 Removed ClientScanServiceSetup
IF anyone can help me out with this I would appreciate it greatly.I forgot to include the other tbl where I get the attid from. so here
it is.
151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0|||It's not your main question, but I thought I'd point out that
the ordering of datetimes is probably not what you want:
1:04 PM
1:11 PM
11:42 AM
11:48 AM
...
If you have more than one year, it will get worse.
Steve Kass
Drew University
rhaazy wrote:
>I'm using ms sql 2000.
>First here is a sample of my XML input:
>- <scan ID="18.0" Section="System Restore">
>- <scanattributes>
> <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
>Time">5/4/2006 11:42 AM</scanattribute>
> <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
>Time">5/4/2006 11:48 AM</scanattribute>
> <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
>Time">5/4/2006 12:05 PM</scanattribute>
> <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
>Time">5/4/2006 12:06 PM</scanattribute>
> <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
>Time">5/4/2006 12:15 PM</scanattribute>
> <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
>Time">5/4/2006 12:36 PM</scanattribute>
> <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
>Time">5/4/2006 12:57 PM</scanattribute>
> <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
>Time">5/4/2006 12:59 PM</scanattribute>
> <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
>Time">5/4/2006 1:04 PM</scanattribute>
> <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
>Time">5/4/2006 1:11 PM</scanattribute>
> </scanattributes>
> </scan>
>Here is what I have so far:
>declare @.iTree int
>create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
>nvarchar(50), scanattribute nvarchar(50))
>create table #dup (attid nvarchar(50), name nvarchar (50), ID
>nvarchar(50))
>EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
>*/
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
>'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> INSERT INTO #dup
> SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
>tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
>tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
>tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
>The Results are as follows for tblTest3:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
>As you can see I am recording each repeating instace of the second
>column, however when the fourth column has a repeating value the method
>does not work, this is what I want it to look like:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 1 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 9 152 18 Installed ClientScanServiceSetup
> 2 152 18 Removed ClientScanServiceSetup
> 8 152 18 Installed ClientScanServiceSetup
> 3 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 7 152 18 Removed ClientScanServiceSetup
> 6 152 18 Installed ClientScanServiceSetup
> 4 152 18 Removed ClientScanServiceSetup
>IF anyone can help me out with this I would appreciate it greatly.
>
>|||rhaazy,
we need another column to break the ties. Try adding an identity column to
the temporary table #temp.
create table #temp (
[ID] nvarchar(50),
ParentID nvarchar(50),
[Name] nvarchar(50),
scanattribute nvarchar(50),
pk int not null identity unique -- new column
)
INSERT INTO #temp ([ID], ParentID, [Name], scanattribute)
SELECT * FROM openxml(...
...
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk < #temp)
)
),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM
tblScanAttribute, #temp
WHERE
#temp.ID like '18.%'
AND tblScanAttribute.Name = #temp.Name
AND tblScanAttribute.ScanSectionID like '18'
AMB
"rhaazy" wrote:
> I forgot to include the other tbl where I get the attid from. so here
> it is.
> 151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
> 152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
>|||NSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk <
#temp)
)
),
Does this imply I also need to add the pk column to the #dup table?|||You should be able to use your ID column to resolve duplicates.
Try changing
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'
to
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'|||rhaazy,
Sure, but here it will not have identity property. I will be populated from
#temp.
AMB
"rhaazy" wrote:
> NSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT
> instance = (
> select count(*)
> from #dup
> where
> #dup.attid = tblScanAttribute.ScanAttributeID
> AND
> (
> #dup.[name] <= #temp.scanattribute
> or
> (#dup.[name] = #temp.scanattribute and #dup.puk <
> #temp)
> )
> ),
> Does this imply I also need to add the pk column to the #dup table?
>|||I don't know who you are or where you come from but you are the
greatest person who ever lived... I have spent all w
trying tofigure this out, I knew it needed an 'or' clause in there but I
couldn't quite get it right. I am an intern and I've only been
database programming for 2 w
s and was pretty pleased I got as far asI did. I just wanted to tell you how thankful I am for you helping me.
The fact you were able to make sense of all the crazy stuff I gave and
crank out exactly what I needed is truely amazing to me.
Thanks again.|||Good catch!!!
"markc600@.hotmail.com" wrote:
> You should be able to use your ID column to resolve duplicates.
> Try changing
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
> to
>
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND ((#dup.name<#temp.scanattribute)
> or (#dup.name=#temp.scanattribute) and
> (#dup.ID<=#temp.ID))),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID,
> #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
Sunday, February 19, 2012
country city area ..... help
hi all :
i am working in HR system , the user table is linked to the counter table and the city and the area tables . i want to fill this tables with all the country and cities and its area all over the world , i think that i could find such data on the internet , can any one help me how to find this tables and the data . beside the nationality table .... any help please
I am not sure you will get it on single location. May be wikipedia help you.. http://en.wikipedia.org/wiki/List_of_countries|||And...
http://en.wikipedia.org/wiki/List_of_cities
Friday, February 17, 2012
counting records in all non system database table
is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?
Try this:
EXECsp_msforeachtable'sp_spaceused "?"'
|||
You can use the TSQL below to get this information and some others. The results is similar to SP_SPACEUSED.
SELECT object_name(id) AS name,
rowcnt AS rows,
reserved * 8 AS reserved_kb,
dpages * 8 AS data_kb,
(sum(used) * 8) - (dpages * 8) AS index_size_kb,
(sum(reserved) * 8) - (sum(used) * 8) AS unused_kb
FROM sysindexes
WHERE indid IN (0,1) -- cluster e n?o cluster
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
GROUP BY id, rowcnt, reserved, dpages
ORDER BY rowcnt DESC
Hi,
Try the following procedure
Code Snippet
ALTER procedure USP_CountAllTables
AS
DECLARE @.TblName Nvarchar(255)
DECLARE @.SQL nvarchar(1000)
DECLARE CntCursor CURSOR FOR
select Name from sysobjects where xtype='U'
Set @.SQL=NULL
create table #Temp
(tableName nvarchar(255),
Record_Count int
)
OPEN CntCursor
FETCH NEXT FROM CntCursor
into @.TblName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL='Select ''' + @.TblName + ''',count(*) from '+ @.TblName
insert #Temp
execute sp_Executesql @.SQL
FETCH NEXT FROM CntCursor
into @.TblName
END
CLOSE CntCursor
DEALLOCATE CntCursor
select * from #Temp
Drop table #Temp
This will give u the record count for all the user tables.
But its gonna take some time so have patience.
RegarDs,
Jacx
counting problem...
Hi. I have a small problem that i just can't seem to figure out. I'm trying to generate a report for a case management system. The problem I am having is trying to exclude some of these results. Here is my query:
SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55
AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9
This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.
now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )|||I hope I don't show up in that result set :)|||select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )
thank you! that works perfectly!
SELECT COUNT(DefendantCase.ProsAtty) AS CountOfProsAtty
FROM DefendantCase LEFT JOIN DefendantEventPros ON DefendantCase.VBKey=DefendantEventPros.VBKey
WHERE DefendantCase.StatusID=17 AND DefendantCase.ProsAtty=55
AND DefendantEventPros.EventDate BETWEEN DATEADD(DAY,-60,GETDATE()) AND GETDATE() AND DefendantEventPros.EventID=9
This query is trying to find the total amount of cases where the statusid=17, the prosatty=55, the date is between today and 60 days ago, and there is an eventid=9.
now, i'm not getting errors in the query itself; it's just that it's inflating the total number. If a case has more than one eventid=9, it will include that extra in the results. I do not want to include those in the results. Does anyone have any suggestions? Thanks!select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )|||I hope I don't show up in that result set :)|||select count(ProsAtty) AS CountOfProsAtty
from DefendantCase
where StatusID=17
and ProsAtty=55
and EventID=9
and exists
( select 937
from DefendantEventPros
where VBKey = DefendantCase.VBKey
and EventDate
between dateadd(day,-60,getdate())
and getdate() )
thank you! that works perfectly!
Subscribe to:
Posts (Atom)