|
Another way to do it is (just replace your object names in the carrots):
If Object_Id('<database>.<schema>.<table>') Is Not Null
Begin
Create Table...
End
Which is pretty much the same as posted above, it's just using a system function. It can also work with temp tables:
--Assuming that #table is the temp table name.
If Object_Id('tempdb..#table') Is Not Null
...
|
|
|
|
|
Hi,
I have a c# (.Net 2) program that connects to access mdb file.
I use strong typed dataset to do this.
Now I have searched the web and found ms sql connection string
I replaced what was generated by the wizard with the new one and
now I can successfully connect to MS SQL server (with the same table).
The problem starts when I try to delete rows from the table.
In access it works great obviously, But with MS SQL I get exception when calling udpate (Wrong syntax near '.').
Is it possible at all to use strong typed datasets to connect to both MS SQL and ACESS. If yes how?
Thanks,
Yaniv
|
|
|
|
|
The SQL syntax is different between Access  and SQL server. You are not going to be able to just change connection strings - you will need to duplicate some/all your SQL statements to conform with the TSQL syntax.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Thanks for your reply.
I ran the sql query: DELETE FROM table1 WHERE id = 1
on both access and ms sql and it worked fine.
But I don't create the queries manually, It is created by the adapter (I think) of my strong typed database ( so I don't know whats really going there).
Is there a way I can view the query created by it?
Sincerely yours
Y.R.
|
|
|
|
|
Y_R wrote: But I don't create the queries manually
This where you have a problem. You are using the MS built in tools and therefore have only limited knowledge and understanding of the mechanics of the database and the DAL.
I suggest you completely reengineer your application to use a proper data access layer where you have control and understanding of the DAL and database operations.
As for
Y_R wrote: Is there a way I can view the query created by it?
I have absolutely no idea (but would expect it to be there somewhere) as I have only used the built in stuff enough to identify what a disaster it can be.
BTW this is not a rant against you, everyone has to start somewhere and you have asked intelligent questions and shown a willingness to learn. Do some reasearch into DALs, there are plenty of examples around.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Before we started the project we checked and found strong typed datasets the most fitting solution, Though some people strongly advised against it.
I works fine in a different area of my project (With mysql), So I don't know why people dislike it so much, But I have little experience with databases so I guess you have your reasons.
Anyway, For my specific problem I will do as you suggested and further my research in to the DALs.
Thank you very much you really helped me.
Sincerely yours
Y.R.
|
|
|
|
|
hi, All
please,, correct this sql statement (select column name1 ,count(column name2) from table name where columnname2='1',column name3 from table name )..
thanks for all person who help me.....
|
|
|
|
|
zead wrote: where columnname2='1',column name3
this bit does not make sense.
where columnname2 = '1' AND columnname3 ....
Do I get anything for doing your homework?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
In this exceptional case,....we might point him in the right general direction? 
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
can u explain the meaning of multivalued dependency and functional dependency with an example .
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Functional dependency example: a Human Readable Code (like the hex value CFADE) created based on an Internal ID (850654) of a record (the Internal ID precisely determmine the hex value )
(One)Internal ID -> (One)Human Readable Code
Multivalued dependency example:
(One)Internal ID -> (One)Human Readable Code -> (Multiple)Account Number
You can find some examples here:
http://en.wikipedia.org/wiki/Functional_dependency[^]
http://en.wikipedia.org/wiki/Multivalued_dependency[^]
modified on Wednesday, July 9, 2008 10:57 AM
|
|
|
|
|
Hi all,
How do I connect to a MS-access data base which is present in localhost from an application runnig on web server. I want to send data from DB Server (sqlserver 2000) which is on remote machine to Ms-Access DB present in localhost.
After googling I found the following
Open connection to Access database located on a remote server:
"Provider=MS Remote; Remote Server=http://Your-Remote-Server-IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb"
But, here my localhost IP varies depending on the client's system on which the web application is running. So, how do I set the clients' IP value or is there any other way to acheive this.
Thank U all,
Kiran
|
|
|
|
|
try this...
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server_name\db_folder\Your_Database_Name.mdb;User Id=admin;Password=;
|
|
|
|
|
Hi Jack,
Thanks for ur response. But my actual requirement is as follows..,
I uploaded an application to remotely located web server.
Whoever the client calls that application it needs to send the content of its sqlserver database to clients' ms-access database.
For this I need to establish a connection in that web-application to have access to clients' MS-Access data base.
Irrespective of the connection i.e., whether it is from a proxy, Dial-Up, Broad band the data should flow exactly to the clients' system who is currently using that application.
So, How do I know the exact systems' address to copy the database?
Thank U all,
Kiran.
|
|
|
|
|
I think you are trying to PUSH (from SQL Server to Access) when you do not know where the Access DB is. This requires telepathinc software OR have your app/access db tell SQL where it is before trying to push.
kadkir wrote: Whoever the client calls that application
As part of the call make it identify itself.
IIRC you used to be able to link an Access DB to use sql tables, this implies a connection residing in the Access DB!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How to create a user with multiple roles or groups in TSQL?
Your help is really appreciated.
|
|
|
|
|
Create the user and add to any roles you need. By groups I assume you mean Windows (NT) groups. This must be done in the OS and the group granted permissions in SQL Server.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I am writing an small membership application. It provides a layer above an Access database. The program will probably run an a single machine but I need a way to allow any user that logs on to access the underlying database. I have heard that I could implement this feature through a windows service and use remoting. Could anyone give me a head start on this issue? BTW the program wil be distributed via CD-ROM so the installation needs to be as simple as possible.
"Make it as simple as possible, but no simpler" Issac Newton
|
|
|
|
|
You certainly wouldn't want to do this via a service. A config file can contain a connection string, which you would use to point to a single instance of the database. It's that simple.
|
|
|
|
|
Recommendation - use SQL Express, it's free and gives you a path which will allow you to grow. It is also a MUCH better platform to develop against.
A good % of the issues here are about crappy Access problems.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm using C#, .net2.0 framework and DB2 as my database.
I need to validate a database(DB2) query which is given by the user in the textbox. Please note that I just need to validate not to execute the query(text in textbox). I dont want any third party software and I want to do in .net2.0, ASP.net, C#, DB2
Please share your ideas and solution. 
|
|
|
|
|
Please don't cross post, its not useful.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
What do you mean by validate?
You could use a DataReader object to do a GetSchemaTable and if it successfully returns, you can be relatively sure the SQL query was good. You could include put "WHERE 1=2" in the WHERE portion of the SQL to return just the schema of an SQL statement, as a different way to check. You could replace any selected fields with COUNT(*) to return a count of how many records will be pulle before you run the query. You could do a .DeriveParameters on the command. There are all kinds of options available to you.
Hope this helps.
|
|
|
|
|
I'm using SQL Server 2005 Express, running on a machine on our domain. The ODBC connection string for my application normally looks like this:
Driver={SQL Server}; Server=HostSys\SQLEXPRESS; Trusted_Connection=yes; Database=VersamarkCPT;
<small>(spaces inserted for readability)</small>I would like non-domain users to be able to access this data base as well. The only way I've been able to do that is to set SQL Server to "Mixed Authentication" mode, and use a connection string like this:
Driver={SQL Server}; Server=123.123.123.123\SQLEXPRESS; Database=VersamarkCPT; Uid=username; Pwd=passwrd;I would rather use Windows Authentication if possible. I would also like to use a machine name instead of its IP address.
Is there some other connection string syntax I should be using? I've read through the SQL Server Books Online, MSDN, and articles here at CP, and haven't had a lot of luck deciphering what's going on. I'm a complete noob at DB programming, so please be kind.
Software Zen: delete this;
|
|
|
|
|
I have always found connectionstrings.com to be an invaluable source of connection information. Hopefully you will be able to answer your questions and learn a few tips from them - I know I have.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
