How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks
|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

How to execute a single step in a SSIS package?

Hi all,

I am new to SQL Server 2005 (but many years in SQL Server and .NET), and I am sort of having everything figured out for my company. However, one thing that still bothering me is that:

In the old Server 2000, you can execute a single step in a DTS package by right click the step and then click execute step.

In the new server 2005, I can only execute the whole package from the Management Tool and Edit the pack from VS 2005. Is there also a way for me to execute a single step in a SSIS package?

This is important for us, many thanks!

You can execute a single step from Visual Studio by right-clicking on the step and choosing Execute Task.|||Thanks!

I found more, you must start a new project and then add some existing dts packages into that. After that things will become all good! I just do not understand that why you cannot directly work on a single package without a project!

Thanks|||

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

|||

Andymcdba1 wrote:

And you have to build that DTS package. This a lot of extra steps over SQL 2000 DTS.

Build? What do you mean, build? You don't have to build.|||

I have always received an error message when I tried that. I presumed it was because the task I was trying to execute didn't have a "success" message from the preceding task.

I'm sorry that I don't have the text of the error message at the moment. But it was something that made me believe the error was a result of trying to execute a task in the middle of a series of tasks.

You don't have such problems when you try to run tasks that have dependencies on other tasks?

Dan

|||A precedence constraint shouldn't cause a problem. However, if the task you are executing is dependent on something produced in a previous step (such as a text file), then the task will fail.|||

Mr. Welch,

Thanks for the info. I will pay more attention to the error message. If I cannot figure it out, I will post as much information as possible. I won't be back in the office until Monday afternoon.

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

I couldn't see any reason that the task would fail to execute, other than a precedence constraint. So I jumped to that conclusion as the reason for failure.

When I pasted the "exec" and "go" lines into an SS05 query window, with the same SQL Server login as is being used in the SSIS connection, it ran just fine.

I hope I can solve this one, since I miss the DTS package ability to execute a single task in an "on success..." series.

Dan

|||

DanR1 wrote:

The task I was trying to execute is one that copies tables from one "database.schema" to another "database.schema". The task is performed by calling a stored procedure in an SSIS "SQL Task" object. There is nothing there other than an "exec my_procedure_name" line and a "GO" line.

Dan

Don't use the "GO" lines. Leave them out. Separate multiple exec statements with a carriage return.|||

Phil,

Thanks for the suggestion. I'll give that a try on Monday.

Dan

|||

Phil,

Thanks for your suggestion. Removing the "GO" line stopped the "ACCESS DENIED" error message from appearing, and allowed the single task to run without any problem.

Dan

how to exec a stored procedure

hi,

how do I exec stored procedure that accept parameter and return a single value?

here is example of report

stu_id = ******

stu_name = ****

subject | marks

aa****** | call sp_mark and return student mark for that particular student id and subject

bb****** | call sp_mark and return student mark for that particular student id and subject

cc****** | call sp_mark and return student mark for that particular student id and subject

thks,

You cannot call a stored procedure per row if you mean that with your mentioned design, you would have to get all the information within one procedure to display it in the bound table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Charles,

Have you tried using a user defined function in place of the stored procedure?

Simone

|||

A potentially better performing alternative to a user defined function would probably be a derived table containing the marks for each student by subject. You would then join on the table.

Something like

select stu_id, stu_name, subject, mark

from students s

left outer join (select stu_id, subject, marks from marks) m on m.stu_id = s.stu_id

Of course you would need to summarize the marks into a table...

cheers,

Andrew

How to escape out single quotes from an T-SQL variable

How do I escape out the single quotes in the T-SQL Variable Assignment
appearing below around the comma for setting the FIELDTERMINATOR and the \n
setting the ROWTERMINATOR?
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
Thanks,
DavidTranslate each single quote into two single quotes:
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:B423BD8F-F0A8-4516-B720-C8990908BD1D@.microsoft.com...
How do I escape out the single quotes in the T-SQL Variable Assignment
appearing below around the comma for setting the FIELDTERMINATOR and the \n
setting the ROWTERMINATOR?
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
Thanks,
David|||David,
Double them.
SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
WITH ( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' )'
AMB
"David Bowles" wrote:

> How do I escape out the single quotes in the T-SQL Variable Assignment
> appearing below around the comma for setting the FIELDTERMINATOR and the \
n
> setting the ROWTERMINATOR?
> SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
> WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
> Thanks,
> David|||By using two single quotes.
PRINT 'O''Leary';
"David Bowles" <DavidBowles@.discussions.microsoft.com> wrote in message
news:B423BD8F-F0A8-4516-B720-C8990908BD1D@.microsoft.com...
> How do I escape out the single quotes in the T-SQL Variable Assignment
> appearing below around the comma for setting the FIELDTERMINATOR and the
> \n
> setting the ROWTERMINATOR?
> SET @.sql = ' BULK INSERT sandbox.dbo.core_edi FROM ' + @.full_filename + '
> WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )'
> Thanks,
> David

how to enforce a trigger when update for each record when updates several records bulky?

I made a trigger on a table that fires when update happens, the trigger fires when attempting to update a single record (that is normally) but when trying to update several records bulky using one update statement it fires only once either.

My question is, how to enforce firing the trigger for each record when updates bulky? i.e. how to ensure that when I use the following update statement

UPDATE MyTableName SET ColumnName = 5

And there are 10 records that affected; that the trigger would fire 10 times? (I have the fact that it fires only once)

In SQL server, a trigger fires once per statement, not per row and this can not be changed. You must write your trigger to be able to handle a multiple row update. Post your trigger code and likely someone here can help you re-write it to work for multiple row updates.|||Thanks David, your reply was helpfulsql

how to encrypt a single field like a password field

without writing code in my application? Does SQL Server have stored procedure to do it?
Any help is appreciated.
Thanks.SQL Server 2005 has support for encryption, but you have to manage keys, and write your own stored procedures that use the encrypt and decrypt functions. SQL 2000 does not have native support for encryption, I believe.|||Attached is function that is suitable for encrypting passwords.|||naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.|||Attached is function that is suitable for encrypting passwords.
I have downloaded the code and will take a look at it.|||Theres an inbuilt function in SQL2000:

column type must be:
Declare PWCol varbinary(256)

to insert/update use:

...CONVERT(varbinary(256), PWDENCRYPT('THEPASSWORD'))

and to compare a password...

...where PWDCOMPARE('thepassword', PWCol) = 1

Cheers,
Phil
--
Always remember that you're unique, just like everyone else.|||a quick google search on this undocumented function gives you results on how to hack it. PUBLIC KEY ENCRYPTION is the safest bet. It's been a while since I have done this (4 years?) but I used the RSA cypher.|||naaahh. any encryption formula you put together just is not going to do the job the public-key encryption is going to do.
Not true. The encryption algorithm I gave is a "one-way" algorithm. It cannot be unencrypted, and thus is only suitable in limited situations such as password encryption. It is relatively easy to make secure one-way encryption schemes.
The challenge is to make a secure "two-way" encryption algorithm. SQL Server's built-in encryption is "two-way" but is not secure and was hacked years ago, and the decryption method is readily available on the web.|||Duplicate post.|||a quick google search on this undocumented function gives you results on how to hack it.

Blimey you boys do love to p!$$ on someones fire.

It's only hackable if your front end code is crap and you don't parse throu before SQL.
If you're stupid enough to leave your SQL server open to access then the fact you can hack a password in a table is pretty irrelevant when you can get control of the whole box.

Right, I'm off to sulk in the corner.

...

To err is human, to forgive is not our Policy.|||Whoa, Mr. Sensitive! You're gonna need thicker skin than that!

And no sulking, either. If you think we're full-o-crap, then just say so (but without throwing all tact to the wind...).

P!$$!ng on someone's fire: allowed.
Sulking in the corner: frowned upon.
P!$$!ing in the corner: well, when ya gotta go...|||Well at least I now know why my sulking corner is starting to smell so bad.|||We generally do our sulking and grousing in the Yak Corral. You can join us there:
http://www.dbforums.com/showthread.php?t=989246&page=289|||Wy not to use the in-built function encrypt() ?|||It is an undocumented function which may not be supported, or may use a different algorithm in future releases.

The algorithm has changed through releases in the past, rendering whole databases inaccessible for applications that relied upon it.

how to enable auditing in SQL (or how to maintain session state variables)

My application is developed in VB.NET and uses a single/same connection
string for all users (all
installations), which uses SQL Server 2000 as the data store.
But I need to keep track of who inserted a record and who modified a record
(along with the dates and times).
Using triggers, I can easily setup the dates when a record was added and
last modified.
But how can i determine what users is connected to the application and is
making the changes.
As this information is stored in the application.
Is there a way that i can inform SQL what user is connecting to the server
(database), or set this information in SQL Server such that this information
is stored for each session. I.e. is there some technology similar to session
states in websites in Sql server. where i can store session variables for
each session on the website, thus enabling me to store session variables in
sql server for each session (connection).
any help on this matter would be great.LeAnne wrote:
> My application is developed in VB.NET and uses a single/same connection
> string for all users (all
> installations), which uses SQL Server 2000 as the data store.
> But I need to keep track of who inserted a record and who modified a recor
d
> (along with the dates and times).
> Using triggers, I can easily setup the dates when a record was added and
> last modified.
> But how can i determine what users is connected to the application and is
> making the changes.
> As this information is stored in the application.
> Is there a way that i can inform SQL what user is connecting to the server
> (database), or set this information in SQL Server such that this informati
on
> is stored for each session. I.e. is there some technology similar to sessi
on
> states in websites in Sql server. where i can store session variables for
> each session on the website, thus enabling me to store session variables i
n
> sql server for each session (connection).
> any help on this matter would be great.
Just pass in the user name or ID as a parameter to the stored procedure
that performs the insert or update. In a two-tier application procs
should certainly be used for all data access.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
There are SYSTEM_USER,HOST_NAME() ,USER_NAME ...system functions
If I remember well you can specify a workstationname within a connection
string to identify an user.
Anne" <Anne@.bogusemail.com> wrote in message
news:eBbc$FpRGHA.5092@.TK2MSFTNGP11.phx.gbl...
> My application is developed in VB.NET and uses a single/same connection
> string for all users (all
> installations), which uses SQL Server 2000 as the data store.
> But I need to keep track of who inserted a record and who modified a
> record
> (along with the dates and times).
> Using triggers, I can easily setup the dates when a record was added and
> last modified.
> But how can i determine what users is connected to the application and is
> making the changes.
> As this information is stored in the application.
> Is there a way that i can inform SQL what user is connecting to the server
> (database), or set this information in SQL Server such that this
> information
> is stored for each session. I.e. is there some technology similar to
> session
> states in websites in Sql server. where i can store session variables for
> each session on the website, thus enabling me to store session variables
> in
> sql server for each session (connection).
> any help on this matter would be great.
>
>

How to emphasize a single word in a paragraph

Using RDL, I want to be able to emphasize (bold, underline, etc.) a word within a paragraph.

For example, to emit: "This is somevery importantfunctionality."

Can this be done?

Edmund

HI, Edmund:

You can first select the TextBox and you wiil see Font in the properties box. In the sublist, you can change the value of FontWeight to hightlight the text

|||

Hi Edmund,

This is not possible(You can not apply different font/colour settings to text within same text box) . The only thing you (and what We are doing) can do is to use three different text boxes side by side and set the font weight of the middle box. So it will be something like this -

TextBox1 TextBox2 TextBox3

This is some very important functionality

|||

Thanks Aj,

This is what I tought of doing, but the problem is that I have a long paragraph and I want to emphasize a single word inside it.

Anyhow, maybe I'll simply UPPERCASE this word to have it show up of the rest.

Thanks, Edmund

How to duplicate a row in SQL Table using a single query

Hi,
I have a table with about 50 fields including intCompanyId as the
table identity.
I want to be able to use one query (or a few in a stored proc to
duplicate a specific row in this table.
I do not want to list the fields in case some are added later.
Ex : insert into T_Company select * from T_Company where intCompanyId
= 17
This query gives me an error :
An explicit value for the identity column in table 'T_Company' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Even if I turn it OFF it will not let it insert it since it will have
the same intCompanyId as the other one.
Can some one help me?
Thanks
Alain
> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
Why on earth would you want to _duplicate_ a row in a table? It makes no
sense to do that.

> I do not want to list the fields in case some are added later.
Good practice is to *always* list the columns, never use SELECT * in
production code. This should actually make the code easier to maintain and
debug if the DDL changes because your code will fail safe. INSERT ... SELECT
* is dangerous because it assumes a fixed sequential order to the columns
and that is something you shouldn't need to worry about and isn't always
easy to have complete control over.
David Portas
SQL Server MVP
|||I am really curious to know why you want to do something like that. It's like
asking us how to mess up your table...
"Alain Filiatrault" wrote:

> Hi,
> I have a table with about 50 fields including intCompanyId as the
> table identity.
> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
> I do not want to list the fields in case some are added later.
> Ex : insert into T_Company select * from T_Company where intCompanyId
> = 17
> This query gives me an error :
> An explicit value for the identity column in table 'T_Company' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Even if I turn it OFF it will not let it insert it since it will have
> the same intCompanyId as the other one.
> Can some one help me?
> Thanks
> Alain
>
|||Alain,
Like Susan, I wonder why you want to do this. But there are two
things going on here. First, because there is an identity column, you
can't insert values into every column of the table, as insert into
T_Company select * ... would do. Because you say that you can't insert
even with IDENTITY_INSERT set to ON (I assume you meant "Even if I turn
it ON", not OFF), there must be a unique or primary key constraint on
the identity column. I would hope that constraint is in place for a
reason, and that constraint is preventing you from entering duplicate
information (in the identity column, at least) into the table. Your
desire to add a duplicate row to the table is at odds with the desire of
the database designer, who designed the database to prevent anyone from
doing what you want to do.
Steve Kass
Drew University
Alain Filiatrault wrote:

>Hi,
>I have a table with about 50 fields including intCompanyId as the
>table identity.
>I want to be able to use one query (or a few in a stored proc to
>duplicate a specific row in this table.
>I do not want to list the fields in case some are added later.
>Ex : insert into T_Company select * from T_Company where intCompanyId
>= 17
>This query gives me an error :
>An explicit value for the identity column in table 'T_Company' can
>only be specified when a column list is used and IDENTITY_INSERT is
>ON.
>Even if I turn it OFF it will not let it insert it since it will have
>the same intCompanyId as the other one.
>Can some one help me?
>Thanks
>Alain
>
|||Guys, Guys, Guys,
It never ceases to amaze me how so many questions get answered with another question... instead of an answer.
I have the same question, but I think what the original author is trying to do here is duplicate the row while allowing the identity/key to grow on its own... without having to list every other column in the table. (Hence the * he is wanting to use). Furthermore, the author would like to accomplish this in a single SQL statement.
Afterall, a primary key is a primary key, and a unique identifier is a unique identifer.
It seems to me this would be a capability often persued by any seasoned and active SQL programmer. And thus, I would expect such functionality from a seasoned language (aka, sql)
With the previous assumption having been made, does anyone have the answer? Is there a way to duplicate everything in a row except for any autonumbering identifiers while allowing those autonumbers to autonumber as they were designed to (without listing every single other column in the table)?|||Yes, this is a an activity that I end up doing regularly. Typically
when I am writing a stored procedure and I want to get a working set
of data into a temporary table and have the temporary table use its
own identity key.
There is no straightforward way that I know of doing this but I use a
technique that Dan Guzman MVP gave me some time back which I think is
very neat.
--create temp table with resultset columns
SELECT *
INTO #MetaData
FROM MyBigTable
...or...
select * (where all columns are listed and have column names)
into #MetaData
from [my_bespoke_query_joining_several_tables]
--list meta data
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either your tempdb or your current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) =
OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
-- More specific case where I want to prepare a create table statement
for a
-- temp table that will hold the result set from a stored procedure
because the
-- temporary table must exist before you can use a statement like...
-- INSERT #my_tbl
-- EXECmy_proc
SELECT case ordinal_position
when 1 then ' '
else ' ,'
end
+ quotename(column_name) collate SQL_Latin1_General_CP1_CI_AS +
' '
+ case data_type
when 'int' then data_type
when 'char' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'varchar' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'nvarchar' then 'varchar(' + convert(varchar(5),
character_maximum_length) + ')'
when 'decimal' then data_type + '(' + convert(varchar(5),
numeric_precision) + ',' + convert(varchar(5), numeric_scale) + ')'
when 'smallint' then 'int'
when 'datetime' then data_type
when 'smalldatetime' then 'datetime'
else 'datatype not handled'
end
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either the tempdb or the current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) =
OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
In fact, if I am writing a stored proc that will later almost
certainly become an input to a further aggregated proc, I always put
in an additional parameter that allows the meta-data of the proc to be
returned as an additional result set.
I think this should give you a nice workaround that at worst means a
bit of cutting and pasting.
Regards
Liam
TheSqlGuy <TheSqlGuy.1dqkdq@.mail.mcse.ms> wrote in message news:<TheSqlGuy.1dqkdq@.mail.mcse.ms>...
> Guys, Guys, Guys,
> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
> I have the same question, but I think what the original author is
> trying to do here is duplicate the row while allowing the identity/key
> to grow on its own... without having to list every other column in the
> table. (Hence the * he is wanting to use). Furthermore, the author
> would like to accomplish this in a single SQL statement.
> Afterall, a primary key is a primary key, and a unique identifier is a
> unique identifer.
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer. And thus, I would expect such functionality
> from a seasoned language (aka, sql)
> With the previous assumption having been made, does anyone have the
> answer? Is there a way to duplicate everything in a row except for any
> autonumbering identifiers while allowing those autonumbers to
> autonumber as they were designed to (without listing every single other
> column in the table)?
|||> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
Answering a question directly isn't always the appropriate and professional
way to help someone who needs it. If someone asked you "Where can I get a
gun so I can shoot myself?" would you just answer the direct question or
would you offer some more constructive suggestions? The OP was asking how to
do something that will destroy the integrity of his data (if indeed it had
any to start with). My response was to ask for more information about his
actual business requirement so I could advise him better. Unfortunately
there are a lot of people posting to this group who are desperately trying
to shoot themselves in the foot. Not all of us want to help them to do it!

> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer.
Not by a *good* SQL programmer!
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...

> If someone asked you "Where can I get a
> gun so I can shoot myself?" would you just answer the direct question or
> would you offer some more constructive suggestions?
Depends on the person.
|||Also depends on what the original poster meant by "duplicate". I have
often had users ask me for funtionality to "duplicate" an order and
what they really mean is that they want the original order used as a
template to produce a new order - which is fair enough. Of course, it
would suggest that such functionality was an afterthought (which it
often is) and should have been factored into the original design.
An ability to appreciate the difference between the syntactic
precision of gun mechanics(parsed code) and the semantic ambiguity of
target shooting(meeting users requirements and expectations) is a very
useful skill.
Regards
Liam
"Mark Wilden" <mark@.mwilden.com> wrote in message news:<TfSdnZTmCJhXW_jcRVn-rw@.sti.net>...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
>
> Depends on the person.

How to duplicate a row in SQL Table using a single query

Hi,
I have a table with about 50 fields including intCompanyId as the
table identity.
I want to be able to use one query (or a few in a stored proc to
duplicate a specific row in this table.
I do not want to list the fields in case some are added later.
Ex : insert into T_Company select * from T_Company where intCompanyId
= 17
This query gives me an error :
An explicit value for the identity column in table 'T_Company' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Even if I turn it OFF it will not let it insert it since it will have
the same intCompanyId as the other one.
Can some one help me?
Thanks
Alain> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
Why on earth would you want to _duplicate_ a row in a table? It makes no
sense to do that.
> I do not want to list the fields in case some are added later.
Good practice is to *always* list the columns, never use SELECT * in
production code. This should actually make the code easier to maintain and
debug if the DDL changes because your code will fail safe. INSERT ... SELECT
* is dangerous because it assumes a fixed sequential order to the columns
and that is something you shouldn't need to worry about and isn't always
easy to have complete control over.
--
David Portas
SQL Server MVP
--|||I am really curious to know why you want to do something like that. It's like
asking us how to mess up your table...
"Alain Filiatrault" wrote:
> Hi,
> I have a table with about 50 fields including intCompanyId as the
> table identity.
> I want to be able to use one query (or a few in a stored proc to
> duplicate a specific row in this table.
> I do not want to list the fields in case some are added later.
> Ex : insert into T_Company select * from T_Company where intCompanyId
> = 17
> This query gives me an error :
> An explicit value for the identity column in table 'T_Company' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Even if I turn it OFF it will not let it insert it since it will have
> the same intCompanyId as the other one.
> Can some one help me?
> Thanks
> Alain
>|||Alain,
Like Susan, I wonder why you want to do this. But there are two
things going on here. First, because there is an identity column, you
can't insert values into every column of the table, as insert into
T_Company select * ... would do. Because you say that you can't insert
even with IDENTITY_INSERT set to ON (I assume you meant "Even if I turn
it ON", not OFF), there must be a unique or primary key constraint on
the identity column. I would hope that constraint is in place for a
reason, and that constraint is preventing you from entering duplicate
information (in the identity column, at least) into the table. Your
desire to add a duplicate row to the table is at odds with the desire of
the database designer, who designed the database to prevent anyone from
doing what you want to do.
Steve Kass
Drew University
Alain Filiatrault wrote:
>Hi,
>I have a table with about 50 fields including intCompanyId as the
>table identity.
>I want to be able to use one query (or a few in a stored proc to
>duplicate a specific row in this table.
>I do not want to list the fields in case some are added later.
>Ex : insert into T_Company select * from T_Company where intCompanyId
>= 17
>This query gives me an error :
>An explicit value for the identity column in table 'T_Company' can
>only be specified when a column list is used and IDENTITY_INSERT is
>ON.
>Even if I turn it OFF it will not let it insert it since it will have
>the same intCompanyId as the other one.
>Can some one help me?
>Thanks
>Alain
>|||Guys, Guys, Guys,
It never ceases to amaze me how so many questions get answered wit
another question... instead of an answer.
I have the same question, but I think what the original author i
trying to do here is duplicate the row while allowing the identity/ke
to grow on its own... without having to list every other column in th
table. (Hence the * he is wanting to use). Furthermore, the autho
would like to accomplish this in a single SQL statement.
Afterall, a primary key is a primary key, and a unique identifier is
unique identifer.
It seems to me this would be a capability often persued by any seasone
and active SQL programmer. And thus, I would expect such functionalit
from a seasoned language (aka, sql)
With the previous assumption having been made, does anyone have th
answer? Is there a way to duplicate everything in a row except for an
autonumbering identifiers while allowing those autonumbers t
autonumber as they were designed to (without listing every single othe
column in the table)
-
TheSqlGu
----
Posted via http://www.mcse.m
----
View this thread: http://www.mcse.ms/message1119654.htm|||Yes, this is a an activity that I end up doing regularly. Typically
when I am writing a stored procedure and I want to get a working set
of data into a temporary table and have the temporary table use its
own identity key.
There is no straightforward way that I know of doing this but I use a
technique that Dan Guzman MVP gave me some time back which I think is
very neat.
--create temp table with resultset columns
SELECT *
INTO #MetaData
FROM MyBigTable
...or...
select * (where all columns are listed and have column names)
into #MetaData
from [my_bespoke_query_joining_several_tables]
--list meta data
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either your tempdb or your current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) = OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
-- More specific case where I want to prepare a create table statement
for a
-- temp table that will hold the result set from a stored procedure
because the
-- temporary table must exist before you can use a statement like...
-- INSERT #my_tbl
-- EXECmy_proc
SELECT case ordinal_position
when 1 then ' '
else ' ,'
end
+ quotename(column_name) collate SQL_Latin1_General_CP1_CI_AS +
' '
+ case data_type
when 'int' then data_type
when 'char' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'varchar' then data_type + '(' + convert(varchar(5),
character_maximum_length) + ')'
when 'nvarchar' then 'varchar(' + convert(varchar(5),
character_maximum_length) + ')'
when 'decimal' then data_type + '(' + convert(varchar(5),
numeric_precision) + ',' + convert(varchar(5), numeric_scale) + ')'
when 'smallint' then 'int'
when 'datetime' then data_type
when 'smalldatetime' then 'datetime'
else 'datatype not handled'
end
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
OBJECT_ID(
(
-- Use the collation of either the tempdb or the current db here...
-- Collations must match. Experiment.
+ QUOTENAME(TABLE_CATALOG) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_SCHEMA) COLLATE
SQL_Latin1_General_CP1_CI_AS
+ N'.' COLLATE SQL_Latin1_General_CP1_CI_AS
+ QUOTENAME(TABLE_NAME) COLLATE
SQL_Latin1_General_CP1_CI_AS)
) = OBJECT_ID('tempdb.dbo.#MetaData' COLLATE
SQL_Latin1_General_CP1_CI_AS) ORDER BY ORDINAL_POSITION
In fact, if I am writing a stored proc that will later almost
certainly become an input to a further aggregated proc, I always put
in an additional parameter that allows the meta-data of the proc to be
returned as an additional result set.
I think this should give you a nice workaround that at worst means a
bit of cutting and pasting.
Regards
Liam
TheSqlGuy <TheSqlGuy.1dqkdq@.mail.mcse.ms> wrote in message news:<TheSqlGuy.1dqkdq@.mail.mcse.ms>...
> Guys, Guys, Guys,
> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
> I have the same question, but I think what the original author is
> trying to do here is duplicate the row while allowing the identity/key
> to grow on its own... without having to list every other column in the
> table. (Hence the * he is wanting to use). Furthermore, the author
> would like to accomplish this in a single SQL statement.
> Afterall, a primary key is a primary key, and a unique identifier is a
> unique identifer.
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer. And thus, I would expect such functionality
> from a seasoned language (aka, sql)
> With the previous assumption having been made, does anyone have the
> answer? Is there a way to duplicate everything in a row except for any
> autonumbering identifiers while allowing those autonumbers to
> autonumber as they were designed to (without listing every single other
> column in the table)?|||> It never ceases to amaze me how so many questions get answered with
> another question... instead of an answer.
Answering a question directly isn't always the appropriate and professional
way to help someone who needs it. If someone asked you "Where can I get a
gun so I can shoot myself?" would you just answer the direct question or
would you offer some more constructive suggestions? The OP was asking how to
do something that will destroy the integrity of his data (if indeed it had
any to start with). My response was to ask for more information about his
actual business requirement so I could advise him better. Unfortunately
there are a lot of people posting to this group who are desperately trying
to shoot themselves in the foot. Not all of us want to help them to do it!
> It seems to me this would be a capability often persued by any seasoned
> and active SQL programmer.
Not by a *good* SQL programmer!
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
> If someone asked you "Where can I get a
> gun so I can shoot myself?" would you just answer the direct question or
> would you offer some more constructive suggestions?
Depends on the person.|||Also depends on what the original poster meant by "duplicate". I have
often had users ask me for funtionality to "duplicate" an order and
what they really mean is that they want the original order used as a
template to produce a new order - which is fair enough. Of course, it
would suggest that such functionality was an afterthought (which it
often is) and should have been factored into the original design.
An ability to appreciate the difference between the syntactic
precision of gun mechanics(parsed code) and the semantic ambiguity of
target shooting(meeting users requirements and expectations) is a very
useful skill.
Regards
Liam
"Mark Wilden" <mark@.mwilden.com> wrote in message news:<TfSdnZTmCJhXW_jcRVn-rw@.sti.net>...
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:hO-dnef2Xcp_B_jcRVn-iw@.giganews.com...
> > If someone asked you "Where can I get a
> > gun so I can shoot myself?" would you just answer the direct question or
> > would you offer some more constructive suggestions?
> Depends on the person.

How to do this?

I need to combine two fields in a single column in a query like Stock Number
and Product in a way it will look like this:
Stock NumberA - ProductA
Stock NumberB - ProductA
Stock NumberC - ProductA
...and so on
What I want to achieve here is the position of the "-" seperate the Stock
Number and the Product. I want to algin the "-" in the same position.
However the Stock Number is varied in length and I am not sure how to do
this.
ThanksAssuming that both StockNumber and ProductName use character datatypes:
SELECT LEFT(StockNumber + REPLICATE(' ', 20), 20) + '-' + ProductName
FROM YourTable
Apply appropriate cast/convert if they are not...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eXfHLks7FHA.3416@.TK2MSFTNGP15.phx.gbl...
>I need to combine two fields in a single column in a query like Stock
>Number and Product in a way it will look like this:
> Stock NumberA - ProductA
> Stock NumberB - ProductA
> Stock NumberC - ProductA
> ...and so on
> What I want to achieve here is the position of the "-" seperate the Stock
> Number and the Product. I want to algin the "-" in the same position.
> However the Stock Number is varied in length and I am not sure how to do
> this.
> Thanks
>|||Thank you for your reply.
However the width of a character is different like "i" and "w", simply by
adding " " to it can not make the "-" appears at the same location
horizontally.
Thanks.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uo7fbos7FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Assuming that both StockNumber and ProductName use character datatypes:
>
> SELECT LEFT(StockNumber + REPLICATE(' ', 20), 20) + '-' + ProductName
> FROM YourTable
>
> Apply appropriate cast/convert if they are not...
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Paul" <paul_mak@.hotmail.com> wrote in message
> news:eXfHLks7FHA.3416@.TK2MSFTNGP15.phx.gbl...
>|||That's a font problem. Use a fixed-width font if you need that.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Paul" <paul_mak@.hotmail.com> wrote in message
news:u5$dPdu7FHA.2092@.TK2MSFTNGP12.phx.gbl...
> Thank you for your reply.
> However the width of a character is different like "i" and "w", simply by
> adding " " to it can not make the "-" appears at the same location
> horizontally.
> Thanks.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uo7fbos7FHA.1184@.TK2MSFTNGP12.phx.gbl...
>|||Paul (paul_mak@.hotmail.com) writes:
> Thank you for your reply.
> However the width of a character is different like "i" and "w", simply by
> adding " " to it can not make the "-" appears at the same location
> horizontally.
If you need to cater for proportional fonts, then you have entirely
grown out of SQL Server. Then you need to find client-side solution.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please 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.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files.
This is an insane design flaw that will make all your queries, data
audits, etc. ten times harder than they should be . Improve your data
quality by killing the person that did this. Then switch to UPC or EAN
or another fixed length industry code.|||>> However the width of a character is different like "i" and "w", simply by
adding " " to it can not make the "-" appears at the same location horizont
ally. <<
Paul, you are an idiot. Step away from the database. Fonts are
display and not data!! This is soooooooooooo fundamental that I cannot
believe you posted this. I have to use this in a book!