|
From the OleDbCommand.CommandText documentation:
"The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL Statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used."
If you're talking to SQL Server (7.0 or 2000), use the SqlConnection and SqlCommand classes instead. Otherwise, replace the named parameter in the command text with a ?.
|
|
|
|
|
Thanks for the info, I was still wondering how it was done with OleDb...thanks.
|
|
|
|
|
Hi-
I'm a real beginner, so be gentle with me.
I'm trying to write a store procedure that basically takes value from Database A and insert it into Database B. Can someone give me an example of how to do this please?
Thanks! 
|
|
|
|
|
You need to do two things.
1. Make sure the user the stored procedure is using has access to the other database
2. In your TSQL statement, reference the other database and owner
Here is an example:
Two databases, Bob and Bill. In Bill there is a UserGroup table owned by DBO that I want to insert a record into, from Bob.
So INSERT INTO [Bill].[dbo].[UserGroups] ([Title], [Description]) VALUES ('title', 'description')
The only difference being the [Bill].[dbo] bit.
regards,
Paul Watson
Bluegrass
South Africa
Brian Welsch wrote:
"blah blah blah, maybe a potato?" while translating my Afrikaans.
Crikey! ain't life grand?
Einstein says...
|
|
|
|
|
There seems to be a problem filter records from a dataset by date when the date spans a year.
This produces the expected results
string strFilter = "date >= '1/1/2003' AND date <= '12/31/2003'; <br />
DataRow[] rows = ds.Tables[0].Select(strFilter);
This does not
string strFilter = "date >= '1/1/2003' AND date <= '1/1/2004'; <br />
DataRow[] rows = ds.Tables[0].Select(strFilter);
Anyone know what is going on here or how to work around it?
|
|
|
|
|
I see what is going on now. The dates being compared as strings (dah!! ). I needed to write some code to convert and compare the dates as dates.
|
|
|
|
|
I'm using the followng code to access an Oracle database procedure. All works well until the ExecuteNonQuery() member is called. It throws an exception with the Oracle error message of "ORA-01008: not all variables bound".
try
{
m_ReturnRate = -1.0;
m_FXConn = new OracleConnection();
m_FXConn.ConnectionString = "Server=XXXXXX;User ID=XXXXXX; Password=XXXXXX";
m_FXCommand = m_FXConn.CreateCommand();
m_FXCommand.CommandType = CommandType.StoredProcedure;
m_FXCommand.CommandText = "get_currency_forward_rate( :TRADECURRENCY, :SETTLECURRENCY, :VALDATE, :SETTLEDATE, :STATUS, :CALCRATE )";
m_parmTradeCurrency = m_FXCommand.CreateParameter();
m_parmTradeCurrency.ParameterName = "TRADECURRENCY";
m_parmTradeCurrency.OracleType = OracleType.VarChar;
m_parmTradeCurrency.Direction = ParameterDirection.Input;
m_parmSettlementCurrency = m_FXCommand.CreateParameter();
m_parmSettlementCurrency.ParameterName = "SETTLECURRENCY";
m_parmSettlementCurrency.OracleType = OracleType.VarChar;
m_parmSettlementCurrency.Direction = ParameterDirection.Input;
m_parmValuationDate = m_FXCommand.CreateParameter();
m_parmValuationDate.ParameterName = "VALDATE";
m_parmValuationDate.OracleType = OracleType.DateTime;
m_parmValuationDate.Direction = ParameterDirection.Input;
m_parmSettlementDate = m_FXCommand.CreateParameter();
m_parmSettlementDate.ParameterName = "SETTLEDATE";
m_parmSettlementDate.OracleType = OracleType.DateTime;
m_parmSettlementDate.Direction = ParameterDirection.Input;
m_parmStatus = m_FXCommand.CreateParameter();
m_parmStatus.ParameterName = "STATUS";
m_parmStatus.OracleType = OracleType.Int32;
m_parmStatus.Direction = ParameterDirection.Output;
m_parmCalculatedRate = m_FXCommand.CreateParameter();
m_parmCalculatedRate.ParameterName = "CALCRATE";
m_parmCalculatedRate.OracleType = OracleType.Double;
m_parmCalculatedRate.Direction = ParameterDirection.Output;
m_FXConn.Open();
Console.WriteLine( "OPENED" );
m_FXCommand.Prepare();
Console.WriteLine( "PREPARED" );
}
...
...
...
try
{
m_parmTradeCurrency.Value = CurrencyTrade;
m_parmSettlementCurrency.Value = CurrencySettle;
m_parmValuationDate.Value = ValuationDate;
m_parmSettlementDate.Value = SettlementDate;
m_parmCalculatedRate.Value = -1.0;
m_parmStatus.Value = -1;
m_FXCommand.ExecuteNonQuery();
Console.WriteLine( "EXECUTED" );
if ( m_parmStatus.Value.Equals(0) )
{
m_ReturnRate = (double)m_parmCalculatedRate.Value;
}
}
The first try block is in a constructor that works fine. The second try block is in a method that is passed values to assign to the parameters. Have I forgot to call some Bind method that I can't locate or is there some simpler answer to what's going wrong? Thanks.
Chris Meech
It's much easier to get rich telling people what they want to hear. Christopher Duncan
I can't help getting older, but I refuse to grow up. Roger Wright
I've been meaning to change my sig. Thanks! Alvaro Mendez
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
|
|
|
|
|
Turns out that I made an assumption that is incorrect.  It seems that just cause you have an OracleCommand object create an OracleParameter object for you (instead of using new OracleParameter), it doesn't add the parameter to its parameter collection. For some reason, you still have to do that. So I've added the following
m_FXCommand.Parameters.Add(m_parmTradeCurrency);
m_FXCommand.Parameters.Add(m_parmSettlementCurrency);
m_FXCommand.Parameters.Add(m_parmValuationDate);
m_FXCommand.Parameters.Add(m_parmSettlementDate);
m_FXCommand.Parameters.Add(m_parmStatus);
m_FXCommand.Parameters.Add(m_parmCalculatedRate);
and things seem to be working fine.
Chris Meech
It's much easier to get rich telling people what they want to hear. Christopher Duncan
I can't help getting older, but I refuse to grow up. Roger Wright
I've been meaning to change my sig. Thanks! Alvaro Mendez
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
|
|
|
|
|
Dear Friends,
I have a SQL Server 2000 database named test. The database has 2 files: 01 data file (test_Data.mdf) and 01 log file (test_Log.ldf). But the log file has been lost. Please help me on how to recover the database from the data file only.
Thank in advance.
|
|
|
|
|
I am doing some research and would like to know if any fellow DBAs out there or programmers support Best MS500 ERP at your organization.
Could you tell me :
1) Can you code it in a standard language such as VB or C?
2) How well do you like supporting it
3) Anything you can tell me in your experience supporting it which may be helpful. Good or Bad
|
|
|
|
|
I am a DBA doing some research and would like to know if any fellow DBAs/programmers out there or programmers support Syspro ERP at your organization.
Could you tell me
1) Can you code it in a standard language such as VB or C?
2) How well do you like supporting it
3) Anything you can tell me in your experience supporting it which may be helpful. Good or Bad
|
|
|
|
|
i've been developing a program for a customer for recipe editing. I am new to .NET dataaccess and am having a problem. I open the DBF file, export it to an xml file so i can edit it (cause i know how to do that). Then when i'm done i want to import it back into the database. I have this working except for it duplicates every record in the table. Any help would be greatly appreciated.
Thanks for your time,
Jamie Lindemann,
Lindemann Electric Services Inc.
|
|
|
|
|
Could use some more information.
Do you want to Update or Insert the information that you are sending back?
Do you have a check that would tell you if a file exists so that it does not get re-inserted?
How is the XML string constructed that you are sending back?
Could you post some of you procedure?
Thanks
Will
|
|
|
|
|
Hi,
I would like to update any information in the database. I don't have a check to see if the file is being re-inserted.
Thanks for your time,
Jamie Lindemann,
Lindemann Electric Services Inc.
|
|
|
|
|
I recently learned a web site ( http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5 ) contains a working solution which decrypts stored-procedures in a database.
Does anyone know how to protect their stored-procedure source-code against this? ( It is not possible to set any security options in the database because it is a user-controlled system -- only the stored-procedures have been protected using "with encryption" to stop prying eyes and fool-hardy users with thoughts of making-it-better.
Thanks for any help or insights on this one. I'm truly concerned.
The main things are the plain things, and the plain things are the main things.
|
|
|
|
|
For those who don't know, you can specify WITH ENCRYPTION as part of a CREATE PROCEDURE statement to hide the query text. However, SQL Server needs to be able to decrypt it to parse the text.
Firstly, the reason it works is that MS have used a simple form of encryption - using the XOR with a key in order to obscure the text. This can be strong if the key isn't known. However, this is easily broken if you can force it to reuse the same key to encrypt text you already know - you can then XOR the trojan ciphertext with the known plaintext to get the key, then XOR the key with the original ciphertext to get the original plaintext.
It appears that the key is a random string of data. SQL Server probably stores the seed for the random number generator so it doesn't have to store the same amount of key as it has procedure text. The decryption methods works because SQL Server uses the same key to encrypt an alteration to the procedure, i.e. when you use ALTER PROCEDURE.
This kind of encryption, known as a one-time-pad, is actually very strong, if it didn't use the same key it already had. It's called a one-time pad because you're only supposed to use the key one time.
Kalen Delaney, in Inside SQL Server 2000, mentions that before WITH ENCRYPTION was added (in version 6), developers set the text column of syscomments to NULL. This works (or, at least, worked) because SQL Server doesn't (or didn't) use the query text directly when compiling an execution plan, it uses a pre-parsed version. However, the structure of this parsed version changed between 4.2 and 6; the installer for version 6 tried to re-parse the original query to generate the new structure. WITH ENCRYPTION allows the text to be hidden but still be upgradable if required.
Trying this on SQL Server 2000 (i.e. UPDATE syscomments SET ctext = NULL WHERE id = object_id( @objname )) in the default configuration returns the message 'Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.' You can enable in Enterprise Manager: right-click the server, choose Properties, the Server Settings tab, and check 'Allow modifications to be made directly to the system catalogs'. Click OK.
Setting the ctext field to the empty string does work, if an execution plan has been cached. However, as soon as the cache is spilled, the server is restarted, or a dependency changes (perhaps an index's statistics change significantly) SQL Server can't rebuild it - it looks like it parses the query text every time. So this isn't an option either.
I'm going to send a message to security@microsoft.com about the reused one-time pad; hopefully they'll release a hotfix. You could raise a call with SQL Server or developer support.
|
|
|
|
|
Thank you very much for such a thoughtful response. 
I will pursue giving the SQL Server team at Microsoft a support call and post my findings here.
|
|
|
|
|
Sorry, the correct link is: http://www.planet-source-code.com/URLSEO/vb/scripts/ShowCode!asp/txtCodeId!728/lngWid!5/anyname.htm
|
|
|
|
|
|
Here was the reponse to my inquiry at Bill's place ( slightly edited for brevity, clarity, and protection of innocent individuals 
1. Protecting a stored procedure and making it totally non-decryptable is technically impossible.
2. The Encryption SQL-Server is offering is obfuscation through a feature called "encryption".
-- Possible solutions --
1. Use extended stored procedures in a DLL.
2. Protect your asset like other data products do: obfuscation ( like IBM, Oracle, and Microsoft offer ), or legal protection (copy write, patents, etc).
|
|
|
|
|
hi
do any body has any know how of 'iff function used with select statement' in sql server 2000. plz help me out of this problem i'll b gratefull
khattak
|
|
|
|
|
CASE WHEN condition THEN true-value ELSE false-value END
|
|
|
|
|
Hi, I have an oleDbAdapter which read data from visual fox tables into DataTable! But unfortunately the encoding is different (or something else), so I get strange characters (not all but a few). In general I must get this text in appropriate encoding so it looks laike a normal Latvian text!
Does anybody know some solutions?
thanx anyway
|
|
|
|
|
As all know we use paging when we want to a void return a larg set of records over network
so the question is
How i know in advance if the query will return many records or not?
so using paging or not.
I know i can put some logic in my application to prevent the user to run query which my return
many record like select all,.......
but i can't know in all cases
thanks in advance
|
|
|
|
|
What comes to my mind is doing 2 querys.
The first one with COUNT statement, so you'll know how many records will the query have.
The second, the query that'll return the records.
Paging ?
If your application is Web, ASP.NET DataGrid provides Paging functions. I've used them and they worked fine, but my query never returned more than 200 records.
Free your mind...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
