hi, i have few questions,please can any one answer for me? 1. how many columns can we create in one table?does it depene on the size? 2. how many tables can be used in one select query? ie select * from emp1,emp2,emp3 etc......... 3. how many tables can be created in one database? 4,what is the size limitation of one table?
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
Goal:Off-site backup of database without onsite tech intervention
Obstacles: * Webhost does not want SQL Server Agent running for fear of viruses * Database is 3.4GB, LDF is 3.7GB, and a Full Backup is 3.1GB.
Connection / Access:Enterprise Manager, RDP and FTP all work to access data and backups.
I have looked into doing a full backup then differential backups, doing transaction replication, and log shipping. The client doesn't really need replication, he just wants to know that if his server with the production database goes up in flames he has a way of getting back in business without losing more than one day of data.
I am hoping there is a method of "restoring" on his off-site server the data that is "backed up" on the production server. Is that possible if we're doing differential backups?
Is that the best solution or is there a better way?
Thanks for any recommendations or advice, Alec Sherman
Hi Everyone,I'm working on a site that is running MS SQL 2000. They performregular backups of databases and log files and in the event of adisaster they have a second backup server.At present they regularly copy the backup up files from the productionserver to the standby server. The plan, in the event of a disaster, isto restore these backups onto the standby server and change the dnsrecords so that client machines will point to the standby server.Is there a better way?Thanks for any advice,Danny
We have a huge terabyte database that we are planning to upgrade to 2005. We are working on realistic backup solutions in case we have to rollback the upgrade. We have over 400 tables out of which about 100 are kind of static. The other 300 tables have very high transaction rate - thousands of batch transactions per sec. Earlier, prior to 2005 SP2 release we tried setting up replication to a 2000 box and replication agents failed to catch up with the transactions. Not sure of SP2 has any improvements. We are thinking of setting up replication for only the 300 or so tables and we will take a regular nightly back up and can use a day's old data for the 100 small tables.
What are your experiences/suggestions on a proper back up solution to have a 2000 server in almost stand by mode that we can switch back to in case of any issues?
I've just inherited (i.e., our sys admin / DBA left the company) a fairly small SQL Server that's running 7 production databases. Most are quite small, but there are two which are about 40gb each. Traffic is quite low - ~30-40 users at one time doing your basic SELECT / UPDATE / INSERT stuff.
Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.
The T-SQL being run in each case was
Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:
BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD
5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,
BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @i INT select @i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db') RESTORE VERIFYONLY FROM <device> WITH FILE = @i
This is then backed up to tape each night.
Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.
If I issue a
BACKUP LOG <database> to <device> WITH INIT, SKIP
then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?
Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?
Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?
Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated! Matt
Our backups run using a stored proceedure called sp_fullbackup. This takes a dump of every database to a device each night. The only problem is that it overwrites the previous nights backup, due to the way it has been written:
DUMP DATABASE master TO @bkupdevice WITH NOUNLOAD , INIT , SKIP
How do I change the syntax so that it will retain the previous 3 backups?
If I decide to backup my transaction logs on one server and move themto another server with the same "everything"What do I need to do in order to automate this if it is possibleVincento
I am using SQL Server 2000, when I do a backup I use the database maintenance plan at enterprise manager. I select my database and then I schedule the complete backup and transaction log backup to "everyday at 22:00:00" My question is, do I really need to do the transaction log backup? If I am not wrong with the complete backup I can already recover all my data, the transaction log backup is useless if done at the same time that I do the complete backup, right?
I am using SQL Server 2000, when I do a backup I use the database maintenance plan at enterprise manager. I select my database and then I schedule the complete backup and transaction log backup to "everyday at 22:00:00" My question is, do I really need to do the transaction log backup? If I am not wrong with the complete backup I can already recover all my data, the transaction log backup is useless if done at the same time that I do the complete backup, right?
I created a Backup plan using the SSMS Maintenance Wizard. It created an SSIS package for me but the package didn't delete files older than, say, 3 days. So I exported that package from MSDB, added a Script task to it (to do what I needed), and imported it back into MSDB. I can open the new package under SSMS and everything looked good ... until I double-clicked the Script task. That task is simply not supported under the SSMS Maintenance Wizard. Goin in and out of that task under SSMS gave me the following errors:
"Microsoft Visual Studio Macros: The operation could not be compelted. Call was rejected by callee.".
Or the ever popular "cannot show the editor for this task'.
I was asked to "Wait for Completion" or "Switch tasks or "Cancel".
It seems the only way to delete these files is to set up a seperate SSIS job that calls my Script task sometime after the nightly backup has completed. But now the completion of the Backup job and the file deletion processes are decoupled, which is not very good. Can you offer any ideas how to handle the gracefully? (The real problem is that the SSMS Maintenance wizard does not recognize the full set of SSIS tasks. It would be nice if double-clicking on the Maintenance plan pane opened up the SSIS editor but that's for a later day.)
In my enrv. DBA just migrated SQL Server 2000 Databases from WINDOWS 2000 to WINDOWS 2003 Enterprise Edition and the same SQL version. The problem which i need to analyse
1. Why the full backup size is occupying only 70GB when DB size is 120GB?
Here is the situation
Full Backup is taken once every day ---- 70gb Diff Backup Taken every 3 hrs till 5 PM ----- size is 50GB Transaction log backed up every 10 min uptill 8 PM ----- not a big size
I am really confused as to why Full DB Backup is taking only 70GB.............Can some one please throw a Light on how the SQL Server 2000 Backup functions.
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
Any help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
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?
Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.
Hi folks, guidance required! Clients have an MSDE installed and they need all of their data to be dowloaded for the first time from our website. 30 tables r involved. Can i automate this? I suspect BCP allows only one table to be exported into a text file. Since it's the first time process i don't want to setup replication.
Hello All, I have a transaction table that holds nearly a million records and my master with 60000 records,I want to fetch details based on two dates.
I use Union operator to query the info from a view,is this right or fetch the result into a temporary table and fetch the result from temporary table using the same Union operator.
Which is the better way to fetch details faster.
Nirene
My SP is pasted below just advice me to execute this more efficiently,cos this SP is called thru a Web application.
CREATE PROCEDURE GL @SDt Varchar(10),@EDt Varchar(10),@Loccode Char(5),@OP Char(4) AS
IF EXISTS (Select 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='#Gltmp') Begin Drop Table #Gltmp End
Select @Cocode=Cocode from Location Where Loccode=@Loccode Select @CashGL=Glcode,@Gldesc=Gldesc from Glmast Where Cocode=@Cocode and Subgroupcode='CASH'
Set @OpBalQry='Select A.Glcode,'''' as Trtype,''' + @SDt + ''' as Refdt,'' Opening Balance'' as Refno, Max(B.Gldesc) as Narration,Case When (Sum(A.Deb)-Sum(A.Cre))<0 Then (-1*(Sum(A.Deb)-Sum(A.Cre))) Else 0 End as Dr, Case When (Sum(A.Deb)-Sum(A.Cre))>=0 Then (Sum(A.Deb)-Sum(A.Cre)) Else 0 End as Cr from ( Select Glcode, (Case When Drcrflag=''D'' Then Opbal Else 0 End) as Deb, (Case When Drcrflag=''C'' Then Opbal Else 0 End) as Cre from Obdata Union Select Glcode, (Case When Drcrflag=''D'' Then Tramt Else 0 End) as Deb, (Case When Drcrflag=''C'' Then Tramt Else 0 End) as Cre from Trans_Journal Where Refdt <Convert(Datetime,''' + @SDt + ''',103) ) A,Glmast B Where B.Cocode=''' + @Cocode + ''' and A.Glcode=B.Glcode Group By A.Glcode Union ' Set @TrnQry='SELECT T.Glcode,T.Trtype,Convert(Varchar,Max(T.Refdt),103) as Refdt , '''' AS Refno, (Rtrim(Ltrim(Max(G.Gldesc))) + '' -- From Sub-Ledger'') AS Narration, SUM(CASE WHEN T.Drcrflag = ''D'' THEN T.tramt ELSE 0 END) AS Dr, SUM(CASE WHEN T.Drcrflag = ''C'' THEN T.tramt ELSE 0 END) AS Cr FROM Trans_Journal T,Glmast G WHERE T.Glcode=G.Glcode and G.Cocode=''' + @Cocode + ''' and (T.Slcode IS NOT NULL AND Len(Rtrim(Ltrim(T.Slcode)))>0) and (T.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and T.Refdt <=Convert(Datetime,''' + @EDt + ''',103) and T.Loccode= ''' + @Loccode + ''' and (G.Subgroupcode<>''BANK'' or G.Subgroupcode<>''CASH'')) GROUP BY T.Glcode,T.Trtype UNION SELECT A.Glcode,'''' as Trtype,Convert(Varchar,A.Refdt,103) as Refdt, A.Refno, B.Gldesc AS Narration, CASE WHEN A.Drcrflag = ''D'' THEN A.Tramt ELSE 0 END AS Dr, CASE WHEN A.Drcrflag = ''C'' THEN A.Tramt ELSE 0 END AS Cr FROM Trans_Journal, Glmast B WHERE (A.Glcode = B.Glcode and B.Cocode=''' + @Cocode + ''') and (A.Slcode IS NULL OR Len(Rtrim(Ltrim(A.Slcode)))=0) and (A.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and A.Refdt<=Convert(Datetime,''' + @EDt + ''',103)) and A.Loccode=''' + @Loccode + ''' and (B.Subgroupcode<>''BANK'' or B.Subgroupcode<>''CASH'') Union Select Glcode,Trtype,Convert(Varchar,Max(Refdt),103) as Refdt,'' '' as Refno,Max(Narration) as Narration,Sum(Dr) as Dr,Sum(Cr) as Cr from (Select A.Glcode,A.Trtype,A.Refdt,B.Gldesc as Narration, CASE WHEN A.Drcrflag =''D'' THEN A.Tramt ELSE 0 END AS Dr, CASE WHEN A.Drcrflag =''C'' THEN A.Tramt ELSE 0 END AS Cr from Trans_Journal,Glmast B WHERE (A.Glcode=B.Glcode and B.Cocode=''' + @Cocode + ''' and A.Refdt>=Convert(Datetime,''' + @SDt + ''',103) and A.Refdt<=Convert(Datetime,''' + @EDt + ''' ,103) and A.Loccode=''' + @Loccode + ''') and (B.Subgroupcode=''BANK'' or B.Subgroupcode=''CASH'') Union Select ''' + @CashGL + ''' as Glcode,Trtype,Refdt,''' + @Gldesc + ''' as Narration, CASE WHEN Drcrflag =''C'' THEN Tramt ELSE 0 END AS Dr, CASE WHEN Drcrflag =''D'' THEN Tramt ELSE 0 END AS Cr from Trans_Transnarr WHERE (Trtype=''CPM'' or Trtype=''CRT'') and Loccode=''' + @Loccode + ''' and Refdt>=Convert(Datetime,''' + @SDt + ''' ,103) and Refdt<=Convert(Datetime,''' + @EDt + ''',103)) TT Group By Glcode,Trtype) X'
Set @MainSQry='Select Glcode,Trtype,Refdt,Refno,Narration,Dr,Cr Into #Gltmp from ('
Declare @Fullqry NVarchar(4000)
If @OP='WOB' Begin Set @TrnQry = @OpBalQry+@TrnQry End
Set @FullQry = @MainSQry+@TrnQry
Exec sp_executesql @FullQry
Select Glcode,Trtype,Refdt,Refno,Narration,Ltrim(Rtrim(Str(Dr,14,2))) as Dr,Ltrim(Rtrim(Str(Cr,14,2))) as Cr from #Gltmp SQL2XML Where Dr+Cr<>0 Order By Glcode,Trtype,Refdt,Refno for XML AUTO
i want to get following output: id_order | type | number ------------------------- 1234 | A | 1 1235 |A | 0 1235 |B | 0 1236 |B | 1 1237 |C | 0 1237 |D | 0
create table tbl_order ( id_order int ,type nvarchar(40) )
insert into tbl_order (id_order, type) values (1234, 'A' ) insert into tbl_order (id_order, type) values (1235, 'A' ) insert into tbl_order (id_order, type) values (1235, 'B' ) insert into tbl_order (id_order, type) values (1236, 'B' ) insert into tbl_order (id_order, type) values (1237, 'C' ) insert into tbl_order (id_order, type) values (1237, 'D' ) insert into tbl_order (id_order, type) values (1238, 'A' ) insert into tbl_order (id_order, type) values (1239, 'D' ) insert into tbl_order (id_order, type) values (1239, 'B' ) insert into tbl_order (id_order, type) values (1239, 'A' )
select id_order ,type --,isnull(orderX,'') as number ,case when orderX > 1 then 1 else 0 end as number2 from tbl_order left join (select t2.id_order as orderX from tbl_order as t2 where (select count(t1.id_order) from tbl_order as t1 where t1.id_order = t2.id_order) = 1 )as x on tbl_order.id_order = x.orderX
Is there any better/faster select sentance to do this? i'm using sql2000.
Hi, i am not sure if i can solve the problem with SSIS. I wanted do do it hardcoded with C# or so.
So, for my project I need to download zip-files on a daily-base. In these zip-files are xml-Files. And in these files is information stored for inserting or updating tables on a MS-SQL-Server.
So, my question, is it possible to solve that with SSIS? And if, is it easy to understand and to learn within a few days?
I haven't found good sources for information on how to get the data out of XML-Files.
I've got a .dts which load data to several Sql2k servers by Copy SQL Server Objects Task. I'd like to add another task for a Sql25k but I can't because of it's requesting SMO.
It seems that such task need DMO. I know that there are other ways for do such stuff in that DTS but I wonder if there is any trick or shortcut for to avoid this barrier.
I think i have a quite specific problem that we have to resolve with replication. however, i don't know which type of replication to use and how to configure it for this:
To simplify the case a bit: We have one central server which has a table Sales(id, shopid, article). Also our 50 shops have the same table Sales(id, shopid, article).
spec 1: When a shop sells an article, the row of the Sales table at the local shop has to be replicated to the central server. This way, the central server its Sales table has all sold articles from all shops.
spec2: But now we have also an internet shop. All e-orders arrive immediately in the table Sales at the Central Serve with a specific shopid where the article will arrive. Now i have to find a way to replicate the new rows from the Central Server to the specific Shop.... Remark: the other shops should not have rows of other shops.
which type of replication would i use the best and how?
The encrypted value for the "LogonCred" configuration setting cannot be decrypted. (rsFailedToDecryptConfigInformation) (Microsoft.ReportingServices.Diagnostics)
I'm running VS 2005 Enterprise, and SQL Server 2005 Developer / workgroup edition.I'm trying to follow a few online examples to play around, and some of these examples require that you add a SQL DB to your solution by going:-right click on website-Add new Item-Add SQL DataBase.WHen I do that, I get the error that SQL Express isn't installed. Now the question is, why would I want SQL Express installed when I have SQL server 2005 already installed? My Machine can support it, but it's sort of a waste when I need to run SQL Server 2000, SQL Server 2005 and then SQL Server express all at the same time.Any advice?
I'm new in SQL Server 2000. Anyone has a good materials or sites to recommend which talk about Multidimensional Expression? Especially MDX work in "Analysis Manager".
You information are highly appreaciate. Thanking you in advance.
Thought I'd got my head round using a set-based approach but my brain's gone dead on this one
CREATE TABLE #mytable (SWID INT , T INT , DateA DATETIME , DateB DATETIME)
INSERT #mytable (swid , t , DateA , DateB)
SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Aug-2006'AS DATETIME) UNION ALL SELECT 63967 , 1 , CAST('31-Mar-2006' AS DATETIME),CAST( '01-Feb-2007'AS DATETIME) UNION ALL SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME) UNION ALL SELECT 63967 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME) UNION ALL SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME) UNION ALL SELECT 63967 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME) UNION ALL SELECT 63967 , 9999 , CAST('31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME) UNION ALL SELECT 63967 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME) UNION ALL SELECT 10051 , 1 ,CAST('31-Mar-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME) UNION ALL SELECT 10051 , 1 ,CAST( '31-Mar-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME) UNION ALL SELECT 10051 , 0 , CAST('15-Mar-2006' AS DATETIME), CAST('01-Aug-2006'AS DATETIME) UNION ALL SELECT 10051 , 0 , CAST('15-Mar-2006'AS DATETIME) , CAST( '01-Feb-2007'AS DATETIME) UNION ALL SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) ,CAST( '01-Aug-2006'AS DATETIME) UNION ALL SELECT 10051 , 9999 ,CAST( '28-Feb-2006'AS DATETIME) , CAST('01-Feb-2007'AS DATETIME) UNION ALL SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME) UNION ALL SELECT 10051 , 9999 ,CAST( '31-Jan-2006'AS DATETIME), CAST('01-Feb-2007'AS DATETIME) UNION ALL SELECT 10051 , 9999 , CAST('31-Dec-2005'AS DATETIME) , CAST('01-Aug-2006'AS DATETIME) UNION ALL SELECT 10051 , 9999 ,CAST( '31-Dec-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME) UNION ALL SELECT 10051 , 9999 ,CAST('30-Nov-2005'AS DATETIME) , CAST( '01-Aug-2006' AS DATETIME) UNION ALL SELECT 10051 , 9999 ,CAST( '30-Nov-2005' AS DATETIME), CAST('01-Feb-2007' AS DATETIME) select * from #mytable order by SWID desc, DateA desc
The Columns where T values are 1 and 0 are OK having already been derived. I need to UPDATE the remaining rows from the Default T Value of 9999 to Decrementing values (starting at -1) commencing at the highest remaining (ie non 9999 T Value) DateA value and working 'backwards' 'grouping' on SWID
tblClientsCities ID (primary key identity/autonumber) clientID (int) cityID(int)
A client can be located in more than 1 city so i have tblClientsCities (think thats the right way to do it). Say i add a new client and the autonumber changes to "10" which is that client's identifier. How do i then add that identifier to tblClientsCities? I mean it could have been 3,7,205 absolutley anything.
I thought is would be easier to make up a unique key for each client with a script eg
Now that the primary key is known in advance it can be added to tblClients and then tblClientCities. But! i was reading around and many seem to think primary key's like this will slow things down.
So my question is what's the best way of accomplishing this?
