escaping single quotes

Hi,

I need to have an varchar value with single quotes. For eg: the below code throws compilation error.

Declare @.val VARCHAR(20)
SELECT @.val = ''+name+''
print @.val

Error: Invalid column 'name'

I want to print name enclosed with single quotes. Please guide me.

Regards,
SamDeclare @.val VARCHAR(20)
SELECT @.val = 'Kaiowas'
PRINT @.VAL

EDIT (added): SET @.VAL = @.VAL + '''Kaiowas'''
EDIT (added): PRINT @.VAL

SET @.VAL = @.VAL + '''' + @.VAL + ''''
PRINT @.VAL

escape single qoute

Is there a way to escape single quotes ' in a sql statement that uses a sql data source that is automated? I know you can do it with a string manip and replacing them with double single quotes. I am just looking for a simple way.

Thanks

Adam

If you are using parameters, you don't have to worry about single quotes in strings because SQL Server preserves the input data as it is.

If you aren't using parameters, you'll have to use two single quotes.

|||

You need to use two single quotes.

Errors using multiple parameters in a SQL Statement

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...|||

I used to have a lot of problems with that when the query was to big or complex (Sub queries, etc). To solve it, and I use it as a standard, I place the query inside of a variable and then use an expression to make it parameterized.

Here is how:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=756434&SiteID=1

|||

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

|||

Rafael Salas wrote:

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

Kirk Haselden comments otherwise in his SSIS book... (Page 182) Granted, that's for Execute SQL Tasks.

Now I guess I'll have to go test it myself as that's what I've always used.|||Either will work. "Parameter0" or just "0".

The issue is likely as Rafael pointed out if you are using a complex query.|||

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

|||

Mike Batton wrote:

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

Can you post your query?|||

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

|||

Mike Batton wrote:

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

Use question marks - always, and don't number them.

select * from tew_d_dt where dt_key = ? and cal_yr_id = ?

Then, in the parameter mappings, you can use "Parameter0" or "0" for the parameter name to map to your variable.|||

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

|||

Mike Batton wrote:

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

You mean to dynamically select columns? That would not be a good idea as it will change metadata.|||

What about in the case part of a select statement... should that work or not?

Below is part of the actual query that we're trying to get to work.

Mike

select 'ent' AS level_desc,

sku.new_concept_cd AS code_1,

sku.color AS color_name,

case when sls.date_key = ?

then

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) else 0 end as sales_lw,

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) as sales_life,

from edw.jjwhse.tew_l_stock_status_sku sku

left outer join

edw_jj.sales.vew_r_sls_sku_loc_wk sls

on sls.sku_key = sku.sku_key

where sls.date_key <= ?

and sku.sku_type_desc = 'sales'

and sls.loc_key not in (6,9,10,11,12,13,14,106)

and sku.new_concept_cd is not null

group by sku.new_concept_cd, sku.color, sls.date_key

|||

Mike Batton wrote:

What about in the case part of a select statement... should that work or not?

Try it... I don't know. If it doesn't work, then as Rafael stated, you can build your query first as an expression in a variable. Build it there first, then use that variable in the OLE DB connector.|||

Anything that changes the structure of the dataflow pipeline will not work; this is in the select part of the query: adding/removing columns; changing data types; changing column names, etc.

Perhaps if you provide an example of what you need to accomplish; somebody around here could help you.

errors

Hello everyone, i like to know if someone knows how to denied errors y SQL
server 2000, i have many webs over a single server and i do not want to show
errors in explorer when a bad user writes false urls intentionally trying to
inject code via sql. Or maybe that security i have to control via the code in
asp.
Thanks in advance
Trapping the error in the application is the best way.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Arturo J" <Arturo J@.discussions.microsoft.com> wrote in message
news:5B649D1E-2B9C-4293-A383-90F28FD1D8A8@.microsoft.com...
> Hello everyone, i like to know if someone knows how to denied errors y SQL
> server 2000, i have many webs over a single server and i do not want to
> show
> errors in explorer when a bad user writes false urls intentionally trying
> to
> inject code via sql. Or maybe that security i have to control via the code
> in
> asp.
> Thanks in advance
>
|||Thanks for your qick repond, just one doubt, you mean to manage the error
reports through the asp?
"Jason Massie" wrote:

> Trapping the error in the application is the best way.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "Arturo J" <Arturo J@.discussions.microsoft.com> wrote in message
> news:5B649D1E-2B9C-4293-A383-90F28FD1D8A8@.microsoft.com...
>
>

errors

Hello everyone, i like to know if someone knows how to denied errors y SQL
server 2000, i have many webs over a single server and i do not want to show
errors in explorer when a bad user writes false urls intentionally trying to
inject code via sql. Or maybe that security i have to control via the code in
asp.
Thanks in advanceTrapping the error in the application is the best way.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Arturo J" <Arturo J@.discussions.microsoft.com> wrote in message
news:5B649D1E-2B9C-4293-A383-90F28FD1D8A8@.microsoft.com...
> Hello everyone, i like to know if someone knows how to denied errors y SQL
> server 2000, i have many webs over a single server and i do not want to
> show
> errors in explorer when a bad user writes false urls intentionally trying
> to
> inject code via sql. Or maybe that security i have to control via the code
> in
> asp.
> Thanks in advance
>|||Thanks for your qick repond, just one doubt, you mean to manage the error
reports through the asp?
"Jason Massie" wrote:
> Trapping the error in the application is the best way.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "Arturo J" <Arturo J@.discussions.microsoft.com> wrote in message
> news:5B649D1E-2B9C-4293-A383-90F28FD1D8A8@.microsoft.com...
> > Hello everyone, i like to know if someone knows how to denied errors y SQL
> > server 2000, i have many webs over a single server and i do not want to
> > show
> > errors in explorer when a bad user writes false urls intentionally trying
> > to
> > inject code via sql. Or maybe that security i have to control via the code
> > in
> > asp.
> >
> > Thanks in advance
> >
> >
>
>

errors

Hello everyone, i like to know if someone knows how to denied errors y SQL
server 2000, i have many webs over a single server and i do not want to show
errors in explorer when a bad user writes false urls intentionally trying to
inject code via sql. Or maybe that security i have to control via the code i
n
asp.
Thanks in advanceTrapping the error in the application is the best way.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Arturo J" <Arturo J@.discussions.microsoft.com> wrote in message
news:5B649D1E-2B9C-4293-A383-90F28FD1D8A8@.microsoft.com...
> Hello everyone, i like to know if someone knows how to denied errors y SQL
> server 2000, i have many webs over a single server and i do not want to
> show
> errors in explorer when a bad user writes false urls intentionally trying
> to
> inject code via sql. Or maybe that security i have to control via the code
> in
> asp.
> Thanks in advance
>|||Thanks for your qick repond, just one doubt, you mean to manage the error
reports through the asp?
"Jason Massie" wrote:

> Trapping the error in the application is the best way.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "Arturo J" <Arturo J@.discussions.microsoft.com> wrote in message
> news:5B649D1E-2B9C-4293-A383-90F28FD1D8A8@.microsoft.com...
>
>

Error: The connection manager failed to defect from the transaction.

Does anybody know what it means?

The context is: a package with a single Data Flow finishes with this error: [Connection manager "xxx"] Error: The connection manager failed to defect from the transaction.

All components inside the data flow reports a successfull green status, however the DataFlow Task remains yellow for a long time, when suddenly the package stops with the above message. Another information is that the table remains locked during package execution (a select on that table does not complete...). This is a serious issue, and I couldnt find any relevant information about that. Thanks in advance.

Hi Half Abude,

Can you tell us more about the package? What kind of SQL is being executed? Also I'm just guessing you're using transactions in the package, can you tell us how you've set the transaction boundaries in the package? (i.e. on what object(s) have you set TransactionOption to Required?)

Thanks, -David

Error: Subreport could not be shown.

I have to print 4 forms one form per page from a datatable generated by
a single query, since on the design window I can see only one page, my
initial solution was to create 4 reports, and they work fine, but the
end user needs to see the 4 forms at once to send it to a pdf.
Now, I created a new report and add the 4 reports as a subreports, but
I am getting the message "Error: Subreport could not be shown. "
I will apreciate any help.
Thank you
GustavoHi,
Just check the right report you have selected from the drop down for sub
reportand most importantly all the parameters are selected propely. I doubt
it should be some missing parameters.
Amarnath.
"gvt99@.hotmail.com" wrote:
> I have to print 4 forms one form per page from a datatable generated by
> a single query, since on the design window I can see only one page, my
> initial solution was to create 4 reports, and they work fine, but the
> end user needs to see the 4 forms at once to send it to a pdf.
> Now, I created a new report and add the 4 reports as a subreports, but
> I am getting the message "Error: Subreport could not be shown. "
> I will apreciate any help.
> Thank you
> Gustavo
>|||Gustavo,
You are probably missing some parameters. You must supply the
subreports with parameters from the Master report.
Also, Amarnath, dont forget that if the report has temp tables, it will
not automatically fill those parameters.
regards,
Stas K.