Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

Flat file source not communicating with script component properly

Hi there,

I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.

This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.

Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.

I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.

Any advice would be welcome

Regards
Mike

Put data viewer between the Flat File Source and the Script component and check what data actually enters your script.

HTH,

Bob

|||Thanks for the reply,

I put a viewer on and saw that the script gets all the populated columns except for the one I mentioned earlier. Thus the problem seem to be with the flat file source not passing the data in that column through to the script.
But why would it do that even though it shows all of the columns populated when I click on the preview button on the flat file source?

Regards

**EDITED**

I have also noted that when I check the "retail null values from row as null values in data flow" checked box, then it does populate all columns correctly but I cant have that checked box checked since it does not go to the correct path on my workflow with the nulls. When I uncheck it, it misses the one column again but not the other ones. This is puzzling me very much
|||

What version of the SSIS do you have installed (RTM, SP1, SP2)? I think there was a flat file parsing bug fixed in one of SP releases that would potentialy cause this behavior.

Thanks,

Bob

|||Hi Bob,

I have recently (last week) upgraded to service pack 2 and also updated my ssis from Version 9.00.20(something) to Version 9.00.3042.00.

The package was built on an older version and pre service pack 2. I wonder if that is not the case here.

Thanks for the reply

Regards
Mike
|||

Would it be possible for you to isolate a few rows that fail, create another simple file with only those two rows, obfuscate your data and post your file here?

Just make sure the newly created file still fails the same way as the original one.

Thanks,

Bob

|||Hi Bob,

Just for some extra clarity, none of the rows are actually failing. They all pass through successfully except for one of the columns (SiteCode) in the file does not get read properly by the flat file source. It does not fail or warn me about anything, it just doesn't detect the data in this specific column. It passes an empty string through for all rows in that column.

Like I mentioned before, the funny thing is that when I preview the file in the flat file source in the data flow task, it does actually show that the column is populated. All of the SiteCodes are populated in the preview window but that preview data just doesn't get passed through to the other data flow component.

Would it still be usefull for me to post the file?

Regards
Mike
|||

Yes, by "failing" I meant not being parsed properly.

If you can provide the sample of data I can try to debug it here and eventually find out what is causing the behavior you are seeing.

Thanks,

Bob

|||Hi,

I hope this will be sufficient
The column with the 'BAL' data doesn't get read properly
Regards

FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10030||Cost - Comp Equip 3yrs|2006|10|144.00|AA|General Ledger||
FMC|FMCG|22110|FMCG Warh|BAL|Balance Sheet|BAL|Balance Sheet|22110|FMCG Warehouse Balance sheet|10050||Cost - Comp Equip 10yrs|2006|10|233.00|AA|General Ledger||

|||

I tried to parse these two rows using Flat File Source and could not see any problem with it.

Could you tell me again what is not read properly? I have seen "BAL" values read in both; 5th and 7th columns.

|||Hi Bob,

I managed to find a workaround on this problem. When I click the "retain null values from source as null values in data flow" check box, the data does get passed on correctly but to the wrong exit point (error exit point).

I did further investigation as to why it would go to the error exit point and realized that it actually picks up on a null value and does not know how to handle it. I got the "object reference not set as an instance of a ...etc" error.

Adding code to check for these nulls and setting it to dbnull.value helped with my problem and things are working fine now. I also believe that my problem might also be because we did not use text qualifiers in this project but this is only speculation.

From now on I will know what to do with custom scripts reading and processing text files so that I don't leave the system open for these possibly unhandled situations.

Regards

FKs

Does anyone have a script that will evaluate the foreign key relationships in a db and generate a script to drop and re-create them?you might be able to write the sql procedure by using the sysforeignkeys table|||What will this be used for ?|||declare @.cr nchar(2)
declare @.go nvarchar(8)

set @.cr = nchar(13)+nchar(10)
set @.go = @.cr + 'GO' + @.cr

declare @.tablename nvarchar(128)
declare @.column nvarchar(128)
declare @.schema nvarchar(128)
declare @.constraint nvarchar(128)
declare @.fktable nvarchar(128)
declare @.fkconstraint nvarchar(128)
declare @.onupdate varchar(9)
declare @.ondelete varchar(9)
declare @.comma char(1)

declare @.sql nvarchar(4000)

declare cstrts cursor local fast_forward read_only for
select
c.[TABLE_SCHEMA],
c.[TABLE_NAME],
u.CONSTRAINT_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
inner join [INFORMATION_SCHEMA].[table_constraints] t
on u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
where t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'

open cstrts

fetch next from cstrts
into @.schema, @.tablename, @.constraint

while @.@.fetch_status = 0
begin
select
@.fktable = u2.[TABLE_NAME],
@.fkconstraint = r.[UNIQUE_CONSTRAINT_NAME],
@.onupdate = r.[UPDATE_RULE],
@.ondelete = r.[DELETE_RULE],
@.column = u.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u2
on r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
where r.[CONSTRAINT_NAME] = @.constraint

set @.sql =
'ALTER TABLE ['
+ @.schema
+ '].['
+ @.tablename
+ '] ADD CONSTRAINT ['
+ @.constraint
+ '] '
+ @.cr
+ 'FOREIGN KEY (['
+ @.column
+ ']) REFERENCES ['
+ @.fktable
+ '] ('

-- for each ordinal in the foreign key index...
declare idx cursor local fast_forward read_only for
select c.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
where u.[CONSTRAINT_NAME] = @.fkconstraint
order by u.[ORDINAL_POSITION]

open idx

set @.comma = ''

fetch next from idx
into @.column

while @.@.fetch_status = 0
begin
set @.sql = @.sql + @.comma + '[' + @.column + ']'
set @.comma = ','

fetch next from idx
into @.column

end

close idx
deallocate idx

set @.sql = @.sql + ') ON DELETE '
+ @.ondelete
+ ' ON UPDATE '
+ @.onupdate
+ @.go

print @.sql

fetch next from cstrts
into @.schema, @.tablename, @.constraint


end

close cstrts
deallocate cstrts
-------

See also my attachment to 'Restoring Databases from Win2000 to Win2003/Collation' which uses a drop and recreate to all forms of indexes to recollate a databse.

Hope This helps

HH|||Thanx, this is going to be used in conjunction with a proc that truncates all of the user tables in the db. I'm building a new system and need to frequently purge the data in the db to test data migration scripts and delete statements take too long to execute.

Fishing the DTS Knowledge Pool

I am having a puzzling problem with a DTS package in SQL 2000 that uses a combination of "Execute Process Tasks" and "ActiveX Script Tasks." The issue occurs with one of (the second) the ActiveX Script Task. The script invokes a COM object that was written in C#. This COM object connects to the database and writes a record set and then calls a stored procedure.
When I run this step individually, everything works fine. When I run it as part of the package, the record set is being written but the stored procedure is not invoked (SQL Profile confirms this).

I haven't really begun a serious attempt to troubleshoot the problem, though I have played around with the Transactions and OLE DB properties of the DTS Package.

Any suggestions as to why the behavior might be different, or some straightforward tips on troubleshooting would be appreciated. I have access to the COM component source.
Try the *real* DTS knowledge pool: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

This forum is limited to SSIS.

Fiscal Month Date Range: 22 thru 21

I'm looking for a script to do the following?
I need to have a date range for each month of the year from the 22nd of each
month to the 21st of the next.
This doesn't work... as it starts at the first, I need it to start at the 22
of
the previous month.
declare @.date datetime
set @.date = '1/1/2005'
select case when datepart(mm,@.date) = 1 and datepart(dd,@.date) between 1 and
21
then 1 else 0 end
TIA
JeffP...Considered a calendar table to mark fiscal months?
http://www.aspfaq.com/2519
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ewMNyExBGHA.1288@.TK2MSFTNGP09.phx.gbl...
> I'm looking for a script to do the following?
> I need to have a date range for each month of the year from the 22nd of
> each
> month to the 21st of the next.
> This doesn't work... as it starts at the first, I need it to start at the
> 22 of
> the previous month.
> declare @.date datetime
> set @.date = '1/1/2005'
> select case when datepart(mm,@.date) = 1 and datepart(dd,@.date) between 1
> and 21
> then 1 else 0 end
>
> TIA
> JeffP...
>|||The calendar table is an excellent solution. If you do not want to do
that, you could use a Case statement for your solution. This example
assumes your fiscal year starts on December 22.
select <all other columns>,
case when datepart(day,datefield) <= 21
then cast(year(datefield) as varchar(4)) + ' ' + right('0' +
cast(month(datefield) as varchar(2)),2)
when datepart(day,datefield > 21 and month(datefield) = 12 then
cast(year(datefield) + 1 as varchar(4)) + ' 01'
else cast(year(datefield) as varchar(4)) + ' ' + right('0' +
cast(month(datefield) + 1 as varchar(2)),2)
else date(mm,datefield) + 1 end)
as FiscalMonth|||Oops - don't forget the END to the Case statement ;-)|||All, Thanks...
Thanks, one the one hand the case stmt works well for a quick & dirty query,
but
I'm like'ing the table, that way if anything changes in the rules I can crea
te
an interfact to the cal table and make adjustments.
TIA
JeffP....
"Gary Gibbs" <ggibbs@.aahs.org> wrote in message
news:1135271277.811769.38450@.g49g2000cwa.googlegroups.com...
> Oops - don't forget the END to the Case statement ;-)
>|||Here is another way. Probably a bit faster then using table lookups if you
call it a lot.
declare @.mthStart datetime
declare @.mthEnd datetime
declare @.i int
set @.mthStart = '1/22/2005'
set @.mthEnd = dateadd(day, 30, @.mthStart)
set @.i = 1;
while (@.i <= 12)
begin
select @.mthStart, @.mthEnd
set @.mthStart = dateadd(month, 1, @.mthStart)
set @.mthEnd = dateadd(month, 1, @.mthEnd)
set @.i = @.i + 1
end
William Stacey [MVP]
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23bXcyTyBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> All, Thanks...
> Thanks, one the one hand the case stmt works well for a quick & dirty
> query, but
> I'm like'ing the table, that way if anything changes in the rules I can
> create
> an interfact to the cal table and make adjustments.
> TIA
> JeffP....
> "Gary Gibbs" <ggibbs@.aahs.org> wrote in message
> news:1135271277.811769.38450@.g49g2000cwa.googlegroups.com...
>|||Thanks to all who posted...
This is nearly perfect... All the links were helpful as I have other client
s
with different date range issues.
create table #view_paid (datepaid varchar(10) ,polchoice varchar(2))
insert #view_paid
select '01/03/2005' ,10
insert #view_paid
select '01/13/2005' ,10
insert #view_paid
select '01/23/2005' ,10
insert #view_paid
select '01/31/2005' ,10
insert #view_paid
select '02/03/2005' ,10
insert #view_paid
select '02/13/2005' ,10
insert #view_paid
select '02/23/2005' ,10
declare @.mthStart datetime ,@.mthEnd datetime
set @.mthStart = '1/22/2005'
set @.mthEnd = dateadd(day, 30, @.mthStart)
select datepaid ,polchoice
,dateadd(ms,-3,dateadd(mm,datediff(mm,0,dateadd(mm,-1,datepaid)),0)+22)
Period_Start
,dateadd(mm,datediff(mm,0,datepaid),0)+2
1 Period_End
,Jan = case when datepaid between dateadd(mm,-1,@.mthStart) and
dateadd(mm,-1,@.mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0
end
,Feb = case when datepaid between dateadd(mm,0,@.mthStart) and
dateadd(mm,0,@.mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0
end
,Mar = case when datepaid between dateadd(mm,1,@.mthStart) and
dateadd(mm,1,@.mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0
end
from #view_paid with(nolock)
where cast(datepaid as datetime) between '01/01/2005' and '03/27/2005'
order by datepaid
select [Year] = datepart(yy,datepaid)
,JanTotals =sum( case when datepaid between dateadd(mm,-1,@.mthStart) and
dateadd(mm,-1,@.mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0
end)
,FebTotals = sum(case when datepaid between dateadd(mm,0,@.mthStart) and
dateadd(mm,0,@.mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0
end)
,MarTotals = sum(case when datepaid between dateadd(mm,1,@.mthStart) and
dateadd(mm,1,@.mthEnd) and (cast(PolChoice as varchar(1)) = 1) then 1 else 0
end)
from #view_paid with(nolock)
where cast(datepaid as datetime) between '01/01/2005' and '03/27/2005'
group by datepart(yy,datepaid)
drop table #view_paid
JeffP....
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:ud17lO0BGHA.3600@.TK2MSFTNGP10.phx.gbl...
> Here is another way. Probably a bit faster then using table lookups if yo
u
> call it a lot.
> declare @.mthStart datetime
> declare @.mthEnd datetime
> declare @.i int
> set @.mthStart = '1/22/2005'
> set @.mthEnd = dateadd(day, 30, @.mthStart)
> set @.i = 1;
> while (@.i <= 12)
> begin
> select @.mthStart, @.mthEnd
> set @.mthStart = dateadd(month, 1, @.mthStart)
> set @.mthEnd = dateadd(month, 1, @.mthEnd)
> set @.i = @.i + 1
> end
> --
> William Stacey [MVP]
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23bXcyTyBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>

First Timer Having an Insert Problem

Ok...I am stumped

I am currently writing a custom DTS package using an ActiveX script. This is my first time writing an ActiveX script and using the VBscripting language.

What I need to do is take data from three existing MS SQL Server 2k tables and import it into a single new table after serveral numerical data manipulations.

The specific problem I am having is that when I insert my recorsets into the new table several of the records are out of their sequential order. I am confused because if I cut down on the amount of data I insert, either in the number of rows and/or number of columns, I have no problem with my insert, but when I insert all the data I need things get out of order and swap places. I am inserting using what I think is called a connection/execute command with TSQL commands, and from what I have read this is the most efficient way to go about it.

I have approximately 4800 rows to insert with 6 columns, but my program seems to error with I try to insert in excess of 4100 rows.

Does anyone have any ideas? I was told that it might have something to do with a buffer, but I have not been able to find any helpful documentation. I have included the code for my insert loop.

Thank you in Advance!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

LoopDo you have a primary key on GPSIMPORT? If not, then your data structure is known as a "heap" and SQL server makes no guarantees about the order in which data is stored, or even the order in which it is retrieved in consecutive statements.

It is not a good idea to rely on the order in which data is inserted to be the order in which it is kept or retrieved. Define a primary key for your data.

blindman|||Thanks a lot Blindman!!

This seems to have worked!! I would have never found that solution.

thanks again,

TRoche|||"First timer having an insert problem."?

Maybe that's why you always remember your first....

First Timer having an INSERT Problem

Ok I am stumped....

I am currently writing a custom DTS task through the DTS designer and an ActiveX script. The goal of my Script is to access 3 tables which already exist in the DB, extract and numerically manipulate data from each table, and then insert this data into a new table.

This is my first time writing an ActiveX script and my first time using the VB scripting language, so most of my script is based on examples I could find.

To be more exact about my problem here is the description. I created six recordsets from the data I extracted from the 3 tables. When I attempt to insert all the data into my new table some of the data inserts in the wrong order. Three of my columns are time columns, so it is easy to see where the data falls out of its intended sequential order. It is also easy to see that this happens at the exact same time value, every time I run the code.

I am confused because if I write a for loop and insert less data either through fewer columns or fewer rows, my code seems to work perfectly. I have approximately 4800 rows total to insert, but my code seems to mess up if I try and insert in excess of 4100 rows. Someone suggested that I may be exceeding my allowed buffer, but I don't really know what this means or how to correct it.

Becasue my data inserts correctly if I only insert, say 2 columns, but all the rows, I think that the recordsets are being creatred correctly, and that the fault lies in my insert loop.

I have included the script for my insert loop and an example of how the data looks when it is in error.

I am very grateful for any help you might be able to provide and please let me thank you in advance for you time!

-TRoche

do until GPSxRecord.EOF
GPSx = GPSxRecord.Fields ("GPS_x").value
tx = GPSxRecord.Fields ("tx").value

GPSy = GPSyRecord.Fields ("GPS_y").value
ty = GPSyRecord.Fields ("ty").value

GPSz = GPSzRecord.Fields ("GPS_z").value
tz = GPSzRecord.Fields ("tz").value

'Executing the Insert Command
DestCmd = "INSERT INTO GPSIMPORT VALUES ( " & tx & ", " & GPSx & ", " & ty & ", " & GPSy & ", " & tz & ", " & GPSz & ") "

DestCon.Execute DestCmd

GPSxRecord.MoveNext
GPSyRecord.MoveNext
GPSzRecord.MoveNext

Loop

Time(x) GPSx Time(y) GPS(y)
58.9447 383421.96 58.94497 1213470.912 58.94526 488
58.99134 383421.959 58.99162 1213470.912 58.99191 489
59.04329 383421.957 59.04356 1213470.912 59.04385 490
59.0951 383421.956 59.09538 1213470.912 59.09566 490
59.14204 383421.955 59.14231 1213470.912 59.1426 490
221.19337 383447.07 221.19364 1213349.901 221.19393 479
221.24045 383447.069 221.24072 1213349.895 221.241 479
221.29253 383447.068 221.2928 1213349.889 221.29308 479
221.34434 383447.067 221.34461 1213349.883 221.3449 478'************************************************* **********
' Visual Basic ActiveX Script
'************************************************* **********

Function Main()

dim ConnSQL1 ' SQL Server connection
dim RSSQL ' SQL Server recordset
dim strSQL ' SQL String
dim rc

' SET DATA HANDLING OBJECTS
set ConnSQL1 = CreateObject("ADODB.Connection")
set RSSQL = CreateObject("ADODB.Recordset")

'OPEN DATA CONNECTION
ConnSQL1.Open = "Provider=SQLOLEDB;Data Source=server;Initial Catalog=database;UID=username;Password=asdf"

'create a select statement and put into temp table in the
'format that you are after from the three tables
'I assume you can do that
'Once you have all those records, then just insert them
'from the temp table

strSQL = "SELECT * into #temp from table " & _
"insert into newtable(f1, f2, f3, f4)" & _
"select f1, f2, f3, f4 from #temp"

RSSQL.Open strSQL, ConnSQL1
ConnSQL1.execute strSQL
RSSQL.close
Main = rc
End Function

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.

FireEvent issues with Reporting Services

I am attempting to use the following command line (and associated script) to trigger an existing subscription within Reporting Services (2000):
command line:

rs.exe -i MyScriptFile.rss -s "MyReportServer/ReportServer"

The input scriptfile (MyScriptFile) contains the lines below where the scheduleID is taken from Reporting Services from an existing 'once only' schedule. The schedule, - when triggered - should fire off a subscription and email recipients with a particular existing report.

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

Unfortunately, it doesn't work and I almost always get the response:

Unhandled exception:
The request failed with HTTP status 404: Not Found.

If I comment out the line rs.FireEvent( . . . from the script then the command line runs successfully. I can only conclude that there appears to be something wrong with this particular line.

Any ideas anyone?

Kev

Rs.exe takes care of creating the proxy to SSRS and doing authentication, so the first two lines of code (Dim RS & rs.Credentials) aren't actually necessary. The rest of your code looks fine, though.

You're saying that it sometimes does work?

Here are a few things to try:

- Can you check your Subscription ID to make sure it actually exists?

- Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

- FireEvent is the only method you're actually firing against the SOAP API...why don't you include another before you call FireEvent (something simple like ListChildren()) to make sure that the web service can do *any* work on your behalf.

- I've never seen someone specify the server name in their command-line w/o appending http:// first. This mean it won't work, I've just never seen it. Can you try something like this: rs.exe -i myFile.rss -s http://localhost/reportserver -u myDomain\myAccount -p myPassword ? Do this after you remove the first two lines of your code.

Let us know...good luck!

|||Russell, Thanks tremendously for the reply! I spent two whole days on this (without success) trying every variation I could think of.

Firstly, no the code in the original post has never worked; I've not been able to trigger anything. The Subscription ID definitely exists however: I highlighted it and copied it from within the Reporting Services address bar (in the Scheduling page) directly making sure I had it perfect. I did wonder whether it 'was' the intended ScheduleID but the address bar in the browser reads:

http://MyServer/Reports/Pages/Schedule.aspx?ScheduleID=87206163-7665-4458-a86c-75b84cf18b2d

. . . so I am assuming that I really 'do' indeed have the correct ScheduleID it's looking for (87206163-7665-4458-a86c-75b84cf18b2d)? Or is it? I've tried this with double quotes, without quotes, with single quotes all with no luck.

Secondly, the code below works perfectly indicating that the SOAP API will indeed do something for me:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"

with the following code in the ListFormatExtensions.rss file:

Sub Main
For Each Ext As Extension In rs.ListExtensions(ExtensionTypeEnum.Render)
Console.WriteLine(Ext.Name)
Next
End Sub

Strangely, ListChildren (as you suggested) didn't work at all? It failed with error BC30451: Name 'ListChildren' is not declared. I would assume that this is something perhaps due to the fact that I am using Reporting Services 2000 and not 2005? The actual failed code I used in the rss file was:

Sub Main
Dim rs As New ReportingService()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.ListChildren()
rs.FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub
The server name also works either way I specify it with the ListFormatExtensions test. So both these code snippets work happily and there's no issue with username and password:

rs.exe -i ListFormatExtensions.rss -s "MyServer/ReportServer"
rs.exe -i ListFormatExtensions.rss -s http://MyServer/ReportServer

I tried the option you suggested (without the first two lines of code) which it didn't like:

Sub Main
FireEvent("TimedSubscription", "87206163-7665-4458-a86c-75b84cf18b2d")
End Sub

error BC30451: Name 'FireEvent' is not declared

. . . so I promptly put the first two lines back in.

I am still trying to figure out what you mean by - Does this code work against a Subscription ID directly (in code) from a different (known good) subscription? (For example, see if this sample works: http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx)

The web address you gave simply takes me straight to the MSDN definition for FireEvent in Reporting Services 2005.

Kev


|||

Hey Kev --

Even though you can remove "Dim RS", etc. you still use "rs.Whatever" in your code

This worked for me:

1. Created a new subscription off the Adventure Works - Company Sales report: Used the fileshare delivery extension because I don't have an SMTP server handy

2. Put it on a schedule that would not fire until 3 days from now

3. Executed select * from reportserver..subscriptions to get the Subscription ID

4. Threw code below in an RSS file:

Sub Main()

Try
rs.FireEvent("TimedSubscription", "E34849EE-63EE-4BF6-957F-55A9B1132DDB")
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

5. In Report Manager, made sure that my user had "Generate Events" permissions (a system permission)

6. Executed the code with rs.exe -i c:\tryme.rss -s http://servername/reportserver

7. Checked my fileshare, found the report sitting there.

|||

Yes, make sure that you are using the subscription ID, as Russell is, and not the schedule ID that you said you were using.

-Daniel

|||

Guys, thanks for your input!! The problem is now sorted.

Unfortunately, I never gave a thought to the fact that I was running the rs.exe command on my own desktop and not the server on which Reporting Services is actually installed ! Dumb hey! When I put the file TriggerReport.rss on the report server and then ran the command to execute on the report server itself (using the psexec command):

psexec \\myserver rs.exe -i C:\TriggerReport.rss -s "http://myserver/ReportServer"

. . . it triggered the report immediately. The report promptly arrived in my email. I guess the lesson here is to stand back often and ask yourself if you're doing anything obviously stupid before blaming the code.

Kev

|||What Imports do you need for your .NET app if using SSRS 2005? Just thought I'd ask since you guys are the only place I've found really good info on this subject.