Managed Procedure to automate archiving files in a database

I need to archive files in a database by checking an archive date for the file contained in a field in a table of a database, if the archive date is greater than todays date then archive the file by moving it to an archive folder. I am thinking the best way might be to use a manged stored procedure, but I also need to run this procedure once every 24 hours at about midnight so how would I do thi? Another way might be by using DTS or something. Has someone else done this and how did they go about it?

Hi,

You might want to have a look atJobsin sql server. You are able setup jobs to run at set intervals (in your case, midnight).

With moving archived files into a different directory u can consider usingxp_cmdshell

eg. EXECxp_cmdshell 'copy c:\test.txt d:\archived\text.txt --this is equivelent to running this in command prompt.

If you dont like this idea then consider writing aWindows Service.

Manage Indexes in Sql 2005

Hi,

I'm currently migrating to Sql 2005 from 2000 and have a quick question about indexes.

In 2000 i used to click on Table > All Tasks > Manage Indexes... to view and create all my table indexes. There doesn't seem to be anything similar in 2005 Management Studio. I thought it was under Table > Modify but there's nothing there for indexes. I've had to create all my indexes manually through t-sql but this doesn't help when needing to view and manage current indexes.

Can anyone help me on this?

If you go to Modify the table, you'll notice 'Table Designer' on the menu bar, there's 'Indexes/keys', it's also one of the icon on the tool bar.

If you just want to simply view the indexesof a table on the object exploere, you can going down the hierarchy tree of a table, it's one node below triggers.

|||

Excellent, thanks very much.

I didn't realise there were more options underneath the table tree, i normally right click the table for all options.

Cheers

Malicious user erase a table

Please. I need to find the way to identify a user of the database that erase
a table. Since I can do it ?Hi,
Try to read the Trasnaction log using Loagexplorer (www.lumigent.com) .
Otherway is to enable the profiler.
Thanks
Hari
SQL Server MVP
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that
> erase
> a table. Since I can do it ?|||Start with the users who have rights to delete tables in that database. If
your security is set up right, that should narrow it down considerably.
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that
> erase
> a table. Since I can do it ?|||Assuming you are using trusted connections or user-specific SQL logins, your
only chance would be to get a log reading program that allows you read the
transaction log.
Thomas
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that era
se
> a table. Since I can do it ?|||If you don't know who (or what) deleted the table, then how do you know they
are malicious?
When did this occur? If it happened like less than an hour ago, then perhaps
they are still logged into EM or QA. Go into Enterprise Manager and look
under Managment\Current Activity\Process Info. This lists all current
connections to the server (including login name), and clicking on each
process will show the last SQL batch command they executed.
"BED" <BED@.discussions.microsoft.com> wrote in message
news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> Please. I need to find the way to identify a user of the database that
erase
> a table. Since I can do it ?|||Thanks, but log I erase.
It is left in some table the registry of drop and the user who did it ?
"Hari Pra" wrote:

> Hi,
> Try to read the Trasnaction log using Loagexplorer (www.lumigent.com) .
> Otherway is to enable the profiler.
> Thanks
> Hari
> SQL Server MVP
>
> "BED" <BED@.discussions.microsoft.com> wrote in message
> news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
>
>|||The answer would be a resounding "No". Barring a custom logging solution, th
e
transaction log is *the* tool which gave you any hope of finding who emptied
your table.
Thomas
"BED" <BED@.discussions.microsoft.com> wrote in message
news:CC2D80A2-F74D-4C42-B765-94F897D9ADC1@.microsoft.com...
> Thanks, but log I erase.
> It is left in some table the registry of drop and the user who did it ?
> "Hari Pra" wrote:
>|||ok. Thank you very much
"Thomas Coleman" wrote:

> The answer would be a resounding "No". Barring a custom logging solution,
the
> transaction log is *the* tool which gave you any hope of finding who empti
ed
> your table.
>
> Thomas
>
> "BED" <BED@.discussions.microsoft.com> wrote in message
> news:CC2D80A2-F74D-4C42-B765-94F897D9ADC1@.microsoft.com...
>
>|||If you can't figure out "who deleted the table" then your security
configuration is pretty wack. Restore from backup and take away the rights
to "delete tables" from users who don't need it (more often than not in a
production environment this will be almost *all* of your users).
"JT" <someone@.microsoft.com> wrote in message
news:eam0HCYXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> If you don't know who (or what) deleted the table, then how do you know
> they
> are malicious?
> When did this occur? If it happened like less than an hour ago, then
> perhaps
> they are still logged into EM or QA. Go into Enterprise Manager and look
> under Managment\Current Activity\Process Info. This lists all current
> connections to the server (including login name), and clicking on each
> process will show the last SQL batch command they executed.
> "BED" <BED@.discussions.microsoft.com> wrote in message
> news:1B5F07B7-A43B-4F7E-80C5-2D86E1D6D947@.microsoft.com...
> erase
>

Making text bold in a table cell

I want to build a string to be displayed in a table cell using an
expression, I want to make some parts of the string bold. Can I do this..?
here's an example, in my table cell I have the following expression:
= "Cause:" + Fields!Cause.Value
I want to make the text string "Cause:" bold
TIA,
DanIt's all or nothing in the current release - this attribute can be set at a
cell level, not to a part of string in the cell.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%23H8vxFBfEHA.248@.TK2MSFTNGP12.phx.gbl...
> I want to build a string to be displayed in a table cell using an
> expression, I want to make some parts of the string bold. Can I do this..?
> here's an example, in my table cell I have the following expression:
> = "Cause:" + Fields!Cause.Value
> I want to make the text string "Cause:" bold
>
> TIA,
> Dan
>|||Formatting only part of the string (i.e. rich text) isn't in the current
version. On the wishlist for a future version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:%23H8vxFBfEHA.248@.TK2MSFTNGP12.phx.gbl...
> I want to build a string to be displayed in a table cell using an
> expression, I want to make some parts of the string bold. Can I do this..?
> here's an example, in my table cell I have the following expression:
> = "Cause:" + Fields!Cause.Value
> I want to make the text string "Cause:" bold
>
> TIA,
> Dan
>

Making random names!

Hi
I have no idea but want to learn it how to make random names with sql server...
I have a table, called table1, for colums Firstname and Last name
I want it to make random names, so much it is possible it can get, in table2 where the colums is named Names
PLEASE HELP :)

knuff--

Regardign this...

knuff wrote:

...I have no idea but want to learn it how to make random names with sql server...

...I say, I am just guessing (and yet I think this is quite possible)...

...I expect that one could use these TSQL functions...

RAND ( [ seed ] )
Returns a random float value from 0 through 1.

ROUND ( numeric_expression , length [ , function ] )
Returns a numeric expression, rounded to the specified length or precision.

CHAR ( int )
A string function that converts an int ASCII code to a character.

...and some string contatenation to get the job done.

That said, I will add that doing something like this is MUCH better suited to middle-tier logic rather than the database, IMHO.

HTH.

Thank you.

--Mark Kamoski

|||Ok... Sounds logical to me what you wrote there i can try out of that

making JOINS

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join
>
nice weekeend
>
Table 1
>
10 5012 10
1600502
100 502 3
1500800 2
>
>
>
>
Table 2
>
1500503 1
14004000
100502 10
100600
100502 3
>

Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)|||Hi Daniel,
thanks alot for the prompt reply. this is what i had:

100 502
100 502
1500 800

as seen in the table 100 502was ordered and shipped but more articles
were shipped not ordered by customerID(100). I do not need this
information. I need a list where a customer ordered and received
shippment where no product ordered was shipped. in this case
customer(1500) should be the right answer. He ordered and recived
shippement but within the list no article oredered was shiped. but
customer (100) received the ordered product plus some extra
Hope to read from u and any one else

DA Morgan wrote:

Quote:

Originally Posted by

MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3

>
Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)

|||On 23 Oct 2006 00:52:26 -0700, MORALBAROMETER wrote:

Quote:

Originally Posted by

>Hi Daniel,
>thanks alot for the prompt reply. this is what i had:
>
>100 502
>100 502
>1500 800
>
>as seen in the table 100 502was ordered and shipped but more articles
>were shipped not ordered by customerID(100). I do not need this
>information. I need a list where a customer ordered and received
>shippment where no product ordered was shipped. in this case
>customer(1500) should be the right answer. He ordered and recived
>shippement but within the list no article oredered was shiped. but
>customer (100) received the ordered product plus some extra
>Hope to read from u and any one else

Hi MORALBAROMETER,

Maybe something like this?

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
LEFT JOIN Shipments AS s
ON s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID
WHERE s.CustomerID IS NULL

or the following (logically equivalent, but easier to understand for
beginning SQL coders):

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
WHERE NOT EXISTS
(SELECT *
FROM Shipments AS s
WHERE s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID)

If these are not what you're after, then I recommend that you post your
table structure (as CREATE TABLE statements, including constraints,
properties and indexes), some rows of sample data (as INSERT statements)
and expected results. See www.aspfaq.com/5006 for more info.

--
Hugo Kornelis, SQL Server MVP

Making groups available

I'd like to get the sum of a row in a reporting services table. Unlike
summing columns which are easy, I'm told I need to create a group.
I create the group with the fields I want to sum. Then I right click on the
table cell I want to hold the sum value, and select expression. My table
column names are there, but not my group (which I need to sum my row).
When I create the group, I give it a name and add the fields I want to sum.
What else do I have to do to make the group available when I edit expressions?
Of course, if there's some aggregate functions that let's me sum a row
without a group, all the better.
Thanks
--
RandyHi Randy,
Welcome to the MSDN newsgroup.
Regarding on the "sum of a row in a reporting services table", do you mean
perform sum on all the columns in a single data row or still sum a certain
column , but restricted in a certain group?
If you're wantting to perform the sum on all the columns in a single row,
I'm afraid, the group or other filter can not help on this. You may need to
manually write the sum code in the expression of the textbox or certain
table cell. Also, since you'll perform sum on the columns, we need to make
sure all the columns are of number type.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Many thanks for the reply.
Yes, I would like to sum all the columns in the row, and they are all type
int.
I have found the code window, and written a VB function to sum all the
columns. When I say Code.MySum(...) in the Edit Expression dialog box, MySum
is underlined in red and "not defined".
I've tried to just sum the columns in the edit expression dialog box. The
sum function provided only takes one parameter. I'd need an example or BOM
reference to see how to do this. At one point, someone suggested the Subtotal
function, but I couldn't find that in the available aggregate functions list.
If you could point me toward an article on writing functions for reporting
services under the Code tab or in the Edit Expression dialog box, that would
help a lot.
Thanks again,
--
Randy
"Steven Cheng[MSFT]" wrote:
> Hi Randy,
> Welcome to the MSDN newsgroup.
> Regarding on the "sum of a row in a reporting services table", do you mean
> perform sum on all the columns in a single data row or still sum a certain
> column , but restricted in a certain group?
> If you're wantting to perform the sum on all the columns in a single row,
> I'm afraid, the group or other filter can not help on this. You may need to
> manually write the sum code in the expression of the textbox or certain
> table cell. Also, since you'll perform sum on the columns, we need to make
> sure all the columns are of number type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||Thanks for your followup Randy,
IMO, if you just want to do sum on all the columns in a single row, you can
just put the sum code in the expression of the textbox( use + operator).
For example:
=Fields("col1").Value + Fields("col2").Value + Fields("col3").Value
For custom code in SSRS, you can have a look at the following web reference:
#Using Custom Code References in Expressions [Reporting Services]
http://msdn2.microsoft.com/en-US/library/ms155798(SQL.90).aspx
#Chapter 5: Using Custom .Net Code with Reports
http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Making Database Empty

Hello Experts,
Can anyone tell me, how can I make SQL server database empty.I mean i need to keep only restore the table structur, no table data.Hi

I imagine going the other way about it would be the best solution. Script the entire db (including all objects etc) and then run the script i.e. create a new empty db rather than delete the data within your existing one.

HTH

making columns unique

We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
BrandonThere's no big difference between a unique index and a unique constraint.
When you create the unique constraint, a unique index gets created in the
background. However, if you want a foreign key to use that table, you must
use a primary key or unique constraint - a unique index by itself isn't
sufficient.
The above are sufficient to enforce uniqueness. There is no reason to use a
trigger here. Also, don't worry about the size of the table. Much large
tables have been built without problems.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon|||I would not use triggers. Triggers have more overhead to OLTP applications.
Unique constraint should take care of the issue. 3 column 6 columns should
not be a bit concern with the size/groth you are looking for.
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
> We have a table in our database that has 6 columns that as a whole are not
> indexed together however we would like for them to be unique. Would it be
> more efficent or better to create a unique constraint...or a unique
> index...or to create a insert trigger that would perform a look up for a
> record with the same values you are wanting to insert. It would do this
> using an index on 3 of the 6 fields that would not always narrow it down
to
> one row. I am asking this because there is some concern in the office
that
> adding a 6 column index on this table would add a good bit of burdern and
> adding 3 columns to the existing index wouldn't be a whole lot better.
This
> table could contain 100s of 1000s of records. What would you recommend.
> Thanks,
> Brandon
>sql

making columns unique

We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon
There's no big difference between a unique index and a unique constraint.
When you create the unique constraint, a unique index gets created in the
background. However, if you want a foreign key to use that table, you must
use a primary key or unique constraint - a unique index by itself isn't
sufficient.
The above are sufficient to enforce uniqueness. There is no reason to use a
trigger here. Also, don't worry about the size of the table. Much large
tables have been built without problems.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
We have a table in our database that has 6 columns that as a whole are not
indexed together however we would like for them to be unique. Would it be
more efficent or better to create a unique constraint...or a unique
index...or to create a insert trigger that would perform a look up for a
record with the same values you are wanting to insert. It would do this
using an index on 3 of the 6 fields that would not always narrow it down to
one row. I am asking this because there is some concern in the office that
adding a 6 column index on this table would add a good bit of burdern and
adding 3 columns to the existing index wouldn't be a whole lot better. This
table could contain 100s of 1000s of records. What would you recommend.
Thanks,
Brandon
|||I would not use triggers. Triggers have more overhead to OLTP applications.
Unique constraint should take care of the issue. 3 column 6 columns should
not be a bit concern with the size/groth you are looking for.
"Brandon Owensby" <123@.abc.com> wrote in message
news:uJLcwoWrEHA.3520@.TK2MSFTNGP11.phx.gbl...
> We have a table in our database that has 6 columns that as a whole are not
> indexed together however we would like for them to be unique. Would it be
> more efficent or better to create a unique constraint...or a unique
> index...or to create a insert trigger that would perform a look up for a
> record with the same values you are wanting to insert. It would do this
> using an index on 3 of the 6 fields that would not always narrow it down
to
> one row. I am asking this because there is some concern in the office
that
> adding a 6 column index on this table would add a good bit of burdern and
> adding 3 columns to the existing index wouldn't be a whole lot better.
This
> table could contain 100s of 1000s of records. What would you recommend.
> Thanks,
> Brandon
>

Making changes to a table with large amounts of data. Timeout?!

Hello,

I have a table that is fairly large, and I need to make a change to one of the columns in the table. Namely I need to change the datatype and rename that column. When I try to save the updated table, I keep getting a timeout error that says.

'eligibility (dbo)' table
- Unable to create index 'PK_eligibility'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Any ideas on how to make the table change more efficient or change the timeout period. I have to keep the existing data in the table. I am using sql server managment studio(2005) connected to a sql server 2000 database.

Thanks!

this sort of thing always happens to me when using EM. I use only code now.

If you look at the code behind the scenes that EM uses, it is creating a temp table, shoving all the data into that table, re-creating the original table and pushing all the data back. (if you tell it to save the script when you make the change, you'll see what I mean)

For a large table, I would probably create a new column, update the values in the new column with what is in the old column, then drop the old column. The only time consuming step would be the updates, however, you can space those out and update based on a range of values from one of your other columns. (i.e., update table set newcolumn = oldcolumn where datefield between '1/1/2001' and '2/1/2001')

This way it shouldn't have to create the index.

If you are trying to do this on a column that has a constraint, you will have to drop the constraint first. If you are changing the PK, and your PK is the clustered index, it's probably going to be messy any way you go.

Using script, you can't put your columns in different orders. They will always be added at the end.

sql

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued increment
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
pmud
You could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud
|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:

> You could have an identity column AND a calculated column that combined the
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> increment
> so
>
>
|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
[vbcol=seagreen]
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? In
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
...)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
Anith
|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued incremen
t
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
--
pmudYou could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:

> You could have an identity column AND a calculated column that combined th
e
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> increment
> so
>
>|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
[vbcol=seagreen]
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? I
n
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
>|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
..)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
Anith|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:
>

Making an IDENTITY column

Hi,
I have a field in my sql table called ORDER_NO which is also the primary
key. Now, I want to add amother field called AO_Number whose valued increment
whenever a record is added . Those values should be AO-1, AO-2, AO-3...& so
on...
How to have these values for this field coz making it an identity column
makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
Is this possible? & how?
--
pmudYou could have an identity column AND a calculated column that combined the
identity with the value you wanted.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> Hi,
> I have a field in my sql table called ORDER_NO which is also the primary
> key. Now, I want to add amother field called AO_Number whose valued
increment
> whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
so
> on...
> How to have these values for this field coz making it an identity column
> makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> Is this possible? & how?
> --
> pmud|||Hi Mike,
Where do I have to write the sql code for creating a calculated column? In
user defined functions?
Thanks
"Mike Jansen" wrote:
> You could have an identity column AND a calculated column that combined the
> identity with the value you wanted.
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > Hi,
> >
> > I have a field in my sql table called ORDER_NO which is also the primary
> > key. Now, I want to add amother field called AO_Number whose valued
> increment
> > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> so
> > on...
> >
> > How to have these values for this field coz making it an identity column
> > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> >
> > Is this possible? & how?
> > --
> > pmud
>
>|||Here is how to do it. You can also create a view.
alter your_table
add ao_number int not null identity(1, 1)
go
alter your_table
all ao_formatted_number as 'AO-' + ltrim(ao_number)
go
selet * from your_table
go
AMB
"pmud" wrote:
> Hi Mike,
> Where do I have to write the sql code for creating a calculated column? In
> user defined functions?
> Thanks
> "Mike Jansen" wrote:
> > You could have an identity column AND a calculated column that combined the
> > identity with the value you wanted.
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > Hi,
> > >
> > > I have a field in my sql table called ORDER_NO which is also the primary
> > > key. Now, I want to add amother field called AO_Number whose valued
> > increment
> > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > so
> > > on...
> > >
> > > How to have these values for this field coz making it an identity column
> > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > >
> > > Is this possible? & how?
> > > --
> > > pmud
> >
> >
> >|||Here is an example:
CREATE TABLE tbl (
key_col INT NOT NULL PRIMARY KEY,
id_col INT NOT NULL IDENTITY,
calc_col AS 'AO-' + CAST( id_col AS VARCHAR ), -- calculated column
...)
If you are looking for a truly monotonic sequence, avoid identity. There are
certain instances where identity column can have gaps its values. If the
value is something that can be derived based on some collating sequence of
existing values in other columns, consider using a ranking mechanism like
the one detailed in KBA 186133. Another alternative, is to use a view which
can generate the sequential values based on existing columns without
exposing its complexity.
--
Anith|||Hi Aljandro,
That solved my problem.
Thanks
"Alejandro Mesa" wrote:
> Here is how to do it. You can also create a view.
> alter your_table
> add ao_number int not null identity(1, 1)
> go
> alter your_table
> all ao_formatted_number as 'AO-' + ltrim(ao_number)
> go
> selet * from your_table
> go
>
> AMB
> "pmud" wrote:
> > Hi Mike,
> >
> > Where do I have to write the sql code for creating a calculated column? In
> > user defined functions?
> >
> > Thanks
> >
> > "Mike Jansen" wrote:
> >
> > > You could have an identity column AND a calculated column that combined the
> > > identity with the value you wanted.
> > >
> > > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > > news:FAA53637-F523-4ABF-977F-6CF66695B6F3@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a field in my sql table called ORDER_NO which is also the primary
> > > > key. Now, I want to add amother field called AO_Number whose valued
> > > increment
> > > > whenever a record is added . Those values should be AO-1, AO-2, AO-3...&
> > > so
> > > > on...
> > > >
> > > > How to have these values for this field coz making it an identity column
> > > > makes the Values 1,2 ,3 ... & not AO-1,AO-2,AO-3...
> > > >
> > > > Is this possible? & how?
> > > > --
> > > > pmud
> > >
> > >
> > >

Making an Existing column an Identity column

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.

Ajay

WORD4LIFE
(http://www.word4life.com)Nope...can't do it...

If you have a table with a int column and want to change it...you can graphically do it in EM...

But guess what it's really doing...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1a
(
col1 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a ON
GO
IF EXISTS(SELECT * FROM dbo.Table1a)
EXEC('INSERT INTO dbo.Tmp_Table1a (col1)
SELECT col1 FROM dbo.Table1a TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_Table1a OFF
GO
DROP TABLE dbo.Table1a
GO
EXECUTE sp_rename N'dbo.Tmp_Table1a', N'Table1a', 'OBJECT'
GO
GRANT SELECT ON dbo.Table1a TO public AS dbo
COMMIT

Just make sure you don't already have a temp_table...

What a hack

M$ is good at them...lots of practice...|||Look it up in BOL

TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.

And the IDENTITY_INSERTS Are inb the correct order...

btw, sql server itself generated it...look a save sql server change script in EM after you make a change to a table...sql

Making a view that shows the results of several different queries.

Hello,

I am trying to create a view that shows the following

Field1: Sum of Amounts from Table A
Field2: Count of Amounts from Table A

Field3: Sum of of Amounts from Table B
Field4: Count of Amounts from Table B
..
..
..
Field3: Sum of of Amounts from Table H
Field4: Count of Amounts from Table H
..
..
..
Things are a bit more complex but this is the gist.

I am using SQL 2000.

I know how to do this pretty easily using a stored procedure. But how
can I do it in a view? A SQL server won't meet my needs in this
situation.

I tried OpenQuery ('myserver', 'exec myprocedure') but get the message
that my server is not configured for data access. I tried the system
stored procedure to set data access to true but nothing seemed to
happen.

I also tried Select * from (

Select Statement1, select statement2

)

but got syntax error at the comma between statement1 and statement2.

Trying to use select Statement1 as ABC to does not seem to work either.

Is there a way to do what I want without making 15 views and then a
final view that shows them all together? I know I could probably do
something by creating a ton of functions, but it really seems this
should not be that hard...

I am definitely open to any easy suggestions!

Thanks,
RyanOn 21 Dec 2005 13:51:38 -0800, Ryan wrote:

>Hello,
>I am trying to create a view that shows the following
>Field1: Sum of Amounts from Table A
>Field2: Count of Amounts from Table A
>Field3: Sum of of Amounts from Table B
>Field4: Count of Amounts from Table B
>.
>.
>.
>Field3: Sum of of Amounts from Table H
>Field4: Count of Amounts from Table H
>.
>.
>.
>Things are a bit more complex but this is the gist.
>I am using SQL 2000.
>I know how to do this pretty easily using a stored procedure. But how
>can I do it in a view? A SQL server won't meet my needs in this
>situation.

Hi Ryan,

This can be done in a single query. You can of course encapsulate that
in a view, stored procedure, or whatever.

To prevent double table-scanning, here's a query that will scan each
table only once:

SELECT Field1, Field2, Field3, ..., Field16
FROM (SELECT SUM(Amount) AS Field1, COUNT(Amount) AS Field2
FROM TableA) AS A
CROSS JOIN (SELECT SUM(Amount) AS Field3, COUNT(Amount) AS Field4
FROM TableB) AS B
....
CROSS JOIN (SELECT SUM(Amount) AS Field15, COUNT(Amount) AS Field16
FROM TableH) AS H

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have tried what you suggested but it seems to snag with Syntax
errors. SQL accepts my Select ... AS A, B, C if I use it with the
Select * construct but it does not accept the CROSS JOIN STATEMENT

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2"...
)
AS A

CROSS JOIN

Select * FROM
(
SELECT count(Amount) as count1, sum(Amount) as sum1
FROM mytable1
WHERE x="1" and y="2" and some other stuff...
)
AS B

Any ideas? I am not sure why the CROSS JOIN won't work actually. It
error out as soon as it hits the SELECT statement that follows CROSS
JOIN...|||On 21 Dec 2005 16:58:18 -0800, Ryan wrote:

>I have tried what you suggested but it seems to snag with Syntax
>errors. SQL accepts my Select ... AS A, B, C if I use it with the
>Select * construct but it does not accept the CROSS JOIN STATEMENT

Hi Ryan,

You didn't use the correct syntax. Check my previous replly and compare
it carefully with your query.

>Select * FROM
>(
>SELECT count(Amount) as count1, sum(Amount) as sum1
>FROM mytable1
>WHERE x="1" and y="2"...
>)
>AS A
>CROSS JOIN
Remove the line below!
>Select * FROM
>(
>SELECT count(Amount) as count1, sum(Amount) as sum1
>FROM mytable1
>WHERE x="1" and y="2" and some other stuff...
>)
>AS B
>Any ideas? I am not sure why the CROSS JOIN won't work actually. It
>error out as soon as it hits the SELECT statement that follows CROSS
>JOIN...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

making a view from the other server

hi

I have two sql servers in the LAN, one is 2000 and the other is 2005. in 2005 server I have a database and I want to have a view of a table in a database in the other server (2000) is it possible?

thank you in advance

Did you used Linked servers (see Books Online) ?

|||

thank you for your answer,I will check but what should I do if they are not

|||another way is OPENROWSET but if this table is importan i should think to import it periodically depends on your business logic|||Just in case you are hitting brick wall of errors with linked server, refer to http://blogs.msdn.com/sqltips/archive/2005/06/07/426578.aspx

making a view from the other server

hi

I have two sql servers in the LAN, one is 2000 and the other is 2005. in 2005 server I have a database and I want to have a view of a table in a database in the other server (2000) is it possible?

thank you in advance

Did you used Linked servers (see Books Online) ?

|||

thank you for your answer,I will check but what should I do if they are not

|||another way is OPENROWSET but if this table is importan i should think to import it periodically depends on your business logic|||Just in case you are hitting brick wall of errors with linked server, refer to http://blogs.msdn.com/sqltips/archive/2005/06/07/426578.aspxsql

Making a T-SQL Query

I have a table like below(bolds are field names)

R_IdNameQ1Q2Q3Q4Q5

M001Mikeabcde

J001 Johnabcde

P001 Peterabcde

I want results based on above table as below. The columns (Q1 to Q5) are put as Question_id for each user like M001 and Question (a, b ,c,d,e ) as column Question exactly as below.

Could anyone help me please writing the script to achieve below from above table.

Result

R_idNameQuestion_idQuestion

M001MikeQ1a

M001MikeQ2b

M001MikeQ3c

M001MikeQ4d

M001MikeQ5e

J001JohnQ1a

J001JohnQ2b

J001JohnQ3c

J001JohnQ4d

J001JohnQ5e

........ ....... ....... ....

Thanks all

If you use sql server 2005,

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select

R_Id

,Name

,Question_id

,Question

From

#Data

UNPIVOT

(

Question For Question_idin

([Q1],[Q2],[Q3],[Q4],[Q5])

) UPVT

|||

Assuming you are using SQL 2005, you need to use the UNPIVOT operator.

The code will be something like this:

Code Snippet

DECLARE @.MyTable table
( R_Id varchar(10),
Name varchar(20),
Q1 char(1),
Q2 char(1),
Q3 char(1),
Q4 char(1),
Q5 char(1)
)

INSERT INTO @.MyTable VALUES ( 'M001', 'Mike', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'J001', 'John', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'P001', 'Peter', 'a', 'b', 'c', 'd', 'e' )

SELECT
R_ID,
Name,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

R_ID Name Question Response
- -- --
M001 Mike Q1 a
M001 Mike Q2 b
M001 Mike Q3 c
M001 Mike Q4 d
M001 Mike Q5 e
J001 John Q1 a
J001 John Q2 b
J001 John Q3 c
J001 John Q4 d
J001 John Q5 e
P001 Peter Q1 a
P001 Peter Q2 b
P001 Peter Q3 c
P001 Peter Q4 d
P001 Peter Q5 e

|||

If you use sql server 2000

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select R_Id,Name,'Q1' Question_id,[Q1] Question From #Data

Union All

Select R_Id,Name,'Q2' Question_id,[Q2] Question From #Data

Union All

Select R_Id,Name,'Q3' Question_id,[Q3] Question From #Data

Union All

Select R_Id,Name,'Q4' Question_id,[Q4] Question From #Data

Union All

Select R_Id,Name,'Q5' Question_id,[Q5] Question From #Data

|||

Arnie, Missed column...

SELECT
R_ID,
Name,
Response,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

Making a table viewable or not on specific page

Hi
I want to make a table or even a row to be visible on the first and the
last pages only. Any suggestions on how to do it will be helpful.
thanxIf you want a table or row to be visible only when on the last page you could
try the following expression in the table's/row's Hidden property:
=iif(Globals!PageNumber = Globals!TotalPages, False, True)
If you want it to appear only on the first page try:
=iif(Globals!PageNumber = 1 , False, True)
HTH,
Magendo_man
"Aquariun25" wrote:
> Hi
> I want to make a table or even a row to be visible on the first and the
> last pages only. Any suggestions on how to do it will be helpful.
> thanx
>|||Sorry, ignore previous suggestion. These Global values can only be used in
page headers and footers!
"magendo_man" wrote:
> If you want a table or row to be visible only when on the last page you could
> try the following expression in the table's/row's Hidden property:
> =iif(Globals!PageNumber = Globals!TotalPages, False, True)
> If you want it to appear only on the first page try:
> =iif(Globals!PageNumber = 1 , False, True)
> HTH,
> Magendo_man
> "Aquariun25" wrote:
> > Hi
> > I want to make a table or even a row to be visible on the first and the
> > last pages only. Any suggestions on how to do it will be helpful.
> > thanx
> >
> >sql