Can I INSERT INTO a temp tbl twice in one stored procedure

In my stored procedure I need to select some data columns and insert them into a #temp tbl and then select the same data columns again using a different from and put them into the same #temp tbl. It sounds like a union, can I union into a #temp tbl?

Any help here is appreciated.

You should be able to insert into the temp table twice, or use a union to just do one insert. Have you actually tried and got an error? If so can you post the error you got.|||The error in my stored procedure is:

There is already an object named '#temp_UN' in the database.

I'm trying to do something like this:

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE...

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE... (This select has a different FROM

SELECT ... FROM tblClient INNER JOIN tblClient.ClientID = #temp_UN.ClientID WHERE...

if

object_id('tempdb..#temp_UN','U')isnotnull

drop

table #temp_UNGifts|||

For your second insert into the temp table you should be able to use the 'Insert into .. select .. ' syntax

Something like this:

SELECT ClinetID, etc... INTO #temp_UN FROM ... WHERE...

INSERT INTO #temp_UN SELECT ClinetID, etc...FROM ... WHERE... (This select has a different FROM)

SELECT ... FROM tblClient INNER JOIN tblClient.ClientID = #temp_UN.ClientID WHERE...

HTH

|||

Thank you Steve, it looks like it was the order of things.

can i have the same parameter 3 times for different results?

I have 3 list/tables/columns..which ever is the easiest. I want to have a
parameter for the user to pick YEAR1 YEAR2 YEAR3 from drop down lists.
The only problem is when i make the first parameter, it doesnt let me make
another one, since its basically the same as the first, When i tried to make
a new one and change the name, it doesnt work. I basically want to have the
same parameter 3 times, for 3 different years. Which will show the measure
for those three different years based on what the user choses from the drop
down.
Year 1 Year 2 Year3
1 1 1
1 3 2
2 3 2
3 2 2
4 5 3
5 2 2
6 1 1
It seems like it would be easy, if it was 2 or 3 different parameters, but
since im working off a Cube (ANALYSIS SERVICES) when i click the parameter
check box in the dataset for the three different tables, nothing shows up
but the first parameter i made. Why is this? and how do i get it to give me
3 drop down boxes with the same field, but different values, based on the
users choice'Should these years be interchangeable, or could you use Year1, Year1+1 and
Year1+2?
If you could let the years be grouped together, you could stick with your
first parameter.
You might be able to create a few named sets or something, to make the
second and third year parameter. Not sure how, though. (And can't access
cubes right now, so can't check it out.)
On a side note, I've just given up on the whole new way of doing cube
queries. I usually do it the old school way. :)
Kaisa M. Lindahl Lervik
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:5BDD6564-F147-4FF5-B837-CD2E17569436@.microsoft.com...
>I have 3 list/tables/columns..which ever is the easiest. I want to have a
> parameter for the user to pick YEAR1 YEAR2 YEAR3 from drop down
> lists.
> The only problem is when i make the first parameter, it doesnt let me make
> another one, since its basically the same as the first, When i tried to
> make
> a new one and change the name, it doesnt work. I basically want to have
> the
> same parameter 3 times, for 3 different years. Which will show the measure
> for those three different years based on what the user choses from the
> drop
> down.
>
> Year 1 Year 2 Year3
> 1 1 1
> 1 3 2
> 2 3 2
> 3 2 2
> 4 5 3
> 5 2 2
> 6 1 1
> It seems like it would be easy, if it was 2 or 3 different parameters, but
> since im working off a Cube (ANALYSIS SERVICES) when i click the parameter
> check box in the dataset for the three different tables, nothing shows up
> but the first parameter i made. Why is this? and how do i get it to give
> me
> 3 drop down boxes with the same field, but different values, based on the
> users choice'
>

Can I Force SQL to Accept INSERT List of Values Less Than Number of Columns?

I have a canned application that does INSERTs with lists of values without
column lists. The table has one additional (uniqueidentifier) column for merge
replication, so the application is inserting 7 values, but there are 8
columns. Is there a way to tell SQL to accept it anyway and just fill the
columns from left to right until it runs out of data? I hope so, because I
have no access to the source code.
--EricHello Eric. you could try with renaming that table (the one you insert in)
and creating a view with old name of the table you just renamed. In the view
definition specify all fields from the renamed table except the one that you
added for replication(uniqueidentifier) .
Hope this works,
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Eric Robinson" <eric@._nospam_nvipa.com> wrote in message
news:CFN379450577312037@.news.microsoft.com...
> I have a canned application that does INSERTs with lists of values without
> column lists. The table has one additional (uniqueidentifier) column for
merge
> replication, so the application is inserting 7 values, but there are 8
> columns. Is there a way to tell SQL to accept it anyway and just fill the
> columns from left to right until it runs out of data? I hope so, because I
> have no access to the source code.
> --Eric
>|||If there isn't a column list specified in the INSERT then the number of
columns in the table must match the number of columns in the INSERT
statement (less the IDENTITY column, if any).
You could set a default for the uniqueidentifier column, rename the table
and then create a view under the original name containing all except the
extra column:
CREATE TABLE newname (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, c
UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ...)
CREATE VIEW oldname
AS
SELECT a,b
FROM newname
Then find the programmer and make him fix his code.
--
David Portas
--
Please reply only to the newsgroup
--|||"Tomislav Kralj" <tomislav.kralj1@.zg.tel.hr> wrote in message
news:bpi4j6$vq0$1@.sunce.iskon.hr...
> Hello Eric. you could try with renaming that table (the one you insert in)
> and creating a view with old name of the table you just renamed. In the
view
> definition specify all fields from the renamed table except the one that
you
> added for replication(uniqueidentifier) .
Oh, and i forgot. create view with VIEW_METADATA option !!!
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hrsql

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I dynamically change the width of objects?

I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
Is there a way to dynamically change the size of the table and report so that it doesn't print blank pages if it doesn't need to?
Thanks,
John
Unfortunately, there is no way to shrink the body size of the report. This is under consideration for a future release.|||

We had same situation where user would select few columns out of some possible 50 data columns.

We ended with doing some logic programmatically.

1) GetReportDefinition() of report containing 50 columns

2) As rdl from #1 is XML, we manipluate the xml by deleting the unselected columns and reordering them as per selected display order

3) publish the new report with unique name to a temp folder in reporing server

4) Now generate the report

5) then delete the report from temp folder

|||

We have the same issue with a client where a dense grid in 12.5 in body with borders it is 13in. The client uses IE6.

I read that IE7 supports Shrink-to-fit. Would that work with SSRS reports pending the Shrink-to-fit functionality printing in SSRS?

Thanks

Can I do this without a cursor?

Consider a table called Target with a character column called StringColumn.
I
also have a lookup table called ToReplace which contains 2 columns, OldValue
and NewValue. I'm trying to use the replace function to update the
StringColumn value in the Target table, finding the text in OldValue and
replacing it with the text in NewValue from the ToReplace table. The update
I'm using just grabs the first record from the ToReplace table. Can anyone
think of a way to do this in one update statement? Thanks in advance. Here i
s
some sloppy ddl, but it gives you the idea:
Create table ToReplace
(OldValue varchar(10),
NewValue varchar(10))
insert into ToReplace
values
('old1', 'new1')
insert into ToReplace
values
('old2', 'new2')
insert into ToReplace
values
('old3', 'new3')
Create table Target
(StringColumn varchar(50))
insert into target
values
('old1 some other text old3')
insert into target
values
('old3 old2 some other text')
select * from target
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
from ToReplace
select * from targetI'm . Why are you just using a simple update
statement against the table instead of all this insert
and create table stuff?
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
where some condition is true
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
> Consider a table called Target with a character column called
> StringColumn. I
> also have a lookup table called ToReplace which contains 2 columns,
> OldValue
> and NewValue. I'm trying to use the replace function to update the
> StringColumn value in the Target table, finding the text in OldValue and
> replacing it with the text in NewValue from the ToReplace table. The
> update
> I'm using just grabs the first record from the ToReplace table. Can anyone
> think of a way to do this in one update statement? Thanks in advance. Here
> is
> some sloppy ddl, but it gives you the idea:
> Create table ToReplace
> (OldValue varchar(10),
> NewValue varchar(10))
> insert into ToReplace
> values
> ('old1', 'new1')
> insert into ToReplace
> values
> ('old2', 'new2')
> insert into ToReplace
> values
> ('old3', 'new3')
>
> Create table Target
> (StringColumn varchar(50))
> insert into target
> values
> ('old1 some other text old3')
> insert into target
> values
> ('old3 old2 some other text')
>
> select * from target
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> from ToReplace
> select * from target
>|||Now I'm . That insert and create table stuff is the ddl. If you run
it all you'll see my problem.
"Robbe Morris [C# MVP]" wrote:

> I'm . Why are you just using a simple update
> statement against the table instead of all this insert
> and create table stuff?
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> where some condition is true
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Earn money answering .NET questions
> http://www.eggheadcafe.com/forums/merit.asp
>
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
>
>|||The problem you have run into relates to why UPDATE has no FROM clause
in standard SQL.
A row in the table you are updating matches more than one row in the
table you are joining it to. However, the row you are updating is
always the same "before image" of that row. So one matching ToReplace
changes old1, another changes old2, but they BOTH change the ORIGINAL
image of the row in Target. So, the row in Target gets updated more
than once, but the result is any (unpredictable) ONE of the updates,
not all of them together.
I would simply run each value to be changed as a single UPDATE, with
hard-coded values, rather than use any sort of ToReplace table.
Otherwise you need to work in some sort of loop to apply only one
ToReplace row at a time.
Also, it would have been a good idea to add a WHERE clause to your
UPDATE:
where StringColumn like '%' + OldValue + '%'
Roy Harvey
Beacon Falls, CT
On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
<sqlboy2000@.discussions.microsoft.com> wrote:

>Consider a table called Target with a character column called StringColumn.
I
>also have a lookup table called ToReplace which contains 2 columns, OldValu
e
>and NewValue. I'm trying to use the replace function to update the
>StringColumn value in the Target table, finding the text in OldValue and
>replacing it with the text in NewValue from the ToReplace table. The update
>I'm using just grabs the first record from the ToReplace table. Can anyone
>think of a way to do this in one update statement? Thanks in advance. Here
is
>some sloppy ddl, but it gives you the idea:
>Create table ToReplace
>(OldValue varchar(10),
>NewValue varchar(10))
>insert into ToReplace
>values
>('old1', 'new1')
>insert into ToReplace
>values
>('old2', 'new2')
>insert into ToReplace
>values
>('old3', 'new3')
>
>Create table Target
>(StringColumn varchar(50))
>insert into target
>values
>('old1 some other text old3')
>insert into target
>values
>('old3 old2 some other text')
>
>select * from target
>Update target
>set StringColumn = replace(StringColumn, OldValue, NewValue)
>from ToReplace
>select * from target
>|||Thanks Robbe, I didn't see any elegant solution to this either. I'll just
loop through it, it's a nightly run, so it's not the end of the world.
"sqlboy2000" wrote:
> Now I'm . That insert and create table stuff is the ddl. If you ru
n
> it all you'll see my problem.
> "Robbe Morris [C# MVP]" wrote:
>|||Sorry, got your name wrong. Thank you Roy
"Roy Harvey" wrote:

> The problem you have run into relates to why UPDATE has no FROM clause
> in standard SQL.
> A row in the table you are updating matches more than one row in the
> table you are joining it to. However, the row you are updating is
> always the same "before image" of that row. So one matching ToReplace
> changes old1, another changes old2, but they BOTH change the ORIGINAL
> image of the row in Target. So, the row in Target gets updated more
> than once, but the result is any (unpredictable) ONE of the updates,
> not all of them together.
> I would simply run each value to be changed as a single UPDATE, with
> hard-coded values, rather than use any sort of ToReplace table.
> Otherwise you need to work in some sort of loop to apply only one
> ToReplace row at a time.
> Also, it would have been a good idea to add a WHERE clause to your
> UPDATE:
> where StringColumn like '%' + OldValue + '%'
> Roy Harvey
> Beacon Falls, CT
> On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
> <sqlboy2000@.discussions.microsoft.com> wrote:
>
>

Can I disable all exporting formats but Excel worksheets?

Hi, there

Some of the reports I am generating have tens of columns so the management decides to use Excel files only.

Is there any way that for a single report (not the whole project) I can disable printing and most of the exporting options (including PDF, HTML, TXT ...) and only leave the xls files available?

Thanks a lot.

Heng

Hi hengm,

Here is a useful site http://www.exceluser.com/index.htm

sql

Can I disable all exporting formats but Excel spreadsheets?

Hi, there

Some of the reports I am generating have tens of columns so the management decides to use Excel files only.

Is there any way that for a single report (not the whole project) I can disable printing and most of the exporting options (including PDF, HTML, TXT ...) and only leave the xls files available?

Thanks a lot.

Heng

Hi hengm,

Here is a useful site http://www.exceluser.com/index.htm

Can I create new rows by using spreadsheet with less columns?

I wondering if I can create new rows in a table that has 48 columns by impor
ting data in a spreadsheet that has only 4 columns?yes, assuming the 44 columns that won't have data allow nulls or have defaul
t values assigned to them.
joel wrote:

> I wondering if I can create new rows in a table that has 48 columns by importing d
ata in a spreadsheet that has only 4 columns?|||I did it using the DTS designer - worked great.
Thanks
Joel

Can I copy a DTS Package?

The scenario:
9 db tables populated by 9 Excel Import Files via DTS.
Will I need to create a DTS package for each import? Columns are identical in all 9 - the only thing different is the destination table name and source file name.

I've had to map over 80 columns using DTS and don't want to do it for each instance!

Any help would be appreciated..1. Rightclick the DTS-package in Enterprise Manager
2. Choose "design package"
3. Make the changes you want
4. Go to menuitem "Package" and choose "Save as"
5. You now have a copy of your DTS-package|||Nice one! Cheers.|||This is not the best way -
Dude - create a table in the destination db called tblFileSource that has:
ID, SourceFile, DestinationTable, importDate

Define FileSource and DestinationTable as Global Variables in the package.
Then, the first step of you package use a Execute SQL task that will set you global Varaiables to the result of

select top 1 SourceFile, DestinationTable
from tblFileSource
where importDate is null


Then, use a Dynamic Properties Task to change the source and destination in the Data Transformation Step.

Then, after the Transformation, do another Execute SQL Task (on Success):


Update tblFileSource
Set importDate = getDate()
where SourceFile = ?

Where the ? is the global Variable FileSource.

Isn't this a more professional method - comes in handy when the number of files increases.

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||

have you tried the list control?

Can I "freeze" the leftmost 2 columns when scrolling horizontally?

Hello,

I have a very wide report that uses a table control. I would like to "freeze" the two leftmost columns so that they remain visible when the user is scrolling horizontally (sideways). Is this possibe?

Thanks,

BCB

In RS2005, there is an option to "Header should remain visible while scrolling"
These can be found in your table or matrix groups' properties/options,
or as a whole table/matrix (not doing it by a group)

|||

Hi,

But that option just applies to up and down scrolling. I'm trying to keep the leftmost columns in view while I scroll a very wide report to the right. I've never found that the option you mentioned works for sideways scrolling.

|||

Select both entire columns of the table that you want to freeze by using the column selectors.

Then in the VS properties window, you should find a "FixedHeader" property at the bottom. Set it to True.

-- Robert

|||

Beautiful, Robert - thanks.

BCB

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||

have you tried the list control?

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||have you tried the list control?

Can columns be aliased in Transactional Replication?

I have finally got all the bugs worked out of my TransRep publisher ->
Distributor/Subscriber set up when I got hit with a new question that I can't
answer. I've been asked to alias a few of the columns so that in the
replicated DB they show up with a different header than the columns in the
publisher DB.
IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2 to
say User_ID so that the people reading the data won't get confused. Data in
the column will remain the same and has already been filtered on a row
(horizontal) level. I've also filtered on columns, only sending what is
necessary so the users don't see private information.
But no where I look can I find an "alias" box. BOL mentioned something
about using DTS in replication, but I can't find how to link the two. I know
how to change a column name in DTS (I've become quite the expert in using the
Import / Export Wizard to create packages). But, again, I can't figure out
how to link the two or if this is even necessary.
Help is appreciated. Thank you all in advance. Even if you could tell me
what references to search for in BOL would be a great help.
But doesn't replicating a vew (indexed or otherwise) require the base tables
be published with it?
The replication is getting sent to a customer's server, these people have
sysadmin rights on their machines, and if we send over both the table and the
view, they're probably not even going to look at the view.
"Paul Ibison" wrote:

> I wouldn't use transformable publications for this -
> there are easier ways with less overhead. You could
> replicate an indexed view, or alternatively use an
> @.creation_script.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||No - if you use an indexed view, it will appear in a
separate section when you are selecting the articles, and
it doesn't require the underlying table(s). What you are
saying applies to standard views, which of course is
another posibility - you could replicate the table to
another named table and have a view on the subscriber
with the same name as the original table. This would
avoid the overhead on the publisher of maintaining the
indexed view behind the scenes.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||you can modify the replication stored procedures to reflect the new names on
the subscriber.
To do this you will also need to use a pre-creation script containing the
schema of these tables with the new names.
Then you will need to use the delete existing data in the table option in
the snapshot tab of your table article properties dialog box.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:D16A826B-586C-4FD6-AFFE-A22AE222A06F@.microsoft.com...
> I have finally got all the bugs worked out of my TransRep publisher ->
> Distributor/Subscriber set up when I got hit with a new question that I
can't
> answer. I've been asked to alias a few of the columns so that in the
> replicated DB they show up with a different header than the columns in the
> publisher DB.
> IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2
to
> say User_ID so that the people reading the data won't get confused. Data
in
> the column will remain the same and has already been filtered on a row
> (horizontal) level. I've also filtered on columns, only sending what is
> necessary so the users don't see private information.
> But no where I look can I find an "alias" box. BOL mentioned something
> about using DTS in replication, but I can't find how to link the two. I
know
> how to change a column name in DTS (I've become quite the expert in using
the
> Import / Export Wizard to create packages). But, again, I can't figure
out
> how to link the two or if this is even necessary.
> Help is appreciated. Thank you all in advance. Even if you could tell me
> what references to search for in BOL would be a great help.
>
|||Well, the system is not letting me say both of you answered my question.
However, you both did (with different ideas) and I greatly appreciate all
your assistance. You just made my life a LOT easier. @.=)
Thank you very much for your time.

Can columns be added to an error output?

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

Answering my own question. The answer is 'YES', you can enhance the error output.

You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.

===============================================================================

// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);

===============================================================================

// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;

// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);

===============================================================================

// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion

buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}

===============================================================================

|||

Can you give me more details on this. I will wait to see if you reply beofre I elaborate..

It sounds like this is something I am looking to do based on my post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1

|||

Rob,

The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.

-Jamie

|||

Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

|||

ronemac wrote:

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.

-Jamie

Can columns be added to an error output?

When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?

I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.

IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;

// Add extra column here, e.g. ErrorMessage

Answering my own question. The answer is 'YES', you can enhance the error output.

You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.

===============================================================================

// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);

===============================================================================

// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;

// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);

===============================================================================

// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion

buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}

===============================================================================

|||

Can you give me more details on this. I will wait to see if you reply beofre I elaborate..

It sounds like this is something I am looking to do based on my post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1

|||

Rob,

The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.

-Jamie

|||

Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

|||

ronemac wrote:

Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?

If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.

-Jamie

can alter table drop multiple columns

i tried to run this script:
ALTER TABLE RDKCOCUS
DROP COLUMN [Administration Executive],
DROP COLUMN [Administrator],
DROP COLUMN [Auditor],
DROP COLUMN [Chairman],
DROP COLUMN [Chief Executive Officer],
DROP COLUMN [Chief Financial Officer],
DROP COLUMN [Chief Operating Officer],
DROP COLUMN [Co-Chairman/Vice Chairman],
DROP COLUMN [Controller],
DROP COLUMN [Corporate Communications Executive],
DROP COLUMN [Corporate Secretary],
DROP COLUMN [Data Processing Executive],
DROP COLUMN [Director],
DROP COLUMN [Executive Director],
DROP COLUMN [Executive Vice President],
DROP COLUMN [Finance Executive],
DROP COLUMN [General Counsel],
DROP COLUMN [Human Resources Executive],
DROP COLUMN [Manager],
DROP COLUMN [Marketing Executive],
DROP COLUMN [Officer],
DROP COLUMN [Operations Executive],
DROP COLUMN [Owner],
DROP COLUMN [Partner],
DROP COLUMN [Plant Manager],
DROP COLUMN [President],
DROP COLUMN [Principal],
DROP COLUMN [Publisher],
DROP COLUMN [Purchasing Agent],
DROP COLUMN [Sales Executive],
DROP COLUMN [Senior Vice President],
DROP COLUMN [Telecommunications Executive],
DROP COLUMN [Treasurer],
DROP COLUMN [Vice President]
and i got the error, incorrect syntax near keyword drop
so i tried this script instead:
ALTER TABLE RDKCOCUS
DROP COLUMN [Administration Executive]
and it worked. so i guess my question is, do i really have to rerun the
script for every columnlist them one after another spereated by comma's
example
CREATE TABLE doc_exe ( column_a INT, column_a_un int, colC int)
GO
select * from doc_exe
GO
ALTER TABLE doc_exe DROP COLUMN column_a_un,colC
GO
select * from doc_exe
GO
drop table doc_exe
GO
http://sqlservercode.blogspot.com/|||tyty
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136924539.257435.5670@.f14g2000cwb.googlegroups.com...
> list them one after another spereated by comma's
> example
> CREATE TABLE doc_exe ( column_a INT, column_a_un int, colC int)
> GO
> select * from doc_exe
> GO
> ALTER TABLE doc_exe DROP COLUMN column_a_un,colC
> GO
> select * from doc_exe
> GO
> drop table doc_exe
> GO
> http://sqlservercode.blogspot.com/
>|||Cool. I didn't realize that this would work :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1136924539.257435.5670@.f14g2000cwb.googlegroups.com...
> list them one after another spereated by comma's
> example
> CREATE TABLE doc_exe ( column_a INT, column_a_un int, colC int)
> GO
> select * from doc_exe
> GO
> ALTER TABLE doc_exe DROP COLUMN column_a_un,colC
> GO
> select * from doc_exe
> GO
> drop table doc_exe
> GO
> http://sqlservercode.blogspot.com/
>|||Use drop column followed by a list of fields, seperated by commas. Dont
repeat the drop column keywords for each column.
ALTER TABLE RDKCOCUS
DROP COLUMN [Administration Executive],
[Administrator],
[Auditor],
[Chairman],....
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:uy2OKKiFGHA.2708@.TK2MSFTNGP11.phx.gbl...
> i tried to run this script:
> ALTER TABLE RDKCOCUS
> DROP COLUMN [Administration Executive],
> DROP COLUMN [Administrator],
> DROP COLUMN [Auditor],
> DROP COLUMN [Chairman],
> DROP COLUMN [Chief Executive Officer],
> DROP COLUMN [Chief Financial Officer],
> DROP COLUMN [Chief Operating Officer],
> DROP COLUMN [Co-Chairman/Vice Chairman],
> DROP COLUMN [Controller],
> DROP COLUMN [Corporate Communications Executive],
> DROP COLUMN [Corporate Secretary],
> DROP COLUMN [Data Processing Executive],
> DROP COLUMN [Director],
> DROP COLUMN [Executive Director],
> DROP COLUMN [Executive Vice President],
> DROP COLUMN [Finance Executive],
> DROP COLUMN [General Counsel],
> DROP COLUMN [Human Resources Executive],
> DROP COLUMN [Manager],
> DROP COLUMN [Marketing Executive],
> DROP COLUMN [Officer],
> DROP COLUMN [Operations Executive],
> DROP COLUMN [Owner],
> DROP COLUMN [Partner],
> DROP COLUMN [Plant Manager],
> DROP COLUMN [President],
> DROP COLUMN [Principal],
> DROP COLUMN [Publisher],
> DROP COLUMN [Purchasing Agent],
> DROP COLUMN [Sales Executive],
> DROP COLUMN [Senior Vice President],
> DROP COLUMN [Telecommunications Executive],
> DROP COLUMN [Treasurer],
> DROP COLUMN [Vice President]
>
> and i got the error, incorrect syntax near keyword drop
> so i tried this script instead:
> ALTER TABLE RDKCOCUS
> DROP COLUMN [Administration Executive]
> and it worked. so i guess my question is, do i really have to rerun the
> script for every column
>|||I remember reading somewhere that you can drop multiple objects in 1
shot
Like this
CREATE TABLE doc_exe1 ( name INT)
GO
CREATE TABLE doc_exe2 ( name INT)
GO
DROP TABLE doc_exe1,doc_exe2
I just tried it with columns and it seems to work

Can a WHERE clause refer to a column based on a subquery ?

I've got a stored procedure that contains a query similar to the following which works.
(The real query contains lots more columns and the subquery is quite long)

SELECT table1.ColA, table1.ColB, (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1

Sometimes the sub-query returns nulls which is fine.

I now want to modify the stored procedure so that it will optionally only return the rows where the sub-query returns non-null values. I've added an int parameter @.filter to the procedure. If @.filter = 0 I will return all rows; if @.filter = 1 I will filter the results.

The query now looks like this:

SELECT table1.ColA, table1.ColB, subquery = (SELECT TOP 1 colX from table2 WHERE etc. ) FROM table1
WHERE (@.filter = 0 or not subquery is null).

However, I get an invalid column name 'subquery' error.

Have I simply got my syntax wrong or is it not possible to do this. I've played with a few variations such as '(SELECT ....) as subquery' but nothing seems to work.

I can imagine a solution where the sub-query is used to generate a table that table1 can be inner joined with, but the query is quite big (and has been tested) and I didn't want to start hacking it about if there was a trivial fix.

Any help appreciated.I have found the answer to my own question.

The SELECT parameters (including the sub-query) are not evaluated until after the WHERE so what I wanted to do is not possible.

I found the link below useful.

http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html|||i din get ur exact requirement , but yes the syntax is not correct....
read a bit abt 'corelated subqueries' from books online..u'll hopefuly get a solution...
if u dont post back , i'll try to put some example...|||

The SELECT list expressions can be evaluated before the WHERE clause in SQL Server. But that has nothing to do with the problem though. Column aliases are only part of the result set metadata that the client uses. As such it can be used only in the ORDER BY clause (in ANSI SQL, the ORDER BY belongs on the client-side & hence it is allowed in ORDER BY). You can use derived table to simplify complex SELECT statements and avoid repeating expressions like:

SELECT ColA, ColB, ColN

FROM (

SELECT table1.ColA, table1.ColB

, (SELECT TOP 1 colX from table2 WHERE etc. ) as ColN

FROM table1

) as t

WHERE (@.filter = 0 or ColN is not null)

|||Thanks for that.

It works now.