Data fields in the header

I'm getting this error when I try to preview my report: The value expression
for the textbox 'RepairOrderID' refers to a field. Fields cannot be used in
page headers or footers.
I searched through the news groups and a found suggestion to replace:
Fields!RepairOrderID.Value
with
ReportItems!RepairOrderID.Value
I tried this, it compiles now but when the report renders "#Error" displays
where the RepairOrderID should appear. I found another suggestion to use the
First function this does not work either...
Can someone please help....?
Thanks!!!
DanLooks like your textbox is referring to itself. You need to refer to a
textbox from report body.
More reliable solution is to create read-only parameter with default value
from query and then to use this parameter in the page header textbox.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> I'm getting this error when I try to preview my report: The value
> expression
> for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> in
> page headers or footers.
> I searched through the news groups and a found suggestion to replace:
> Fields!RepairOrderID.Value
> with
> ReportItems!RepairOrderID.Value
> I tried this, it compiles now but when the report renders "#Error"
> displays
> where the RepairOrderID should appear. I found another suggestion to use
> the
> First function this does not work either...
> Can someone please help....?
> Thanks!!!
> Dan
>|||I don't think my textbox is referring to its self... The name of the textbox
is textbox18, besides I'm explicitly qualifying the Value property with
Fields! or ReportItems!. How do I create a read-only parameter from
query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
Thanks,
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> Looks like your textbox is referring to itself. You need to refer to a
> textbox from report body.
> More reliable solution is to create read-only parameter with default value
> from query and then to use this parameter in the page header textbox.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> > I'm getting this error when I try to preview my report: The value
> > expression
> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> > in
> > page headers or footers.
> >
> > I searched through the news groups and a found suggestion to replace:
> >
> > Fields!RepairOrderID.Value
> > with
> > ReportItems!RepairOrderID.Value
> >
> > I tried this, it compiles now but when the report renders "#Error"
> > displays
> > where the RepairOrderID should appear. I found another suggestion to use
> > the
> > First function this does not work either...
> >
> > Can someone please help....?
> >
> > Thanks!!!
> >
> > Dan
> >
> >
>|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
Create a parameter without Prompt , set its default value to From Query,
select your dataset and ReportOrderID as value field.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
>I don't think my textbox is referring to its self... The name of the
>textbox
> is textbox18, besides I'm explicitly qualifying the Value property with
> Fields! or ReportItems!. How do I create a read-only parameter from
> query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> Thanks,
> Dan
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
>> Looks like your textbox is referring to itself. You need to refer to a
>> textbox from report body.
>> More reliable solution is to create read-only parameter with default
>> value
>> from query and then to use this parameter in the page header textbox.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
>> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
>> > I'm getting this error when I try to preview my report: The value
>> > expression
>> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
>> > used
>> > in
>> > page headers or footers.
>> >
>> > I searched through the news groups and a found suggestion to replace:
>> >
>> > Fields!RepairOrderID.Value
>> > with
>> > ReportItems!RepairOrderID.Value
>> >
>> > I tried this, it compiles now but when the report renders "#Error"
>> > displays
>> > where the RepairOrderID should appear. I found another suggestion to
>> > use
>> > the
>> > First function this does not work either...
>> >
>> > Can someone please help....?
>> >
>> > Thanks!!!
>> >
>> > Dan
>> >
>> >
>>
>|||Excellent!!... That's what I needed... Thanks..:)
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:udrYUvEfEHA.4092@.TK2MSFTNGP10.phx.gbl...
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
> Create a parameter without Prompt , set its default value to From Query,
> select your dataset and ReportOrderID as value field.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
> >I don't think my textbox is referring to its self... The name of the
> >textbox
> > is textbox18, besides I'm explicitly qualifying the Value property with
> > Fields! or ReportItems!. How do I create a read-only parameter from
> > query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> >
> > Thanks,
> >
> > Dan
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> >> Looks like your textbox is referring to itself. You need to refer to a
> >> textbox from report body.
> >> More reliable solution is to create read-only parameter with default
> >> value
> >> from query and then to use this parameter in the page header textbox.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> > wrote
> >> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> >> > I'm getting this error when I try to preview my report: The value
> >> > expression
> >> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
> >> > used
> >> > in
> >> > page headers or footers.
> >> >
> >> > I searched through the news groups and a found suggestion to replace:
> >> >
> >> > Fields!RepairOrderID.Value
> >> > with
> >> > ReportItems!RepairOrderID.Value
> >> >
> >> > I tried this, it compiles now but when the report renders "#Error"
> >> > displays
> >> > where the RepairOrderID should appear. I found another suggestion to
> >> > use
> >> > the
> >> > First function this does not work either...
> >> >
> >> > Can someone please help....?
> >> >
> >> > Thanks!!!
> >> >
> >> > Dan
> >> >
> >> >
> >>
> >>
> >
> >
>

data fields in header or footer

Hi all,
is there any workaorund for using fields in report header or footer?
Now I user parameters as workaround and I´m not really happy with that...
Thanks,
ToniYou mean dataset fields in the header?
Use reportitem for refering fields in the header.
like this.. =First(ReportItems("EmpName").Value)
Amarnath
"Toni Pohl" wrote:
> Hi all,
> is there any workaorund for using fields in report header or footer?
> Now I user parameters as workaround and Im not really happy with that...
> Thanks,
> Toni
>|||Hi Armanath,
well, this works! (and is a good workaround ;-)
Thanks!!!
Toni
"Amarnath" <Amarnath@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BDAEC70F-7992-4277-89D9-6BCA627BC926@.microsoft.com...
> You mean dataset fields in the header?
> Use reportitem for refering fields in the header.
> like this.. =First(ReportItems("EmpName").Value)
> Amarnath
> "Toni Pohl" wrote:
>> is there any workaorund for using fields in report header or footer?
>> Now I user parameters as workaround and Im not really happy with that...
>> Thanks,sql

Data fields are not allowed in the Page Header section

How can I build the page header (Sales for John Smith) where John Smith is
returned in the query results? Data fields are not allowed in the Page
Header section.
All responses greatly appreciated.
--
Any and all contributions are greatly appreciated ...
Regards TJYou can use read-only parameter with default value from query (assuming that
you render separate instance of report for every person)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:O2Mt1aNoEHA.3564@.tk2msftngp13.phx.gbl...
> How can I build the page header (Sales for John Smith) where John Smith is
> returned in the query results? Data fields are not allowed in the Page
> Header section.
> All responses greatly appreciated.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>
>

Data Extract then create new table

Please help in SQL2000

I have a table called dbo.DataFile which has 31 fields...I need to extract data from this table then create a new table under two conditions:

1. I only need to extract the data if the data from DF_SC_Case_Nbr field doesn't start with '0000%'

2. I need to merge [DF_SC_Case_Nbr] & [DF_SC_Def_Nbr] then call it DF_Combo_SC_Nbr (and keep the rest of the columns in tact), so the new table will now have 32 columns

Can somebody please help with the codes?..thanks. :confused:I don't speak SQL2000 :rolleyes: , but the general idea would be something like this:

CREATE TABLE new_table AS
SELECT column1, column2, ... column 31,
DF_SC_Case_Nbr || DF_SC_Def_Nbr DF_Combo_SC_Nbr
FROM dbo.DataFile
WHERE DF_SC_Case_Nbr NOT LIKE '0000%';


Merging two columns can be done using concatenation operator (in Oracle SQL represented by "||").|||Yeah that sounds right...but is there anybody out there who can translate this so sql can understand it?... :confused:|||Yes, but this sounds like homework. If you understand the basic idea and at least something about SQL Server 2000, then I think you should be able to type the answer as fast as I can.

If you are missing something, please explain. If you are trying to get someone to do your homework, sorry, that isn't my style.

-PatP|||Hi All,
I am using the sqlldr to load data with direct=true and parellel=true.
now my question is i have some column with unique indexes and primary keys also. if at all any record is duplicate then index becomes unusable. so what is the solution to delete those records which are violating unique indexes.

2) can any one provide me the query which making use of hints which does not make use of the index which is in un usable state and delete the records.|||You could create a temporary table which looks exactly like your original table, but without any indexes / unique / primary keys.
Load data into the temporary table.
Insert data into original table, eliminating duplicate rows.

I'd say you'll finish it much faster that way than trying to make Loader do a job itself.sql

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I need
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!
Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx
Good luck.
sql

data export to flat file

Hi, I hope you can help, and thank you.
SQL 2000. I need to generate a flat file export from a single table. I nee
d
a line feed between select fields. I've tried using bcp and Bulk Insert with
a format file, but I've never used either, and I must be missing a critical
step somewhere.
The flat file format for a single record would be similar to the following:
Field1, Field2, Field3, Field4, Field5, Image Path1
Image Path2
Image Path3
I appreciate any assistance you can provide. Thanks!Did you try the DTS (data transfomation service)tool in SQL Server 2000?
Easy to use for transforming data to export files.
http://www.microsoft.com/technet/pr...y/dtssql2k.mspx
Good luck.

Data Driven Subscription multi-line email content

I am trying to set up a data driven subscription where one of the fields
returned from the query is sent as the content of the email.
This works fine for a one line email by selecting that column in the
Comment field of the delivery extension screen but I need to create a
multi-line email.
I have tried storing the field with CHAR(13) & CHAR(10) separators in the
table but it still displays on one line when RS sends the email.
Does anyone know if this is possible and how I can achieve it ?The comment can be HTML. So make the comment use html to render on multiple
lines.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"magicboy" <magicboy@.discussions.microsoft.com> wrote in message
news:48673C18-06E1-47CA-A8C7-F6B691C404A4@.microsoft.com...
>I am trying to set up a data driven subscription where one of the fields
> returned from the query is sent as the content of the email.
> This works fine for a one line email by selecting that column in the
> Comment field of the delivery extension screen but I need to create a
> multi-line email.
> I have tried storing the field with CHAR(13) & CHAR(10) separators in the
> table but it still displays on one line when RS sends the email.
> Does anyone know if this is possible and how I can achieve it ?|||This works fine, thanks
"Daniel Reib [MSFT]" wrote:
> The comment can be HTML. So make the comment use html to render on multiple
> lines.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "magicboy" <magicboy@.discussions.microsoft.com> wrote in message
> news:48673C18-06E1-47CA-A8C7-F6B691C404A4@.microsoft.com...
> >I am trying to set up a data driven subscription where one of the fields
> > returned from the query is sent as the content of the email.
> > This works fine for a one line email by selecting that column in the
> > Comment field of the delivery extension screen but I need to create a
> > multi-line email.
> > I have tried storing the field with CHAR(13) & CHAR(10) separators in the
> > table but it still displays on one line when RS sends the email.
> > Does anyone know if this is possible and how I can achieve it ?
>
>

Data driven query - update only CERTAIN fields?

Hi,
I'm trying to set up a Data Driven Query task, to update only certain
fields in a table.
However, even though the update query only contains the fields I'm
wanting to update, when I try and run it I get:
"One or more destination parameter columns had no transform specified"
Thing is, I don't WANT to specify a transform for most of the
destination columns - I want them left alone!!
I would be so, so grateful if anyone who's done something like this
could help, as there are so precious few decent example of this sort of
thing on the net. Any links anyone has to good in-depth tutorials
covering more than just the basic 'update every single field' scenario
would be fantastic too.
Many, many thanks folks.
ChampersJust to quickly illustrate this (I'm not sure I explained this too well
yesterday)
The destination table (which is my binding table) has, let's say, 10
columns
During the update query, I only want maybe 2 fields to be updated
So my code would be something like...
Function Main()
If
IsEmpty(DTSLookups("DoesRecordExist").Execute(DTSSource("PersonID").Value))
Then
DTSDestination("Field3") = DTSSource("SURNAME")
DTSDestination("Field7") = DTSSource("FORENAME")
Main = DTSTransformstat_InsertQuery
Else..
End If
End Function
But I get the feeling that to avoid the error message above, I still
have to specify all the destination columns, even if I don't want to
update them...but, of course, I would have to set them to update to
something...which I don't want to do!
This is driving me crazy. Thanks in advance for any advice!|||I think I may have just cracked this, so I'll post the answer for
anyone else struggling with it. Basically, on the Transformations tab
(the one with the graphical list of all source and destination
columns), you have to select ALL destination columns (and presumably
all source ones too) regardless of whether you're using them in a query
or not. This is REALLY confusing, and I have not been able to find a
tutorial that explains this anywhere. I'm going to press on now with my
DTS task, and I'l post any other useful info I find on this thread, as
I'm sure other people must have been tearing their hair out over this.|||I'm nearly there with this now, but I have to admit it's such a
confusing thing to use.
I just have one more question that someone could maybe answer - I have
2 DDQ's, one to transfer data from some columns of the source table
into table 1, and another to transfer other columns into a separate
table, table 2.
Now, the first DDQ is OK. However, in the second, one of my queries
refers to a source column that doesn't directly transfer to a column in
table 2. Table 2 here is my binding table.
In order to 'reference' the source column, I'm having to basically map
that source column to a column in the binding 'version' of table 2 (one
that I'm not 'using' in this DDQ), so that I can use it in the
Parameters list.
Is this the correct way to do this? i.e. although the binding table
used is originally a real destination table, it's only actually used as
a way of mapping and referencing source colunmns, and in actual fact
bears no relevance to any data transformations (i.e. this 'mapping'
doesn't actually alter data in the destination column - only my QUERY
can does this, in which the destination table itself is used as a real
query destination table, rather than as the binding table).
Sorry to be so verbose...I'd be grateful if someone could set my mind
at ease and clarify that I've got this right in my head!
Thanks so much guys.

Data drive subscritpion sQL2000- Hide parameter for email recipien

Hi,
I am looking for a way to hide the parameter input fields in the report. I
want to avoid that customer could modify his assigned parameter. If I disable
option in report property "prompt for user" (I have German version
"Eingabeaufforderung für Benutzer") I cannot assign parameter from my "data
driven" source.
Thanks for help.Hi,
I would "normally" get what I want if I attach report to mail and not the
link. But everytime I try this the scheduled execution fails. If I try it
with a simple test report it works fine. In My report I use a dynamic MDX
query like
="With meber..." & Parameters!Para1.Value & ...
Maybe here is the issue?
"mathiasr" wrote:
> Hi,
> I am looking for a way to hide the parameter input fields in the report. I
> want to avoid that customer could modify his assigned parameter. If I disable
> option in report property "prompt for user" (I have German version
> "Eingabeaufforderung für Benutzer") I cannot assign parameter from my "data
> driven" source.
> Thanks for help.|||I found it myself:
I need a domain user instead of sql authentication (sa user) in the
datasource property. Then the execution of report works unattended and can be
emailed.
"mathiasr" wrote:
> Hi,
> I would "normally" get what I want if I attach report to mail and not the
> link. But everytime I try this the scheduled execution fails. If I try it
> with a simple test report it works fine. In My report I use a dynamic MDX
> query like
> ="With meber..." & Parameters!Para1.Value & ...
> Maybe here is the issue?
> "mathiasr" wrote:
> > Hi,
> >
> > I am looking for a way to hide the parameter input fields in the report. I
> > want to avoid that customer could modify his assigned parameter. If I disable
> > option in report property "prompt for user" (I have German version
> > "Eingabeaufforderung für Benutzer") I cannot assign parameter from my "data
> > driven" source.
> > Thanks for help.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

Data Conversion

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

If they are strings, what are you converting to in SQL Server?|||?|||

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

|||

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

|||It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.|||

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.

d99_tmp table?

i noticed a table named 'd99_tmp' appeared in one of my SQL db from nowhere.
the fields are 'subdirectory', 'dept' and 'file'. And it contains info about
my server harddisk file information.
Does anyone knows where this table comes from?
This is a User Created Table.
sp_help d99_tmp: This will help you to find who has created the table and
when?
sp_spaceused d99_tmp: This will tell size of the table and Row Count.
"twlancer" wrote:

> i noticed a table named 'd99_tmp' appeared in one of my SQL db from nowhere.
> the fields are 'subdirectory', 'dept' and 'file'. And it contains info about
> my server harddisk file information.
> Does anyone knows where this table comes from?
|||I know the which account was used to create this table. but none of my team
members did it. We changed the password and the table reappear days later.
"Absar Ahmad" wrote:
[vbcol=seagreen]
> This is a User Created Table.
> sp_help d99_tmp: This will help you to find who has created the table and
> when?
> sp_spaceused d99_tmp: This will tell size of the table and Row Count.
> "twlancer" wrote:
|||If noone is owning up to it and it reappears after dropping, trace it or
disable the account and see what fails.
"twlancer" <twlancer@.discussions.microsoft.com> wrote in message
news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...[vbcol=seagreen]
>I know the which account was used to create this table. but none of my team
> members did it. We changed the password and the table reappear days later.
> "Absar Ahmad" wrote:
|||How do I trace? I checked the SQL log and couldnt see any entries on this
d99_tmp table.
"Danny" wrote:

> If noone is owning up to it and it reappears after dropping, trace it or
> disable the account and see what fails.
> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
> news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...
>
>
|||Use Profiler to trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"twlancer" <twlancer@.discussions.microsoft.com> wrote in message
news:3ABA6BE8-99BB-4454-B053-C7B98804DEBC@.microsoft.com...[vbcol=seagreen]
> How do I trace? I checked the SQL log and couldnt see any entries on this
> d99_tmp table.
> "Danny" wrote:
|||Sounds like a 3rd party application is creating the table...some sort of
network monitoring thing perhaps given the info being stored. Maybe someone
installed this and pointed the utility to the wrong server/database
combination?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OsuQ2oB7FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Use Profiler to trace.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
> news:3ABA6BE8-99BB-4454-B053-C7B98804DEBC@.microsoft.com...
>

d99_tmp table?

i noticed a table named 'd99_tmp' appeared in one of my SQL db from nowhere.
the fields are 'subdirectory', 'dept' and 'file'. And it contains info about
my server harddisk file information.
Does anyone knows where this table comes from?This is a User Created Table.
sp_help d99_tmp: This will help you to find who has created the table and
when?
sp_spaceused d99_tmp: This will tell size of the table and Row Count.
"twlancer" wrote:
> i noticed a table named 'd99_tmp' appeared in one of my SQL db from nowhere.
> the fields are 'subdirectory', 'dept' and 'file'. And it contains info about
> my server harddisk file information.
> Does anyone knows where this table comes from?|||I know the which account was used to create this table. but none of my team
members did it. We changed the password and the table reappear days later.
"Absar Ahmad" wrote:
> This is a User Created Table.
> sp_help d99_tmp: This will help you to find who has created the table and
> when?
> sp_spaceused d99_tmp: This will tell size of the table and Row Count.
> "twlancer" wrote:
> > i noticed a table named 'd99_tmp' appeared in one of my SQL db from nowhere.
> > the fields are 'subdirectory', 'dept' and 'file'. And it contains info about
> > my server harddisk file information.
> >
> > Does anyone knows where this table comes from?|||If noone is owning up to it and it reappears after dropping, trace it or
disable the account and see what fails.
"twlancer" <twlancer@.discussions.microsoft.com> wrote in message
news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...
>I know the which account was used to create this table. but none of my team
> members did it. We changed the password and the table reappear days later.
> "Absar Ahmad" wrote:
>> This is a User Created Table.
>> sp_help d99_tmp: This will help you to find who has created the table and
>> when?
>> sp_spaceused d99_tmp: This will tell size of the table and Row Count.
>> "twlancer" wrote:
>> > i noticed a table named 'd99_tmp' appeared in one of my SQL db from
>> > nowhere.
>> > the fields are 'subdirectory', 'dept' and 'file'. And it contains info
>> > about
>> > my server harddisk file information.
>> >
>> > Does anyone knows where this table comes from?|||How do I trace? I checked the SQL log and couldnt see any entries on this
d99_tmp table.
"Danny" wrote:
> If noone is owning up to it and it reappears after dropping, trace it or
> disable the account and see what fails.
> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
> news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...
> >I know the which account was used to create this table. but none of my team
> > members did it. We changed the password and the table reappear days later.
> >
> > "Absar Ahmad" wrote:
> >
> >> This is a User Created Table.
> >>
> >> sp_help d99_tmp: This will help you to find who has created the table and
> >> when?
> >>
> >> sp_spaceused d99_tmp: This will tell size of the table and Row Count.
> >>
> >> "twlancer" wrote:
> >>
> >> > i noticed a table named 'd99_tmp' appeared in one of my SQL db from
> >> > nowhere.
> >> > the fields are 'subdirectory', 'dept' and 'file'. And it contains info
> >> > about
> >> > my server harddisk file information.
> >> >
> >> > Does anyone knows where this table comes from?
>
>|||Use Profiler to trace.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"twlancer" <twlancer@.discussions.microsoft.com> wrote in message
news:3ABA6BE8-99BB-4454-B053-C7B98804DEBC@.microsoft.com...
> How do I trace? I checked the SQL log and couldnt see any entries on this
> d99_tmp table.
> "Danny" wrote:
>> If noone is owning up to it and it reappears after dropping, trace it or
>> disable the account and see what fails.
>> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
>> news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...
>> >I know the which account was used to create this table. but none of my team
>> > members did it. We changed the password and the table reappear days later.
>> >
>> > "Absar Ahmad" wrote:
>> >
>> >> This is a User Created Table.
>> >>
>> >> sp_help d99_tmp: This will help you to find who has created the table and
>> >> when?
>> >>
>> >> sp_spaceused d99_tmp: This will tell size of the table and Row Count.
>> >>
>> >> "twlancer" wrote:
>> >>
>> >> > i noticed a table named 'd99_tmp' appeared in one of my SQL db from
>> >> > nowhere.
>> >> > the fields are 'subdirectory', 'dept' and 'file'. And it contains info
>> >> > about
>> >> > my server harddisk file information.
>> >> >
>> >> > Does anyone knows where this table comes from?
>>|||Sounds like a 3rd party application is creating the table...some sort of
network monitoring thing perhaps given the info being stored. Maybe someone
installed this and pointed the utility to the wrong server/database
combination?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OsuQ2oB7FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Use Profiler to trace.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
> news:3ABA6BE8-99BB-4454-B053-C7B98804DEBC@.microsoft.com...
>> How do I trace? I checked the SQL log and couldnt see any entries on this
>> d99_tmp table.
>> "Danny" wrote:
>> If noone is owning up to it and it reappears after dropping, trace it or
>> disable the account and see what fails.
>> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
>> news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...
>> >I know the which account was used to create this table. but none of my
>> >team
>> > members did it. We changed the password and the table reappear days
>> > later.
>> >
>> > "Absar Ahmad" wrote:
>> >
>> >> This is a User Created Table.
>> >>
>> >> sp_help d99_tmp: This will help you to find who has created the table
>> >> and
>> >> when?
>> >>
>> >> sp_spaceused d99_tmp: This will tell size of the table and Row Count.
>> >>
>> >> "twlancer" wrote:
>> >>
>> >> > i noticed a table named 'd99_tmp' appeared in one of my SQL db from
>> >> > nowhere.
>> >> > the fields are 'subdirectory', 'dept' and 'file'. And it contains
>> >> > info
>> >> > about
>> >> > my server harddisk file information.
>> >> >
>> >> > Does anyone knows where this table comes from?
>>
>|||i have the same problem, something has created d99_tmp table in sql 2000
database (with c:\ listing in table). Table was created with login that use
my web site to read and write data (nothing else), so i think its some sort
of hack. Or some automatic hacking/checking tools. Know someone something
more about it?|||"sql2000" <thakx@.hi.com> wrote in message
news:bc15216f81d1471cac3a652d8eb0fdfb@.ureader.com...
>i have the same problem, something has created d99_tmp table in sql 2000
> database (with c:\ listing in table). Table was created with login that
> use
> my web site to read and write data (nothing else), so i think its some
> sort
> of hack. Or some automatic hacking/checking tools. Know someone something
> more about it?
Do you have xp_cmdshell enabled? How much permissions does that web account
have? Does your web server use dynamic SQL?
David|||I'd like to hear if anyone has more information on this issue. I also have a
D99_Tmp table in my database that was created by the user that my web
appication uses. The table contains a directory listing of the "D" drive on
the database server as shown below:
subdirectory depth file
Microsoft SQL Server 1 0
RECYCLER 1 0
System Volume Information 1 0
tlogs_backup 1 0
WUTemp 1 0
This appears to be a SQL injection exploit. What is suprising to me is the
directory listing is correct. The user is a SQL user and not a windows user.
The database server is behind a firewall and can only be accessed via the web
application. Comments? Info? Ideas?

d99_tmp table?

i noticed a table named 'd99_tmp' appeared in one of my SQL db from nowhere.
the fields are 'subdirectory', 'dept' and 'file'. And it contains info about
my server harddisk file information.
Does anyone knows where this table comes from?This is a User Created Table.
sp_help d99_tmp: This will help you to find who has created the table and
when?
sp_spaceused d99_tmp: This will tell size of the table and Row Count.
"twlancer" wrote:

> i noticed a table named 'd99_tmp' appeared in one of my SQL db from nowher
e.
> the fields are 'subdirectory', 'dept' and 'file'. And it contains info abo
ut
> my server harddisk file information.
> Does anyone knows where this table comes from?|||I know the which account was used to create this table. but none of my team
members did it. We changed the password and the table reappear days later.
"Absar Ahmad" wrote:
[vbcol=seagreen]
> This is a User Created Table.
> sp_help d99_tmp: This will help you to find who has created the table and
> when?
> sp_spaceused d99_tmp: This will tell size of the table and Row Count.
> "twlancer" wrote:
>|||If noone is owning up to it and it reappears after dropping, trace it or
disable the account and see what fails.
"twlancer" <twlancer@.discussions.microsoft.com> wrote in message
news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...[vbcol=seagreen]
>I know the which account was used to create this table. but none of my team
> members did it. We changed the password and the table reappear days later.
> "Absar Ahmad" wrote:
>|||How do I trace? I checked the SQL log and couldnt see any entries on this
d99_tmp table.
"Danny" wrote:

> If noone is owning up to it and it reappears after dropping, trace it or
> disable the account and see what fails.
> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
> news:39B5254C-87EE-478A-B006-1B80329681A8@.microsoft.com...
>
>|||Use Profiler to trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"twlancer" <twlancer@.discussions.microsoft.com> wrote in message
news:3ABA6BE8-99BB-4454-B053-C7B98804DEBC@.microsoft.com...[vbcol=seagreen]
> How do I trace? I checked the SQL log and couldnt see any entries on this
> d99_tmp table.
> "Danny" wrote:
>|||Sounds like a 3rd party application is creating the table...some sort of
network monitoring thing perhaps given the info being stored. Maybe someone
installed this and pointed the utility to the wrong server/database
combination?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OsuQ2oB7FHA.3760@.TK2MSFTNGP14.phx.gbl...
> Use Profiler to trace.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "twlancer" <twlancer@.discussions.microsoft.com> wrote in message
> news:3ABA6BE8-99BB-4454-B053-C7B98804DEBC@.microsoft.com...
>|||i have the same problem, something has created d99_tmp table in sql 2000
database (with c:\ listing in table). Table was created with login that use
my web site to read and write data (nothing else), so i think its some sort
of hack. Or some automatic hacking/checking tools. Know someone something
more about it?|||"sql2000" <thakx@.hi.com> wrote in message
news:bc15216f81d1471cac3a652d8eb0fdfb@.ur
eader.com...
>i have the same problem, something has created d99_tmp table in sql 2000
> database (with c:\ listing in table). Table was created with login that
> use
> my web site to read and write data (nothing else), so i think its some
> sort
> of hack. Or some automatic hacking/checking tools. Know someone something
> more about it?
Do you have xp_cmdshell enabled? How much permissions does that web account
have? Does your web server use dynamic SQL?
David|||xp is disabled - Could not find stored procedure 'xp_cmdshell'.
user have permisisson
-public
-db_owner
-db_backupoperator
-db_datareader
-db_datawriter
so how can this user get directory list? i know there is a way run some
commands trought the query parameters (if someone find a hole), but
directory structure table hmm.

d99_tmp

I noticed a table named 'd99_tmp' in my SQL Server 2000 db. I nor
anyone on my team creatd the this table. The fields are
'subdirectory', 'depth' and 'file'. It contains basically the entire
file structure of the D: drive on the server.
The table has the permissions of the web application that uses that
SQL Server database.
This appears to be a SQL Injection attack and I know that there is
some dynamic SQL used in the application.
Any suggestions (other than removing all of the public facing dynamic
SQL)?
Thanks!Hi
"gdunnjr" wrote:

> I noticed a table named 'd99_tmp' in my SQL Server 2000 db. I nor
> anyone on my team creatd the this table. The fields are
> 'subdirectory', 'depth' and 'file'. It contains basically the entire
> file structure of the D: drive on the server.
> The table has the permissions of the web application that uses that
> SQL Server database.
> This appears to be a SQL Injection attack and I know that there is
> some dynamic SQL used in the application.
> Any suggestions (other than removing all of the public facing dynamic
> SQL)?
> Thanks!
>
Check out http://www.sommarskog.se/dynamic_sql.html#SQL_injection and the
rest of the article about dynamic SQL. Make sure that overall you have
tightened up permissions so the minimum access is granted to provide the
functionality required. You could run Best Practice Analyser
http://www.microsoft.com/downloads/...&displaylang=en and the Microsoft Security Assessment
Tool https://www.securityguidance.com/faq.htm reading the Windows Server
2003 Security Guide may also help
http://www.microsoft.com/downloads/...&displaylang=en
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:172DFD61-F6D1-496A-B591-79B4B3F1EF0A@.microsoft.com...
> Hi
> "gdunnjr" wrote:
>
Exactly that. :-)
But also make sure the user doesn't have DDL permissins like that.
Won't prevent all SQL Injection attacks, but if the user can't
create/drop/alter tables (or execute xp_cmdshell) you'll be a lot better
off.
[vbcol=seagreen]
> Check out http://www.sommarskog.se/dynamic_sql.html#SQL_injection and the
> rest of the article about dynamic SQL. Make sure that overall you have
> tightened up permissions so the minimum access is granted to provide the
> functionality required. You could run Best Practice Analyser
> http://www.microsoft.com/downloads/...&displaylang=en
> and the Microsoft Security Assessment
> Tool https://www.securityguidance.com/faq.htm reading the Windows Server
> 2003 Security Guide may also help
> http://www.microsoft.com/downloads/...&displaylang=en
> John
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks for the information. I verified that the application user does
not have access to xp_cmdshell.
But the application user is assigned the role "db_owner". I would like
to remove that role and assign the roles: db_reader and db_writer.
I know that db_owner lets you execute DDL statements, but what are the
other major priviliges the user lose and/or risks of changing the user
from db_owner to db_reader and db_writer?
On Apr 12, 9:56 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> news:172DFD61-F6D1-496A-B591-79B4B3F1EF0A@.microsoft.com...
>
>
>
>
>
>
>
>
> Exactly that. :-)
> But also make sure the user doesn't have DDL permissins like that.
> Won't prevent allSQLInjectionattacks, but if the user can't
> create/drop/alter tables (or execute xp_cmdshell) you'll be a lot better
> off.
>
>
>
>
> --
> Greg MooreSQLServer DBA Consulting Remote and Onsite available!
> Email:sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html-[/url
] Hide quoted text -
> - Show quoted text -|||"gdunnjr" <gdunnjr@.gmail.com> wrote in message
news:1176467724.629968.31880@.y5g2000hsa.googlegroups.com...
> Thanks for the information. I verified that the application user does
> not have access to xp_cmdshell.
> But the application user is assigned the role "db_owner". I would like
> to remove that role and assign the roles: db_reader and db_writer.
> I know that db_owner lets you execute DDL statements, but what are the
> other major priviliges the user lose and/or risks of changing the user
> from db_owner to db_reader and db_writer?
Make sure the user has all stored procs, functions execution privileges (at
least all it should.)

> On Apr 12, 9:56 am, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html