How do I mimic autoNumber for non-identity columns?

Assume I have an inventory system used by several customers. Each customer
wants each item in their inventory to have a number, and they don't want any
gaps.
So, there's an Item table that has information about each item, as well as
the customer the item belongs to. When an item is inserted, I cannot use an
Identity column to autonumber the ItemId because if a customer inserts an
item, then a second customer inserts a hundred items, then the original
customer inserts another item, there is a gap of one hundred items from the
perspective of the original customer. This isn't desired behavior.
I need a way to do a per-customer autonumber, but I don't know how to do
this without running into concurrency problems.Greg, If I understood you properly you need
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
WHERE custid=.....
INSERT INTO AnothetTable VALUES (@.max_item)
COMMIT TRAN
"Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
> Assume I have an inventory system used by several customers. Each
> customer
> wants each item in their inventory to have a number, and they don't want
> any
> gaps.
> So, there's an Item table that has information about each item, as well as
> the customer the item belongs to. When an item is inserted, I cannot use
> an
> Identity column to autonumber the ItemId because if a customer inserts an
> item, then a second customer inserts a hundred items, then the original
> customer inserts another item, there is a gap of one hundred items from
> the
> perspective of the original customer. This isn't desired behavior.
> I need a way to do a per-customer autonumber, but I don't know how to do
> this without running into concurrency problems.|||If Table can be the same as AnotherTable, I think this could work. So,
assuming ItemNumber is the column I want to mimic autonumber on, we'd have:
DECLARE @.max_item
BEGIN TRAN
SELECT @.max_item=COALESCE(MAX(ItenNumber),0) FROM Inventory WITH
(UPDLOCK,HOLDLOCK)
WHERE custid=4
INSERT INTO Inventory VALUES (@.max_item + 1)
COMMIT TRAN
Would that work? Are UPDLOCK and HOLDLOCK merely hints? What if the hints
get ignored?
Thanks.
"Uri Dimant" wrote:

> Greg, If I understood you properly you need
> DECLARE @.max_item
> BEGIN TRAN
> SELECT @.max_item=COALESCE(MAX(item),0) FROM Table WITH (UPDLOCK,HOLDLOCK)
> WHERE custid=.....
> INSERT INTO AnothetTable VALUES (@.max_item)
> COMMIT TRAN
>
>
> "Greg Smalter" <GregSmalter@.discussions.microsoft.com> wrote in message
> news:5BA8A4B0-26E8-4C20-8198-F63D10B36AE4@.microsoft.com...
>
>

How do I make Newspaper Columns in RS

I have 3 columns on my report in a table region. I would like for these 3 cols to print across the page (landscape)width such that more data can fit on to a page - like using newspaper columns in word. How can I do this in reporting services? Thanks in advance for your help.

You can add columns to a report. However, they only show up in certain rendering extensions. In the PDF and TIFF rendering extensions. This means you will not see the column layout in Report Manager or when Previewing the reports in Report Designer. See this link:

http://msdn2.microsoft.com/en-us/library/ms155816.aspx

sql

How do i make a column using 3 other columns together within the same table

Hello all,

The example to this question is better:

ID PName Ppurchased PSold PSellPrice AvgSellprice
1 Water 50 10 100 20
2 Water 40 20 200 100
3 Water 70 35 50 25

What i want to happen within the table or maybe on a different table is to add the AvgSellprice of all 3 together to where it will look like this in a table:

ID PName Ppurchased PSold PSellPrice AvgSellprice TotalSellPrice
1 Water 50 10 100 20 145
2 Water 40 20 200 100 145
3 Water 70 35 50 25 145

Or maybe a new table which i want to look like this:

ID PName TotalSellPrice
1 Water 145

I know i can get the TotalSellPrice from the query:
SELECT SUM(AvgSellprice) AS TotalSellPrice
FROM tablename
WHERE PName='Water'

Also i am using MSSQL 2005 if this may differ. Thanks for any help.Also within this table there are other entries that do not pertain to the water. They have different names which may complicate things a little, not to sure as i don't know how to write the code for this table.

How do I identify the replication generated column(s) in a table

I need to dynamically generate a select command that does not include
replication generated columns. Is there a way to do this? I could use
GetOleDbSchemaTable and filter for column names with "rowguid" but that does
not seem robust to me. Is there a system table or stored procedure that can
help? Dropping a replication subscription and publication removes these
columns so I suspect that the information is available somewhere.
Thanks
Dropping a publication doesn't drop the guid columns added to a merge
publication. As far as I know we can't identify those columns added.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||What about something like this:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and name <>'rowguid'
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
print @.mystring
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kohn" <Kohn@.discussions.microsoft.com> wrote in message
news:915755D4-E2FC-4790-B0E8-6E493DE1BA4F@.microsoft.com...
>I need to dynamically generate a select command that does not include
> replication generated columns. Is there a way to do this? I could use
> GetOleDbSchemaTable and filter for column names with "rowguid" but that
> does
> not seem robust to me. Is there a system table or stored procedure that
> can
> help? Dropping a replication subscription and publication removes these
> columns so I suspect that the information is available somewhere.
> Thanks
>
|||Hi Hilary - unfortunately this doesn't work . We could query for the
rowguid column on the table, but that might already have existed prior to
the replication setup. As far as I can tell there isn't a way of knowing if
the rowguid column is added by the replication setup or by a user
beforehand.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It will work for the majority of the cases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O$uP0uJNHHA.3424@.TK2MSFTNGP02.phx.gbl...
> Hi Hilary - unfortunately this doesn't work . We could query for the
> rowguid column on the table, but that might already have existed prior to
> the replication setup. As far as I can tell there isn't a way of knowing
> if the rowguid column is added by the replication setup or by a user
> beforehand.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Another thing to take into account is that replication does not require
the column to be called rowguid. It just requires a column with the
ROWGUIDCOL property set. Maybe that is where you should start looking.
Hilary Cotter wrote:
> What about something like this:
> declare @.mystring varchar(2000)
> set @.mystring='select '
> select @.mystring=@.mystring+' '+name+', ' From syscolumns where
> id=object_id('customers')
> and name <>'rowguid'
> select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from customers'
> print @.mystring
>
|||I had a quick look and it seems you have to look for colomns where
syscolumns.colstat = 2. You can also only have one column per table with
the ROWGUIDCOL property set, so you can be pretty sure that is the
column used by replication. So to modify Hilary's query:
declare @.mystring varchar(2000)
set @.mystring='select '
select @.mystring=@.mystring+' '+name+', ' From syscolumns where
id=object_id('customers')
and colstat <> 2
select @.mystring=substring(@.mystring,1,len(@.mystring)-1)+' from
customers'
print @.mystring
JE wrote:[vbcol=seagreen]
> Another thing to take into account is that replication does not require
> the column to be called rowguid. It just requires a column with the
> ROWGUIDCOL property set. Maybe that is where you should start looking.
>
> Hilary Cotter wrote:
|||The information for identifying the column with the rowguid property solves
the problem. My app retrieves the information with the GetOleDbSchemaTable
function (see below).
By the way, rowguid columns created by the wizard are removed when dropping
the publication. I suspect it uses the preserve_rowguidcol column in the
sysmergearticles tables.
cn.Open()
Dim t As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
New Object() {Nothing, Nothing, TableName, Nothing})
cn.Close()
cn.Dispose()
Dim SelectRows() As DataRow
SelectRows = t.Select("(DATA_TYPE <> 72) AND
(COLUMN_HASDEFAULT=FALSE) AND (COLUMN_HASDEFAULT=False) AND
((ISNULL(COLUMN_DEFAULT,'T')='T') OR
(COLUMN_DEFAULT<>'(newsequentialid())'))")
Dim SelectListStringBuilder As New System.Text.StringBuilder
For Each r As DataRow In SelectRows
SelectListStringBuilder.Append(r.Item("COLUMN_NAME "))
SelectListStringBuilder.Append(",")
Next
SelectListStringBuilder.Length -= 1
Debug.WriteLine(SelectListStringBuilder.ToString)
Thanks for the help

How do i getting many Rows from a single XML column?

using SQL 2005...

I have a table called training. It has 2 columns ID and data.
The ID column is a INT.
The data column is an XML data type that contains XML looking like the following:
<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005<ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat<ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>

if we asume that the table has 3 rows with the ID field being 1-3 and all the XML is the same as above.
I'm trying create an SQL statement that outputs the following results

id	ProgramName	UrgencyLevel
1	Learn SQL 2005	3
1	Learn To Walk	1
1	Learn To Eat	1
2	Learn SQL 2005	3
2	Learn To Walk	1
2	Learn To Eat	1
3	Learn SQL 2005	3
3	Learn To Walk	1
3	Learn To Eat	1

please can some one help me?

You will need to use the nodes() to break your xml into multiple rows then CROSS APPLY to that. For example:

Dan

CREATE TABLE #t1(
id int,
data xml
)

INSERT INTO #t1 SELECT 1, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

INSERT INTO #t1 SELECT 2, '<TrainingRequests>
<TrainingRequest>
<ProgramName>Learn SQL 2005</ProgramName>
<UrgencyLevel>3</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Walk</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
<TrainingRequest>
<ProgramName>Learn To Eat</ProgramName>
<UrgencyLevel>1</UrgencyLevel>
</TrainingRequest>
</TrainingRequests>'

SELECT ID, t.c.value('ProgramName[1]', 'NVARCHAR(MAX)') + ' ' + t.c.value('UrgencyLevel[1]', 'NVARCHAR(MAX)')
FROM #t1
CROSS APPLY
#t1.data.nodes('TrainingRequests/TrainingRequest') AS t(c)

|||Thank you, you saved me ripping out the rest of my hair!!!

How do I get it to a precisions scale of .00?

I have set the output columns to decimal and data scale of 2. And have also set the field to be 0.00, and in the csv desination file it always puts .000000, How can I get it to be 0.00?

Thanks you for the help

Try creating a derived column cast to DT_NUMERIC and scale of 2 between your data source and destination. That should allow you to get 2 decimal places for any numeric input type. You can add all the columns you want to do this to in the one derived column task.

How do i get Data Type of Each Column in a Sqldatasource control?

Let's say... the sqldatasource has 3 columns: TableKey(int), TableName(string) and StartDate(datetime) and i'm writing a method to return the data type based on the column name.. for example: GetDataType("StartDate") should return "datetime"...what should i do?

I think this is what you need:http://www.java2s.com/Code/VB/Database-ADO.net/GetColunmDatatype.htm

Good luck.

|||

Thanks.

How do I generate a Unique ID?

Hi,

I have 5 tables that need ID columns. These ID columns are the primary keys for these tables. They appear in the tables like so:

Clients table

CLT00001
CLT00002
CLT00003

Volunteers table

VOL00001
VOL00002
etc...

These ID's are given to the Clients/Volunteers so they need to be more than just a number (I want to use the 3 char prefix), but I want them to auto-increment. I am not concerned with gaps if a row is deleted.

At the moment I have a user defined datatype in MSSQL, and I am incrementing and adding the prefix in VB before inserting into the database.

This works fine, except I dont know how to retrieve the last ID number from the DB so I can increment it when I add a new record.

I was using a VB function that did this: SELECT MAX(client_id) FROM clients, then strip everything of the front but the number, increment + 1, add the prefix and leading zeros back on again, and return the new ID. This worked until CLT00011, then it returned CLT00002 again.

Is there a better way to do what I am trying to do? Please help!One solution:

Separate your ID into two fields; the prefix and the numeric portions. Autoincrement the numeric portion, but pad it with zeros and concatenate it with the prefix for display. You could also create a calculated field in your table that combined the two components.

Either way, you can set the combination of the two columns as the primary key, or just as a unique index.

blindman|||Does that mean I would have a Prefix field with 'VOL' in every row, and my IDENTITY field in another. Sounds good, but does it kmatter that I have redundant info. i.e. the prefix field?

How do I join the two with a calculated field, because I still need to have a 8 char long ID, 3 chars for the prefix, then the ID num padded with zeros between the two, e.g.

VOL00001
VOL00002|||Create a trigger what will generate primary key for your table (you can use autoincremet field or calculate new key).|||If every record has the same prefix then is it not necessary to dedicate a column to it.

Set up your autoincrement row, and then create your calculated field with this formula:

'VOL' + right('00000' + cast([AutoIncColumn] as varchar(5)))

blindman

How do I force upper case in a select statement

I want all of the columns in a select statement to be converted to upper
case. What is the proper syntax for that?SELECT UPPER(col1), UPPER(col2), UPPER(col3), ...
FROM dbo.YourTable;
"Thirsty Traveler" <nfr@.nospam.com> wrote in message
news:ubO0jhqeGHA.3692@.TK2MSFTNGP03.phx.gbl...
>I want all of the columns in a select statement to be converted to upper
>case. What is the proper syntax for that?
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Thanks a lot(001frien@.gmail.com) writes:

Quote:

Originally Posted by

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

--
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|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

What was my INSERT-key up to last night? I missed to paste in the
query I was talking about. Here it is:

SELECT o.name, i.name,
col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
JOIN sysindexkeys ik ON ik.id = i.id
AND ik.indid = i.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(o.id, i.name, 'IsStatistics') = 0
AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
GROUP BY o.name, i.name
ORDER BY o.name, i.name

--
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|||Thanks a lot Erland

Erland Sommarskog wrote:

Quote:

Originally Posted by

Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

Here is a query. It lists only the five first index column, but you can
easily augment it if needed.

>
What was my INSERT-key up to last night? I missed to paste in the
query I was talking about. Here it is:
>
SELECT o.name, i.name,
col1 = MIN (CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN (CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN (CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN (CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN (CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysobjects o
JOIN sysindexes i ON i.id = o.id
JOIN sysindexkeys ik ON ik.id = i.id
AND ik.indid = i.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid BETWEEN 1 AND 254
AND indexproperty(o.id, i.name, 'IsStatistics') = 0
AND indexproperty(o.id, i.name, 'IsHypothetical') = 0
GROUP BY o.name, i.name
ORDER BY o.name, i.name
>
>
>
--
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

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lotselect object_name(si.id), si.name from Sysindexes SI inner join
Sysojbects SO ON
(si.id = so.id) where xtype = 'u'
001fr...@.gmail.com wrote:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot|||This query will return a list of indexed columns that are NOT primary keys.
SELECT
TableName = cast( TABLE_NAME as varchar(50)),
ColumnName = cast( COLUMN_NAME as varchar(50))
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN dbo.sysindexes i
ON ( c.TABLE_NAME = object_name( i.id )
AND c.COLUMN_NAME = i.name
)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<001frien@.gmail.com> wrote in message news:1163193099.763199.289570@.k70g2000cwa.googlegroups
.com...
>
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
>
> Thanks a lot
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lot
select object_name(si.id), si.name from Sysindexes SI inner join
Sysojbects SO ON
(si.id = so.id) where xtype = 'u'
001fr...@.gmail.com wrote:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
|||This query will return a list of indexed columns that are NOT primary keys.
SELECT
TableName = cast( TABLE_NAME as varchar(50)),
ColumnName = cast( COLUMN_NAME as varchar(50))
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN dbo.sysindexes i
ON ( c.TABLE_NAME = object_name( i.id )
AND c.COLUMN_NAME = i.name
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
<001frien@.gmail.com> wrote in message news:1163193099.763199.289570@.k70g2000cwa.googlegr oups.com...
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
>

How do i find indexes of the columns in SQL server 2000??

How do i find indexes of the columns of all the tables of the
datbase.........most importantly in SQL server 2000
Thanks a lot
See the responses in microsoft.public.sqlserver.server
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"frien" <001frien@.gmail.com> wrote in message
news:1163196068.628580.97620@.b28g2000cwb.googlegro ups.com...
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
> Thanks a lot
>
|||frien (001frien@.gmail.com) writes:
> How do i find indexes of the columns of all the tables of the
> datbase.........most importantly in SQL server 2000
Answered in comp.databases.ms-sqlserver. Please do not post the same
question, independetly to different newsgroups.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

How do I find IDENTITY columns on Table using T-SQL

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?

Found it, a little obscure:

SELECT obj.[name], col.[name], col.[colstat], col.*
FROM [syscolumns] col
JOIN [sysobjects] obj
ON obj.[id] = col.[id]
WHERE obj.type = 'U'
AND col.[status] = 0x80
ORDER BY obj.[name]

Does anyone know a way of doing this using an INFORMATIO_SCHEMA view?

|||I posted that sometime ago:

SELECT IsIdentity=COLUMNPROPERTY(id, name, 'IsIdentity')
FROM syscolumns WHERE OBJECT_NAME(id) = sometable_test'

Mit Information_schema views from
http://weblogs.asp.net/psteele/archive/2003/12/03/41051.aspx

select TABLE_NAME + '.' + COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') =
1
order by TABLE_NAME

HTH, Jens Suessmeyer.

http://www.sqlserver2005.,de

|||

Here is some more ( in technicolor ;-) )

USE northwind
GO

DECLARE @.tableName VARCHAR(50)
SELECT @.tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @.tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
GO

DECLARE @.intObjectID INT
SELECT @.intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@.intObjectID, 'TableHasIdentity'),0) AS HasIdentity

Denis the SQL Menace

http://sqlservercode.blogspot.com/

How do I extract data from selected rows from my excel source file?

The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column.

Thanking in advance :)

You can try to create a named range in the Excel sheet and then import only the range.

HTH.

|||Is there any other way to do it , like writing a query or so? I need help as i'm new to SSIS , any help would be appreciated

how do I determine how many columns there are in a file?

Hi,

I have a few different files in a directory.

I want to take each file and determine which columns and data types it has.

How can I know how many columns there are in a file?

Thank you.

Columns. That depends entirely on what you define a column to be. How is it delimited?

Column data-type. In a file the type of every column is text/char/call it whatever you want. A file is just a text string. You give semantic meaning to those strings (e.g. data-types) which you define yourself.

There are a million and one answers to your question given the information that you have provided. Only you can answer it.

-Jamie

|||

Jamie,

let's assume that I get a csv file from my customer.

I don't know how many columns there are in that file (and I don't want to count it by myself).

I wanted to know which component can take this file and tell me how many columns it has.

This is stage 1.

|||

The Flat File allows you to define a delimiter and therefore tell you how many columns there are.

-Jamie

|||

suppose I use a "flat file" component, and I see that there are 4 columns.

I want to use the "for each" component and apply a "data flow task" on each one of the columns.

The "for each" component will take one column at a time, and run 4 times.

I want to use the same "data flow task" for all of the columns.

How can I do it automatically?

|||

one way is to start a new project in vs as ssis

right click over solution explorer->SSIS Packages and select import and export wizard

select your flat file as source and destination as server, by this way create a new package for every text file, later you can copy and paste of each package contents into one and with little change this will be work for you and hope you get column list by looking at tables.

|||

reut wrote:

suppose I use a "flat file" component, and I see that there are 4 columns.

I want to use the "for each" component and apply a "data flow task" on each one of the columns.

The "for each" component will take one column at a time, and run 4 times.

I want to use the same "data flow task" for all of the columns.

How can I do it automatically?

Data flows operate on datasets, not on a column at a time. Even if you could do what you want to (which you can't - see below) I would recommend not doing it.

The reason that you can't apply the same data-flow to different data set (which in your case would exist of a single column) is because the metadata of the the data-flow (i.e. the columns) is set at design-time and cannot be changed when the package is executed.

-Jamie

|||

This is a good way, thanks for that, but what do I do if I have 30 different files with 100 columns in each one of them, and I don"t want to create 30 different packages?...

I want to create one "smart" packege, that will take a file, take a column, and insert the relevant data into a table on the server.

If I decide to do it with the "for each" component, it will run 30*100 times.

The resault has to be something like this:

fileName columnName dataType

xxx.csv column1 int

xxx.csv column2 date

...

xxx.csv column100 varchar

yyy.csv column1 date

...

How do I do it?

Thank you for your patience.

|||

you can create a raw table with columns from 1 to 500, then fetch every text file into it and call that data flow in your for each loop and then you better know which column tells you about specific table then put the data into that table from raw table by calling just one stored proc from your ssis, and that sp will decide data insert into that specific table based on data

|||

Your only option is to create a "Smart" custom source that is either a script source or a script component. Your custom component would take the column name, or position and output the relevant column into the data flow.

You could also unpivot the data so that your columns were now rows. You then filter the flow based on the column you want to process.