Hi all,
I have posted a thread on the XML forum but its not getting much traction there so I'm posting a link to it from here hoping that more people will pick it up. Hope that's OK.
I have a problem with a T-SQL query involving XML that is taking FAR too long to run.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2404607&SiteID=1&mode=1
-Jamie
I posted the following back in May and received the reply below.
I asked again in July, but got no response so I assume that wasn't the way to handle it ...
So -- here is the RePost & Reply.
Question is still open: Is the CD ready yet ?/
Roger
===========================================
Can I get: SQL Express with Advanced Services on a CD (or DVD)
I'm on DialUp so a download would take 24hr+.
I was able to get the MS .NET Framework 2.0 on DVD because an MVP took pity on me and was kind enough to give me the "Secret" URL to the correct Order Desk.
I'm hoping one of you knows the URL for this CD/DVD
Roger
=========================================
Hi Roger,
We're working on CD images for SQL Server Express with Advanced Services in all the languages that SQL Server usually ships in. The English version is in final testing and should be available in a couple of weeks.
Thanks
Lead Program Manager, Microsoft SQL Server Storage Engine
Here’s a more in depth breakdown of my problem:
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is, send that entry along with the result set, if it is not valid, Check that the dongle area code is valid, if dongle area is valid select with the result set, and if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lnkPBXUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lnkPBXUser]
GO
DDL:
CREATE TABLE [dbo].[lnkPBXUser] (
[RegionalID] [int] NOT NULL ,
[pbxID] [decimal](18, 0) NOT NULL ,
[userID] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO
Sample data:
INSERT INTO lnkPBXUser(RegionalID, pbxID, userID)
SELECT 0, 543, 2 UNION ALL
SELECT 0, 10961, 6 UNION ALL
SELECT 0, 1012, 17 UNION ALL
SELECT 0, 16499, 26 UNION ALL
SELECT 0, 14061, 36 UNION ALL
SELECT 0, 16499, 2
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXID DailupDT DongleAccessNum CLI RegionalID RegionalDialup
83 8/8/2006 8:58:11 AM T2 UQ 28924 0132493700 0 0800003554
543 8/8/2006 8:55:44 AM T0 UA 33902 0123623500 0 0800003554
1219 8/8/2006 8:59:03 AM T3 ZD 02031 0152958095 0 0800003554
1012 8/8/2006 9:02:54 AM T0 UA 41261 0173011050 0 0800003554
1331 8/8/2006 8:59:57 AM T0 UA 01938 0124604627 0 0800003554
1979 8/8/2006 9:02:52 AM T0 UA 09836 0163751210 0 0800003554
1903 8/8/2006 8:58:41 AM T0 UA 26009 0147175356 0 0800003554
1522 8/8/2006 8:58:54 AM T3 MB 94595 0573912871 0 0800004249
319 8/8/2006 8:51:28 AM T2 ZD 32892 0543375100 0 0800004249
3270 8/8/2006 9:04:26 AM T2 MB 87331 0 0800004249
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDialupLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDialupLog]
GO
CREATE TABLE [dbo].[tblDialupLog] (
[RegionalID] [int] NOT NULL ,
[PBXID] [int] NULL ,
[DialupDT] [datetime] NULL ,
[DongleAccessNum] [varchar] (64) NULL ,
[CLI] [varchar] (64) NULL ,
[RegionalDialup] [varchar] (50) NULL
) ON [PRIMARY]
GO
Sample data:
INSERT INTO tblDialupLog(PBXID,DailupDT ,DongleAccessNum,CLI,RegionalID,RegionalDialup )
SELECT 83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554' UNION ALL
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 1012, '8/8/2006 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800003554' UNION ALL
SELECT 16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'
You see that the DongleAccessNumber is actually made up of three parts, and it is the middle part (ie.UQ) which i will use to check that the tbldongleArea.DongleAreaCode is valid for that region
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeID RegionalID ExtName SubsNDCD LocCD UpdateStatus RegionDesc
7973 1 PRETORIA 012 362 0 NORTH EASTERN REGION
7974 1 HARTEBEESHOEK 012 3012 0 NORTH EASTERN REGION
7975 1 HARTEBEESHOEK 012 3013 0 NORTH EASTERN REGION
7976 1 PRETORIA 017 3014 0 NORTH EASTERN REGION
7977 1 PRETORIA 012 3015 0 NORTH EASTERN REGION
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCodes]
GO
CREATE TABLE [dbo].[tblCodes] (
[CodeID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[ExtName] [varchar] (64) NULL ,
[SubsNDCD] [varchar] (10) NULL ,
[LocCD] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionDesc] [varchar] (255) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO tblCodes(CodeID ,RegionalID ,ExtName , SubsNDCD ,LocCD ,UpdateStatus,RegionDesc)
SELECT 7973,1, 'PRETORIA', '012', '362', 0 ,'NORTH EASTERN REGION' UNION ALL
SELECT 7974,1, 'HARTEBEESHOEK ', '012', '3012', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7975,1, 'HARTEBEESHOEK ', '012', '3013', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7976,1, 'PRETORIA', '012', '3014', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7977,1, 'PRETORIA', '017', '3015', 0,'NORTH EASTERN REGION'
I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
AreaID RegionalID DongleAreaCode AreaDesc UpdateStatus
12 1 UA Oumashoop 0
13 1 UB Pietersburg 0
14 1 UC Warmbad 0 1
15 1 UD Nylstroom 0
16 1 UE Potgietersrus 0
27 1 UF Louis Trichardt 0
28 1 UG Messina 0
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDongleArea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDongleArea]
GO
CREATE TABLE [dbo].[tblDongleArea] (
[AreaID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[DongleAreaCode] [varchar] (5) NULL ,
[AreaDesc] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO tblDongleArea(AreaID,RegionalID,DongleAreaCode,AreaDesc,UpdateStatus)
SELECT 12,1, 'UA', Oumashoop, 0 UNION ALL
SELECT 13, 1, 'UB', 'Pietersburg', 0 UNION ALL
SELECT 14, 1 ,'UC' ,'Warmbad', 0 UNION ALL
SELECT 15, 1, 'UD', 'Nylstroom', 0 UNION ALL
SELECT 16, 1, 'UE', 'Potgietersrus', 0 UNION ALL
SELECT 27, 1, 'UF', 'Louis Trichardt', 0 UNION ALL
SELECT 28, 1, 'UG', 'Messina', 0
I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:
RegionalID RegionalDialup Region UpdateStatus RegionCode LocalRegion
1 0800003554 North Eastern 0 1 0
2 0800005027 Gauteng 0 2 0
3 0800006194 Eastern 0 3 0
4 0800004249 Central 0 4 0
5 0800201859 Southern 0 5 0
6 0800201989 Western 0 6 0
7 0800113515 HO 1 0 1
8 0800222204 Tellumat 0 7 0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegionNumbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegionNumbers]
GO
CREATE TABLE [dbo].[tblRegionNumbers] (
[RegionalID] [int] NOT NULL ,
[RegionalDialup] [varchar] (255) NULL ,
[Region] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionCode] [int] NULL ,
[LocalRegion] [int] NULL ,
) ON [PRIMARY]
GO
INSERT INTO tblRegionNumbers(RegionalID ,RegionalDialup,Region,UpdateStatus,RegionCode,LocalRegion)
SELECT 1,'0800003554', 'North Eastern', 0, 1, 0 UNION ALL
SELECT 2, '0800005027' ,'Gauteng', 0 ,2, 0 UNION ALL
SELECT 3, '0800006194','Eastern', 0, 3, 0 UNION ALL
SELECT 4, '0800004249' ,'Central', 0, 4, 0 UNION ALL
SELECT 5, '0800201859','Southern', 0 ,5, 0 UNION ALL
SELECT 6, '0800201989' ,'Western' 0, 6, 0 UNION ALL
SELECT 0, '0800113515', 'HO', 1, 0, 1 UNION ALL
SELECT 8, '0800222204', 'Tellumat', 0, 7, 0
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID
0 1012 17
0 543 2
Please assist, it would be greatly appreciated.
Regards
SQLJunior
Howdy,Does anyone know of any packages that you can create a form and via aODBC connection save the data to a table ?If it is possible to compile the form as I don't want clients to changethe forms ?Any idea/thoughts would be most welcome ?RegardsAndrew
View 4 Replies View RelatedHi,
I have 3 SQL Servers running here. Here are their configurations:
SERVER 1:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4
SERVER 2:
OS: Windows 2000 SP 4
SQL: SQL Server 2000 SP 4
SERVER 3:
OS: Windows XP Professionnal
SQL: SQL Server 2000 SP 3
Now, as you can see, SERVER 1 and SERVER 2 have identical configurations. Plus, both will accept Windows Authentication and SQL Authentication.
If I try to add a new Registration from SERVER 3 to SERVER 1, it works fine but from SERVER 3 to SERVER 2 it doesn't. I always get an error (SQL Server does not exist or access denied). But, using the exact same user name and password, a connection can be established from SERVER 1 to SERVER 2 and vice-versa. Only when trying to connect froms SERVER 3 to SERVER 2 fails.
Any ideas?
Thanks,
Skip.
Originally posted by Jeremy at 12/10/2001 11:39:38 AM
Hello all,
I've written a simple dts job that uses oracle (8.x) as a source and oracle (8.x) as a destination. I'm using SQL 2000 and Microsoft's oledb provider for oracle as the two connections. I've chosen "Transform Data Task" with the following SQL "SELECT * FROM REPORTER_STATUS
WHERE LASTOCCURRENCE > TRUNC(SYSDATE)". As you can see, it's very simple, however it's very very very slow. (averages about 1000 rows per minute). In my column transformations, I've selected many to many versus the one to one. There are no activex scripts or anything along those lines. Just a simple push of the data from one oracle box to the other. The table schemas are identical etc... I've had this problem before with writing to Oracle and I can't imagine that it's really supposed to be this slow. If you need more details, please just let me know.
Thank you,
Jeremy
-----------------------------More -------------------------------
The official response from microsoft is that dts only allows for single inserts... not bulk or bcp for oracle. There must be someone out there who has figured out how to configure / modify / call (something) from a dts pacakage to insert millions of records into Oracle in a decent time frame...
thnx again..
I posted this under 'microsoft.public.sqlserver.client' but got no reply.Any help with this problem would be greatly appreciated---------------------I developed a database under SQL Server 2000, with Access 2000 on Windows2000 as client. This had been running fine for several years. The client isnow upgrading to Windows XP, and has come across a display problem on thereports. The figures are correct but are now displayed with lots of trailingzeros e.g365.00000I have built a test system but cannot duplicate the problem :-( . Also theproblem varies between computers, and even between different users on thesame computer. One solution that seems to work is to explicitly cast alloutput from the server e.g.CAST ( TonsLoaded AS INT) TonsLoadedbut this is a pita since there are 141 stored procedures.Has anyone seen this problem? I haven't found anything in the newsgroups orany Knowlege Base article. It looks like it is to do with Windows XP as theclient ...David
View 5 Replies View Related
I am looking for a way to truncate raw files without losing the metadata. The metadata of the raw file should be automatically detected at run-time. The result will be a raw file with the same metadata as the original file, but no data in it.
There are two reasons I would like such a tool. First, I want to erase the potentially sensitive data stored in the raw files. Second, I want to keep Validation enabled so that development is simpler.
Is anyone aware of such a tool?
Thanks,
Here’s a more in depth breakdown of my problem:
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
RegionalID pbxID userID
0 1012 17
0 543 2
0 10961 6
0 16499 26
0 14061 36
0 15882 2
4 15101 6
4 15101 26
6 16499 2
6 16012 26
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXIDDailupDT DongleAccessNum CLI RegionalID RegionalDialup
838/8/2006 8:58:11 AM T2 UQ 28924 013249370000800003554
5438/8/2006 8:55:44 AM T0 UA 33902 012362350000800003554
12198/8/2006 8:59:03 AM T3 ZD 02031 015295809500800003554
10128/8/2006 9:02:54 AM T0 UA 41261 017301105000800003554
13318/8/2006 8:59:57 AM T0 UA 01938 012460462700800003554
19798/8/2006 9:02:52 AM T0 UA 09836 016375121000800003554
19038/8/2006 8:58:41 AM T0 UA 26009 014717535600800003554
15228/8/2006 8:58:54 AM T3 MB 94595 057391287100800004249
3198/8/2006 8:51:28 AM T2 ZD 32892 054337510000800004249
32708/8/2006 9:04:26 AM T2 MB 8733100800004249
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeIDRegionalID ExtName SubsNDCDLocCDUpdateStatusRegionDesc
79731 PRETORIA 0123620NORTH EASTERN REGION
79741 HARTEBEESHOEK 012 30120NORTH EASTERN REGION
79751 HARTEBEESHOEK 01230130NORTH EASTERN REGION
79761 PRETORIA 01730140NORTH EASTERN REGION
79771 PRETORIA 01230150NORTH EASTERN REGION
I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
AreaIDRegionalIDDongleAreaCodeAreaDescUpdateStatus
121UAOumashoop0
131UBPietersburg0
141UCWarmbad01
151UDNylstroom0
161UEPotgietersrus0
271UFLouis Trichardt0
281UGMessina0
291UHEllisras0
301UIThabazimbi0
311UJPhalaborwa0
321UKTzaneen0
331UTStanderton0
341UMMeyerton0
351UNNelspruit0
361UOWitrivier0
371UPLydenburg0
381UQMiddelburg0
391URWitbank0
401USBronkhorstspruit0
461UZOlifantsfontein0
I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:
RegionalID RegionalDialupRegionUpdateStatusRegionCodeLocalRegion
10800003554North Eastern010
20800005027Gauteng020
30800006194Eastern030
40800004249Central040
50800201859Southern050
60800201989Western060
70800113515HO101
80800222204Tellumat070
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID
0 1012 17
0 543 2
Please assist, it would be greatly appreciated.
Regards
SQLJunior
Is anyone using sql server 70 or 2000 and a EMC or LSI Metastore SAN? If so, I would like to know how things are going for you. We are currently evaluating both of these products and will be making a decision as to which we will purchase in February. Please advise if you have anything positive or negative to offer.
Thanks.
Gail Wade
gwade@it.rjf.com
It looks like I'll be assigned with development and database management utilizing Microsoft Office Sharepoint Services and saw that there are some issues as it can become quite unwieldy.
I was wondering what difficulties to expect from MOSS and/or Sharepoint in terms of managing and backing up. Any tips or suggestions?
Hi!
I have 1 sql server 6.5 sp3 with wrong sortorder, is there anyone who can give some feedback of this job.
- How to?
- What issues should i have in mind?
Thanks in advance
Fredrik
Does anyone have any experience detecting and repairing torn pages? Does Optimization repair Torn Pages ? Any help or resources would be much appriciated.
Late
Matt
Looking for anyone that has had any experience with using SQL Merge replication while mirroring the publisher database. Thinking about doing this as a recovery plan in the event of a publisher failure.
Any advice would be appreciated.
Thank you!
I have SQL Server 2000 database involving voluminous data spread across different locations in a state. The database involves lot of transactions. The database involves tables storing photo images and thumb impressions along with the textual data and other needed information in a single table. I have a application accessing and updating about 5 tables and reading most of the master tables. I need a strategy to design the database such that performance improves. Once the photo and thumb are inserted for a particular party it is not required for retrieval immediately at any circumstance. In this scenario how should I design the database and keep data volume in check. Can I archive photo and thumb impressions in CDs. Will it be reliable and feasible. Please clarify on this issue. Please share your valuable experience. Only insertions involved in the partyinfo table having Photo and Thumb impressions. Later the table is only used to fetch the data of party other than Photo and thumb.
View 3 Replies View RelatedI am currently at build 9.00.3054.00 and I'm having some strange
performance problems.. inserts hanging etc.....
Has anyone applied build 3175 or 3215 with good results....
any input is appreciated!
IN ONE OF THE ADVERTISEMENT I SAW, IT SAYS "WE NEED PRODUCTION SUPPORT EXPERIENCE". AND MINIMUM CRITERIA IS, HE/SHE SHOULD BE A SQL SERVER DBA.
My Question is, what do we mean by "PRODUCTION SUPPORT IN SQL SERVER".
Can anyone give a good explantion on that, please?. Thanks in advance.
Srinivasan.
I had a nerve-wracking experience with xp_cmdshell today.
In SSMS, I ran a command to "cd" to a directory, then "rename" some files in that directory. I made the ignorant mistake of running the commands separately - NEVER NEVER DO THIS.
What happened was, by the time I ran the "rename" command it defaulted to the "system32" directory and started renaming files there instead... talk about !!$#*@(!-up!
Has any ever done this, or heard about this horrible experience before? I am trying to document cases of this, so that I don't look like the complete idiot that I am.
Hello all, I was just awarded the job of maintaing the database serverfor our company. I have basically ZERO experience using MS SQL Server2000. Can anyone point me in the direction of a good resource forcreating backups of our database? I would love something that comeswith a gui that really simplifies the process; seeing as how i havenever even opened the MS SQL program.Our database is fairly small we have 7 users with access to thedatabase. That is it.any advice or good resources would be greatly appreciated.
View 2 Replies View Relatedhi i am new to mssql db stuff. I just developed my first VB.NET application and it uses a mssql database which is hosted locally on my development machine. I built the deployment msi file for my application which can be installed easily on client machine, but have no idea how to move the mssql db with it.
View 1 Replies View RelatedI am using SQL 2000 running a couple of large (up to 70GB) databases. I am currently backing up to disk, which is working fine.
I now want to backup the databases using the Snapshot feature of my storage array. To do this however, I first need to put my databases (3 of them) into 'backup mode', so as to stop transactions being written to the database, and provide a consistent database to backup.
Has anyone had any experience with this feature? Microsoft provide a sample C++ program called snaphot.cpp that puts a single database into 'backup mode'. I am not particularly clued up on C++. Can multiple databases be put into this mode simultaneously? Is this particularly difficult to do?
Any help would be greatly appreciated
hi to all , well i succesfully installed SQL server 2005 express edition in windows 2000 however when i am trying to connect my program thru ODBC im having a difficulty to connect. but based on my observation if i am not connected to the internet i cannot connect to my database but if i am connected to the internet i could access the SQL server 2005 express edition.i have read the hardware requirements for SQL server 2005 express edition i have upgraded the IE5 into IE6 SP1,windows 2000 SP4, even my memory to 1GB.but still i have the problem.
my question is, do i have to maintain my connection to the internet for me to have a smooth SQL connection?
BTW, i badly need the answer.Bigthanks!
-gae-