Padding and Writing to a fixed format flat file!

Hi,

I am trying to write to a fixed format flat file using Flat File Destination Data Flow Component. I have all required information gathered from more than one sources. But when I tried to format the columns to a big string that will make up one line in the flat file, I could not figure out how to do that. Couple of issues that I am facing are:

How to padd different columns? For example, One interger column has could be 1 to 10 character long in my case. When I convert to string, dont know how to padd the remaining characters i.e. if the value of integer is '1234', it should be written to file as '1234 ' . Which transformation is best in this case, if available? How to convert T-SQL datetime to a specific date and time format to write in the flate file? I have to write these date formats depending upon one of the parameters passed. Also, I dont want to put a delimiter at the end of each column, just the new line characters at the end of each record. Some of the columns has some unwanted characters (like new line characters) how to find them and remove them from the string. Can we directly write columns to a specific position in the flat file? e.g. col 1 a position 1 and col2 starts at postion 20 etc.

Your co-operation will be appreciated.

Thanks,

Paraclete

Take a look at a ragged-right or fixed width flat file connection manager. WHen you create the connection manager, you can choose ragged right or fixed width instead of delimited. This should solve a number of your issues.sql

PacketSize

I am getting the error:
"General network error. Check your network documentation."
It occurrs when I write/update to MSDE 2000 using ADO.NET's SqlClient classe
s. It only occurrs when the record reaches a certain size (around 10K, or s
o) and only occurs in certain environments. Some XP and some Windows 2000 e
nvironments are OK, while o
thers give the error.
I am aware of the PacketSize property in the SqlConnection class, but as of
yet have not set it and have just relied on the default value.
I feel there is something in the .NET or the ADO providers' environment or c
onfigurations that I am not aware of, and would appreciate any help in this
regard.Perhaps try turning off connection pooling (229564)
Bern
"Paul Wicks" <paulwicks@.htninc.com> wrote in message
news:6BAB551B-AC85-4D1C-8B41-89A79A719C81@.microsoft.com...
> I am getting the error:
> "General network error. Check your network documentation."
> It occurrs when I write/update to MSDE 2000 using ADO.NET's SqlClient
classes. It only occurrs when the record reaches a certain size (around
10K, or so) and only occurs in certain environments. Some XP and some
Windows 2000 environments are OK, while others give the error.
> I am aware of the PacketSize property in the SqlConnection class, but as
of yet have not set it and have just relied on the default value.
> I feel there is something in the .NET or the ADO providers' environment or
configurations that I am not aware of, and would appreciate any help in this
regard.
>|||Thanks for your suggestion, Bern.
I wish I could report good news, but the disabling of SQL Server connection
pooling had no effect.
I think I will leave it disabled to avoid possible future complications.

PacketSize

I am getting the error:
"General network error. Check your network documentation."
It occurrs when I write/update to MSDE 2000 using ADO.NET's SqlClient classes. It only occurrs when the record reaches a certain size (around 10K, or so) and only occurs in certain environments. Some XP and some Windows 2000 environments are OK, while o
thers give the error.
I am aware of the PacketSize property in the SqlConnection class, but as of yet have not set it and have just relied on the default value.
I feel there is something in the .NET or the ADO providers' environment or configurations that I am not aware of, and would appreciate any help in this regard.
Perhaps try turning off connection pooling (229564)
Bern
"Paul Wicks" <paulwicks@.htninc.com> wrote in message
news:6BAB551B-AC85-4D1C-8B41-89A79A719C81@.microsoft.com...
> I am getting the error:
> "General network error. Check your network documentation."
> It occurrs when I write/update to MSDE 2000 using ADO.NET's SqlClient
classes. It only occurrs when the record reaches a certain size (around
10K, or so) and only occurs in certain environments. Some XP and some
Windows 2000 environments are OK, while others give the error.
> I am aware of the PacketSize property in the SqlConnection class, but as
of yet have not set it and have just relied on the default value.
> I feel there is something in the .NET or the ADO providers' environment or
configurations that I am not aware of, and would appreciate any help in this
regard.
>
|||Thanks for your suggestion, Bern.
I wish I could report good news, but the disabling of SQL Server connection pooling had no effect.
I think I will leave it disabled to avoid possible future complications.

overview query

I'm trying to write a query to get a table that contains some basic information such as table names, table sizes (in rows) and such.

I am able to do a query to get the table names:
SELECT name FROM dbo.sysobjects WHERE type='U' and name !='dtproperties' ORDER BY name

This shows a listing sorta like:
table1
table2
table3
table4

I would like to do a
SELECT COUNT(*) table1
SELECT COUNT(*) table2
SELECT COUNT(*) table3
SELECT COUNT(*) table4

so that my output looks like
table1 13
table2 47
table3 0
table4 93

anyone know a way i can do this in 1 sql query, or know a way to use multiple sql statements with an asp.net datagrid?don't know of a single select to handel your request but could yo ucall a stored procedure from your ASP page?

create procedure TableRowCount
as
declare @.TableName sysname
create table #Tmp (TableName sysname, [RowCount] int)
select @.TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties'
while (@.TableName is not null) begin
insert into #Tmp
exec('select ''' + @.TableName + ''', count(*) from ' + @.TableName)
select @.TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties' and [name] > @.TableName
end
select cast(TableName as varchar) as TableName, [RowCount] From #Tmp
return 0
go

exec TableRowCount
go|||thanks! you're a genius. now i just have to figure out what all that means. i'm not used to stored procedures, but that gives the output i need, in the format i need it.

Thanks again!|||Basicaly, I created a temporary table to hold the results of counting the number of records in a table, then stepped through the sysobjects table looking for records that matched your original where clause. Once a table was found I did the count and stuffed the result set into the temp table. When there were no more table to count I returned the result set. All of this is basic stuff and Books Online will answer any questions you have.

I should have put comments in the code!|||na, it's fine how it was, because I understood most of it, and i'm just a sql beginner.

I just didn't understand the #Tmp being a temporary table, since i'd never seen those, but that's the idea i was going to try, but keeping track of the table was going to be a pain.

Hopefully now I'll be able to finish my application :)|||would #Tmp be what i'm hearing be referred to as a cursor? My boss does a lot of programming, and suggested that i make a cursor, but i had never heard that terminology.|||No #Tmp is a sesion temporary table. Once you log off the table is destroyed.

You can read up on cursors in Microsofts's Books Online. The have their uses but are VERY resource intensive.

The solution I posted is an alternative to a cursor.|||ok cool

you bet i'll be reading those ;) I'd really eventually like to do some more sql programming, it's kinda just going along with my asp.net stuff right now, but that's next on the list, since data is important to all applications ;)

thanks again paul

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob

overload function call

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions

thanks,

Hi Alessandro,

Yes, this can be done. You would use the sql_variant datatype as such:

create function fntest (@.one sql_variant, @.two sql_variant)
returns int
as
begin
declare @.ret int

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = 99
end

return (@.ret)
end;

The SQL_VARIANT_PROPERTY function will return the base data type, so you can therefore test for not only int, but varchar etc.

Cheers,

Rob

|||

Hi Rob,

Thank you.... Can I do this for return parameter?

Thanks

|||

Hi Alessandro,

Sure can, in the above example, you'd simply change the returning datatype:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin

Cheers,

Rob

|||

Hi Rob,

Yes it is true, but I can send or return float values sql_variant doesn't work for this :(

In fact I want to do this.

funtion ( number (always number) , char/number(param1) , char/number(param2) )

if number = 1

return param1

else param 2

similar to oracle decode, but I dont know how to do, do you have any ideia? Maybe clr function but I think overload function via c# doesn't work as well

thanks,

|||

Hi Alessandro,

I think I understand what you want, and the below will let you do this:

create function fntest2 (@.one sql_variant, @.two sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.one,'BaseType')) = 'int'
begin
set @.ret = @.one
end
else
begin
set @.ret = @.two
end

return (@.ret)
end;

Is that what you were after, or did you mean something else?

Cheers,
Rob

|||

Hi Rob,

Yes, but when you call with float from Select clauses like

select fntest( 1 , value1(float) , value2 (float) )

doesn't work I need to convert, have you tried?

Thanks

|||

Hi Alessandro,

What about the below:

create function fntest2 (@.one int, @.two sql_variant, @.three sql_variant)
returns sql_variant
as
begin
declare @.ret sql_variant

set @.ret = 0

if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'int'
begin
set @.ret = 'it is an int'
end
else if (select SQL_VARIANT_PROPERTY(@.two,'BaseType')) = 'float'
begin
set @.ret = 'it is a float'
end
--more if else statements here...

return (@.ret)
end;

declare @.f float
set @.f = 123.9982

select tempdb.dbo.fntest2( 1 , @.f , 'another param here' )

This returns the character line "it is a float" as the param @.f is explicity defined as float. If you execute the below line:

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

0 will be returned as 23.55 is interpreted as type numeric

Cheers,

Rob

|||

Hi Rob,

Woks well, thanks, however when I try doing this

select tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' ) - tempdb.dbo.fntest2( 1 , 23.55 , 'another param here' )

I receive this msg

Operand data type sql_variant is invalid for subtract operator.

thanks,

|||

Hi Alessandro,

Yes, that's correct - you cannot perform mathematical operations on the sql_variant datatype. This makes sense as sql has no idea what type it is: it may be binary, or one may be char, and the other numeric or both may be char, in which case you would receive concatenation of the two char values: probably not what you intended...

You'll need to perform an explicit cast as such:

declare @.a sql_variant,
@.b sql_variant

set @.a = 2;
set @.b = 1;

begin try
select (cast(@.a as int) + cast(@.b as int)) as 'sum'
end try
begin catch
print ERROR_MESSAGE()
end catch

You'll need to include the explicit cast in a try block in order to catch any conversion errors (for example, if @.a is a char value, not int).

Cheers,

Rob