Know list database and table in SQL Server

I use this macro to list in A column of sheet the name of sql server on
my lan.
Is possible to "scan server by server" and insert into column B the
name of database and related table?
Tks.
Sub test_sql()
Dim TEST As String
Dim RIGA As String

Set sqlApp = CreateObject("SQLDMO.Application")

Set serverList = sqlApp.ListAvailableSQLServers

numServers = serverList.Count
RIGA = 2

For I = 1 To numServers

TEST = serverList(I)

Range("A" + RIGA) = TEST
RIGA = RIGA + 1
Next

Set sqlApp = Nothing

End Subsorry for UP...|||Give this a try - the code assumes that you can connect to each server via Windows Authentication and that you have appropriate permissions to list the databases/tables. I've added Error handling for the connection to each server - if there is a problem listing the databases/tables, the code will stop with an error. Please note that I haven't performed any extensive testing of the code.

Sub SQLAudit()
'Clear sheet
Cells.ClearContents
Cells.ClearFormats

Const DisplaySystemDatabases = False ' Change this if you want to view system databases
Const DisplaySystemTables = False ' change this if you want to view system tables
Dim objSQLApp, objSQLServer, objSQLDatabase, objSQLTable
Dim strSQLServer
Dim i As Integer
i = 1

Set objSQLApp = CreateObject("SQLDMO.Application")

' Enumerate list of available SQL Servers
For Each strSQLServer In objSQLApp.ListAvailableSQLServers
' Server Header (Remove if header not required)
Range("A" & i).Value = strSQLServer
Range("A" & i & ":C" & i).Merge
Range("A" & i & ":C" & i).Font.Bold = True
Range("A" & i & ":C" & i).Font.Size = 14
Range("A" & i & ":C" & i).HorizontalAlignment = xlCenter
i = i + 1
' ***********************
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True ' Connect using Windows Authentication
On Error Resume Next
' Connect to the server (will fail if user does not have logon for server)
objSQLServer.Connect strSQLServer
If Err.Number <> 0 Then
' Display a message if unable to connect to the server
MsgBox ("Failed to connect to: " & strSQLServer & vbCrLf & Err.Description)
Err.Clear
Else
On Error GoTo 0 ' Turn off resume next error handling (throw exception if error occurs)
' Enumerate databases on server
For Each objSQLDatabase In objSQLServer.Databases
If (Not objSQLDatabase.SystemObject) Or DisplaySystemDatabases Then
' Database Header (Remove if header not requied)
Range("B" & i).Value = objSQLDatabase.Name
Range("B" & i & ":C" & i).Merge
Range("B" & i & ":C" & i).Font.Bold = True
Range("B" & i & ":C" & i).HorizontalAlignment = xlCenter
i = i + 1
' ***********************
'Enumerate tables in database
For Each objSQLTable In objSQLDatabase.Tables
If (Not objSQLTable.SystemObject) Or DisplaySystemTables Then
Range("A" & i).Value = objSQLServer.Name
Range("B" & i).Value = objSQLDatabase.Name
Range("C" & i).Value = objSQLTable.Name
i = i + 1
End If
Next
End If
Next
End If
Next

Set objSQLApp = Nothing
Set objSQLServer = Nothing
Set objSQLDatabase = Nothing
Set objSQLTable = Nothing
End Sub

Kirk: Importing/Exporting with column ErrorCode, ErrorColumns

I am currently redirecting lookup failures into error tables with ErrorCode and ErrorColumn. It works fine until I want to transfer data into the archived database. The SSIS pacakage generate by SQL Exporting tool is throwing an "duplicate name of 'output column ErrorCode and ErrorColumn" error. This is caused by oledb source error output. The error output automatically add ErrorCode and ErrorColumn to the error output selection and not happy with it.

I think the question is down to "How to importing/exporting data when table contains ErrorCode or ErrorColumn column?"

Can you not use the derived column component to create 2 differntly named rows containing the same data?

-Jamie

|||

Yes, we can use different name and map them on ole db destination when writing to the error tables. We really don't want to go that way unless there is no other option.

Currently it if failing on the first step of the Data Flow, OLE DB Source, it is not reaching Derived column Transformation, and the build in SQL import/export is not working because of the same issue.

It will be good to verify so we can enhence in our sql naming standard. "Don't use ErrorCode or ErrorColumn as column name in the table; otherwise you can't use sql import/exprot tool. They are reserved keywords in SSIS."

-tianyu

|||

Are you inserting into a database table? If so then of course you cannot do this - a table cannot have 2 columns with the same name.

That doesn't mean that you can't insert identically named pipeline columns into that table. You just have to set up the mappings correctly in the destination adapter.

Have I misunderstood the problem?

-Jamie

|||

Use case for my question,

In PayRoll package, data failed username lookup redirect to Error_Dim_PayRoll table during the process. Later on I want to export the error rows to an archiving database. When you use the SQL exporting tool, the wizard will fail and complaint duplicate ErrorCode and ErrorColumn.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

This is based on default settings for both SQL 2005 and SSIS.

Repro Steps:

1. Create ErrorDB and ErrorDB_Reporting
2. Create Error_Dim_PayRoll tables for both database (script included bellow)
3. Run the Insert statement in ErrorDB
4. Run SQL Import/Export tool to export the row from ErrorDB to ErrorDB_Reporting (you can save generated SSIS package somewhere)
5. You will get complaints and export fails
6. Run the generated package, still fails with duplicate column name error.

CREATE TABLE [dbo].[Error_Dim_PayRoll](
[UserName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrorCode] [int] NULL,
[ErrorColumn] [int] NULL, [FailureReason] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

INSERT INTO Error_Dim_Customer (UserName, ErrorCode, ErrorColumn, FailureReason) VALUES(NULL, 1, 1, 'Can not lookup UserKey from Employee table by UserName')
GO

|||

Tianyu Li wrote:

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream.

It is caused by OleDb Source in the package created and used by import/export tool. The OleDb source will automatically add ErrorCode and ErrorColumn column on its Error output stream which conflict the columns come in from the data source.

Key time column for time series algorithm

Hi, all experts here,

Thank you very much for your kind attention.

I am confused on key time column selection. e.g, I want to predict monthly sales amount, then what column in date dimension should I choose to be the key time column? Is it calendar_date (the key of date dimension) column or calendar_month?

Thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Key Time is the column that sorts your data in a series and the granularity for the column should be the same as your data. Since in your example you're predicting Monthly Sales, the Key Time should be the calendar_month.

However, the data type for key time is required to be sortable, i.e. long, double or datetime.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,

Key column is insufficient or incorrect. too many rows were affect

Hi,
When I open table, return all rows using Enterprise Manager and I tried to
edit a column, it returned "Key column is insufficient or incorrect. too man
y
rows were affected by update"
This is a standalone table. No constraint. No formula.
Advice please. TIA !Any reasons to open it in EM?
Do you have any triggers on the table that you modifies to?
If you edit some row , click om another one and see if it worked
Do any modifications thru SP and not by EM
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too
> many
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !|||Has the table a primary key defined?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too m
any
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !

Key column information is insufficient or incorrect. Too many...

Hi,

Im trying to Update a table called Contratos in the field Cliente from the table Clientes with the field Cliente.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clientes ON dbo.Clientes
FOR UPDATE
AS
update Contratos
set Cliente = inserted.Cliente
from Clientes
inner join inserted on Clientes.Cliente = inserted.Cliente

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

What is happen...Can somebody help me?

Leonardo AlmeidaOriginally posted by vectords
Hi,

Im trying to Update a table called Contratos in the field Cliente from the table Clientes with the field Cliente.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clientes ON dbo.Clientes
FOR UPDATE
AS
update Contratos
set Cliente = inserted.Cliente
from Clientes
inner join inserted on Clientes.Cliente = inserted.Cliente

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

What is happen...Can somebody help me?

Leonardo Almeida

Where is relation between Contratos and Clientes?|||This is the case... I would like to learn the statement that make the relation between these tables.
Why? Cos these are separated in two different databases and if a user make an update in a table from database X these changes must to be applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida|||Try this one:

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from inserted on Contratos.ID_Client = inserted.ID_Client

--------
Only because of Pele|||Originally posted by vectords
This is the case... I would like to learn the statement that make the relation between these tables.
Why? Cos these are separated in two different databases and if a user make an update in a table from database X these changes must to be applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

Do every table have a primary key? If no - create primary keys...|||Yes ... each table has the primary key

ID_Client for Clients
ID_Con for Contratos|||This is working:

create table table1(id int primary key,uname varchar(10))
create table table2(id int primary key,id_2 int,uname varchar(10))
go
create trigger i_table1 on table1
for update
as
update table2
set uname=inserted.uname
from inserted where table2.id_2=inserted.id
go
insert table1 select 1,'test'
insert table2 select 1,1,'test'
go
update table1 set uname='test2' where id=1
select * from table1
select * from table2

Try to find error in your script.|||Thank you very much...
Can you send me your e-mail to leonardoalmeida2004@.yahoo.com.br to me give back a return from whats happen to my database I will put this struture you send me in my database and tables

THANK YOU VERY MUCH.

Leonardo Almeida

Key column information is insufficient or incorrect. Too many rows were affected by u

I have edit sql server 2000 table record by vb or direct enterprise mangafer and i have recived one message "Key column information is insufficient or incorrect. Too many rows were affected by update." any guys know this problem pl send the solution

Manish jain

ThanksThe Name Of Allah
hi

In the following table, for example, the error message appears if you attempt to delete one or both of the rows containing "abc" by using SQL Enterprise Manager with the following steps:
1. Right-click on the table.
2. Click on Open table, and then click on Return All Rows.
3. Highlight the rows, and then press the Delete button.

Character_Column1 Character_Column2
abc abc
abc abc
zxy zxy
fgt art

Eng. Maged

key column information is insufficient or incorrect

Dear Friends,
I have table contain 2000 out of those some are
duplicate when i select duplicate records by using Enterprise Manager
and make modification to one of those duplicate records the following
message flashes/display.

key columen information is insufficient or incorrect.Too many rows were
affected by update

pls suggest what is this and how to solve this problem

Thanks in advance

Dinesh Patwaldinu wrote:

Quote:

Originally Posted by

Dear Friends,
I have table contain 2000 out of those some are
duplicate when i select duplicate records by using Enterprise Manager
and make modification to one of those duplicate records the following
message flashes/display.
>
key columen information is insufficient or incorrect.Too many rows were
affected by update
>
pls suggest what is this and how to solve this problem
>
Thanks in advance
>
Dinesh Patwal

Before you can edit the data in Enterprise Manager you need to add a
unique constraint or unique index. To facilitate that it may help to
use SELECT DISTINCT to eliminate duplicates and copy the data to a new
table. It rather depends on just how you want to eliminate the
duplicate data. You can Google for lots of previous posts on this topic
in this group and in the microsoft.public.sqlserver groups.

--
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/...US,SQL.90).aspx
--

Key column information is insufficient of incorrect....

I am trying to delete all records from a table and could
delete all but two of them. I noticed the two records
have identical info. I am getting the following message:
Key Column information is insufficient of incorrect. Too
many rows were affected by update.
Please help
in query analyzer issue a set rowcount 1, then delete the record.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Diane" <anonymous@.discussions.microsoft.com> wrote in message
news:1b72c01c44ff1$f8ab3920$a401280a@.phx.gbl...
> I am trying to delete all records from a table and could
> delete all but two of them. I noticed the two records
> have identical info. I am getting the following message:
> Key Column information is insufficient of incorrect. Too
> many rows were affected by update.
> Please help

Key column information is insufficient ....II

This is the case... I would like to learn the statement that make the
relation between these tables.
Why? Cos these are separated in two different databases and if a user
make an update in a table from database X these changes must to be
applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a
user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Leonardo Almeida" <leonardoalmeida2004@.yahoo.com.br> wrote in message
news:3f674b6d$0$62079$75868355@.news.frii.net...
>
> This is the case... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
> The tables are :
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
> If somebody can help me THANKS A LOT OF...
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

See my reply to your previous post.

Simon|||Leonardo Almeida (leonardoalmeida2004@.yahoo.com.br) writes:
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."

Include a SET NOCOUNT ON first in the trigger. If that does not help,
remove the trigger and run the update again. I would expect in such
case that you get the error anyway. Which would indicate that the error
is in the client code which you did not show us.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What code are you talking about?

In the client code I use Delphi + ADO

ADOTable1.Open;
ADOTable1.Edit;

Now edit the Client registrer

Post the register with the command:

ADOTable1.Post;

The message arise again...
and each table has a primary key...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Leonardo Almeida (leonardoalmeida2004@.yahoo.com.br) writes:
> What code are you talking about?

The SET NOCOUNT ON command should be added to your trigger.

> In the client code I use Delphi + ADO
> ADOTable1.Open;
> ADOTable1.Edit;
> Now edit the Client registrer
> Post the register with the command:
> ADOTable1.Post;
> The message arise again...
> and each table has a primary key...

There is no .Post method in ADO, so I conclude that this is something
Delphi-specific, and I don't know Delphi.

If SET NOCOUNT ON did not help, I can only suggest to use the Profiler
to see what is going on behind the covers.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Leonardo,

Your trigger doesn't look correct to me! Why are you using Clients
table in your trigger? In order to update Contratos table using new
values in Clients, you need to use inserted table not Clients.
Something like this:

create trigger Update_Clients on Clients
for update
as
update Contratos
set Client = inserted.Client
from Contratos join inserted
on Contratos.ID_Client = inserted.ID_Client

In your current trigger, you are dealing with 3 tables (Contratos,
Clients & inserted) without joining them correctly. So when you try to
update Client field of Contratos table, it finds more than one value
in inserted table which causes that problem.
I hope this one works fine. I didn't test it...

Good Luck,
Shervin

Leonardo Almeida <leonardoalmeida2004@.yahoo.com.br> wrote in message news:<3f674b6d$0$62079$75868355@.news.frii.net>...
> This is the case... I would like to learn the statement that make the
> relation between these tables.
> Why? Cos these are separated in two different databases and if a user
> make an update in a table from database X these changes must to be
> applied in the other table in the another database:
> The tables are :
> Principal Database Name : Server Information 2004
> Table Name : Clients
> Fields : ID_Client, Client
> Secondary Database Name : Index2003
> Table Name : Contratos
> Fields : ID_Con, ID_Client, Client
> I need to write a Trigger for Update the table Contratos everytime a
> user change the values in Clients.
> Im using the follow Trigger :
> CREATE TRIGGER UPDate_Clients ON dbo.Clients
> FOR UPDATE
> AS
> update Contratos
> set Client = inserted.Client
> from Clients
> inner join inserted on Clients.Client = inserted.Client
> When I update the register the follow message in the application raise :
> "Key column information is insufficient or incorrect. Too many rows were
> affected by update."
> If somebody can help me THANKS A LOT OF...
> Leonardo Almeida
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Key column information is insufficient ....

This is the case... I would like to learn the statement that make the
relation between these tables.
Why? Cos these are separated in two different databases and if a user
make an update in a table from database X these changes must to be
applied in the other table in the another database:

The tables are :

Principal Database Name : Server Information 2004
Table Name : Clients
Fields : ID_Client, Client

Secondary Database Name : Index2003
Table Name : Contratos
Fields : ID_Con, ID_Client, Client

I need to write a Trigger for Update the table Contratos everytime a
user change the values in Clients.

Im using the follow Trigger :

CREATE TRIGGER UPDate_Clients ON dbo.Clients
FOR UPDATE
AS
update Contratos
set Client = inserted.Client
from Clients
inner join inserted on Clients.Client = inserted.Client

When I update the register the follow message in the application raise :

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

If somebody can help me THANKS A LOT OF...

Leonardo Almeida

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hey Leonardo;

I noticed that there are no replies...I am getting exactly the same
error but cannot find a solution, nor a workaround. What did you
finally wind up doing? Please reply to my email directly rtodd@.metro.ca

I have similar triggers, they all work when I update my tables directly
in SQL Server. When I move to my ACCESS ADP and perform the same
updates directly in the same table, I get the same error message.

If anyone can help us....please do !

--
Posted via http://dbforums.com

key column from 0000001 to 9999999

Hi all,

I want to add a column to my db table with numbers starting at 0000001
and going t'ill the end...It has 2.7 millions entries..so it should be
around .. 2700000. I really need the first number to have all the extra
'0's in front...whenever I place a key column with an identity..it
starts a 1 and increments...I need it to start at 0000001. I tried
placing that(0000001) in the identity seed and increment by 1 but it
still start at 1. I'm fairly a begginer in sql server 2000 but can
manage my way around...
I also tried a query:

alter table dbo.tablename
add column columnname int not null
identity(0000001,1)

and that didn't work out either.(just starts at 1)
So if someone could help me out here I would appreciate. Thanks again
for all the help!!
JMTYou are making the double mistake of A) assigning some business meaning
to an artificial IDENTITY key and B) performing formatting in the
database rather than in the client or display tier.

Store the value as a number and forget about how it's formatted (you
can always do it in a view if you must) or use a CHAR / VARCHAR column
and assign a meaningful key rather than generate one artificially.

--
David Portas
SQL Server MVP
--|||You are making the double mistake of A) assigning some business meaning
to an artificial IDENTITY key and B) performing formatting in the
database rather than in the client or display tier.

Store the value as a number and forget about how it's formatted (you
can always do it in a view if you must) or use a CHAR / VARCHAR column
and assign a meaningful key rather than generate one artificially.

--
David Portas
SQL Server MVP
--|||This is just for a prototype that I'm building. I don't really care who
gets what number and in what order but I need a column that has 7
digits for every row and every row must have a different number. Is
this possible ?? My column is already a VARCHAR.
I am just wondering if it's possible to do something like this ??
I understand that you're trying to teach me something that would be
better for databases in general but I really just need this to work...
THanks alot for the reply!
JMT|||This is just for a prototype that I'm building. I don't really care who
gets what number and in what order but I need a column that has 7
digits for every row and every row must have a different number. Is
this possible ?? My column is already a VARCHAR.
I am just wondering if it's possible to do something like this ??
I understand that you're trying to teach me something that would be
better for databases in general but I really just need this to work...
THanks alot for the reply!
JMT|||If this is just a one-off:

DECLARE @.x VARCHAR(7)
SET @.x = 0

UPDATE YourTable
SET @.x = col = RIGHT('0000000'+CAST(@.x + 1 AS VARCHAR),7)

This is undefined behaviour so don't rely on it in any persistent code.

--
David Portas
SQL Server MVP
--|||If this is just a one-off:

DECLARE @.x VARCHAR(7)
SET @.x = 0

UPDATE YourTable
SET @.x = col = RIGHT('0000000'+CAST(@.x + 1 AS VARCHAR),7)

This is undefined behaviour so don't rely on it in any persistent code.

--
David Portas
SQL Server MVP
--|||I would like to know how to do this in SQL Server also. I scanned the
online documentation but I didn't find a useful example. In Oracle
this is real easy:

UT1 > select to_char(123,'000009') as CNUM from dual;

CNUM
---
000123

I have got to believe that there is a fairly simple way to do this in
SQL Server via a couple of provided functions but I haven't been able
to figure it out yet looking at CONVERT and STR. Who can save me a
couple of hours?

-- Mark D Powell --|||Thanks alot David, this does the job just fine. It was just what I was
looking for...

THanks again,
JMT|||vbnetrookie (bigjmt@.hotmail.com) writes:
> This is just for a prototype that I'm building. I don't really care who
> gets what number and in what order but I need a column that has 7
> digits for every row and every row must have a different number. Is
> this possible ?? My column is already a VARCHAR.
> I am just wondering if it's possible to do something like this ??
> I understand that you're trying to teach me something that would be
> better for databases in general but I really just need this to work...
> THanks alot for the reply!

This is quite easy actually. Drop your varchar column as it is now. Then
say:

ALTER TABLE tbl ADD
ident int IDENTITY,
displaykey AS RIGHT('0000000'+CAST(ident + 1 AS VARCHAR),7)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you, Erland

Using the Northwind Db for testing

select RIGHT('0000000'+CAST(CategoryID AS VARCHAR),7), CategoryId
from Categories

00000011
00000022
00000033
00000044
00000055
00000066
00000077
00000088

-- Mark D Powell --

Key and Index on Same Column?

Is there any advantage to doing this:

ALTER TABLE testtable ADD
CONSTRAINT PK_sysUser
PRIMARY KEY NONCLUSTERED (UserID)
WITH FILLFACTOR = 100,

CONSTRAINT IX_sysUser
UNIQUE NONCLUSTERED (UserID)
WITH FILLFACTOR = 100
GO

over just having the primary key? Does having both an index and a
primary key add anything?

thanks
chrisOn 26 Aug 2005 12:24:19 -0700, christopher.secord@.gmail.com wrote:

>Is there any advantage to doing this:
>ALTER TABLE testtable ADD
>CONSTRAINT PK_sysUser
>PRIMARY KEY NONCLUSTERED (UserID)
>WITH FILLFACTOR = 100,
>CONSTRAINT IX_sysUser
>UNIQUE NONCLUSTERED (UserID)
>WITH FILLFACTOR = 100
>GO
>over just having the primary key? Does having both an index and a
>primary key add anything?

Hi Chris,

No.

When you declare a PRIMARY KEY constraint, SQL Server will immediately
create an index to support it. Default is clustered, but in this case,
you override the default and get a non-clustered index.

When you declare a UNIQUE constraint, SQL Server will immediately create
an index to support it. Default is nonclustered; in this case you're
also asking for non-clustered, so non-clustered is what you'll get.

The end result: two exactly identical nonclustered indexes, double
overhead on data modification, and one of those indexes will never be
used.

However, if you define the primary key with the default clustered
option, there are some circumstances where an extra nonclustered index
on the same column might help. If the table is wide, but some queries
use only the primary key value, a scan of the nonclustered index will be
faster than a scan of the clustered index. I'd not use the UNIQUE
constraint to declare such an index, thoug, but use a CREATE INDEX
statement to stress that this is just a supporting index instead of
another constraint.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>If the table is wide, but some queries
>use only the primary key value, a scan of the nonclustered index will be
>faster than a scan of the clustered index.

Thanks. That's good info.|||Chris,

Hugo already explained the technical details. Here is an other aspect of
the issue.

Both a Primary Key and a Unique constraint are logical construct that
give information about your data. They are true regardless of the
database you use.

You have designed you schema based on some world view. You have
determined that a specific column (or set of columns) is unique, and
therefore is a candidate key for the table. What you typically do, is
choose one of the candidate keys to be the Primary Key of the table, and
you specify all the other candidate keys as Unique. It would be very
confusing to specify the same set of colums as both Unique and Primary
Key, since (by definition), the Primary Key means that the key is unique
(and non-null).

If you are looking at these topics from a performance perspective, then
I would suggest you do not use the modelling concepts (Constraints), but
limit yourself to the implementation concepts (i.e. Indexes). This means
that if you remove all indexes, but keep all constraints, then the
database would still work properly (although it might be slow). In your
example, if you want to experiment with extra indexes for performance
reasons, you can simply add a unique index.

Gert-Jan

"christopher.secord@.gmail.com" wrote:
> Is there any advantage to doing this:
> ALTER TABLE testtable ADD
> CONSTRAINT PK_sysUser
> PRIMARY KEY NONCLUSTERED (UserID)
> WITH FILLFACTOR = 100,
> CONSTRAINT IX_sysUser
> UNIQUE NONCLUSTERED (UserID)
> WITH FILLFACTOR = 100
> GO
> over just having the primary key? Does having both an index and a
> primary key add anything?
> thanks
> chris|||Gert-Jan Strik wrote:
> What you typically do, is
> choose one of the candidate keys to be the Primary Key of the table, and
> you specify all the other candidate keys as Unique. It would be very
> confusing to specify the same set of colums as both Unique and Primary
> Key, since (by definition), the Primary Key means that the key is unique
> (and non-null).

I think that what threw me off was that the database even allowed me to
do both. I was trying hard to think of some reason why I would want to
do that and thought I'd go ahead and ask here. You guys have cleared
it up for me.

> This means
> that if you remove all indexes, but keep all constraints, then the
> database would still work properly (although it might be slow).

Is there any situation where removing an index would cause the database
to not function??

chris|||"christopher.secord@.gmail.com" wrote:
[snip]
> > This means
> > that if you remove all indexes, but keep all constraints, then the
> > database would still work properly (although it might be slow).
> Is there any situation where removing an index would cause the database
> to not function??

No, from a theoretical point of view, you never need to manually create
an index.

What I meant to say is if you remove the constraints (or never create
them in the first place), then you are very likely to get corruption in
your data, such as orphaned rows (missing Foreign Key), duplicate values
(missing Primary Key / Unique constraint), etc. If you have the proper
constraints in place, you can add or remove indexes without affecting
the correctness of the database.

Or course, from a practical point of view, you do need indexes. This is
because by default only Primary Keys and Unique constraints are
automatically indexed. Foreign Keys are not automatically indexed. Your
system would be unnecessarily slow without indexes (generally more I/O
needed), and SQL-Server's locking strategy would also be very limited,
which means lower concurrency (more users 'waiting' for their
transaction).

Gert-Jan|||christopher.secord@.gmail.com (christopher.secord@.gmail.com) writes:
> I think that what threw me off was that the database even allowed me to
> do both. I was trying hard to think of some reason why I would want to
> do that and thought I'd go ahead and ask here.

I guess the reason that SQL Server did not say anything is that no
one thought the condition was worth the extra piece of code need to
add such a check.

Just because something is possible to do with warning or error message,
does not mean that it is a meaningful or a harmless thing to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Keep Group Together

Hi,
we currently have a situation where the column headings for our group is
printed on the bottom of the page and the data starts on the next page. Is
there anyway to ensure the group header prints on the next page instead if
no data can be fitted on the page. Hope this is clear - in Crystal there
was an option Keep group together which did this.
thanks
MattTry putting the items you want to keep together in a rectangle. Sometimes
that keeps them on the same page. If you're working with tables, there are
problems with pagination that probably we'll have to wait for a later
version to fix.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:eiyCb601EHA.3392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we currently have a situation where the column headings for our group is
> printed on the bottom of the page and the data starts on the next page.
> Is
> there anyway to ensure the group header prints on the next page instead if
> no data can be fitted on the page. Hope this is clear - in Crystal there
> was an option Keep group together which did this.
> thanks
> Matt
>|||Thanks for the response Jeff.
Just clarifying:
Are you saying put the whole table in a rectangle?
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:e6dvxV11EHA.4000@.TK2MSFTNGP10.phx.gbl...
> Try putting the items you want to keep together in a rectangle. Sometimes
> that keeps them on the same page. If you're working with tables, there
are
> problems with pagination that probably we'll have to wait for a later
> version to fix.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:eiyCb601EHA.3392@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > we currently have a situation where the column headings for our group is
> > printed on the bottom of the page and the data starts on the next page.
> > Is
> > there anyway to ensure the group header prints on the next page instead
if
> > no data can be fitted on the page. Hope this is clear - in Crystal
there
> > was an option Keep group together which did this.
> >
> > thanks
> >
> > Matt
> >
> >
>|||Yes, and other items as well if preferable. It's not completely
deterministic, but can influence the page breaks.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:%23$AxPFC3EHA.3840@.tk2msftngp13.phx.gbl...
> Thanks for the response Jeff.
> Just clarifying:
> Are you saying put the whole table in a rectangle?
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:e6dvxV11EHA.4000@.TK2MSFTNGP10.phx.gbl...
>> Try putting the items you want to keep together in a rectangle.
>> Sometimes
>> that keeps them on the same page. If you're working with tables, there
> are
>> problems with pagination that probably we'll have to wait for a later
>> version to fix.
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
>> news:eiyCb601EHA.3392@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > we currently have a situation where the column headings for our group
>> > is
>> > printed on the bottom of the page and the data starts on the next page.
>> > Is
>> > there anyway to ensure the group header prints on the next page instead
> if
>> > no data can be fitted on the page. Hope this is clear - in Crystal
> there
>> > was an option Keep group together which did this.
>> >
>> > thanks
>> >
>> > Matt
>> >
>> >
>>
>|||Ok, will give it a try. Thanks
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:%23CoByFI3EHA.1392@.tk2msftngp13.phx.gbl...
> Yes, and other items as well if preferable. It's not completely
> deterministic, but can influence the page breaks.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:%23$AxPFC3EHA.3840@.tk2msftngp13.phx.gbl...
> > Thanks for the response Jeff.
> >
> > Just clarifying:
> > Are you saying put the whole table in a rectangle?
> >
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:e6dvxV11EHA.4000@.TK2MSFTNGP10.phx.gbl...
> >> Try putting the items you want to keep together in a rectangle.
> >> Sometimes
> >> that keeps them on the same page. If you're working with tables, there
> > are
> >> problems with pagination that probably we'll have to wait for a later
> >> version to fix.
> >>
> >> --
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> >> news:eiyCb601EHA.3392@.TK2MSFTNGP10.phx.gbl...
> >> > Hi,
> >> >
> >> > we currently have a situation where the column headings for our group
> >> > is
> >> > printed on the bottom of the page and the data starts on the next
page.
> >> > Is
> >> > there anyway to ensure the group header prints on the next page
instead
> > if
> >> > no data can be fitted on the page. Hope this is clear - in Crystal
> > there
> >> > was an option Keep group together which did this.
> >> >
> >> > thanks
> >> >
> >> > Matt
> >> >
> >> >
> >>
> >>
> >
> >
>