July 15, 2015 at 5:11 am
I have come across a bug in SSIS. If I run the below code in SSMS it works on both SQL20008 and SQL2012.
If I run this code through SSIS using an OLEDB Data source and destination, it only works for SQL2012.
If i run this code through SSIS using an ODA.net connection, it works for both SQL2012 and SQL2008.
I need to run this using OLEDB Data source and connection as I am dynamically passing connection string through to the task with the OLEDB format. Does anyone know a work around this this and has anyone come across it before?
CODE:
--variables to hold each 'iteration'
declare @query varchar(100)
declare @dbname sysname
declare @vlfs int
--table variable used to 'loop' over databases
declare @databases table (dbname sysname)
insert into @databases
--only choose online databases
select name from sys.databases where state = 0 and name not in ('master' , 'model')
--table variable to hold results
declare @vlfcounts table
(ServerInstance nvarchar (50),
dbname sysname,
vlfcount int)
--table variable to capture DBCC loginfo output
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version
declare @MajorVersion tinyint
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1)
if @MajorVersion < 11 -- pre-SQL2012
begin
declare @dbccloginfo table
(
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo
exec (@query)
set @vlfs = @@rowcount
insert into @vlfcounts
values(@@SERVERNAME, @dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
else
begin
declare @dbccloginfo2012 table
(
RecoveryUnitId int,
fileid tinyint,
file_size bigint,
start_offset bigint,
fseqno int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
while exists(select top 1 dbname from @databases)
begin
set @dbname = (select top 1 dbname from @databases)
set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '
insert into @dbccloginfo2012
exec (@query)
set @vlfs = @@rowcount
insert into @vlfcounts
values(@@SERVERNAME, @dbname, @vlfs)
delete from @databases where dbname = @dbname
end --while
end
----output the full list
select serverinstance, dbname, vlfcount
from @vlfcounts
--drop table #vlfcounts
--order by dbname
July 21, 2015 at 4:34 am
anyone got experience with this?
Or is it just due to changes with the OLEDB connection managers from SQL 2008 - SQL 2012
July 21, 2015 at 11:51 am
You say you're running the connection string manually through SSIS. What is the connection string? That's likely your problem. I've seen similar issues between SSIS 2005 and SSIS 2008.
July 22, 2015 at 9:16 am
Hi .
I am not sure what you mean by "running the connection string manually"
I dynamically pass the connection strings through variables from a SQL table.. but thats not the issue. Seems to be a mis-match of capabilities between SQL 2008 OLEDB connection manager and SQL 2012 OLEDB connection manager.
I have had to set up 2 processes.. one to tackle SQL 2012.. and one to tackle SQL 2005 and SQL 2008.. which is not ideal
July 22, 2015 at 11:51 am
SQLAssAS (7/22/2015)
Hi .I am not sure what you mean by "running the connection string manually"
I dynamically pass the connection strings through variables from a SQL table.. but thats not the issue. Seems to be a mis-match of capabilities between SQL 2008 OLEDB connection manager and SQL 2012 OLEDB connection manager.
That is exactly what I mean by "manually".
Scrubbing for any sensitive data, please post the syntax of the entire string to this thread.
July 23, 2015 at 4:01 am
OLEDB - Data Source=Server;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
ADO.net - Data Source=Server;Initial Catalog=master;Integrated Security=SSPI;
July 23, 2015 at 4:58 am
SQLAssAS (7/23/2015)
OLEDB - Data Source=Server;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
This is your problem. See the bolded? That is a specific reference to SQL 2012.
To connect to SQL 2008, you need to use "Provider=SQLNCLI10.1". So you need to change your string depending on if you're connecting to 2008 or 2012.
July 23, 2015 at 9:39 am
Thanks for the reply, but this works fine for all of my other processing which connects to SQL 2005, SQL 2008 and SQL 2012 with the same method and connection string format.
I am wondering why this query is any different to the other stuff I run.
Example of a query which works fine using the connection string format above for other versions of SQL :
SELECT (select SERVERPROPERTY ('ServerName') ) as [ServerInstance], name AS [Database Name],
(select cast(SERVERPROPERTY ('ServerName') as nvarchar(100)) ) + '_' + name as [ServerinstanceDBName],
recovery_model_desc AS [Recovery Model],
compatibility_level AS [Compatibility Level], state_desc AS State, SUSER_SNAME(owner_sid) AS Owner
from sys.databases
WHERE (name NOT IN ('master', 'tempdb', 'model', 'msdb'))
July 23, 2015 at 12:10 pm
Could you try making the change to see if it makes a difference?
Sometimes SSIS does weird things with one package that doesn't affect other packages. I've had instances where I've actually had to remove a config file from the package definition to get it to recognize the server level config file, for instance, or where I had to recreate an OLE DB connection manager for only one package.
Give it a shot first. If it fixes it, great. If it doesn't, we'll try something else.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply