|
no its not vb that has the problem its doesnt think its a comment its when its submitted to the stored procedure that it gets the problem
like the stored procedure would accept this
execute mystoreproc 'blah','blah'
no problem
but when you have something like this
execute mystoreproc 'blah's','blah'
the stored proc will interpet it like this
execute mystoreproc 'blah' s ,'blah'
because sql uses the ' character to enclose strings that are inserted or otherwise used in queries
|
|
|
|
|
i found the solution if you want to use something like
'jake's bigtime' in an insert statement then you would have to simply put two apostrophies and sql will then put the one apostrophe character in the db
so the query would look like this
insert into test values('jake''s bigtime')
and a this query select * from test
would return
jake's bigtime
|
|
|
|
|
Hello,
All you need to do is use two single quotes instead.
e.g. 'o''neill'.
HTH,
Simon.
[EDIT]
Ahh, I see you posted the correct answer 2 minutes before me! 
[/EDIT]
|
|
|
|
|
You should use the parameters collection to submit your parameters to the stored proc.
<br />
SqlCommand cmd = new SqlCommand("mystoredproc");<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
cmd.Parameters.Add("@param1", "blahblah");
cmd.Parameters.Add("@param2", "jamie's");
cmd.ExecuteNonQuery();<br />
Then the necessary convertions will be done automagically for you.
|
|
|
|
|
Has anyone had the same experience?
I have multiple databases on a SQL Server 2000 Enterprise Box.
OS: Windows 2000 Advanced Server.
SQL: Enterprise SQL Server 2000
I checked the disk space cause the space was getting a little low, and then D'OH 
The Log Files were 14.9GB in size.
I ran a script through QA and shrunk the log files to an acceptable size, but before, I had to set the database recovery option to Simple from Full.
Script:
USE DATABASENAME
-- FileName as it is in sysdevices, Size in MB's
DBCC SHRINKFILE (FILENAME_LOG, 1000)
GO
Is this sort of thing a normal occurence in SQL Server 2000?
Thanks.
Tony
|
|
|
|
|
Yes, it is, if you don't regularly empty your log files. (Usually when taking a full backup)
You should set a max size for your logs, so that they don't grow this much again. (It's a pain discovering your disks are full, and ALL your databases halt...)
|
|
|
|
|
Perfectly normal.
When Recovery Mode is set to Full or Bulk-Logged, all operations are recorded in the transaction log, which is only truncated when the transaction log is backed up.
This enables you to roll back the database to any point in time, even if the database wasn't backed up at that time.
You should either set the mode back to Simple, or back up the transaction log regularly.
Simple only allows you to restore from the most recent backup, IIRC.
The Bulk-Logged mode behaves like Full for normal transactions, but like Simple for bulk insert operations via the bcp tool or the BULK INSERT statement.
|
|
|
|
|
I am writing an application that needs to connect to an Oracle 9i database, select rows from one table, and insert some in another. It needs to be easily deployable. The goal is to have an installation program that is able to setup anything the user needs, coming from a fresh install of windows (win2k and XP are the only ones I’m worrying about supporting). This includes setting up the .NET runtime, Oracle data provider, and oracle client if necessary. This is just a simple application that automates some daily tasks people do around the office here.
I started trying to use Microsoft's Oracle data provider that ships with the 1.1 SDK. This works fine on a machine with an oracle9 client installed, but I need to be able to run this without installing a client - or be able to automatically install the client when installing my application. I'm not sure if this is possible - can anyone help?
Thinking the above might not be feasible, I looked into Data Tech's Data Providers: http://www.datadirect-technologies.com/products/dotnet/dotnetrelhigh.asp this worked great and you didn’t need an oracle client installed on the client PC. HOWEVER, the licensing scheme for this product is less than ideal, and you have to pay per client license. That just isn’t something we want to worry about (and they don’t offer unlimited use). Furthermore, you still need to install their data providers through their installation program, so really I'm not gaining what I really wanted from their product.
Are there any other options? Am I making myself clear? I just want to write a program that connects to an oracle database, without having to worry about what the client has or doesn’t have on their pc (though if I can automate a client install of oracle, that would work). Like Oracle's thin driver for java. Any help would be appreciated.
Thank you
Dave Ratti
|
|
|
|
|
If you have the oracle client installed, you can peruse the Oracle Universal Installer Concepts Guide Release 2.2 (Part Number A96697-01), particularly the section in Chapter 3 on creating and using a response file. Essentially, you can run through the installer, having oracle installer keep track of your responses, then create an silent install using those parameters. You can start with a simple hardcoded install, then move toward customizing the response file. I've used installshield installs to let the user decide where to install oracle, then modify the RSP file accordingly. But it's definitely best to let Oracle handle the guts of the install.
HTH
On the road of life, there are passengers, drivers, and those guys with steamrollers.
|
|
|
|
|
In Windows XP Home Edition, when trying to list MSDE servers using C# and SQLDMO the list is empty. For instance, when doing
SQLDMO.ApplicationClass DMOApp= new SQLDMO.ApplicationClass();
SQLDMO.NameList names=DMOApp.ListAvailableSQLServers();
I get names.Count=0.
Can anybody point me in the right direction?
|
|
|
|
|
hi 
i have a declared a string which i want to write to a text file
using the bcp command
i have so far
declare @String varchar(300)
exec master..xp_cmdshell 'bcp @String queryout @String -o z:\callparent\text.txt'
which is not working 
does ne1 have any idea's im not that familar with bcp
thanks
si
|
|
|
|
|
If you are trying to use the contents of the string @String in your BCP command, you need to do the following:
declare @cCmd varchar(400)
declare @String varchar(300)
-- populate @String....
select @cCmd = 'bcp ' + @String + ' queryout ' + @String + ' -o z:\callparent\text.txt'
exec master..xp_cmdshell @cCmd
onwards and upwards...
|
|
|
|
|
Hi,
How do I get all database name in SQL server 7?
Thanks.
H.Dung
|
|
|
|
|
SELECT name FROM master..sysdatabases
|
|
|
|
|
Is there an easy way to "archive" a database so that a user can see what has changed since the last time it was modified...while also tracking all previous modifications on it as well? The contents of this database would change very rarley.
Thanks
|
|
|
|
|
Probably the easiest way would be to add a time stamp to the record, not backup the entire database.
|
|
|
|
|
Got an application here at work on Access, and its getting large, so I wanted to go with SQL Server 2000.
Now from one group that installs the servers has blocked us, saying its not approved. Funny, becuase that group was outsourced to IBM.
Never the less, we now have to port just the database to Oracle or DB2, and can't make up my mind. The business logic and client apps will stay the same. Only heard horrible things about Oracle, and as for DB2, we only use it here on one of our mainframess. What I'm worried about is any funnies when using ADO to connect to these. I know its fine with SQL Server as I've already ported the DB.
Opinions please!!
"Je pense, donc je mange." - Rene Descartes 1689 - Just before his mother put his tea on the table.
Shameless Plug - Distributed Database Transactions in .NET using COM+
|
|
|
|
|
Well, being that Oracle does stand for One Real Asshole Called Larry Ellison, I would normally advise against it; however, there are specific ADO.NET classes for Oracle (Oracle .NET) so it might be your best option. You could still connect to the DB2 through the OleDb classes in ADO.NET, but then you're limited to what OLE DB can do (more specific, what the OLE DB provider provides ADO). Usually, this is only basic - but that may suit your needs. The mainframe thing is also a problem for DB2.
My vote is for Oracle (since you can't use SQL Server, which obviously has the best support in .NET).
Good luck!
Reminiscent of my younger years...
10 LOAD "SCISSORS"
20 RUN
|
|
|
|
|
I'll warn you from our experience: give the machine LOTS of memory.
While in a slow period (we're a custom software house, for the most part), we tried out both Oracle's Developer Edition equivalent and DB2's equivalent on, admittedly, a fairly old spec test machine - Pentium 200 with 192Mb of RAM, if memory serves.
SQL Server 2000 (Developer Edition, typically) on this box isn't exactly fast, but it's certainly competent.
Oracle and DB2's user interfaces - the equivalent of Enterprise Manager - are both written in Java and a bit quirky and slow.
Obviously the first thing to try with any new database software is to create a new database.
SQL Server takes about eight seconds. All it has to do is make a physical copy of the model database.
DB2 took about half an hour.
Oracle took all night, ate the entirety of system memory and swap file, swapped to hell and back (we had to kill off some processes to relieve pressure on the swap file), and ultimately failed because the system was using DHCP and had to renew its lease. This caused the connection between the client software and the server - running on the same machine - to break. The client tools couldn't recover.
If you go with DB2, you'll have to install Visual Studio 6.0 on the same system if you want to use stored procedures - DB2 translates your SQL into C, then compiles the C code into a DLL which gets loaded at run time.
For Rapid Application Development, SQL Server is streets ahead.
If you don't need to support many users, I'd go with the SQL Server Desktop Engine[^] (also known as MSDE), which is free if you have a license for Visual Studio .NET, or many other products. See Microsoft's Appropriate Uses FAQ[^]. MSDE has a query governor built in that seriously affects performance if more than five batches are executed simultaneously. The latest download already includes Service Pack 3.
Traditionally SQL Server has been a little more susceptible to security issues - searching for 'microsoft sql server' at http://cve.mitre.org/cve/[^] yields 56 results, while searching for DB2 only finds 3 issues. Searching for 'oracle' produces 70 results. There are probably some false positives in these results.
--
Mike Dimmick
|
|
|
|
|
Wow, that long to create a new database? Ouch. Our Internet-based solution creates a new database for each new corporate account so that each company / branch has their own database. This is done from a template and done as soon as its requested (after some security measures). Good thing we use SQL Server! 
Reminiscent of my younger years...
10 LOAD "SCISSORS"
20 RUN
|
|
|
|
|
Well, it's possible that we selected the wrong options, that caused it to be really slow. I seem to recall that we tried looking for online help, and found that the online help viewer (not HTML Help, of course :P) was modal to the application.
No, you can't read the help while using the app, why would you want to do that? 
--
Mike Dimmick
|
|
|
|
|
Reading help while trying to solve the problem would just be stupid and unnecessary. Of course Larry Ellison would take that option away. Besides, Oracle is self-explanitory and really requires no help to use!
I dunno, even if you did pick the wrong options, that still seems like a long time. SQL Server comes back within a second or two and pokes fun at your bad choice in preferences! But maybe that's just Oracle's greatness hard at work.
Oh, and DB2 is an ancient relic that is steps away from both SQL Server and Oracle. It doesn't really suprise me.
Reminiscent of my younger years...
10 LOAD "SCISSORS"
20 RUN
|
|
|
|
|
*sigh*
Ok. You will have some problems with porting either way. SQL servers view of what a DB vs tablespace vs schema is is completely different from Oracle or dB2. Oracle and DB2s are more traditional style which is inline with the SQL workingroup specs. MS did their own thing. It's simple to manage but being compeltely different form eveyone else it makes porting not be a no-brainer.
that's just off the top of my head. there are a number of other differences that will depend on your sue of various featuresets that could also impact you.
As for the time to create/ work on the db that was posted...
Oif. Ok, I can't speak for DB2 because I've not done anything more than read most fo the docs. Most of what I am going to mention apply to DB2 though. In the case of Oracle you hit at least two, maybe three problems. One, I beleive the machine you mentioned is WAY under the mins for Oracle. The base memory footprint for Oracle is arround 200M for a small db so you're swapping constantly. CPU matters to Oracle, but memory is its usually bottleneck. Second one you selected probably to use a started db. In other words the Installation created a db for you. all though these template dbs are ok, they arenot optimized in any way and tend to install ALL the Oracle options. Unless you want to be able to compute the GPS location of a guy standing on the corner in the capital of zimbabwe based on his Zipcode and what kind of cofee he drinks, you loaded WAY too much. Each option increases the memory and CPU requirement, in some case drastically. The other thing I think you probably ran into is, you put all the Oracle Datafiles on one or two physical disks. Oracle is very disk intensive. Putting everythign on a couple disks is a nightmare to Oracle IO -wise. It will just chug. Most Oracle Dbs, even small ones, are laid out on Combined Raid 5 and raid 0/1 disks depending on file type. You can put it on one or two disks but you will pay a performance penalty.
For example, I created a raw Oracle Db consisting of 6 datafiles, 3 redo groups and loaded the data dictionary in about 30 mins on a 600Mhz machine with 512Meg fo Ram.
Please don't compare the tools to the RDBMS engine. The RDBMS engine is very robust and up to most challenges you can throw at it. Their tool selection, on the other hand, is aweful. Oracle bit hard on java ad continues to do so and they haven't figured out a good way to build a toolset that is competitive with third party makers. No ONE that I know of in my area uses the offered tools from Oracle. We all use third party tools whether is somethign we write, something we buy (DB Artisan, TOAD, etc) or in worst case resourt to SQL*Plus which we can all use in a pinch.
Anyay, I'm not gonna bash SQL Server. It's a decent product with its own market and its own faults. Oracle has the same situation.
Mark Conger
Sonork:100.28396
|
|
|
|
|
I need to create an ODBC connection to a remote server (my webserver).
I am trying to populate a combobox from a DB query, but the DB resides on a remote machine.
The remote machine is running NT 4.0
My development machine is Windows98
Is this possible?
TIA!
I'm going to live forever or die trying!
|
|
|
|
|
tnolley wrote:
the DB resides on a remote machine
I'm assuming you are using MS Access, and that this is an ASP App, And that your are running Peer Web Services on your Win98 W/S.
You can share out the folder that the DB resides in:
\\servername\sharename\accessdb.mdb
You can then build your connection string by the following:
<code>Dim ConnectionToDb
Dim rsToDB
Set ConnectionToDb = Server.CreateObject("ADODB.Connection")
Set rsToDb = Server.CreateObject("ADODB.Recordset")
ConnectionToDb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=\\servername\sharename\accessdb.mdb;" &_
"Persist Security Info=False"
rsToDB.Open "TableName", ConnectionToDb, adOpenForwardOnly, adCmdTable
While Not rsToDb.EOF
Response.Write rsToTb("FieldName")
rsToDb.MoveNext
Wend
rsToDb.Close
ConnectionToDb.Close
Set rsToDb = Nothing
Set ConnectionToDb = Nothing
Tony
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
