How do I manage this mess? Thanks!

Say for example I have the following 2 database tables, the first one contains the old employee data, and has the fields shown below:

oldEmployeeID

FirstName

LastName

DateOfBirth

HiringDate

TerminationDate

and another one containing the new employee data with similar fields but instead of oldEmployeeID, it is showing the newEmployeeID.

During the conversion process, something were messed up and instead of putting in the original hiring date of the workers into the new employee database, the conversion date was put in, which, depending on the mood of HR ladies, could be any date, and at the same time, of course, new employee join the company, and we assume their hiring dates were entered correctly. On top of that, there are some employee who were terminated before the conversion took place but we still need to keep a record of that.

And I created a third table, say, emplyeeAll with similar fields to the employee data tables.

So here is what I need to do: if the firstName, lastName and DateOfBirth in the old employee data table and the new employee data table matches, I would assume they are the same employee, hence I would put the information for the employee obtained from the new employee data table to the employeeAll table, with the Hiring Date changed to the Hiring Date of the old employee data table (and do not copy the record from the old employee table to prevent duplicates), otherwise, I would simply copy and paste the data in new and old employee table to my employeeAll table.

I know this is really confusing, but...well...hope you know what I am saying...

Is it possible to have a SQL statement for all these? If so, how should the statement looks like?

Thanks a lot!

Regards,

Anyi

Hi,

If I understand this correctly, (I think I do....perhaps) ... if you have data in two tables (and I know you have three, the last would be the destinationtable employeeAll) and you wish to collect the difference between table 1 and 2 (employeeOld, employeeNew) then the left outer join is your answer...

--If you wish to collect differential rows from old table

select employeeOld.*

from employeeOld

left outer join employeeNew on employeeOld.FirstName = employeeNew.FirstName and employeeOld.DateOfBirth = employeeNew.DateOfBirth

where employeeNew.DateOfBirth is null

Similarly you can mix 'n match the above to collect differetial data from the other two tables...note the 'select' is table where the additional data lies and the where clause (is null) is the comparison table.

Hope it helps

|||

I thinks that something like this is what you want:

Code Snippet

-- This collects the data
INSERT INTO EmployeeAll
SELECT
n.EmployeeID,
n.LastName,
n.FirstName,
n.DateOfBirth,
coalesce( o.HiringDate, n.HiringDate ),
coalesce( o.TerminationDate, n.TerminationDate ),
n.{RemainingColumns}
FROM NewEmployees n
LEFT JOIN OldEmployees o
ON ( n.LastName = o.LastName
AND n.FirstName = o.FirstName
AND n.DateOfBirth = o.DateOfBirth
)

|||

But would this actually copy the rest of the record into EmployeeAll, i.e., the records that only showed up in the old employee table (the employees terminated before the conversion took place) or the records that only showed up on the new employee table (i.e., the employees hired after the conversion was completed)?

Thanks!

Regards,

Anyi

Arnie Rowland wrote:

I thinks that something like this is what you want:

Code Snippet

-- This collects the data
INSERT INTO EmployeeAll
SELECT
n.EmployeeID,
n.LastName,
n.FirstName,
n.DateOfBirth,
coalesce( o.HiringDate, n.HiringDate ),
coalesce( o.TerminationDate, n.TerminationDate ),
n.{RemainingColumns}
FROM NewEmployees n
LEFT JOIN OldEmployees o
ON ( n.LastName = o.LastName
AND n.FirstName = o.FirstName
AND n.DateOfBirth = o.DateOfBirth
)

|||

If you want both the records in the old table that don't exists in the new table, the records in the new table that don't exists in the old table, and the records that are in both (with the corrected HiringDate), then change the JOIN from a LEFT JOIN to a FULL JOIN. (This works in SQL 2005 -NOT SQL 2000.)

If you are using SQL 2000, you will need three queries to accomplish the same task.

How do I link two different field lengths with the same data

Hi,
In Crystal Reports 8.5, I am linking one file to another but do not get report data. The reason is the fields I am using to link one string is 4 characters while the other is 6. Is there a way to get two fields containing the same data in different files but wth different field lengths to link. thank youBump. This post is old, but I'm needing to do basically the same thing. I've got Crystal Reports 11.

Thanks

how do I keep a table from shifting items below the table

I have a table on a report that will have the max of 6 rows and below that I
need to have other information on the report to print onto fields on a form,
when the rows change in the table the textboxes below get shifted. How do I
stop this from happening?
Thanks,
JimMake your textboxes as large as they should grow and turn off 'CanGrow'.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim Ciotuszynski" <jimcio@.hotmail.com> wrote in message
news:uu2Kr%237WEHA.1128@.TK2MSFTNGP10.phx.gbl...
>I have a table on a report that will have the max of 6 rows and below that
>I
> need to have other information on the report to print onto fields on a
> form,
> when the rows change in the table the textboxes below get shifted. How do
> I
> stop this from happening?
> Thanks,
> Jim
>

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

How do I hide zeros w/o a conditional stmt

I need a way to format fields without having to write a conditional statement
based on each textbox's value. I'm dealing with hundreds of fields.
Something like a HideZeros setting on the field. Any help would be
appreciatedWhat I do is change the Format property of each textbox to a custom value:
#,##0.0;(#,##0.0);-
the 1st group is how to display positive values;
the 2nd is for negatives;
and the 3rd is for zeroes (I am just displaying a dash here).
good luck,
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:64AAF2A7-F92B-4E91-8F96-3A9AAB2088FC@.microsoft.com...
>I need a way to format fields without having to write a conditional
>statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||Thanks!
Worked great. Any idea on how to use that with a Percentage field.
"Blake Gremillion" wrote:
> I need a way to format fields without having to write a conditional statement
> based on each textbox's value. I'm dealing with hundreds of fields.
> Something like a HideZeros setting on the field. Any help would be
> appreciated|||I would try custom again and this time use:
0.0%;(0.0%);-
I pesonally use custom with percentages anyways and use
P1
to get just one decimal place. I think the standard "percentage" option,
gives you two. Of course neither hides zeroes. ;(
Greg
"Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> Thanks!
> Worked great. Any idea on how to use that with a Percentage field.
>
>
> "Blake Gremillion" wrote:
>> I need a way to format fields without having to write a conditional
>> statement
>> based on each textbox's value. I'm dealing with hundreds of fields.
>> Something like a HideZeros setting on the field. Any help would be
>> appreciated|||Yeah, I think that's what I'll have to do instead of using the Px format.
Thanks Again!
"Greg Burns" wrote:
> I would try custom again and this time use:
> 0.0%;(0.0%);-
> I pesonally use custom with percentages anyways and use
> P1
> to get just one decimal place. I think the standard "percentage" option,
> gives you two. Of course neither hides zeroes. ;(
> Greg
>
> "Blake Gremillion" <BlakeGremillion@.discussions.microsoft.com> wrote in
> message news:0B62BEDB-123A-4B5A-B363-ACAF9759425C@.microsoft.com...
> > Thanks!
> >
> > Worked great. Any idea on how to use that with a Percentage field.
> >
> >
> >
> >
> > "Blake Gremillion" wrote:
> >
> >> I need a way to format fields without having to write a conditional
> >> statement
> >> based on each textbox's value. I'm dealing with hundreds of fields.
> >> Something like a HideZeros setting on the field. Any help would be
> >> appreciated
>
>

How do I handle single quotes in data fields

I want to set company name in table company to "Don's Autoworks". When I run
a simple query:
update company
set company_name = 'Don''s Autoworks'
where id = 125
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 's'.
However, select queries with 2 single quotes like below work fine:
select * from company
where company_name = 'Mel''s Diner'
What should I do to insert or update data with single quotation marks in it.
Thanks,
Ashhad.>I want to set company name in table company to "Don's Autoworks". When I
>run
> a simple query:
> update company
> set company_name = 'Don''s Autoworks'
> where id = 125
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near 's'.
This query should work. Where are you running it?
A|||Query Analyzer, SQL Server 2000, on Windows 2000
"Aaron Bertrand [SQL Server MVP]" wrote:

> This query should work. Where are you running it?
> A
>
>|||The query seems correct. Check it again
Madhivanansql

How do I get number of fields returned by query?

Hi.

I am trying to get the results of a dynamic sql statement into a #table, in order to filter them. Given that I don't know how many fields will be returned, how do I accomplish this?

I believe I need to create the #table in advance, and then run the dynamic string as part of an insert [eg 'insert into #table exec (@.sql)' ], but in order to do this I need to know how many fields are going to be returned.

The results might also be returned by a procedure rather than a simple SQL statement, so I can't just parse @.sql to get the fields.

As an example,

declare @.sql nvarchar(200)

select @.sql = 'select "a" as ColA, "b" as ColB'

exec sp_executesql @.sql

returns two columns of data.

I think I need to get the results, count the fields, create the table and finally re-run the query with an insert to poulate the table. So how do I count the columns? And for bonus points, how do I get the column names?

Many thanks,

Neil Jones

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..)

|||

Thanks for your reply

Nitin Khurana wrote:

u can do this (though not advisable...)

select col1,col2.....

into #temp

from ...select condition...

this will create a table and insert into it at runtime....

The problem here is that I can't edit the query. All I know at runtime is that it's a dynamic sql statement, which could be a simple select, or an execution of a stored procedure

Nitin Khurana wrote:

or u can just create a temp table with 1 col and alter it dynamically as per ur requirement when u get the number of columns....

My question is, how do I get the number of columns?

Nitin Khurana wrote:

(@.@.ROWCOUNT is the system variable which returns the num of rows returned ny the query

select @.@.ROWNUM --after the query 'just in case ur pivoting the result and puttin in the temp table..

Unfortunately it's not the number of rows that is the problem.

Cheers,

Neil Jones

|||

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

|||

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

|||

Try this:

SELECT * INTO #Temp
FROM (<Your SQL Query>) as T;

Note: If the columns returned by the query aren't properly named (no column name), this will not work. Otherwise the table will be created automatically with the column names from the query. Once the table ist created, you can access the Information about the columns from system views:

USE tempdb
GO
SELECT COUNT(*) FROM sys.columns WHERE object_id = (SELECT object_id FROM sys.tables WHERE name like '#Test%');

This will work with MS SQL Server 2005

Regards,

Paddy

|||

Here you go with a complete sample when having the statement as a variable:

USE AdventureWorks
GO

SET NOCOUNT ON;
GO

-- Create a Table for Testing
CREATE TABLE Test
( col1 INT NOT NULL
, col2 VARCHAR(20) NOT NULL
);
GO

-- Some useful Information
INSERT INTO Test VALUES (1, 'Test1');
INSERT INTO Test VALUES (2, 'Test2');
INSERT INTO Test VALUES (3, 'Test3');
INSERT INTO Test VALUES (4, 'Test4');
INSERT INTO Test VALUES (5, 'Test5');
INSERT INTO Test VALUES (6, 'Test6');
INSERT INTO Test VALUES (7, 'Test7');
INSERT INTO Test VALUES (8, 'Test8');
INSERT INTO Test VALUES (9, 'Test9');
INSERT INTO Test VALUES (10, 'Test10');
GO

-- The dynamic sql statement
DECLARE @.sql NVARCHAR(200);
SET @.sql = 'SELECT col2, col1 FROM Test';

-- Extend the statement for creating a temporary table (Note: Use Global Temporary Table (##TableName))
DECLARE @.sql2 NVARCHAR(250)
set @.sql2 = 'SELECT * INTO ##Test FROM (' + @.sql + ') AS T;';

-- Run the extended Statement
EXEC(@.sql2);
GO

-- View the result
SELECT * FROM ##Test;
GO

-- Get Information about the Temporary Table
USE tempdb
GO

-- Column Count
SELECT COUNT(*) FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- Names of Columns
SELECT name FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE name LIKE '##Test%');

-- CleanUp
USE AdventureWorks
GO

-- Drop the temporary Table
DROP TABLE ##Test;
GO

-- Drop the Test Data Table
DROP TABLE Test;
GO

SET NOCOUNT OFF;

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of you table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Neil_D_Jones wrote:

PersianAmir wrote:

hi
first use sys tables and fetch count of table.
then use count of column(s) in the dynamic query
good luck

Hi.

I don't know what columns are in the query, and I don't know which table(s) (if any) the query is looking at.

If I knew which columns were being returned, this wouldn't be a problem.

Regards,

Neil

hi
use this query for return columns of your table:

select dbo.syscolumns.name from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

and use this query for return count of your field:

select count(dbo.syscolumns.name) from dbo.syscolumns
inner join dbo.sysobjects on dbo.syscolumns.id = dbo.sysobjects.id
where dbo.sysobjects.name = 'TABLE_NAME'

good luck

|||

Lucky P wrote:

Here you go with a complete sample when having the statement as a variable:

That looks great, thanks!

I think I'm going to have to accept that it's not possible with all dynamic sql statements, such as "exec xp_proc1", and take what I can.

Cheers,

Neil

How do I get just one set?

I am joining two tables in an SQL query.
Table 1: item has three fields, id, org_id, and name
Table 2: item_set has five fields item_id, org_id, leftIndex, depth, and
rightIndex
The item_set table is used to help sort structures hierarchically.
For example the following query would return a parent item and all its
children starting at the top of a tree:
Select
Itm1.id as parent_id,
Itm1.name as parent_name,
Is1.leftIndex as parentIndex,
Itm2.id as child_id,
Itm2.name as child_name,
Is2.leftIndex as childIndex,
Is2.depth as degree_of_separation
From
item as itm1
Inner join
Item_set as is1
On (itm1.id = is1.item_id and is1.depth = 0)
And (itm1.org_id = is1.org_id)
Inner join
Item_set as is2
On (is2.leftIndex between is1.leftIndex and is1.rightIndex)
And (is1.item_id <> is2.item_id)
And (is1.org_id = is2.org_id)
Inner join
Item as itm2
On (is2.item_id = itm2.id)
And (itm2.org_id = is2.org_id)
order by itm1.id , itm2.id
The trouble is that there may be different representations of trees for the
same items in the item_set table. One representation of four items might
have leftIndex values 1, 2, 3, 4 for the parent and its three children and
there could be another set of leftIndex values of 20, 21, 22, 24 for the
very same set of items.
My question for the SQL experts out there is how do I write a query to get
only one set of the items such as 1, 2, 3, 4 ? I can't assume that the
sequence will always be starting at the top of the tree, e.g. depth = 0.
27-1006Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, rows are not records and columns are not fields, there is not
such things as a magical "id", a vague "name", etc. Read ISO_11179 for
the proper way to name things
CREATE TABLE Items
(item_id INTEGER NOT NULL ,
org_id INTEGER NOT NULL,
PRIMARY KEY(item_id, org_id),
item_name CHAR(15) NOT NULL);
Is this a nested sets model!
CREATE TABLE ItemSets
(item_id INTEGER NOT NULL
org_id INTEGER NOT NULL,
FOREIGN KEY (item_id, org_id),
REFERENCES Items (item_id, org_id)
ON DELETE CASCADE -- guess at biz rule
ON UPDATE CASCADE,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0) ,
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
depth is computable, so do not store it. It will get out of synch and
screw up things.
Get a copy of TREES & HIERARCHIES IN SQL and look at chapter about
compare sub-tree structures. I am not going to give you a few
thoiusand words and illustrations in a newsgroup.
The basic idea is pick the root node. find the first subtree and
substract MIN(lft) from the lft and rgt values. Find the second
subtree and repeat the process. UNION ALL the two canonical subtrees
1) if you have a table with exactly duplicated rows. the subtress are
identical
2) if the nodes match, but not the (lft, rgt) pairs, they are different
arrangements of the same nodes
3) If the nodes do not match, but the (lft, rgt) pairs do, the have the
same structure with different nodes.
The pictures will help when you buy the book.
Here is query for depth. Assume an organizational chart in a Nested
Set model.
SELECT COUNT(O2.emp) AS depth, O1.emp
FROM OrgChart AS O1, OrgChart AS O2
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
GROUP BY O1.lft, O1.emp;

How do I get a Fields List with Stored Procedures

If I type in a query I get a fields list. However I do not with a stored
procedure. I have tried pressing the Refresh button. This does not work. I
have tried entering the fields manually, but when I get to the value column,
the expression builder tells me that the dataset has no fields. I can run
the stored procedure in Reporting Services and get the correct results. I
just cannot figure out how to get a dataset to see the fields in a stored
procedure.
I've been trying everything I can think of for two days. Does anyone out
there know how to get the fields to show with a stored procedure?The value will be the same as the field name and the type is databasefield
so for example:
fieldname type value
CREW DatabaseField CREW
AND SO ON
You can't use the expression to pick fields because you have not entered the
fields manually. I find I have to enter my fields manually if I have temp
tables used in my stored proc
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||They should show automatically when you execute the dataset on the Data tab.
Are you sure you have a command type of 'storedprocedure'?
Worst case scenario try deleting your dataset and recreating it.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:6C363085-B23B-4B5B-AC32-1C0C1DA4AF3F@.microsoft.com...
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work.
> I
> have tried entering the fields manually, but when I get to the value
> column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||I have tried that. I have also tried right-clicking on the fields list pane
and adding fields. Either way when I try to pull the fields into the report,
they get put in there as SUM (Fields.fieldname.value). When I edit the sum
function away, I get an error saying that the field is not in the dataset.
When I hit Refresh, the fields disappear.
There are no temp tables or table variables in the stored procedure. It
returns one resultset.
How do I delete a dataset?
"John Grant" wrote:
> The value will be the same as the field name and the type is databasefield
> so for example:
> fieldname type value
> CREW DatabaseField CREW
> AND SO ON
> You can't use the expression to pick fields because you have not entered the
> fields manually. I find I have to enter my fields manually if I have temp
> tables used in my stored proc
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I fought with the same issue for 2 days when first working with sprocs in
rs. after you set the command type to stored procedure, run it it once from
the data window. Then refresh it. that did the trick for me. if you have
parameters, don't forget to include them as well.
--
John Cleveland
Network Manager
Urban systems Ltd
"Liz" wrote:
> If I type in a query I get a fields list. However I do not with a stored
> procedure. I have tried pressing the Refresh button. This does not work. I
> have tried entering the fields manually, but when I get to the value column,
> the expression builder tells me that the dataset has no fields. I can run
> the stored procedure in Reporting Services and get the correct results. I
> just cannot figure out how to get a dataset to see the fields in a stored
> procedure.
> I've been trying everything I can think of for two days. Does anyone out
> there know how to get the fields to show with a stored procedure?|||mmmmmmmmmmm, do you have set nocount on inside your stored proc?
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have the button to delete dataset while in the data tab
"Liz" wrote:
> I have tried that. I have also tried right-clicking on the fields list pane
> and adding fields. Either way when I try to pull the fields into the report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
> > The value will be the same as the field name and the type is databasefield
> > so for example:
> >
> > fieldname type value
> > CREW DatabaseField CREW
> > AND SO ON
> >
> > You can't use the expression to pick fields because you have not entered the
> > fields manually. I find I have to enter my fields manually if I have temp
> > tables used in my stored proc
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||You have several good suggestions. Here is a list:
1. Click on the refresh fields button (to the right of the ...)
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
5. If your stored procedure calls another stored procedure then try this:
add Set FMTONLY Off (the following is from Simon Sabin a SQL Server MVP).
"The issue with RS is that the rowset of the SP is defined by calling the SP
with SET FMTONLY ON because Temp tables don't get created if you select from
the temp table the metadata from the rowset can't be returned. This can be
worked around by turning FMTONLY OFF in the SP."
One of the 5 above should solve your problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Liz" <Liz@.discussions.microsoft.com> wrote in message
news:59D6F2CC-1CE4-450F-9D55-74B724CB5AAF@.microsoft.com...
>I have tried that. I have also tried right-clicking on the fields list
>pane
> and adding fields. Either way when I try to pull the fields into the
> report,
> they get put in there as SUM (Fields.fieldname.value). When I edit the
> sum
> function away, I get an error saying that the field is not in the dataset.
> When I hit Refresh, the fields disappear.
> There are no temp tables or table variables in the stored procedure. It
> returns one resultset.
> How do I delete a dataset?
> "John Grant" wrote:
>> The value will be the same as the field name and the type is
>> databasefield
>> so for example:
>> fieldname type value
>> CREW DatabaseField CREW
>> AND SO ON
>> You can't use the expression to pick fields because you have not entered
>> the
>> fields manually. I find I have to enter my fields manually if I have
>> temp
>> tables used in my stored proc
>> "Liz" wrote:
>> > If I type in a query I get a fields list. However I do not with a
>> > stored
>> > procedure. I have tried pressing the Refresh button. This does not
>> > work. I
>> > have tried entering the fields manually, but when I get to the value
>> > column,
>> > the expression builder tells me that the dataset has no fields. I can
>> > run
>> > the stored procedure in Reporting Services and get the correct results.
>> > I
>> > just cannot figure out how to get a dataset to see the fields in a
>> > stored
>> > procedure.
>> >
>> > I've been trying everything I can think of for two days. Does anyone
>> > out
>> > there know how to get the fields to show with a stored procedure?|||I'm afraid that none of this is working. I deleted the dataset and recreated
it. I commented out the "Set Nocount On". I ran the stored procedure and
got the result set. I pressed Refresh. No good.
Any other ideas?
"John Cleveland" wrote:
> Liz,
> I fought with the same issue for 2 days when first working with sprocs in
> rs. after you set the command type to stored procedure, run it it once from
> the data window. Then refresh it. that did the trick for me. if you have
> parameters, don't forget to include them as well.
> --
> John Cleveland
> Network Manager
> Urban systems Ltd
>
> "Liz" wrote:
> > If I type in a query I get a fields list. However I do not with a stored
> > procedure. I have tried pressing the Refresh button. This does not work. I
> > have tried entering the fields manually, but when I get to the value column,
> > the expression builder tells me that the dataset has no fields. I can run
> > the stored procedure in Reporting Services and get the correct results. I
> > just cannot figure out how to get a dataset to see the fields in a stored
> > procedure.
> >
> > I've been trying everything I can think of for two days. Does anyone out
> > there know how to get the fields to show with a stored procedure?|||Liz,
I know how frustrating it was for me to get it going as well. So if you
would like you can contact me dierctly. I'm not with MS at all, but do know
how to get it to work. Use my contact info below if you wish.
--
John Cleveland
Network Manager
Urban systems Ltd
office: (250) 762-2517
jcleveland@.urban-systems.com
"Liz" wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> >
> >
> >
> > "Liz" wrote:
> >
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > >
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure?|||On Apr 12, 6:02 pm, Liz <L...@.discussions.microsoft.com> wrote:
> I'm afraid that none of this is working. I deleted the dataset and recreated
> it. I commented out the "Set Nocount On". I ran the stored procedure and
> got the result set. I pressed Refresh. No good.
> Any other ideas?
>
> "John Cleveland" wrote:
> > Liz,
> > I fought with the same issue for 2 days when first working with sprocs in
> > rs. after you set the command type to stored procedure, run it it once from
> > the data window. Then refresh it. that did the trick for me. if you have
> > parameters, don't forget to include them as well.
> > --
> > John Cleveland
> > Network Manager
> > Urban systems Ltd
> > "Liz" wrote:
In your SP make a select * into temptable from ...
and then in the last line write select * from temptable
When you do that if you refresh the fields appear, after that just
remove the into command and the "select * from temptable" but you
can't refresh otherwise the fields disappear.
> > > If I type in a query I get a fields list. However I do not with a stored
> > > procedure. I have tried pressing the Refresh button. This does not work. I
> > > have tried entering the fields manually, but when I get to the value column,
> > > the expression builder tells me that the dataset has no fields. I can run
> > > the stored procedure in Reporting Services and get the correct results. I
> > > just cannot figure out how to get a dataset to see the fields in a stored
> > > procedure.
> > > I've been trying everything I can think of for two days. Does anyone out
> > > there know how to get the fields to show with a stored procedure... Hide quoted text -
> - Show quoted text -

How do I format mailing address fields for display in reports

I am very new to development with SQL Server but I have lots of
experience with Access. I am producing a report in VB.Net using
CrystalReports that will display a company's address differently
depending on the contents of the data. For example, if the second
address line is NULL then I don't want it to display at all or if the
customer is not from the US then I want the country field displayed. My
database design is your typical address line 1, line 2, city, state,
postal code, country with lookup tables providing the full text for
state/provinces and countries. There's nothing fancy with the data.
My learning curve is both with Crystal and with SQL Server. I think
I'm better off trying to write a function in Transact SQL that I
could call from the query that VB.Net will use to create the XML that
will drive the report. I simply want to send a completely formatted
string to VB.Net, including commas, spaces and carriage returns and
line feeds. Please let me know if I'm nuts. I'm basing this
decision on the fact that Crystal is not the easiest tool to deal with
and that I probably have a better chance doing it on the server end.
I also have to believe that I'm not the first one who's ever wanted
to do this and am hoping that this code is out there somewhere for me
to legally pilfer and modify. I'm posting because my searches have so
far been unsuccessful.
Now, if anyone in Redmond is listening, those VB developers need to
walk down the hall and talk to the Access developers! I REALLY miss the
Access report writer and query builder. Their functionality and
productivity are superb. I find myself often linking Access to my SQL
Server databases, going into Access to build my query and then cutting
and pasting the SQL into Enterprise Manager. Now, if I could just write
my user-defined functions in VB or C then the world would then be a
much prettier and productive place.I kind of understand where you are coming from here, but you are probably
best served by doing this kind of formatting within crystal rather than in
T-SQL. This really is a presentation issue rather than a data issue.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>|||There is a *crystal* related microsoft newsgroup.
"Foofs" <marta@.mindcrafted.com> wrote in message
news:1132331118.184758.42090@.g43g2000cwa.googlegroups.com...
>I am very new to development with SQL Server but I have lots of
> experience with Access. I am producing a report in VB.Net using
> CrystalReports that will display a company's address differently
> depending on the contents of the data. For example, if the second
> address line is NULL then I don't want it to display at all or if the
> customer is not from the US then I want the country field displayed. My
> database design is your typical address line 1, line 2, city, state,
> postal code, country with lookup tables providing the full text for
> state/provinces and countries. There's nothing fancy with the data.
> My learning curve is both with Crystal and with SQL Server. I think
> I'm better off trying to write a function in Transact SQL that I
> could call from the query that VB.Net will use to create the XML that
> will drive the report. I simply want to send a completely formatted
> string to VB.Net, including commas, spaces and carriage returns and
> line feeds. Please let me know if I'm nuts. I'm basing this
> decision on the fact that Crystal is not the easiest tool to deal with
> and that I probably have a better chance doing it on the server end.
> I also have to believe that I'm not the first one who's ever wanted
> to do this and am hoping that this code is out there somewhere for me
> to legally pilfer and modify. I'm posting because my searches have so
> far been unsuccessful.
> Now, if anyone in Redmond is listening, those VB developers need to
> walk down the hall and talk to the Access developers! I REALLY miss the
> Access report writer and query builder. Their functionality and
> productivity are superb. I find myself often linking Access to my SQL
> Server databases, going into Access to build my query and then cutting
> and pasting the SQL into Enterprise Manager. Now, if I could just write
> my user-defined functions in VB or C then the world would then be a
> much prettier and productive place.
>

How do I exclude null fields?

Is there a way to write a select statement that will pull only fields that are not null?

SELECT col1, col2 FROM yourtable WHERE yourcol IS NOT NULL

col1, col2, and yourcol are columns in your table.

If you have specific question, please post it here.

|||Thanks!

How do I exclude carriage returns as part of a IS NULL exclusion clause...

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]

Cheers - AndyThis is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:LIKE '%[!-~]%' to see if there are any printable characters (this expression ignores whitespace).

-PatP|||Very interesting and very cool (speaking purely as a geek!).

I got it to work by doing this:

and field like '%[a-z0-9]%'

This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

Thanks very much for your help!

Andy|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!

-PatP

How do i do this with dts

Im using DTS to a basic import of a products csv into a corresponding table in sql server. I use the transformations tab to map up all the fields between the two. I have one field in the sql server table that I need to include a value everytime the dts is run. So for eg one of the fields in the sql table is ProductTypeCode and in this case it should always be 1. Now as a cheap and dirty workaround I could add another column in my csv and give all the fields values of 1 and map that column to my ProductTypeCode in sql server table but surely there a more correct way of doing this simple task.

Thanks in advanceYou can create a query for the source, using the Excel table, you can add a 1 to that query. So when you set up the source, use a query, then use the query builder to setup the initial design from the excel file. Then modify the resulting query to add the 1 parameter.

Brian|||Try this url for all your DTS questions most problems are covered here, it is run by Darren Green a SQL Server MVP.
http://www.sqldts.com Hope this helps.

Kind regards,
Gift Peddie