Ms Sql Server Technology Discussions

[Darling999]

SSIS Runtime Error:

problem: When ever you run a package for Dataload from Oracle or teradata, if the Data Environment is 32bit and your running environment is 64 bit you wuold receive an error called


[PAR Data Source TD [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TD_CM" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

This would be possible error.

Solution:

its Simple, but took a day research for me to find that option.

When you open the SSIS project properties in Bids


[img]http://sqlblog.com/blogs/john_paul_cook/image_thumb_7950D07B.png[/img]


make this option to False, bu default its true. Issue would not persist Any more.

[ghazni]

[quote name='DARLING...' timestamp='1357143307' post='1303042960']
SSIS Runtime Error:

problem: When ever you run a package for Dataload from Oracle or teradata, if the Data Environment is 32bit and your running environment is 64 bit you wuold receive an error called


[PAR Data Source TD [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TD_CM" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

This would be possible error.

Solution:

its Simple, but took a day research for me to find that option.

When you open the SSIS project properties in Bids


[img]http://sqlblog.com/blogs/john_paul_cook/image_thumb_7950D07B.png[/img]


make this option to False, bu default its true. Issue would not persist Any more.
[/quote]
gp

[Darling999]

[quote name='mtkr' timestamp='1356649792' post='1303023557']
mastaaruus... evaraina external sources(files like flat, excel, xml) files nundi oracle ki data load chesara through SSIS..

nen trying to load xml file into oracle db thr SSIS...


matter entante oka execute sql task and daani kinda oka DFT ....n nxt vere konni unnai...

execute sql task fail avutundhiiii....
n danni disable chesthe DFT lo anta baane nadustundhii..


aa execute sql task lo oka query undhii n andi oka condition satisfy chesukoni nxt step ki potundhiii....

Result set:: SINGLE ROW

Sql Stmt:::
select count(*) as found from tbl1 where column = ?

Parameter Mapping::: variable1

result set::: found--- varfound

ee execute sql task ki DFT ki madhyalo exprssion varfound > 0


so adi >0 avuthene nxt step ki pothadi.....

but adi fail avutundhii...
n error iz:::

An error occurred while assigning a value to variable "The type of the value being assigned to
variable differs from the current variable type. Variables may not change type during
execution. Variable types are strict, except for variables of type Object.
[/quote]

hey mktr did you find the issue here? In the Package properties there is an option called delay validation, this is used when a parameter is being generated and passed to next level. so make that option to True your issue should be Solved.

let me know if you could not will try it.

[Darling999]

[quote name='DARLING...' timestamp='1357143307' post='1303042960']
SSIS Runtime Error:

problem: When ever you run a package for Dataload from Oracle or teradata, if the Data Environment is 32bit and your running environment is 64 bit you wuold receive an error called


[PAR Data Source TD [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TD_CM" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

This would be possible error.

Solution:

its Simple, but took a day research for me to find that option.

When you open the SSIS project properties in Bids


[img]http://sqlblog.com/blogs/john_paul_cook/image_thumb_7950D07B.png[/img]


make this option to False, bu default its true. Issue would not persist Any more.
[/quote]

Issue:

Once the Above package is running Successfully in BIDS then if you need to Schedule the package in SQL Agent then you will definitely receive an error again.


Message
[b]Executed as user: VZH\SQL Admin DWM. Microsoft ® SQL Server Execute Package Utility Version 10.0.4000.0 for 32-bit Copyright © Microsoft Corp 1984-2005. All rights reserved. Started: 2:19:12 PM Error: 2012-12-26 14:19:13.24 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2012-12-26 14:19:14.71 Code: 0xC0202009 Source: BSGCube_Processing Connection manager "TT8_TERADATA_CM 1" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "OLE DB Provider for Teradata" Hresult: 0x80040E4D Description: "[OLE DB Provider for Teradata] Not enough information to log on". End Error Error: 2012-12-26 14:19:14.71 Code: 0xC020801C Source: TD Load For 14 days TD Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TT8_TERADATA_CM 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2012-12-26 14:19:14.71 Code: 0xC0047017 Source: TD Load For 14 days SSIS.Pipeline Description: component "TD Source" (1) failed validation and returned error code 0xC020801C. End Error Error: 2012-12-26 14:19:14.71 Code: 0xC004700C Source: TD Load For 14 days SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2012-12-26 14:19:14.71 Code: 0xC0024107 Source: TD Load For 14 days Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:19:12 PM Finished: 2:19:14 PM Elapsed: 1.981 seconds. The package execution failed. The step failed.[/b]

Solution:

While Scheduling a job using SQL Agent, you would take that step as SSIS package from either Server or File system. But few more steps needs to be taken

if you are alowed to store credentials in th package it self then

1. While deploying the package check the option Save ALL with password, that would take care of authentication part. after deployment and during the schedule under execution options of SQl agent step you would see Execute 32 bit Version. Check this option

[img]http://sqlblog.com/blogs/john_paul_cook/image_thumb_50B7E098.png[/img]
and then under the Command Line check the Edit the Command line Manually and

paste this Command

/FILE "D:\APP\package.dtsx" /DECRYPT pwd/X86 /CHECKPOINTING OFF /REPORTING E

in the above pwd: please give the password provided for package to store all information.

Now the package runs successfully every where without issues.

If you are not allowed to store information in package then

2. while deployment check the option do not save sensitivity Data and create a config file for all the Authentication details, during the Job Scheduling just go to the option Config file and attach the Config file created for the package, this would take care of the job.

Guys this is a real time experience issue, might be small one when you see but if you do not know this options ans settings that will be a disaster. So keep an Eye on those settings.

[deals2buy]

[b] Single Quotation Marks in SQL[/b]


Possibly one of the most difficult parts of dynamic SQL is dealing with single quotation marks. I'm guessing most DBAs at one point or another have seen the following:

[CODE]
REPLACE(@quotedvar, '''', '''''')
[/CODE]

For those who haven't seen this, or don't really understand it, what the code is doing is replacing all of the single quotes (') with 2 single quotes (''). I'll go into the why a little farther down.
Here are my are 2 rules when dealing with single quotes. [list=1]
[*]The outside 2 single quotes delimit the string.
[*]On the inside of the string you must have 2 single quotes for each single quote you are representing.
[/list]
First let's break down the strings the REPLACE is using: '''' and ''''''. The first thing I'm going to do is to color the outside two quotes so that we see what we are working with a bit more clearly.

'''' and ''''''

Now we can see the inside quotes a bit more clearly. Note again there are 2 single quotes for each single quote we want to represent. If it helps, think of putting O'Neil into a string. You would write @var = 'O''Neil'. Then if you get rid of the letters you end up with @var = ''''. Hopefully this also makes '''''' a little easier to understand. When you look at it try to ignore the outside quotes and see the inside quotes in pairs. So '''''' actually represents ''.

Why would we want to mess with this? Since T-SQL uses 's to delimit strings there has to be a way to put a single quote inside of the string. (I'm not going into QUOTED_IDENTIFIER here. If you are curious look it up in BOL.) For example the compiler is going to have a hard time understanding 'O'Neil'. Is it the string O'Neil? The string Neil with a mistaken 'O at the beginning? Or the string O with a mistaken Neil' at the end. It's very similar to the problem of extra commas in a comma delimited file. The way this is handled is by using two single quotes. We put 'O''Neil' and the compiler is happy, it understands that what you are trying to say is O'Neil.
Sounds simple right? So let's try it out.

[CODE]
DECLARE @quotedvar nvarchar(100)
DECLARE @sql nvarchar(1000)
SET @quotedvar = 'O''Neil'
SET @sql = 'PRINT ''' + @quotedvar + ''''
PRINT @sql
EXEC sp_executesql @sql
[/CODE]


The output looks like this:
PRINT 'O'Neil'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Neil'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.

Now I hear someone muttering at the back of the class saying “I put in the two single quotes like you told me but it's still wrong!”

And they would be right. We put 2 single quotes in each SET statement. But note, when we printed the @sql statement we got “PRINT 'O'Neil'”. Single quotes both before and after O'Neil just like we intended. However, when we run it, we are back to 'O'Neil' again. We stored 'O''Neil' into @quotedvar, why didn't it transfer correctly? Let’s look.

[CODE]
SET @quotedvar = 'O''Neil'
[/CODE]


Well first the quotes on the outside delimit the string so they are ignored when the value is stored into the variable. That would be why the extra single quotes in the SET @sql statement. ( SET @sql = 'PRINT''' + @quotedvar + '''' ) But remember, when the value was stored into the variable the two single quotes ('') were translated into a single quote ('). So now the variable has “O'Neil” in it. What we need to be stored in @sql is “PRINT 'O''Neil'”.

Now everyone go back to the top, I'll wait.

Everyone back? Ok, here we go

[CODE]
DECLARE @quotedvar nvarchar(100)
DECLARE @sql nvarchar(1000)
SET @quotedvar = 'O''Neil'
SET @sql = 'PRINT ''' + REPLACE(@quotedvar,'''','''''') + ''''
PRINT @sql
EXEC sp_executesql @sql
[/CODE]


Look familiar? Now our output looks like this:

PRINT 'O''Neil'
O'Neil

Everyone follow? Good. Now for homework please fill in the following:

[CODE]
DECLARE @topsql nvarchar(200)
SET @topsql =
'DECLARE @quotedvar nvarchar(100) ' + char(13) +
'DECLARE @sql nvarchar(1000) ' + char(13) +
…..
…..
…..
…..
…..
'PRINT @sql ' + char(13) +
'EXEC sp_executesql @sql '
PRINT @topsql
PRINT '-------'
EXEC sp_executesql @topsql
[/CODE]

If you look closely this piece of code takes the previous example prints it out and then and runs it dynamically. In case you have never tried it before this would be similar to dynamically creating dynamic SQL.

As a clue the output should look like this:

DECLARE @quotedvar nvarchar(100)
DECLARE @sql nvarchar(1000)
SET @quotedvar = 'O''Neil'
SET @sql = 'PRINT ''' + REPLACE(@quotedvar,'''','''''') + ''''
PRINT @sql
EXEC sp_executesql @sql
-------
PRINT 'O''Neil'
O'Neil

[deals2buy]

[b] Inside the SQL Server Query Optimizer[/b]


by Benjamin Nevarez
(1st Edition, November 2010)

[b]Download eBook (PDF)[/b]: [b][url="http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-query-optimizer-ebook?utm_source=ssc&utm_medium=weblink&utm_content=queryoptimizer1&utm_campaign=sqlprompt"]Download here[/url][/b]
[b]Buy Printed Book[/b]: available on [b][url="http://www.amazon.com/Inside-SQL-Server-Query-Optimizer/dp/1906434603/ref=sr_1_11?ie=UTF8&qid=1301970045&sr=8-11"]Amazon.com[/url] [/b]or [url="http://www.amazon.co.uk/Inside-SQL-Server-Query-Optimizer/dp/1906434603/ref=sr_1_1?ie=UTF8&qid=1301970172&sr=8-1"] [b]Amazon.co.uk[/b][/url].
[b]Download ePub: [url="ftp://www.simple-talk.com/simplepod/InsideSQLServerQueryOptimizer.epub"]Download here[/url][/b]

[deals2buy]

[quote name='Kaarthikeya' timestamp='1357134243' post='1303042699']
What will be returned by the following statements?
select ((CONVERT(NUMERIC(5,0),123) / CONVERT(NUMERIC(5,0),100)) * 100) AS [Percentage]
select ROUND(((CONVERT(Float,123) / CONVERT(Float,100)) * 100),1) AS [Percentag]
[/quote]


[b]Answer: [/b]123.000000, 123
[b]Explanation: [/b]The conversions take place based on the parameters passed into the CONVERT function.
Ref: Cast and convert - [url="http://www.sqlservercentral.com/links/1427054/288269"]http://msdn.microsoft.com/en-us/library/ms187928.aspx[/url]

[mtkr]

[quote name='DARLING...' timestamp='1357143747' post='1303042975']
hey mktr did you find the issue here? In the Package properties there is an option called delay validation, this is used when a parameter is being generated and passed to next level. so make that option to True your issue should be Solved.

let me know if you could not will try it.
[/quote]
adi tried mama no use... but solved other way...

na process enti ante oka foreach loop lo execute sql task thn dataflow task...

exesql task lo pl-sql query undhii....

select count(*) as number form tbl1

so nxt e number ni oka variable(int32) ki assign chestunnam.... n nxt a exe sql task n DFT ki madhyalo arrow(process flow) lo expression pettam @variable == 0....
so error em vastundhi ante... datatypes missmatch n it cannot be changed during process aniiii..


so nen aaa select query ni to_char(count(*)) and aaa variable ni string ki change chesa....
nxt a process flow lo @variable == (DT_WSTR,10)0...

so issue solved

[chelsea]

[quote name='mtkr' timestamp='1357262824' post='1303050941']
adi tried mama no use... but solved other way...

na process enti ante oka foreach loop lo execute sql task thn dataflow task...

exesql task lo pl-sql query undhii....

select count(*) as number form tbl1

so nxt e number ni oka variable(int32) ki assign chestunnam.... n nxt a exe sql task n DFT ki madhyalo arrow(process flow) lo expression pettam @variable == 0....
so error em vastundhi ante... datatypes missmatch n it cannot be changed during process aniiii..


so nen aaa select query ni to_char(count(*)) and aaa variable ni string ki change chesa....
nxt a process flow lo @variable == (DT_WSTR,10)0...

so issue solved
[/quote]

ba ila issue sloved ayyinavi cheppaindi ikkada andariki use avthadi

[mtkr]

[quote name='chelsea' timestamp='1357263243' post='1303050967']

ba ila issue sloved ayyinavi cheppaindi ikkada andariki use avthadi
[/quote]

ya mama...

[deals2buy]

[quote name='mtkr' timestamp='1357262824' post='1303050941']
adi tried mama no use... but solved other way...

na process enti ante oka foreach loop lo execute sql task thn dataflow task...

exesql task lo pl-sql query undhii....

select count(*) as number form tbl1

so nxt e number ni oka variable(int32) ki assign chestunnam.... n nxt a exe sql task n DFT ki madhyalo arrow(process flow) lo expression pettam @variable == 0....
so error em vastundhi ante... datatypes missmatch n it cannot be changed during process aniiii..


so nen aaa select query ni to_char(count(*)) and aaa variable ni string ki change chesa....
nxt a process flow lo @variable == (DT_WSTR,10)0...

so issue solved
[/quote]

[deals2buy]

[b] An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3[/b]


[b]Jon[/b] is senior and experienced SQL Server Expert and [b]Mike[/b] is beginner with SQL Server.

[b]Mike[/b] – What is Redundant Index?

[b]Jon[/b] – Indexes are redundant when they have similar columns as a part of a definition. Additionally, the indexes are considered redundant when their first few columns are in the same position with same order by direction are also considered as a redundant.

[b]Mike[/b] – Would you please explain it with examples?

[b]Jon[/b] – Sure, Let us assume we have two indexes:

[b]Index 1:[/b] Col1, Col2, Col3

[b]Index 2:[/b] Col1, Col2, Col3

Now if you look at them – they have the same columns as a part of their definition, so they are indeed redundant indexes. However, look at the following scenario:

[b]Index 3:[/b] Col1, Col2

[b]Index 4:[/b] Col1, Col2, Col3

In this case they are also considered as a redundant because the position of the Col1, Col2 are same in both of the index. It is quite commonly considered that if the initial positions of the columns are the same, they are redundant.
However, there is one more concept here to be looked at as well before we make certain about their redundancy. Look at the following indexes:

[b]Index 5:[/b] Col1 ASC, Col2 DESC

[b]Index 6:[/b] Col1 DESC, Col2 DESC, Col3 ASC

In this case if the initial positions are the same, they are not redundant as the order of the column is not the same.
There are lot more to discuss but this is just to give you an initial idea. There is one more concept we should consider before calling any index redundant is Included Index. Here is the simple scenario for it.

[b]Index 7:[/b] Col1 ASC Included (col2)

[b]Index 8:[/b] Col1 ASC Included (col3)

You can notice they have same initial column but the Included columns are totally different.

[b]Mike[/b] – Thanks, I got it. It seems that Redundant Indexes are not good and they should be dropped correct.

In case of Index 1 and Index 2 I think we should drop either of the one.

In case of Index 3 and Index 4 I believe Index 4 has more columns and covering, so we should keep it and drop the other one.

In case of Index 5 and Index 6, they are both different indexes so we should keep both.

In case of Index 7 and Index 8, again they are both different index in this case. They can be redundant if the included columns are overlapping to each other.

Am I correct to say this?

[b]Jon[/b] – Very good analysis. You are very close to the understanding. Generally, redundant indexes are not good and they should be absolutely addressed. [b]In most cases, redundant cases should be dropped. [/b]

[b]Mike[/b] – Ahha, so in the most cases indexes should be dropped. Ok, so is there any script or guidance to detect redundant indexes for the most cases.

[b]Jon[/b] – Sure, [b][url="http://www.pinaldave.com/sql-downloads/script-downloads/sql-server-missing-index-unused-index-and-duplicate-index-script-2/"]here is the script[/url][/b] which does that – however, this query just addresses the scenario of the Index 3 and Index 4. It does not talk about Included Columns or Index Order (ASC or DESC). Just use that for a start but do your analysis on this subject before you drop your indexes. You still have to check for order of the index and included columns as well.

[b]Mike[/b] – Perfect, I understand that the script is just for a quick start and not the complete solution. Now you mentioned [i]“In Most Cases”[/i] – what are the special cases. What are the cases when an Index which absolutely qualify for the redundant index but should not be dropped. Would you please explain the special cases?

[b]Jon[/b] – Absolutely – there are always special cases. For example the width of the column matters.

[deals2buy]

[b] The SSIS Data Pump[/b]


http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/72493/

Well explained....Useful for a beginner/fresher

[deals2buy]

Row Sorting in SQL

http://www.simple-talk.com/sql/t-sql-programming/row-sorting-in-sql-/

[deals2buy]

[b] Search All Stored Procedures in a Database[/b]


Sometimes there comes a situation when we need search for a text in all the procedures in the current database. This SQL script does it. It searches for the search text in all the stored procedures and gives you back the name of the stored procedures.

[CODE]
declare @searchString varchar(100)
Set @searchString = '%' + 'DtgSchemaOBJECT' + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @searchString
ORDER BY SO.Name
[/CODE]