Repost Along With DDL's And Sample Data.

Mar 6, 2007

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

View 20 Replies


ADVERTISEMENT

Repost!: Extract Data Meeting Specific Criteria.

Mar 5, 2007

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

View 7 Replies View Related

Sample Code - Custom Increment Task Sample

Mar 28, 2006

Hi

Books online mention the existence of sample code for several custom tasks, including the one mentioned in the title. But, when I try to find this code in the location mentioned it is nowhere to be found.

I have run a search on the rest of my drive and come up empty.

Can anyone tell me where to find this?

Thanks

View 3 Replies View Related

Are There Any Sample VB Projects That Use A Sample Sql Server Express DB?

Feb 29, 2008

Im trying to use VB.net 2005 to write a sample app to access a DB. Are there any samples for this and any samples of how I go about making the DB in the first place?

View 1 Replies View Related

CD -- RePost

Aug 4, 2006

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

View 1 Replies View Related

T-SQL XML Experience??? (repost)

Nov 14, 2007



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

View 1 Replies View Related

Repost : Using Forms With Sql Server

Jul 23, 2005

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 Related

Sample Data Required

Feb 13, 2007

Hello.

Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y apologies if I should have posted elsewhere...


I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably everything under the sun :)

I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like follows:

Classification
Category
Sub Category
Product Group
Item


The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following hierarchy can be as follows (shown in a tabular manner)


Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...

Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...

Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..



I hope the above provides you with a clear understanding of my requirement.

I'll be extremely grateful for your help.

Thanks & Regards.

View 4 Replies View Related

Generate Sample Data

Sep 18, 2005

I am needing to o exactly what Zippygoose says (with ordered ID numbers). How do yo make an insert statement that will make a loop until let's say the ID reaches 8000??

Thanks in advanced,

Edit:
User needs to fill his tables with sample data.
(Sorry for the edit, but I prefer to split this thread instead of continuing an old one (2003!!)).

View 1 Replies View Related

Sample Data Required

Feb 13, 2007

Sample data required


Hello.

Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y

apologies if I should have posted elsewhere...


I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably

everything under the sun :)

I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like

follows:

Classification
Category
Sub Category
Product Group
Item


The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following

hierarchy can be as follows (shown in a tabular manner)


Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...

Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...

Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..



I hope the above provides you with a clear understanding of my requirement.

I'll be extremely grateful for your help.

Thanks & Regards.

View 3 Replies View Related

Sample Data Required

Feb 13, 2007

Hello.

Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y

apologies if I should have posted elsewhere...


I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably

everything under the sun :)

I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like

follows:

Classification
Category
Sub Category
Product Group
Item


The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following

hierarchy can be as follows (shown in a tabular manner)


Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...

Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...

Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..



I hope the above provides you with a clear understanding of my requirement.

I'll be extremely grateful for your help.

Thanks & Regards.


Thanks & Regards.

-J

View 4 Replies View Related

Here's The Problem Again With Sample Data

Jul 20, 2005

I want the sum of the last payments (amount) for all customers. The lastpayment is with one with most recent date. And if there are more than onepayment on the most recent date then the one with the higher paymentid isthe last payment. for example in the given data the insert statement thatstarts with capital I is the last payment of that customer. The correctanswer should be 2100 as given below. both queries by Erland and Anith givethe result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause fromboth queries since right now I want current sum (not till some date). Sowhat should be the right query.Thanks again for the help.create table payments (paymentid int,customerid int,amount int,date datetime)insert payments values (1, 1, 100, '1/1/03')insert payments values (2, 1, 200, '2/28/03')Insert payments values (3, 1, 500, '5/15/03')insert payments values (4, 2, 400, '1/16/03')insert payments values (9, 2, 800, '4/30/03')insert payments values (5, 2, 200, '6/15/03')Insert payments values (6, 2, 900, '6/15/03')insert payments values (7, 3, 700, '3/1/03')insert payments values (10,3, 300, '7/10/03')Insert payments values (8, 3, 600, '9/1/03')insert payments values (11,4, 300, '8/1/03')insert payments values (12,4, 400, '9/10/03')Insert payments values (13,4, 100, '9/10/03')customerid lastpayment amount1 3 (on 5/15/03) 5002 6 (on 6/15/03) 9003 8 (on 9/1/03) 6004 13 (on 9/10/03) 100========Result => 2100

View 4 Replies View Related

Sample Data For Mining

Jun 8, 2006

hi, i am bit new in the field of data warehousing and data mining but catching up fast. I am in the last year of BS(Computer Science) and intend to do a project on a Business Intelligence but i was wondering can i get a sample dummy data for my work is that any way possible??

View 1 Replies View Related

REPOST: New SQL Server Registration Failure

Feb 23, 2004

Hi,

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.

View 1 Replies View Related

Sample RFP For A Data Warehouse Project

Jun 26, 2003

Does anyone have a sample RFP for a Data Warehousing project?

My manager hired an outside consultant to draw up a proposal for our company. But it is getting stuck in details and we are way behind schedule.

It will help me greatly if there is an outline of a DW RFP.

View 4 Replies View Related

Adding Sample Data To New Database

Jun 11, 2008

I created a database using SSMS, now I want to add sample data.

What are the methods of adding sample data from the server?

Do I need to use the SQL Server client or can I use the SQL command line or query window?

Thank you,

Goldmember

View 1 Replies View Related

Creating A Script Out Of Sample Data

Jul 20, 2005

SQL Server 2000Is there any way to take sample data in my database and create an INSERT INTO script?I have a commercial application that I would like to include sample data, and instead of restoring a backup like I am doing now, I would like to first run a script that creates the database, stored procedures, etc, then run a script that inserts sample data if the customer so chooses.I know I can do this manually, but is there any way to create the script based on exisiting data?TIA-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com

View 4 Replies View Related

Sample Databases For Data Mining

May 9, 2007

Hello

I'm looking for sample database for data mining in SQL Server 2005. In my project I need to use all algorithms provided by SQL Server 2005. I know that we have "SQL Server 2005 Samples and Sample Databases" on microsoft.com site, but there are only AdventureWorks data sets. I need sth less popular, but similar.

I have found http://www.kdnuggets.com/datasets/index.html and others, but I don't see data sets good for multidimensional analysis.

The origin of data may be natural or artificial, it doesn't matter.
I don't have to generate my own data, because I had to discover new knowledge, that will be surprising to me.

Any help would be appreciated.
Thanks in advance.

(sorry for my English)

View 1 Replies View Related

Repost: DTS -- Insert Into Oracle Is SLOW. Any Tips?

Dec 16, 2001

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

View 2 Replies View Related

Repost: 00000 Display On Upgrade To Windows XP

Jul 20, 2005

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

Truncate All Raw Files In A Given Folder (Repost As Requested)

Mar 30, 2007



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,

View 4 Replies View Related

Sample Code To Importing Data From ODBC

Mar 26, 2007

Hi, there;
I try to import data from ODBC using C# programmatically. Is there any sample code we can have a look. Like how to read schema from ODBC source table and then create source column...

Thanks

View 3 Replies View Related

AdventureWorks Sample Database &&amp; Data Consistency

Oct 17, 2007

Hi,

I'm working with the sample database called Adventure Works and I'm encountering data consistency issues :
If I sum the TotalDue for one particular territory, for one particular time window from the Sales. SalesOrderHeader table and if I try to compare the result to the LastYearSales and YTDSales from the Sales.SalesTerritory table, I don't get the same number, whatever I try. So did I miss something ?
How are the LastYearSales and YTDSales fields from Sales.SalesTerritory calculated ?

Thx,

Anissa

View 3 Replies View Related

HELP-Create Sample Data By Microsofst Spoil And Grow My Mdf Ldf -from 5 MB TO 6 GB

May 21, 2008

help i run this
from this link
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable&referringTitle=Home
and it stuck my sql server!!!!!

and after i run this
my my mdf ldf is 6 GB




Code Snippet-- Suppress data loading messages
SET NOCOUNT ON

-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e


before i was 6 mb
help
now how to fix this problem and shrinking my data



View 8 Replies View Related

SP To Determine Layout, Population And Sample Data In Table(s)

Nov 1, 2007

Hi! I am new to SQL Server... looking for some veteran assistance.

"Data Integrity Report"

I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).

The cursor/report needs to have the following columns:



Ordinal_Position (I.E. Column number)
Column_Name
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)

Data_Type

Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):

Col Num Col Name # Blanks Difference Data Type Col Length Sample Data
1 Name 12 88 varchar 30 Sally Smith
2 Address 34 66 varchar 45 123 Main St Apt 45
3 Acct_ID 0 100 varchar 4 AB12345

Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).

Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.

Sorry for the long post, and thanks in advance!
John

View 1 Replies View Related

Conditional Joins? (table Structure And Sample Data Provided)

Nov 13, 2007

Hello, can anyone tell me if it is possible to conditionally join tables? If so, how could it be done?

What I would like to do is create a query similar to the one below but include the rows in the Asset table where the corresponding Alert field is NULL.


SELECT A.AssetId, A.IndustryId, A.RegionId, A.RevenueId, AL.AlertId

FROM Alerts AS AL INNER JOIN

Assets AS A ON AL.IndustryId = A.IndustryId AND AL.RegionId = A.RegionId AND AL.RevenueId = A.RevenueId
WHERE AlertId = 1

The output I am after would be the first row in the Assets table. But since the RevenueId column is NULL, I get nothing. The only time the above query will work is if all three Id columns are populated. That is not always going to be the case. Please don't suggest changing table structures or adding data. That is not an option.

Below are the table structures and sample data.

CREATE TABLE [dbo].[Alerts](
[AlertId] [int] NOT NULL,
[IndustryId] [int] NULL,
[RegionId] [int] NULL,
[RevenueId] [int] NULL,
CONSTRAINT [PK_Alert] PRIMARY KEY CLUSTERED
(
[AlertId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Assets](
[AssetId] [int] NOT NULL,
[IndustryId] [int] NOT NULL,
[RegionId] [int] NOT NULL,
[RevenueId] [int] NOT NULL,
CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED
(
[AssetId] ASC,
[IndustryId] ASC,
[RegionId] ASC,
[RevenueId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (1, 2, 5, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (2, 2, 5, 1)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (3, 2, NULL, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (4, NULL, 5, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (5, 3, NULL, 4)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (6, NULL, 4, NULL)

INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)

VALUES (7, 3, 4, 1)


INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (1, 2, 4, 3)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (1, 2, 5, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (2, 2, 5, 5)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (2, 3, 4, 5)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (3, 2, 4, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (4, 2, 5, 4)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (5, 3, 5, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (5, 2, 4, 5)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (5, 3, 1, 1)

INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)

VALUES (6, 3, 2, 4)

Thanks.

View 7 Replies View Related

Why Should We Sample Nested Table Separetely For Data Mining Model Trainings?

Dec 1, 2006

Hi, all here,

Thank you very much for your kind attention.

I dont think we should sample any nested tables for data mining model training? Since I think any nested tables are bound to the case table. Therefore whenever we sample the case table, the nested tables are like any other input attributes within the case table to be rectrieved as inputs accordingly?

Thank you very much for any guidance to clear my confusion.

With best regards,

Yours sincerely,

 

 

View 3 Replies View Related

SQL Sample?

Feb 5, 2006

Hi!

This concerns SQL errors showing up unexpectedly and a "Sample" application.

I am running Windows XP Pro SP2 on a Dell 8600 Laptop. It is the only computer I own (or want to own) therefore, home networking not an option. A simple, stand alone computer is all I need or want, yet sometime around September or October '05 whenever I booted up, I was getting SQL error messages (e.g., missing SQLsvc file). While trying to find the problem, I was seeing indications in various places that Windows NT was my OS (what happened to Windows XP as my OS?). I'm not sure how all that happened, I certainly did not intend to make any such change.

I'm not a computer techy, nor do I have such an aspiration. However, I'm quickly discovering that in order to use a computer in any capacity, one must indeed become somewhat of a techy - like it or not!

So, I got rid of the SQL error messages from popping up at bootup and things seemed to be a bit more normal, but now (for the past 2 or 3 months), everytime I shut down, I get the pop up window that indicates a program is not ending properly and I have the option to "end now" or "cancel" to return to windows. The name in the title box of this popup is simply "Sample" -- no extension such as .exe (although I assume that's what it should be). I have no clue what this is and have searched every possible source to find the answer but to no avail. I suspect it has something to do with that blasted SQL thing that seemed to take over my computer. I searched the web for "sample.exe" and found that it may be a virus

"Virus Alert: W32.Nimda.E@mm
The attachment received has been changed to: Sample.exe ... Emails itself out as Sample.exe Shared drives: Infects open network shares."

After reading the above link, I checked and, sure enough, I have this "W32nimda" file. A search as to how to eliminate it, led me to the Symantec website, which has a removal tool to download, along with 7 pages of instructions (did I mention that I did NOT want to be a computer techy?). I used Symantec antivirus 2 years ago and was glad to be rid of it, however getting completely rid of it seems to be another story and I truly wish I knew the secret to that (any hints?). I did not download this tool yet and not sure if I want to. Surely, there's an easier way! I have run my antivirus software (Trend Micro PC-Cillin), which finds nothing.

I seem to be getting away from the subject, although it all seems related, so I'll get back to the SQL question(s) which is, do I need it? If not, how can I get rid of it? Probably more information is required to answer this question such as how I use the computer, what applications, etc. Briefly, I do not create web pages and do not have a web page; no home network; do not play games or download music (afraid of that!); don't visit "taboo" websites. I do not have MS Word or any of the MS office line - Word Perfect 12 is my word processor. In November last year (at the same time all this started) my modem ceased to function, which I attributed to, unknowingly, plugging into a digital telephone jack at my office. I bought and installed another modem - which I could not get to work - took it back and bought wireless modem and router, which I installed and setup myself (with telephone assistance from router manufacturer, Linksys).

My web related usage is mainly research and email (a necessary evil, apparently). I also use the computer for photo storage and enhancement, graphics and Windows Media Player 9.

I apologize for this post being so lengthy, but I wanted to explain the problem with enough detail in the hope of getting a substantially beneficial answer (other attempts at other sites have been answered basically with "I dunno!" - and here's a surprise, I got the same "I dunno" from The Geek Squad at Best Buy!).

Thanks for taking the time to read all this - hope you can help!

View 3 Replies View Related

Sample DB

Nov 28, 2007



Newbie in sql. Is the adventure works db sample compatible with the free sql compact edition, or is there a sample db for the compact edition? A link if possible could help. Was searching on microsoft' s website and was getting the sample for sql server and i have the compact ed. Downloaded the sample, tried to run it but nothing.

View 3 Replies View Related

SCD Sample Example

Jan 10, 2006

Hello All,

I have two tables

SCDTest1

id          stdt                    enddt                 price

1           01/04/2006      09/09/9999       900

2           01/04/2006      09/09/9999        200

and

SCDTest2

id          stdt                    enddt                 price

1           01/04/2006      09/09/9999       100

I want to create a package that will:

check to see if id and stdt from SCDTest1 exists in SCDTest2,

if yes, I would like to expire that row in SCDTest2 (change the enddt to today) and then insert a new record into SCDTest2 with stdt = today and enddt = 09/09/9999. 

If no then I want to insert the row with stdt = today, enddt = 09/09/9999.

SCDTest2 will look like this after the run is complete.

SCDTest2

id          stdt                    enddt                 price

1           01/04/2006       01/10/2006       100

1           01/10/2006       09/09/9999       900

2           01/04/2006      09/09/9999        200

I have come close by using the SCD wizard, but have been unable to figure out how to get the proper date updates to occur.

Any help would be greatly appreciated.

Thanks

View 7 Replies View Related

Looking For A Sample Database

Sep 23, 2005

I'm looking for a sample database for a big company, organization,... to build my db project as a part of my db system courses. Can anybody help me?
Thanks anyway.

View 6 Replies View Related

Sample Query

Apr 9, 2008

Hai

I have one amount field in my table. I need the query to get the record where the amount field must be +/-15%.

View 15 Replies View Related

Sample Database

Nov 21, 2005

for SQL Server 2005 express (no northwind or pubs sample databaes only adventureworks). Please correct me.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved