|
Been trying to write a statment here but not getting it to work I'm wondering if even the concept is valid for instace I want to do:
SELECT * FROM tasks WHERE Status LIKE 'Com%' AND WHERE Status NOT LIKE 'Complete%'
MySQL doesn't accept this as a valid statement, All I want it to do is select everything that starts with "com" but doesn't select anything that contains "Complete". Any ideas?
Thanks!
|
|
|
|
|
|
Doh! What was I thinking. Thanks for pointing it out.
|
|
|
|
|
Hi. I'm using msde 2000 sp3a for my database. Everytime I connect, I got this exception:
"Project SampleDB.exe encountered unhandled exception class System.Security.SecurityException."
I am using the free IDE from Borland, C#Builder Personal and my connection string looks like this:
"Initial Catalog=SampleDB;Data source=urstoff\\msde;Integrated security=SSPI";
This application runs on my other pc where I started it.
What's wrong?
|
|
|
|
|
This is more of a .NET Framework question.
IIRC, the ADO.NET classes (I assume you're using SqlConnection) only allow access - using the CLR's code access security model - if the executable was loaded from the local hard disk.
It sounds like you're actually loading the executable across the network. This places the code in the Local Intranet rather than the My Computer security zone, and hence you don't have permission to use SqlConnection.
Copy the executable to the computer's hard disk, and it ought to work.
|
|
|
|
|
Thanks very much Mike. It compiled when I transfer the files from the network shared folders to the local drive. My source files are in My Documents that is redirected to a file server. Basically, the files run from the server.
|
|
|
|
|
Hi,
I'm a .net newbie & before this i have only worked with asp.
I'm trying to make a sample winforms intranet application using VS.net - c# and sql 2000 database.
The application works if executed on PC on which the sql-server is installed but does not seem to connect to the database when run on any other PC in the intranet.
Connection string used:
"data source=SUNNY;initial catalog=PROJ;password=sunny;persist security info=True;user id=softadmin;workstation id=192.168.1.2;packet size=4096";
Making the necessary changes in the connection string will do the trick OR does it required network socket programming...
Please provide guidance. Any url links to related articles will be of great help. Thanx in advance.
Sunny
|
|
|
|
|
Changing the connectionstring should suffice. The network plumbing has been done for you in the SQL Server OLEDB/ODBC drivers.
A quick checklist:
1. Check that the SQL Server is set up to use mixed mode authentication. (Both NT and SQL logins, NOT NT ONLY...)
2. Check all params in the connectionstring
3. Check you network infrastucture.
|
|
|
|
|
Hi Arjan, Thanx 4 clarifying the point - i've gone thru 2 books and done lots of browsing but could not get the answer whether i needed to do network programming in this scenario. I should have posted my problem on this forum - would have saved me lots of time.
AS PER YOUR CHECKLIST:
1) I checked and found that the authentication is mixed mode.
2) The params all seem to be correct, the code was generated by VS.Net IDE.
The only point i'm not clear about is whether "workstation id=192.168.1.2" is what is required to tell the program the location of the database. The database is on PC with ip address 192.168.1.2
The programs functions satisfactorily on the PC on which the database is located but returns an Alert message "Invalid User" to be displayed after validation if username & password is incorrect.
THE CONNECTION STRING USED:
"data source=SUNNY;initial catalog=PROJ;password=sunny;persist security info=True;user id=softadmin;workstation id=192.168.1.2;packet size=4096";
3) Network Infrastructure seems all ok - I'm able to access each one of the comps from the other.
Sunny.
|
|
|
|
|
Try changing the connection string to
"Data source=SUNNY;initial catalog=PROJ;user id=softadmin;password=sunny;"
accept the defaults for persisting security info (FALSE), packet size. I am not familiar with using 'workstation id' in a connection string. The data source should be the NODE Name where the server is located (or the SQL server instance name in the event you have multiple instances on the same box ( not likely with MSDE, but allowed with full SQL version). In any event, SQL server network library should resolve the targe host. You can also use the IP as the value for data source. Make sure you can ping the server node from the client. Try adding "Network library=Dbmssocn;" to the connection string to force the use of the TCPIP network library
also make sure you have added that user and login to the SQL database (sp_adduser, sp_grantdbaccess - the latter to the specific database, the former to the server in general, not all users get automatic access to all databases).
Make sure the user has access to the tables,etc in the data base (grant Select,insert,update,delete to 'user' on 'table')
you might want to download the SQl Server Books Online[^] for a complete reference to SQL 2000...(I don't think this comes with MSDE, but it is free, and indespensable if you are going to work with SQL 2000).
Another good reference for connection strings is here[^]
This is gor ADO,OLEDB,and ODBC connections, but the ADO/OLEDB strings for SQL server work just fine (just leave out the "Provider=sqloledb" part).
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
Thank u Rob. Thanx Arjan. You guys were a great help. Finally i was able to solve the problem.
I used Try-Catch while connecting to the database & it returned the following error:
"The .net data sql provider (system.data.sqlclinet) requires microsoft data access components (mdac) version 2.6 or later"
So I downloaded MDAC 2.8 from the microsoft website and the problem was solved.
Expect to see a lot of posts from me on this forum. I'll be needing ur expert advise from time to time. Couldn't have solved this problem without your guidance. Thanx again.
Sunny.
|
|
|
|
|
|
Does any1 know how can I convert a database from SQL Server 2000 to SQ Server 7.0
Database is very simple and this shouldn't be very difficult I imagine.
Or am I wrong?...
Love is the law, love under will.
|
|
|
|
|
Easiest way is to use DTS.
Select the 2K database, and transfer all the objects. You might need to set the checkbox that suppresses non-2K specific features.
Steve S
|
|
|
|
|
You wrote: "You might need to set the checkbox that suppresses non-2K specific features."
BUT I don't find this feature. Please where is it. If I click Advanced there is no NON 2k specific features. I found this only in "Generate SQL Script"
Love is the law, love under will.
|
|
|
|
|
Then don't use that feature 
I vaguely remembered it from the generate SQL script. I think DTS will correctly compensate for anything that is SQLS2000 specific when it does the transfer.
We've done just this here, and it seems to work just fine. There are various optional bits, like transferring permissions etc that you can tinker with.
Steve S
|
|
|
|
|
Backup the SQL 7 DB and restore it to your SQL 2000 Server...
|
|
|
|
|
its not from 7-> 2000
I meant 2000 -> 7.0 
Love is the law, love under will.
|
|
|
|
|
Sorry. My wrong...
Then I'd go for eiter Steve S's solution, or use Enterprise Manager to create the scripts...
Just be sure to set up SQL logins correcty first, and doublecheck that indexes and foreign keys will be created...
|
|
|
|
|
How to check whether a table exist not in the database ?
In oracle can use : Select Table_Name from All_Tables
what about in MSAccess and MS SQL Server ?
|
|
|
|
|
SQL Server:
SELECT * FROM information_schema.tables WHERe table_type = 'BASE TABLE'
Access: dunno...
|
|
|
|
|
I use this query
if exists(select name from sysdatabases where name='DatabaseName') begin
-- do some stuffs here
end
cheers
|
|
|
|
|
He asked about tables, not databases, so it would be sysobjects where name = 'Foo' and type = 'U', but accessing the sysdatabases/sysobjects/etc tables directly has been depreciated since SQL7. You should use either the metadata functions or the INFORMATION_SCHEMA views instead.
--
-Blake (com/bcdev/blake)
|
|
|
|
|
Arjan's answer is also correct, but I prefer:
if objectproperty(object_id('YourTableName'), 'IsTable') = 1 begin
-- do something
end
--
-Blake (com/bcdev/blake)
|
|
|
|
|
I apologised for the mistake. I don't know why 'database' was in my mind then. I also prefer the object_property(object_id()) construct.
Tnx for the correction.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
