|
You have to drop the references first. Why are you dropping it each time - can you not just delete the contents?
|
|
|
|
|
Thank you very much, done as you adviced and everything is cool
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
I have got to agree with Paddy
Delete or truncate the table rather than drop it. You should never drop a table as part of a process.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you very much, done as Paddy adviced and everything is cool 
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
I have two tables as follows:
CREATE TABLE [dbo].[WebSyncHistory](
[UserID] [int] NOT NULL,
[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncHistory_SyncTableName] DEFAULT (''),
[SyncTime] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[WebSyncTimesMS](
[UserID] [int] NOT NULL,
[SyncTableName] [nvarchar](50) NOT NULL CONSTRAINT [DF_WebSyncTimesMS_SyncTableName] DEFAULT (''),
[LastActionTime] [datetime] NOT NULL
) ON [PRIMARY]
I have some records in each of these tables:
INSERT INTO WebSyncHistory VALUES (1, 'Products', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (1, 'Categories', '01/01/2008')
INSERT INTO WebSyncHistory VALUES (2, 'DeviceSettings', '01/01/2008')
INSERT INTO WebSyncTimesMS VALUES (1, 'Products', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (1, 'DeviceSettings', '01/01/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'DeviceSettings', '31/08/2008')
INSERT INTO WebSyncTimesMS VALUES (2, 'Categories', '01/01/2008')
Each table has two fields with the same name and data type
What I want to do is
select all records from each table in such a way that I end up with 4 columns - UserID, SyncTableName, SyncTime, LastActionTime
UserID SyncTableName SyncTime LastActionTime
1 Products 01/01/2008 31/08/2008
1 Categories 01/01/2008 NULL
1 DeviceSettings NULL 01/01/2008
2 DeviceSettings 01/01/2008 31/08/2008
2 Categories NULL 01/01/2008
The closest I can get to what I want is the following:
SELECT [WebSyncHistory].[UserID]
,[WebSyncHistory].[SyncTableName]
,MAX([WebSyncHistory].[SyncTime]) As SyncTime
,[WebSyncTimesMS].[UserID]
,[WebSyncTimesMS].[SyncTableName]
,[WebSyncTimesMS].[LastActionTime]
FROM ([WebSyncHistory] FULL OUTER JOIN [WebSyncTimesMS]
ON [WebSyncHistory].[UserID] = [WebSyncTimesMS].[UserID] AND [WebSyncHistory].[SyncTableName] = [WebSyncTimesMS].[SyncTableName])
GROUP BY [WebSyncHistory].[UserID]
,[WebSyncHistory].[SyncTableName]
,[WebSyncTimesMS].[UserID]
,[WebSyncTimesMS].[SyncTableName]
,[WebSyncTimesMS].[LastActionTime]
which returns:
UserID SyncTableName SyncTime UserID SyncTableName LastActionTime
NULL NULL NULL 1 DeviceSettings 2008-01-01 00:00:00.000
NULL NULL NULL 2 Categories 2008-01-01 00:00:00.000
1 Categories 2008-01-01 00:00:00.000 NULL NULL NULL
1 Products 2008-01-01 00:00:00.000 1 Products 2008-08-31 00:00:00.000
2 DeviceSettings 2008-01-01 00:00:00.000 2 DeviceSettings 2008-08-31 00:00:00.000
I'm completely stumped with this one. Can anyone suggest an alternative approach?
Thanks very much,
dlarkin77
|
|
|
|
|
Looking at the output you want, I dont think you need a "full" outer join since you always want to match UserID and SyncTableName. Here's a solution.
I will use a two-step procedure to solve this:
1. Get unique UserID and SyncTableNames (Creating a CTE will be best here)
2. Have a left outer join with the two tables
--create a CTE
With UserTableMods( [UserID], [SyncTableName])
As
(
Select distinct [UserID], [SyncTableName] from [WebSyncTimesMS]
union
Select distinct [UserID], [SyncTableName] from [WebSyncHistory]
)
--Get result
Select UTM.[UserID], UTM.[SyncTableName], H.[SyncTime], M.[LastActionTime]
From UserTableMods UTM
LEFT OUTER JOIN [WebSyncHistory] H ON UTM.USERID=H.USERID and UTM.[SyncTableName]=H.[SyncTableName]
LEFT OUTER JOIN [WebSyncTimesMS] M ON UTM.USERID=M.USERID and UTM.[SyncTableName]=M.[SyncTableName]
And here's the result:
1 Categories 2008-01-01 NULL
1 DeviceSettings NULL 2008-01-01
1 Products 2008-01-01 2008-08-31
2 Categories NULL 2008-01-01
2 DeviceSettings 2008-01-01 2008-08-31
You can use the Grouping and Max functions as per your requirement. Hope that helps.
Regards,
Syed Mehroz Alam
|
|
|
|
|
That works perfectly. Thanks very much Syed
|
|
|
|
|
|
Hi
I have a column in my database named group. However when I put that column in a stored procedure the word 'group' highlights blue and I cant declare it as a column because I think it means something different for sql.
Any help would be great
thanks
Deliver yesterday, code today, think tomorrow.
"http://www.heuse.com/cphumor.htm"
|
|
|
|
|
Surround it with square brackets.
|
|
|
|
|
Cheers found that on another article, but thanks for the answer!
Deliver yesterday, code today, think tomorrow.
"http://www.heuse.com/cphumor.htm"
|
|
|
|
|
Glad to help you
|
|
|
|
|
Dear All,
I have a simple query, and I need to change the returned data.
I mean if some row has a value "Bnk", I need to return this value as "BankDep".
Is this possible in SQL 2005?
This is my query:
SELECT INDEX_CODE from TableTest;
The result: INDEX_CODE
Default
Int
Bnk
Inv
I need to display the previous values as
INDEX_CODE
Default123
International
BankDep
Investment
Kind Regards
OBarahmeh
|
|
|
|
|
select
case INDEX_CODE
when 'Default' Then 'Default123'
when 'Int' Then 'International'
when 'Bnk' Then 'BankDep'
When 'Inv' Then 'Investment'
END
from TableTest
|
|
|
|
|
Thank you very much,
I did not knot that there is a CASE option in SQL
Thanks a lot
Kind Regards
OBarahmeh
|
|
|
|
|
The first answer will work, but you might want to consider making this a lookup column linked to a table with a descriptive name for your codes - bit more maintainable.
|
|
|
|
|
hi...
I have one txt file from d:,I want to retrieve that file using Select query in SQL.anybody knows please reply me...Thanks
Raaj
|
|
|
|
|
what database are you using.
Look at OPENROWSET, I think that allows you to get at a file using select.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I am using Visual Studio 2005 and SQL Server 2005+SQL Server MAnagement Studio 2005.i m using Windows Vista home.
when in "SQL Data source" i configure it ,then after selecting "NewConnection",
i select "Sql Client".
then select the Server (.).
But then in the "Database" Dropdownlist,I couldnt find any of the databases i had made SQL Server MAnagement Studio 2005.
i am new to SQL Server 2005.Please help me out...
thanx in advance
|
|
|
|
|
It could be that SQL Server Browser service is not running.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Are you trying to connect/attach a new database or connect to a new instance of SQL Server?
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
I am taking a class on Databases. As a project assignment I need to write about an 'important database subject (such as query optimization, database design, distributed databases)'. I was hoping someone would be willing to offer some other important database subjects that may not have received much attention. If you have a topic please share, no I am NOT asking anyone to provide any research or writing.
this thing looks like it was written by an epileptic ferret
Dave Kreskowiak
|
|
|
|
|
There's nothing wrong with picking something like database design. You may want to look at database normalization, as well.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Security is always a hot topic.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
