We just encountered an odd failure in ADODB. If it gets an SQL query with a comment embedded in it, and the comment has an apostrophe as one of the characters, ADODB gets confused as it tries to plug in parameters for placeholders later in the query. For example:
SELECT id, name
FROM doc_type /* this won't work! */
WHERE name LIKE ?
If you take out the apostrophe ("this will not work!") or move the comment to follow the placeholder, the query works.
hi have written SSIS script and i am using script component to Row count below my code what i have written. and i am getting error below i have mention...after code see the error using System;
} the error Script component has encountered an exception in user code Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults) at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet) at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
I have an application which runs successfully on a couple of my customer's machines but fails on a third. It seems to fail when opening the database:
Unable to cast COM object of type 'ADODB.CommandClass' to interface type 'ADODB._Command'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{B08400BD-F9D1-4D02-B856-71D5DBA123E9}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false; Initial Catalog=lensdb;Data Source = SQL
Before I got this error I was getting another problem (sorry didn't make a copy of that error's text) that made me think that adodb.dll simply wasn't loaded/registered. I got rid of that error by copying my adodb.dll onto the third machine and running gacutil /i. There is now an entry in winntassemblies for adodb.
Just in case you think it could be an obvious registry problem: when I started getting the current error I thought that maybe the registry needed updating and I merged the following lines into onto the target machine (from my dev machine):
Hey guys, I have a Access adp that backs onto SQL Server 2005, its taken a while but tis nearly done.
Just one last thing to figure out.. and thats how to send a email. I had a look at all the Access ways and they just wont work out for this.
So I decided to use the server to send them, I would insert/update a row into a table which has a trigger on it.
The table has amoung other things a Approved and Declined column. Both at 0 Sends an email to an Approver App 1 Dec 0 Sends on further and App 0 Dec 1 sends back to the original sender.
All I really want to know is could a trigger execute a package or something that will send the email? (Yes thats right someone isnt asking for code for once:D meh must be winter in hell)
If theres any flaws in my idea please feel free to tell me (Saves me denting the desk with my head half a week and 10 pages of code from now)
And where do I create triggers:P (Will probly have found this one in BOL by the time anyone replys)
Sorry about this but I really have no clue about triggers.. seems the site doesnt like me searching for triggers 'This page was generated in 17.03 seconds.' :P
i have a comment column which is varchar(200). when a user types any say for example a couple of small paragraph with commas , colon, hard enter key.
when i run a select statement and save it to csv file and then open with excel, if the comments has comma then i get these info in separate column and if there is hard enter key then it goes into new line.
Is there a way to get these info in one column Thanks,
Plesae tell me the MSSQL Server equivalent of the below MySQL query .create table temp2(a varchar(23) comment 'male m');What is the use of specifying a keyword 'comment' in the column definition. Will it make any difference
I want to get the latest comment for each id based on the maximum createdDate and not sure how to do this, tried to do Max but it was still returning all the rows.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Audit]( [id] [nvarchar](64) NOT NULL,
I have looked everywhere for an answer for this one, so if it's out there and I've missed it, sorry.
I want to include a list of instructions with a report that I am sending out to a list of subscribers (not Data Driven, each is an individual subscription). It seems no matter how I enter the text in the Comments box, the body of the email comes out as a continuous stream of text with no line breaks, which is difficult for the recipients to read.
Is there a way to embed line breaks as a minimum, but preferrably additional formatting, in a subscription comment?
inserted a record with an identity field I had a '-- comment' right Select @data = @@identity
This select failed - @data was NULL I changed it to SET @data = @@identity This also failed @data was null
I then change to the follow which worked - Why would the different form of a comment make a difference? I was the only one in the database, there were no other changes. I could make the logic block fail or succed by just changing the comment syntax.
inserted a record with an identity field I had a '/** comment**/' right Select @data = @@identity
Hi there gurus, can you please add your 2 cents on this design? We'rehaving trouble relating these tables in a diagram because of the keys.Is it necesary to have the references setup? I would assume yes so theforign keys can be setup.If you look at this link, you'll see our diagram. In Red are therelationships that we would like to make for referential integrity, butcannot because of the keys.http://rullo.ca/linktome/QuestionsDB.jpgOur goal in all of this is to have a facility wherin we can store aquestion, that has multiple names over multiple Languages. Forinstance:-Q1| QNameID = 1 | "Do you have a dog in your appartment?" | LangID =1(eng)-Q1| QNameID = 2 | "Do you have a dog in your house?" | LangID =1(eng)-Q1| QNameID = 1 | "-French - Do you have a chien in your appartment?"| LangID = 2(fr)-Q1| QNameID = 2 | "-French - Do you have a chien in your house?" |LangID = 2(fr)The difficulty is when we try and put this in the group details table.We don't want to outline the Language, we'd just pass the language intoa proc to retreive a specific group with a specific language. If youfolks would be so kind as to add your comments to the design I would betruely grateful.CREATE TABLE [Question] ([QuestionID] [int] NOT NULL ,[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED([QuestionID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionAnswer] ([QuestionID] [int] NOT NULL ,[QuestionAnswerID] [int] NOT NULL ,[SystemName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_QuestionAnswer] PRIMARY KEY CLUSTERED([QuestionID],[QuestionAnswerID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionAnswer_Question] FOREIGN KEY([QuestionID]) REFERENCES [Question] ([QuestionID])) ON [PRIMARY]GOCREATE TABLE [QuestionAnswerName] ([QuestionAnswerID] [int] NOT NULL ,[QuestionAnswerNameID] [int] NOT NULL ,[LanguageID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,CONSTRAINT [PK_QuestionAnswerName] PRIMARY KEY CLUSTERED([QuestionAnswerID],[QuestionAnswerNameID],[LanguageID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionGroup] ([QuestionGroupID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,CONSTRAINT [PK_QuestionGroup] PRIMARY KEY CLUSTERED([QuestionGroupID]) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [QuestionGroupDetails] ([QuestionGroupID] [int] NOT NULL ,[QuestionNameID] [int] NOT NULL ,[QuestionAnswerNameID] [int] NOT NULL ,[QuestionSortOrder] [int] NULL ,[AnswerSortOrder] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[DisplayLevel] [int] NULL ,CONSTRAINT [PK_QuestionGroupDetails] PRIMARY KEY CLUSTERED([QuestionGroupID],[QuestionNameID],[QuestionAnswerNameID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionGroupDetails_QuestionGroup1] FOREIGN KEY([QuestionGroupID]) REFERENCES [QuestionGroup] ([QuestionGroupID])) ON [PRIMARY]GOCREATE TABLE [QuestionNames] ([QuestionID] [int] NOT NULL ,[QuestionNameID] [int] NOT NULL ,[LanguageID] [int] NOT NULL ,[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Desciption] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ControlTypeID] [uniqueidentifier] NOT NULL ,CONSTRAINT [PK_QuestionNames] PRIMARY KEY CLUSTERED([QuestionID],[QuestionNameID],[LanguageID]) ON [PRIMARY] ,CONSTRAINT [FK_QuestionNames_Question] FOREIGN KEY([QuestionID]) REFERENCES [Question] ([QuestionID])) ON [PRIMARY]GO
I am setting up a data driven subscription to send out an email which includes a report. The Sql for the data driven subscription outputs the EmailTo, Subject, Comment(Body) and some report parameters. My issue is with the Comment (Body) field which I would like to format with CR/LF to make the email body look neat and readable. I've tried many different permutations of inserting the CR/LF, From concatenating Char(13) + Char(10) in which the email does recognize the Cr/LF to concatenating binary 0x0D + 0x0A which after the first concatenation of binary nothing else can be concatenated. Here is the code I've tried: ---~~~~~~~~ declare @iEndDate datetime set @iEndDate = dbo.fn_today() Declare @Account_group int Set @Account_group = 999 /* DECLARE @mybin1 binary(5), @mybin2 binary(5) SET @mybin1 = 0x0D SET @mybin2 = 0x0A */ Declare @CrLf VarChar(5) set @CrLf = CHAR(13) + CHAR(10) Declare @Str1 VarChar(255) Declare @Str2 VarChar(255) Declare @Str3 VarChar(255) Declare @Str4 VarChar(255) Declare @Str5 VarChar(255) Declare @Str6 VarChar(255) Declare @Str7 VarChar(255) Declare @Str8 VarChar(255) Declare @Str9 VarChar(255) Set @Str1 = 'Good Morning,' Set @Str2 = 'We are exposed by ' Set @Str3 = 'Attached please find the margin call detail.' Set @Str4 = 'Please let us know if you have any questions.' Set @Str5 = 'Thank you.' Set @Str6 = 'Please respond to:' Set @Str7 = '' Set @Str8 = '' Set @Str9 = '' declare @t table ( EmailTo VarChar(255), ReplyTo VarChar(255), Subject VarChar(255), Comment Text, Account_group Int, Trading_Account_Id Int, Broker_Code_Ky VarChar(255), EndDate Datetime ) insert into @t select EmailTo = '', -- isnull(bf.firm_email,''), ReplyTo = '', Subject = 'Margin Call Notice: - ' + Trading_account_name + ' - Exposure: ' + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) , Comment = @Str1 + @CrLf + @CrLf + @Str2 + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) + '.' + @CrLf + @CrLf + @Str3 + @CrLf + @CrLf + @Str4 + @CrLf + @CrLf + @Str5 + @CrLf + @CrLf + @Str6 + @CrLf + @Str7+ @CrLf + @Str8 + @CrLf + @Str9, Account_group = @Account_group, Trading_Account_Id = trading_account_Id, Broker_Code_Ky = EGS.Broker_Code_Ky, EndDate = @iEndDate from fni_ExposureGovernmentSummary (@iEndDate) EGS join broker_group bg on EGS.broker_code_ky = bg.broker_code_ky Join broker_firm bf on bg.fbe_firm_id = bf.broker_firm_id Order by Broker_Group_Name, Trading_account_Name select top 2 * from @t
--~~~~~~~~~~~~~
The Email my company is using is Lotus Notes.
The Question is how do I code the the Comment field so that my email will recognize the Cr/LF.
I have a report where I have to display State, Category Name and the text. Below is the sample report
State Category Name Text
TX Unanticipated High NT due to RADD clean up- drive replacement.
NJ Unanticipated SAR efforts (SEM configuration)
But now my question is if there is no comment in any other state I would like to show no comment in the text line. Is there any formula I can use in the report level that will take care of the issue. I know in Business object there is a formula you can create which is =If (IsNull([Text]);"No Comments Reported";[Text]). I need to know in ssrs there is something.
please let me know ASAP and the steps of doing that.
I am running SQL Server 2005 SP2 and I am getting an SQL error due to the server not recognizing the "--" as a comment. (I am using the 32-bit version) The same code runs on SQL Server 2000 with no problem.
Is there a setting to correct this? Do I need to install the cumulative update pkg 3 to correct this problem?
i'm using delphi 7 and have a query in which i'm trying to find namesthat have an apostrophe in them, i.e. "o'mally". my problem is thatwhen i write my select statement i can't get the quotes right. i getall types of errors no matter what i try. i get "missing right quote","invalid token" etc. i've tried using QuotedStr and nothing works.here is my current attempt in which i get an "invalid use of keyword.token: n'%'"sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +' MiddleInitial, Indep, Book_number, Page_number,' +' CloseRec, TermBegins, TermEnds, DatePickedUp,FailedToQualify,' +' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order bylastname';
My God! What happens if I miss a step, or put it in a different order??? I know I can specify ORDER BY StepID, but what about a missing step? I do have "missing ranges" script, but posting questions filtered through the script process may become a full-time job in itself...What to do, what to do...
Hi,Is there anyone encountered this error before & how it is being resolved?[Microsoft][ODBC SQL Server Driver][SQL Server]Missing end comment mark '*/'The error pops-up when I was running a DTS Import/Export from a SQL server(source) to another SQL server (destination) residing on a differentmachine. I'm copying all tables, views, & stored procedures of a database.Thanks for any input.Regards,Maricel
I have a field that contains some text. Each field will have none or atleast one comment in it. A comment can be a string of any length with *** on both sides. Ex: ***comment***
Declare @Test_tbl Table(TextField Text) Insert Into @Test_tbl Select 'Some text ***comment*** some more text' Union all Select 'Other text ***another comment*** more and more text' Union all Select '***Comment*** some text ***More Comments***' Union all Select 'some text with no comment'
I need the output be ...
TextField -------------- Some text some more text Other text more and more text some text some text with no comment
Hi Friends, I have stored names with ' s (Apostrophes) and without 's in the database(sqlserver).Example:O'Relly I tried to get the values with select statement like Dim str As String = Request.QueryString("Title") Dim query As String = "SELECT * FROM Test where title like '" + str + "'" Problem is getting the all the values from database except O'Relly just getting O other word Relly no..... What should do I and what code do I need ..?
I tried with the below code also Dim x As Long For x = 1 To Len(str) If Mid$(str, x, 1) = "'" Then str = str & "''" Else str = str & Mid(title1, x, 1) End If Next
I need help with a simple query. We have 86 entries with the City of O'Fallon in our db. How do I do this with the apostrophe in O'Fallon? Below is just to give an idea of what I want. Thanks. SELECT * FROM Organization WHERE City=O'Fallon
Can anyone tell me how to replace a single apostrophe in a record with a double apostrophe (in Query Analyzer, SQL7)? I've tried "select replace("d'","d'","d''")FROM RESORT" but it doesn't UPDATE the table.
Ok, I still have some uncertainty as to just exactly how this whole apostrophe thing works with databases. I understand that it is a reserved character and so when a sql query runs into one of these creatures it looks at it as something other than a normal character.
I am working primarily in vb/asp/sql server with a little bit of access. I am familiar with the instrinsic 'Replace' function and I use it but I still have occassional problems.
I would like any information I can get on just exactly why/how this thing works and how to work-around the apostrophe when writing to, reading from, and validating data from sql server/access/any databases.
I am having weird trouble with READING data from my Access database where the field has an apostrophe - but only when I display the field in a textbox.
For example, the field "Don't do this" becomes "Don" when my SQL query outputs the result:
All- I'm a newbie working on a VWD project. A couple questions arose when attempting to work with a project's dataset and its underlying SQL Server Express database:
Is it true that when changing the schema of a database in SQL Server Express through the VWD Database Explorer, one must take care to manually make parallel changes in the dataset? For example, if you make a relation between two tables in Database Explorer, you must do the same with the counterpart tables in the dataset? For example, I find that if I delete or create a relationship between two tables in the database, I must manually do the same in the dataset.
