Using Password Ending In Unmatched Double Quote Mark With OleDbConnection
Mar 14, 2008
Issue
I need to write VS2005 C# code using SQL OLE DB to access SQL Server 2005. I have no choice in that matter. I can create a database user with a password like COMPANY", which is a string of uppercase characters ending in an unmatched double-quote mark.
Using Microsoft SQL Server Server Management Studio I login using Windows Authentication, create an account with the password, COMPANY", check the "Enforce password policy", click "OK", and then exit.
Setup
I launch Microsoft SQL Server Server Management Studio again, select "SQL Server Authentication", type in the account name and the COMPANY" password, click the Connect button, and I'm in.
Problem
Now, I need to connect programmatically and run a stored procedure. The password is stored in clear text in hte Registrynot my choice, it's a legacy application, and changing that is not an option open to me. (We have probably all seen company safes where the combination is scribbled on the wall in case you forget it!)
If I set the connection string in the registry to COMPANY", I get an error like this:
Server Error in '/' Application.
Response is not available in this context.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Web.HttpException: Response is not available in this context.
Source Error:
Line 60: catch (System.Exception e)
Line 61: {
Line 62: Response.Write(e.Message.ToString());
Line 63: }
Line 64:
Source File: C:sourceProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs Line: 62
Stack Trace:
[HttpException (0x80004005): Response is not available in this context.]
System.Web.UI.Page.get_Response() +2077605
SiteIQWeb.CommonCode.Common..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebCommonCodeCommon.cs:62
SiteIQWeb.MasterPage..ctor() in C:ProductNameDataCenterAdm&ReportsWebPagesProductNameWebMasterPage.master.cs:21
ASP.masterpage_master..ctor() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot 255b545c8a400c7App_Web_hfj8popy.0.cs:0
__ASP.FastObjectFactory_app_web_hfj8popy.Create_ASP_masterpage_master() in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot 255b545c8a400c7App_Web_hfj8popy.3.cs:0
System.Web.Compilation.BuildResultCompiledType.CreateInstance() +49
System.Web.UI.MasterPage.CreateMaster(TemplateControl owner, HttpContext context, VirtualPath masterPageFile, IDictionary contentTemplateCollection) +250
System.Web.UI.Page.get_Master() +48
System.Web.UI.Page.ApplyMasterPage() +18
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +685
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
The closes to success I have been able to come is to set the password to "COMPANYNAME""" or 'COMPANYNAME"', which results in a System.Data.OleDb.OleDbConection Exception with a HResult of 0x80040e4d, and the message Login failed for user 'username'.
Questions
Can the password be formatted differently in the registry, or somehow processed after retrieving it, so that SQL Server 2005 will accept it?
Is this a bug in .NET Framework or SQL OleDb?
Is this simply a case of "Is it hurts, don't do it"?
Thanks in advance. As with all postings, my job, promotion, product success, company future, or some combination thereof is on the line.
In my asp.net page when I run a query against the database the datagrid get populated with only the records that starts with the first letter for example A good day. But none of the records that starts like this "A fine day" or "A nice day" displays in the datagrid. I tried to replace the Double Quote but it still returns only the records without " quotes. My query looks like this: Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like @ArticleHeading) ORDER BY [Articles].[ArticleHeading]"
Some of my records starts with a double quote, when I do a search for all records starting with A I can get all except the ones that start "A fine day ...
I can't figure out what is causing this error: can you please have a look at my code and let em know what is wrong or rework it for me:
="SELECT ARIBC.cntbtch, ARIBC.btchdesc, ARIBC.AUDTUSER, ARIBC.AUDTDATE, ARIBC.AUDTTI, GLPSOFTMAP.PSPRODUCT, GLPSOFTMAP.PSDEPT, GLPSOFTMAP.PSACCOUNT, GLPSOFTMAP.ACCTID, ARIBH.CODECURN, ARIBH.INVCDESC, CONVERT(varchar,ARIBH.FISCYR + ARIBH.FISCPER) as PERIOD, CONVERT(varchar,ARIBH.IDCUST) AS ACCOUNT, ARCUS.NAMECUST as ARNAME, CASE TEXTTRX WHEN 1 THEN 'INV' WHEN 2 THEN 'DM' WHEN 3 THEN 'CM' END AS CLASS, CONVERT(varchar,ARIBH.IDINVC) AS TRX_NUMBER, (CASE TEXTTRX WHEN 3 THEN ROUND((ARIBD.AMTEXTN*-1),2) ELSE ROUND((ARIBD.AMTEXTN),2) END) AS ""AMOUNT FROM ARIBC"" INNER JOIN ARIBH ON ARIBC.CNTBTCH = ARIBH.CNTBTCH INNER JOIN ARCUS ON ARIBH.IDCUST = ARCUS.IDCUST INNER JOIN ARIBD ON ARIBH.CNTBTCH = ARIBD.CNTBTCH AND ARIBH.CNTITEM = ARIBD.CNTITEM INNER JOIN GLAMF ON ARIBD.IDACCTREV = GLAMF.ACCTFMTTD INNER JOIN GLPSOFTMAP ON ARIBD.IDACCTREV = GLPSOFTMAP.ACCTID WHERE ARIBC.BTCHSTTS=3 AND ERRENTRY <= 0 AND BTCHDESC NOT LIKE '%54-8003%' AND ARIBH.IDINVC in ('" &Replace(Parameters!invoicenum.Value,",","','")"
Hi , I'm using sql server 2005. In my 'Books' table some of the 'subject' column contains data's with single and double quote. example: Quantu"m phys'ics i'm passing the subject as parameter and it have to display results if the corresponding subject name is in the table. Here is my code... i'm using dynamic query... alter procedure getbooks @sub varchar(200) as begin declare @sqlq nvarchar(2000) set @sqlq='select subject from books where subjectname like '''+@sub+'%''' exec sp_executesql @sqlq end How can i search for words like this Quantu"m phys'ics in the above query? Any one who knows how to do this please send me the code..
i am running sql server 2000 on windows 2000. i have a need to export a view and delimit the columns with double quotes as it has imbedded commas in the columns, how do i do this??
We have an issue with importing a CSV file into SQL where using a double quote " text qualifier is failing. The data is correct but it fails on a particular line, complaining about the qualifier even though the qualifier is in place and previous lines have imported fine.
0 AS SalaryMin, 2088 AS SalaryMax, 2088 AS BillableHours, 'Month' AS SalaryPaidCode, 0 AS SalaryBreakdownHourly, 0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output 0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.
Query example:
Select * from myTable Where item like '%FSC%'
This works great with the exception of when FSC is surrounded by double quotes.
I've a weird problem in my application. In of the pages, while trying to update the text box "Name", when I enter Linda's test, it gets saved as Linda''s test. I'm not sure if this is a problem due to SQL server. When I look at the stored procedure, I don't anything different. Also, when I update the table directly in SQL Server, the result is displayed in single quote. But if I update the field thro' the application, the returned name is with double quotes instead of single quote. Has any of you faced problems like this? What am I missing? What do I need to do to get the name saved the way I entered (with single quotes) instead of double quotes?
I have scoured the Microsoft forums and the internet to find out how I can generate the output of a CSV report that has double quotes around each value and is comma separated as follows:
"Abcd","123456","Efghi","789012","JKLMN"
If I try to concatenate double quotes around the values in the stored procedure or in the RDL, two double quotes appear around each value as follows.
I understand that this is because the default qualifier is double quote. What I see is that every time a double quote appears in a value (along with commas and line breaks), the qualifier will activate. Is there any way to turn this off for double quotes?
If I try to enter: <Qualifier>false</Qualifier>, the word "false" appears as the qualifier instead.
The only way I have found that produces a result similar to what I need ("Abcd","123456","Efghi","789012","JKLMN") is if I add a line break - chr(10) in the RDL in each field. However, this won't work for me because I can't have line breaks in each field in the output.
Note that in SSRS 2005, I was able to produce the report output as I state above by setting the field delimiter and qualifier as follows:
This essentially turned the field delimiter and qualifier off, as the values entered would never appear in the data. I then could add double quotes and commas in the RDL. This used to work in the old version but does not anymore.
I'm having trouble importing a CSV file into SQL using SSIS. The trouble seems to stem from truncation and from quote encapsulated fields.
Firstly it's worth noting that some of the data within the quotes contains the separator. E.g.
12,"some text, and a comma",34
Thankfully SSIS seems to cope with that by specifying " as the Text Qualifier. My next problem was that the SSIS Import and Export Wizard gave an error: "failed because truncation occurred". But I fixed this by specifying the OutputColumnWidth for the NCHAR and NVARCHAR type columns to be the width from the table definition.
But now I have another problem with the length of fields. Consider the following where the center column is NCHAR (22)
101,"some text, and a comma",303 102,"some ""quoted text"" bye",303
The first row has the correct width (once the encapsulating quotes are stripped out), but the second row does not, because it seems that when I exported the table the export wizard escaped the quote characters within encapsulated strings using quote characters. So I am back to getting the "truncation occurred" error. How can I get around this within SSIS?
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
How much of a performance difference is there between connecting to SQL Server 2000 using OleDbConnection or using SqlConnection?
The reason I'm asking is I am taking on the task of updating an older program that uses a Access Database to use SQL Server, but it has a Database Utility class that uses OleDbConnection. I'm just debating whether it would be worthwhile to upgrade the class to use the SQL objects rather than Oledb. Program does a lot of update and insert of invidual records, and a few select statements that usually return from 1 to 2000 records up to a maximum of 50,000 records
I'm trying to connect to a MSDE database using the following code:
'set up connection dim myConnection as new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=LearnASP_DB;Integrated Security=SSPI;Connect Timeout=30")
'open connection dim myCommand as new OleDbDataAdapter("select * from tblUsers",myConnection)
'fill dataset dim ds as DataSet = new DataSet() myCommand.Fill(ds, "tblUsers")
And I'm getting the following error:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Hi, I want to create a connection to a SQL Server with SQLConnection. When I setup the SqlConnection through the Properties and Test Connection, everything seems to be good. However when I run the code sqlconnection1.Open(), it will give me the error of "Server does not exist or access denied". I have tried with the OleDbConnection (SQL Server and Access) too, still had not luck. Anyone had any idea what's wrong? Thanks.
Please note that I tried the ADOBE.Connection (VB 6) to the same SQL Server and Access through ODBC and it didn't give me any problem.
I am running Win2000 Pro and have both Visual Studio 6 and Visual Studio.Net installed
I've just installed SQL Server 2000 on a new machine, and fail to connect it using SqlConnection, the error message is "Database does not exist or access denied". But I can connect it with a OleDbConnection without any problem. The connection string for that SqlConnection works perfectly with my old machine. Could anyone give some advice? Thanks in advance.
I want to get the names of the databases of the server using OleDbConnection in c# which i have to add in to a combobox.....the same task i achieved using SqlConnection where in there is a Class SqlClientCollectionNames...the code is as followsusing (SqlConnection connection =new SqlConnection("Data Source=server;User ID=username;Password=passwd")) { connection.Open(); DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases); foreach (DataRow row in myData.Rows) comboBox1.Items.Add(row[0]); connection.Close();} but the same i did for OleDbConnection it is not working using (OleDbConnection connection =new OleDbConnection("Provider=SQLOLEDB;Data Source=server;User ID=username;Password=passwd")) { connection.Open(); DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);//no such method Databases present in OleDb namespace foreach (DataRow row in myData.Rows) comboBox1.Items.Add(row[0]); connection.Close(); } is there any other way to do this using OleDbConnection........??Please help
I was had just finished creating the architecture for a test databse using sql express 2005 and have been able to get the browser and sql service started just fine. However, When I try to create an oledbconnection or oledbadapter to the database using the Microsoft SQL Server engines I get the following error: I have tried using a direct path to the database in the server name and the computer's name itself. Everything is on the local machine and I have not started remotely connecting. [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied. I am using windows authentication and have also specified the direct patht to the database in the attach database file: Thanks for the help.
The following stored procedure is returning the correct row(s). But I am not sure it is correct. In the stored procedure I need the variable passed in (@lotnum) in the dbo.tblBag_data compare with dbo.tblBag_results and return unmatch rows from dbo.tblBag_data. Basically I need the unmatched records in dbo.tblBag_data between two tables. For example there are five records. In dbo.tblBag_data 1st record contains lotnum(1000), bag1. next record contains lotnum(1000), bag2. .. last record contains lotnum(1000), bag5. In dbo.tblBag_results there are two bags. I need the three records that do not match up. Thanks!
CREATE PROCEDURE sp1 AS
declare @lotnum int
set @lotnum = 4056; SELECT dbo.tblBag_data.work_ord_num, dbo.tblBag_data.work_ord_line_num, dbo.tblBag_data.bag_num, dbo.tblBag_data.lotnum, dbo.tblBag_data.bag_status FROM dbo.tblBag_data WHERE dbo.tblBag_data.lotnum = @lotnum AND dbo.tblBag_data.bag_num not in (select dbo.tblBag_results.bag_num from dbo.tblBag_results Where dbo.tblBag_results.lotnum = @lotnum) GO
Im pretty new to SQL so i was wondering if you might be able to give me a hand with something.
I have 2 tables, clients + orders. Each table contains a column called custID which contains the client code, each order creates a new record in the orders table so the orders table contains many more records than the clients table.
I am trying to find out what clients have not placed orders at all, but i can't wrap my head around it, i can obviously work out who HAS, but i need to find out who HASN'T.
I have to build a query and I am not sure how to build it.
Our data has providers with different products. We have an “DGH” product and a “PRM”. I need to find the provider that don’t have both. Its sounds very simple, but I am having an awful time figuring it out. Sometimes I get a record that has both products, it is probably how my where clause is working.
Anyway, here is an example of what I don’t want in my records because this is good. It has a DGH and PRM.
pro_pcs pro_lname pro_fname prop_prd pro_tax1 pro_addr100018791 Smith John DGH 12345868 Kiem ST 00018791 Smith John PRM 12345868 Keim ST
I would want to see this one because there only a DGH and it need to have a PRM product.
pro_pcspro_lname pro_fname prop_prd pro_tax1 pro_addr1 00018791 Rubin SamDGH2345868 Kiem ST
I came up with this below and it seems to be working. The only thing is, I now have to make this so people can add a parameter on the pro_tax1 to see the different records. Is there a better way to do this or how can I use what I have below and add a parameter too it.
Select pro_pcs, pro_lname, pro_fname, pro_addr1 from #ALLProducts Where pro_tax1 = '12345868' and prop_prd = 'DGH'
except
Select pro_pcs, pro_lname, pro_fname, pro_addr1 from #ALLProducts Where pro_tax1 = '12345868'and prop_prd = 'PRM'
I would like to build a query that will return all the records in Table1 that will not match with records in table 2. All colums in table 1 have NULL values. Only one column is populated with state abreviations.
SELECT nvl(field1, 0), field2, field3, nvl(field4, 0), nvl(field5, 0), nvl(field6, 0) FROM TBL1 ------------------------------------------- Result: Record count 3000.
Only field3 is populated everything else is null.
select field3 from tbl1 group by field2 - record count = 48
Hi folks. Thanks for the opportunity to get a bit of help here.
I've got two tables. Say, table "foo" and table "bar". I LEFT JOIN them on a key, say, foo.id and bar.fooid.
For some reason, when I do this join, I get only records from "foo" which have a mate in "bar".
What I really need is all records from "foo", but the columns from bar when there is a match. I don't want to exclude records from "foo" just because they don't have a match from "bar".
I need to list all the records in Table2 which don't have matching field values in Table1.
This the the exact opposite of what I need: SELECT DISTINCT Field1, Field2, Field3, Field4, Field5 FROM [Table1] WHERE EXISTS( SELECT DISTINCT FieldA, FieldB, FieldC, FieldD, FieldE FROM [Table2] )
The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.
What I need, however, is the exact opposite.
I tried the above using NOT EXISTS but I get no records at all.
The category table has columns [CategoryId], [CategoryName], [TotalCategoryRiskScore] and the lu_category has columns [CategoryId], [CategoryName].
I want a sql query that will list all values from lu_category table and category table and if a categoryid is not available in lu_category table but available in category table, i need that too in the result.
Below is the screenshot of the data and my desired output
