Backup Sample Question

Mar 11, 2008

This is the user instance backup and restore sample:

http://blogs.msdn.com/sqlexpress/archive/2007/03/20/backup-and-restore-with-user-instances.aspx

But what would be wrong with just detaching the database, and copying the .mdf file? I have been doing this and it seems to work. I just treat the .mdf file like any other embedded database this way. To restore I just open the copied .mdf file.

View 4 Replies


ADVERTISEMENT

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

BACKUP LOG Cannot Be Performed Because There Is No Current Database Backup. BACKUP LOG Is Terminating Abnormally.

Jan 31, 2008



Hi there

I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.

The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).


Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?

Thanks

Jane

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

SQL Sample Questions?

Aug 28, 2006

Hey guys,

Does anyone know of a site or a link which gives you sample SQL questions (mostly related to SQL queries) and answers?:) I am not a developer so I don't get problems (to solve) that easily:)

If its already available here, my apologies, I haven't explored that much.


Thanks.

View 2 Replies View Related

Sample Database

Mar 31, 2008

i have a query that uses the adventure works sample database am working on sql server 2000 can i install this sample database to this sql2000 server?
Best Regards

View 2 Replies View Related

Regarding A Sample Query

Jul 23, 2005

Hello,I am a very complex problem in front of me. Kindly help me outin acheiving the same.Say I have a table called InfoName with two columns Name and IDInfoNameName IDOS 1SP 2Driver 3fasdf **** ***** ****(I AM INTERESTED IN ONLY FIRST THREE ROWS )I have another table Infotxt which uses the ID of InfoName asforeign key. It stores the value of this ID as shownInFotxtID Value UnitNAME1 Win 2000 raj2 SP 4 raj3 40 GB raj1 Win xp jay2 SP 2 jay3 20 GB jayNOw I need to present it with unitname's configuration of OS,Sp and disk capacity like below.name OS SP DriveRaj win2000 sp4 40 GBJay winxp sp2 2o GBThat is, the rows of the InfoName table (first 3 rows) shouldbe the columns of my resultant query.How can I achieve the same.Please give me some ideas, and if the question is silly, I amvery sorry, because I am new to database queries...Thanks,cspekcspek*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Sample ASP Script??

Jul 23, 2005

Hello,Im brand new and trying to learn SQL Server 2000, I have purchased a bookand been reading it for a few weeks.I'm tring to find an ASP script that would let me know if my connection tomy database is working.I use DSN, i created a DSN name "mail"anyone have any scripts?--Message posted via http://www.sqlmonster.com

View 4 Replies View Related

HelloWorldCS Sample

Nov 17, 2005

Is anyone is familiar with the HelloWorldCS solution that is available for download at www.SQLServiceBroker.com?

View 5 Replies View Related

Sample's Reference

Sep 26, 2007



Forgive me for my programming ignorance, but how to i find the microsoft.sqlserverce.samples.accesssync.editdata reference?

Thanks

View 1 Replies View Related

Can't Use Sample Databases

Oct 26, 2015

I have installed MS SQL Server 2014. When I try to attach the pubs database that I downloaded, I'm getting an error saying that its non-release version (539) is not supported by this version of SQL Server.

My question is: Is there a way that I can either get an updated version of the database from Microsoft, or update the one I have?

View 3 Replies View Related

FormsAuthentication Sample

Sep 28, 2007



Hello,
I just installed the FormsAuthentication Sample and when I goto http://localhost/reports I get the logon page and I can register the user id ok, but after that when go back and type my username and password and click logon it gives me this:

The request failed with HTTP status 401: Access Denied.

Any help would be greatly appreciated.

View 2 Replies View Related

IReportViewerMessages Sample

Feb 27, 2006

Hello,

Can you give me complete sample of using your own class implemented IReportViewerMessages interface in ASP.NET Application?
I have problems with this task.

Thanks!

View 5 Replies View Related

SQL Server Everywhere OLE DB Sample

Jul 6, 2006

Hi

We want to replace Access/ SQL Server Express/MSDE with a lightweight server with a small footprint, and I've got the assignment to evaluate SQL Server 2005 Everywhere using MFC. The application we want to replace the database in is MFC and not yet converted to VS 2005. I have no previous experience with OLE DB, and a very limited experience using MFC. I was hoping someone could provide me with a sample application using SQL/e with OLE DB, and some good references to getting started. If anyone knows of any good Libraries to simplify the implementation of OLE DB for me, that would be great. I'm currently looking into Express OLE DB Library from Sypram, but having some compilation issues.

I've tried to read the OLE DB tutorial at msdn library, but so far it feels very abstract and seems quite complex compared to ADO providers in C# which most of my exprience is from.

I tried using the MFC Wizard in VS2005 to create a single document application. When selecting the SQL Server 2005 Everywhere OLE DB Provider for Windows and then selecting a table from the data source, Visual Studio crashes with an unhandeled win32 exception (I've reported this bug at labs.msdn.microsoft.com). So I haven't really gotten a good idea on how to use the OLE DB driver for SQL/e.

Does anyone know if there are any plans on providing an ODBC driver for SQL/e? I haven't been able to find any so far.

 

Best regards

Bjørnar Sundsbø

 

View 1 Replies View Related

Looking For A Subreport Does Anyone Has A Sample

Jan 25, 2007

I am new to reports and worked on few but a single report using 1 stored procedure.

Any sample or webcast.

now i have a requirement creating report within a report(subreport)

first stored procedure will bring few records and now using a id of each row i need to bring there child records(from child table)

is it possible to show master record and then its related child records.

and then number 2 master record and its related child records

Please help thank you very much for the information.

View 3 Replies View Related

Need Sample Code In Vb.net

Mar 28, 2006

Hi,

I neew a small sample code in VB.NET to create a custom data flow component for SSIS.
All the examples I was able to find it are in C#.

Answer very apreciated.

View 3 Replies View Related

Sample Database

Nov 1, 2007



Hi
im a beginner in SQL. I installed SQL Server Express on my computer, but i didnt find any databases included. The institute where i go to learn SQL have plenty of databases, but students usually work on databases called AdventureWorks and GlobalToyz. I searched the web for these databases and found something but i still cant figure out what to do with it. So i was hoping if anybody could send me these databases with brief instructions on how to install it into SQL Server. Thanks in advance

View 3 Replies View Related

Ntext Sample Code

Dec 9, 2003

hi,

Does anyone have any sample code for using ntext data type, when inserting or reading big block of texts? How can you read a big huge block of text back into c#?

thx
-Philip

View 1 Replies View Related

Sample Database About Sqlserver2005

Apr 26, 2005

I had installed the sample database " AdventureWorks ",but i couldn't find it in SQL Server Management Studio,and I noticed than it was installed into "Microsoft SQL Server 2005 Samples" directory,but not "Microsoft SQL Server" directory,what can I do to find the datebase.
I am a beginner,and learning sqlserver by using tutorials,someone help me?
many many thanks.

View 1 Replies View Related

Trying To Install Adventureworks Sample

Jan 4, 2006

I've downloaded from msdn and ran the samples.  It installs something, but the database tree doesn't show up in sql studio.  Is there a straight forward way to do something like import or restore a adventureworks database setup?

View 1 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

Installation Of The Sample Databases

Mar 1, 2007

Hi

How do I install the sample databases Northwind and AdventureWorks?

I know there is a link on the Microsoft website, but does anyone know a step-by-step guide to actually get it on the database?

Thanks in advance!

View 2 Replies View Related

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 View Related

Sample Document On Arrays?

Feb 21, 2008

I'm newer to MS SQL, but have a programming background, so I'm going to try to describe what I'm doing in that sense.

What I want to do is pull an entire column of data from one table, and insert it into a column in another table. Typically, I would do this with an array and while loop or something similar. I've figured out how to do a while loop in SQL, but the array situation has me stumped. I tried reading http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx that article, however it referred to a procedure, and I have no background or experience with procedures.

Any sort of idea or document on a technique to do this would be most appreciated, thank you!

View 15 Replies View Related







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