|
Try this
declare @Table1 table(id int)
insert into @Table1
select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8
declare @Table2 table(id int)
insert into @Table2
select 1 union all select 2 union all select 5 union all select 8
Solution 1:
select id from @Table1 except select id from @Table2
Solution 2:
select id from @Table1 where id not in (select id from @Table2)
Let me know in case of any concern
Niladri Biswas
|
|
|
|
|
How do you link a SQL Server through an ODBC connection to an Access DB that has the User Security turned on? The ODBC connection contains the user name and password. When I test the connection in C# I don't have any problems but my client can't get their SQL Server to work.
This is the error they sent me.
OLE DB provider "MSDASQL" for linked server "testDB" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "testDB" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "testDB" returned message "[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "testDB".
|
|
|
|
|
Hi, I have a problem and I'm not even sure what question to ask..
I suspect my problem lays in the design, I have these three identical sets of table that are filled independently and when combined at some time to have them in one table we will have the primary key (ID is a primary key in all the table copies) violated (where set to bold)
Please, how would you do it D'Oh!
ID IDD Name
-- --- ----
1 1 A
2 1 B
3 1 C
1 2 A
2 2 B
3 2 C
ID IDD Name
-- --- ----
1 1 A
2 1 B
3 1 C
ID IDD Name
-- --- ----
1 2 A
2 2 B
3 2 C
modified on Friday, October 23, 2009 4:53 PM
|
|
|
|
|
Perhaps it's just my monitor but the bold you mention is difficult to see. If I understand your problem however;
First, you could ignore the primary keys and insert the other columns into you new table. Provided there is no associations to them that must be maintained
Second, create the combined table as such NewId(PK), ID, IDD, Name
only two letters away from being an asset
|
|
|
|
|
Thank you Mark, I had the very same thought, but then I'll have to make the NewID primary key column autonumbered "I've done this before".. please notice that these tables are not no the same platform and cant be related as I would export the lower copy and combine it with the upper one "the one you suggested to have a new ID primary column".. Anyhow, thanks mate and I'll just wait for other replays today and if no better solution was suggested, I'll go for this one.
|
|
|
|
|
Hey Mark, just to let you know, I found the answer and it was to simply create a composite key!
|
|
|
|
|
Hi,
I use connection to sql server:
Internal::_ConnectionPtr m_pConnection;
m_pConnection.CreateInstance( __uuidof( Internal::Connection ) );
when I am currently testing my application in remote (server sqlserver in another machine) everything works as it should.
But when I simulate a network outage (disconnect and reconnect the cable network) and I run a query to read attributes (for example) I noticed that my application does not know the basis which has been connected even though the tests on the connection it gives me yet opened(pConnection->GetState() = adStateOpen)!
So I wonder how to know if there is a network outage or how to create a connection attempt and also the best test on the connection that it is still connected to my database.
Thank you
|
|
|
|
|
I have an application developed by third party using Oracle 10g database. Oracle 10g express edition (freely available) has been installed in the PC where the application is running.
Now I would like to take a backup of that database. I have goggled it but so many options come that I am unable to decide which one is suitable for my requirement.
After taking the backup, i should be able to restore the database in the new PC in case that PC crashes.
Am not too conversant with Oracle database. Kindly let me know if any more information is required to answer above query.
|
|
|
|
|
Investigate using the EXPORT operation from Oracle as your backup. You will then be able to use the IMPORT operation to completely or partialy restore the database. I've been using and dependant upon these for years with Oracle on Windows servers.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hi All
I have a data of MSSQL Image filed.I have insert picture .bmp then i am getting data like this .
0x433A5C446F63756D656E747320616E642053657474696E67735C4D697274756E6A61795C4465736B746F705C756E7469746C65643131312E626D70<br />
Now i want to convert this data into Image.
Plz help me
|
|
|
|
|
You need to put the data into a stream and load your bitmap from that. SQL Server has no idea what sort of data you stored ( image just means a lump of data in this case, not a picture ).
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
Thanks for reply.How to do that can you help me.That time my table have my table have column name "Image" data type "Image".Plz help me
|
|
|
|
|
Well, you would use the MemoryStream class and the Bitmap.FromStream method. It's hard to say more without knowing more about your project, but the MSDN examples and the names of the classes to use should be all that you need, anyhow.
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
Function LoadImage(ByVal PicSave As PictureBox) As Image
Dim arrayImage() As Byte = CType(dr.Item("DbFld"), Byte())
Dim ms As New MemoryStream(arrayImage)
LoadImage = Image.FromStream(ms)
End If
End While
dr.Close()
Return LoadImage
End Function
Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
Well, I try to make people do a little research, but there's enough assumptions and problems with this code that I assume you're doing the same, so.....
Christian Graus
Driven to the arms of OSX by Vista.
Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
|
|
|
|
|
hmm , Thats interesting , this is the reason why i put the code, can i know what problem this code has , as you know problem is the gateway to development
Best Regards,
SOFTDEV
If you have knowledge, let others light their candles at it
|
|
|
|
|
I have table data like this
Id(PrimaryKey) Time Value Time Order
1 06:00AM 1
2 07:00AM 2
3 08:00AM 3
4 09:00AM 4
5 10:00AM 5
6 11:00AM 6
If I get query like select * from table order by Time Order then I will above data
My requirement is if I have 2 customers
• Customer1 configure start time as 08:00AM then I have to bind drop down first value as 08:00AM , 09:00AM, 10:00AM, 11:00AM, 06:00AM, 07:00AM
• Customer1 configure start time as 10:00AM then I have to bind drop down first value as 10:00AM , 11:00AM, 06:00AM, 07:00AM, 08:00AM, 09:00AM
So it has basically start from ( based on configuration by customer specific) to end time then start time onwards.
Can you please how can we write SQL query above and how can I achive above requirement?
Thank,
Ashok
Ashok Nalam
|
|
|
|
|
I wouldn't write this as SQL. I would populate the list that you use in the dropdown in c#/vb.net using data from the query that you already have.
|
|
|
|
|
I need sql statement for reporting purpose also please provide the sql
Ashok Nalam
|
|
|
|
|
And what they are not using a dropdown list? What if it is needed for a report or a view?
only two letters away from being an asset
|
|
|
|
|
The initial request said "bind drop down" so I assumed that they wanted to populate a list to fill a dropdown.
|
|
|
|
|
SELECT * FROM Table
WHERE TimeOrder >= @StartOrder
UNION ALL
SELECT * FROM Table
WHERE TimeOrder < @StartOrder
only two letters away from being an asset
|
|
|
|
|
The only way to guarantee the order of a resultset is to use an ORDER BY clause;
SELECT Id, TimeValue, TimeOrder
FROM
(
SELECT Id, TimeValue, TimeOrder, 0 as result
FROM #data
WHERE TimeOrder >= @StartOrder
UNION ALL
SELECT Id, TimeValue, TimeOrder, 1
FROM #data
WHERE TimeOrder < @StartOrder
) as t
ORDER BY result ASC, TimeOrder asc
|
|
|
|
|
yes, you are right. If records are already not in order in what we need then we need to sort it as per the requirement.
|
|
|
|
|
That's not what I said. I said that the only way to guarantee the order of the results is to use an ORDER BY clause. Without the sort, the results may or may not be returned in the order that you want depending upon how the query optimizer decides to handle your request.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
