Grid display: columns

Showing posts with label columns. Show all posts

Thursday, March 29, 2012

Get a List of Output Columns on Script Transformation

I am using a script component to transform data. In the script component I created a bunch of fields for the output. Is there any way to loop through that list of columns? Is there code I can use in the script component to access the names, data types, data etc?

I saw a lot of informaiton on the OutputColumnCollection as part of some IDTSOuput90 thing (greek to me). As best I can guess this is for creating your own new columns, but can I see what columns are already defined via the script interface?

I was able to get a specific column by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

MsgBox("Column Count: " & o.OutputColumnCollection.Count)

MsgBox("Colum: " & o.OutputColumnCollection.FindObjectByID(139).Name)

But Is there a way to just loop through all the ids? I have to know the id to stick in findobjectbyid(x). Can i do a loop and have x = all the ids? Additionally the 88 is hard code value... is there a way to get the current or specific output set?

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postion.

|||

Ok... sorry about the multiple posts here... but I got some more logic to work...

I was able to get able to get columns by doing this...

Dim o As IDTSOutput90

o = ComponentMetaData.OutputCollection.GetObjectByID(88)

For Each col As IDTSOutputColumn90 In o.OutputColumnCollection

MsgBox("Name: " & col.Name)

So the updated question is this...

The ultimate goal is to loop through the column collection and set a column = value. Basically I have an array of values and want to loop through the columns and set it to the array postions value. I can't find how to set the column value equal to something while i loop through it. Additionally... is there a reference or good way to get the data type? When i use the col.datatype is returns a number vs description. I want to have logic when it set the value to do coverts based on the data type.

Tuesday, March 27, 2012

Get a list of columns

I'm trying to get a list of columns from a table. Here is my code:

public List<string> GetColumns(string serverName, string dataBaseName, string tableName)

{

List<string> columns = new List<string>();

Server server = new Server(serverName);

server.ConnectionContext.LoginSecure = true;

server.ConnectionContext.Connect();

Database dataBase = new Database(server, dataBaseName);

Table table = new Table(dataBase, tableName);

foreach (Column column in table.Columns)

{

columns.Add(column.Name);

}

columns.Sort();

return columns;

}

The problem is that table.Columns.Count is 0. How do I get a list of columns in a table?

I got this working. Instead of getting a reference to each object individually:

Database dataBase = new Database(server, dataBaseName);

Table table = new Table(dataBase, tableName);

Then looping through the Columns collection of the table, I combined it all in one:

foreach (Column column in server.Database[dataBaseName].Tables[tableName].Columns)

This now gets me a list of all the columns in the table.

Monday, March 26, 2012

Generic Stored Procedure

I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.

I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.

For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @.tablename, @.code, @.desc, and then create a new record in the table specified by @.tablename.

I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.

Any help would be greatly appreciated!::I would like to create three stored procedures (create, update, delete) that would perform
::the task for any of the lookup tables.

Forget it. Not a feasible way.|||Thanks for your input!

I'm currently writing separate stored procs for each of my lookup tables and will continue to do so until somebody shows me a better way.|||Skip the SP's, go with dynamic SQL and use an intelligent DAL to never maintain the SQL anymore.|||I've written generic stored procedures in the past. The trick is to use the EVAL function of SQL. As an example, here is a stored procedure that would take a table name as parameter and return all records from this table.

The stored procedure is defined as follow:
CREATE PROCEDURE SP_Query
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

You must call it as follow:
SP_Query 'Customers'

As your tables have the same columns it should be easy to built your 3 generic stored procedures in the same way as my example.

Good luck,
Olivier Giulieri
www.kakoo.net|||You can use a mixture of dynamic as static.
Have a utility the writes stored proces and either map them or construct their name.

So...
Template:
Update<table>
Gives:
UpdateMyTable1
UpdateMyTable2
...etc

Then the dynamic part is constructing the name of the stored proc...
CommandText = "Update" + strTableName

PS Don't prefix stored procs with "SP_" it's bad news.|||Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').|||I was just making a quick example of stored procedure to illustrate the use of "EVAL". For sure, I picked a very bad name. Here is my example again:

CREATE PROCEDURE GenericQuery
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

PS: Am I accused of not reading documentations because you said "Forget it. Not a feasible way." and my solution may work? Please let's just try to be constructive here.|||*If* you do need to use an "EVAL" function then consider using sp_executeSQL instead. It's geared up for param' queries.|||<Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

Check every website that has examples, I will bet 99% use sp_ . For the record I do not, but if you take samples from the net, or even Microsoft you will see they almost always use sp_ so you cannot get too uptight at people for following the convention, even if it does reduce processing efficiency.|||There is no 'good' way to do this.

If you use a new proc for each it's a lot of writing and a lot of changing if it changes. If you use dynamic stored procs you lose the security of refusing anyone direct access to your tables (dynamic stored procs require the use to have table access) and they run much more slowly.

My work around when this happens,

Write one proc per table.
Write one proc which the application calls, it then calls the correct proc. That way your application programmers only need to remember one stored procedure to call and you do the rest.

Avoid using IF statements in an SQL proc.

If you have something like this:


IF X
 Begin
 {code code code}
 End
Else
 Begin
 {code code code}
 End

When you execute only half of the proc will be in the plan and run efficiently. The other half will be adhoc.

Consider this:


If X
 Begin
 exec prCode1
 End
Else
 Begin
 exec prCode2
 End

Since each If calls a procedure that has an execution plan it runs more efficiently (AKA faster).

</code>

Friday, March 23, 2012

Generating values as part of a compound key

BEGINNER QUESTION

I have a table which has a compound primary key consisting of two columns.

One of these columns is a foreign key which is generated in another table by
an identity.

I want to be able to generate the other primary key column value
automatically when an insert occurs but assume that I cannot use an identity
because it would have to be unique for this table.

There will be potentially more than one user accessing this table so I want
to avoid generating the key on the client side.

How can I do this? Will it require some hardcore T-SQL?

I hope this is clear (I suspect it isn't) I'd be happy to supply more info.
I would be extremely grateful for any help!

Mark.Mark wrote:
> BEGINNER QUESTION
> I have a table which has a compound primary key consisting of two
> columns.
> One of these columns is a foreign key which is generated in another
> table by an identity.

But with "generated" you don't mean it's also inserted into the table with
the compound key at the same time, do you?

> I want to be able to generate the other primary key column value
> automatically when an insert occurs but assume that I cannot use an
> identity because it would have to be unique for this table.

I don't see a problem here because identity *is* unique to your compound
key table.

> There will be potentially more than one user accessing this table so
> I want to avoid generating the key on the client side.

Yes, of course.

> How can I do this? Will it require some hardcore T-SQL?
> I hope this is clear (I suspect it isn't) I'd be happy to supply more
> info. I would be extremely grateful for any help!

Not fully to be honest. Maybe you post some DDL so we can see the table
layout. Also, it's not 100% clear to me when inserts in your main table
occur.

Kind regards

robert|||I hope it may be clearer if I outline what the tables are for:

I'm basically writing an application that stores information about
'behaviour incidents' at a school. The table in question is the 'incidents'
table which is used to record information about individual incidents of
negative behaviour (ok - let's call it being naughty).

The primary key for the 'incidents' table is made up of an 'incidentID' and
'pupilID'. The pupilID indicates the pupil(s) who were involved in the
incident and is itself a foreign key into a 'pupils' table.

This is to reflect the possibility that more than one pupil can be involved
in the same incident. In this case, there may be for example three rows with
the same 'incidentID' - each having a unique pupilID to reflect one incident
in which three different pupils were involved.

My question really revolves around how to generate the 'incidentID' that is
unique at the time of insertion but allows duplicates if more than one pupil
is involved.

Can I insert the first row and retrieve the identity with a scope_identity
and then just insert the rest of the rows with the same incidentID? Wouldn't
that return an error as the identity column wouldn't contain all unique
values.

I hope this is clearer.

Thanks for your time!

Mark.

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3nj2krF1nuhtU1@.individual.net...
> Mark wrote:
>> BEGINNER QUESTION
>>
>> I have a table which has a compound primary key consisting of two
>> columns.
>>
>> One of these columns is a foreign key which is generated in another
>> table by an identity.
> But with "generated" you don't mean it's also inserted into the table with
> the compound key at the same time, do you?
>> I want to be able to generate the other primary key column value
>> automatically when an insert occurs but assume that I cannot use an
>> identity because it would have to be unique for this table.
> I don't see a problem here because identity *is* unique to your compound
> key table.
>> There will be potentially more than one user accessing this table so
>> I want to avoid generating the key on the client side.
> Yes, of course.
>> How can I do this? Will it require some hardcore T-SQL?
>>
>> I hope this is clear (I suspect it isn't) I'd be happy to supply more
>> info. I would be extremely grateful for any help!
> Not fully to be honest. Maybe you post some DDL so we can see the table
> layout. Also, it's not 100% clear to me when inserts in your main table
> occur.
> Kind regards
> robert|||Mark wrote:
> I hope it may be clearer if I outline what the tables are for:
> I'm basically writing an application that stores information about
> 'behaviour incidents' at a school. The table in question is the
> 'incidents' table which is used to record information about
> individual incidents of negative behaviour (ok - let's call it being
> naughty).
> The primary key for the 'incidents' table is made up of an
> 'incidentID' and 'pupilID'. The pupilID indicates the pupil(s) who
> were involved in the incident and is itself a foreign key into a
> 'pupils' table.
> This is to reflect the possibility that more than one pupil can be
> involved in the same incident. In this case, there may be for example
> three rows with the same 'incidentID' - each having a unique pupilID
> to reflect one incident in which three different pupils were involved.
> My question really revolves around how to generate the 'incidentID'
> that is unique at the time of insertion but allows duplicates if more
> than one pupil is involved.
> Can I insert the first row and retrieve the identity with a
> scope_identity and then just insert the rest of the rows with the
> same incidentID? Wouldn't that return an error as the identity column
> wouldn't contain all unique values.

You are right, this table layout would not work with identity. However, I
figure your table layout may not be optimal because you really have a n-m
relationship between incidents and pupils. And as far as I can see
there's no place to store information where there is just one piece per
incident (for example date and time). With all that I know ATM I would
have it as follows:

table incidents with date, time, location whatever and incidentid
(identity)
table pupils with pupilid (identity), name, day of birth - whatever
table pupils_in_incidend with incidentid, pupilid (both foreign keys)

This seems the most normalized approach here.

Kind regards

robert|||> You are right, this table layout would not work with identity. However, I
> figure your table layout may not be optimal because you really have a n-m
> relationship between incidents and pupils. And as far as I can see
> there's no place to store information where there is just one piece per
> incident (for example date and time). With all that I know ATM I would
> have it as follows:
> table incidents with date, time, location whatever and incidentid
> (identity)
> table pupils with pupilid (identity), name, day of birth - whatever
> table pupils_in_incidend with incidentid, pupilid (both foreign keys)
> This seems the most normalized approach here.
> Kind regards
> robert

OF COURSE! I should have seen that it would be silly to duplicate all of the
incident information for every pupil involved in a given incident.

Thank you immensely for your help!

Mark.

Generating Sequence Number....

Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.

Col1
--
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.......

Col1 Col2
-- --
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1

Thanks In Advance,
Rahul JhaI thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method ;)

DECLARE @.col1 int
DECLARE @.col2 int
DECLARE @.id int
DECLARE @.i int
DECLARE @.x int
DECLARE @.myTable table (
id int identity(1,1)
, col1 int
, col2 int
)

SET NOCOUNT ON

INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(4)
INSERT INTO @.myTable(col1) VALUES(5)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(7)

SET @.i = 1
SET @.x = 1

DECLARE theIckyCursor cursor
FOR
SELECT id
, col1
FROM @.myTable
ORDER
BY col1 ASC

OPEN theIckyCursor

FETCH NEXT FROM theIckyCursor INTO @.id, @.col1

WHILE @.@.Fetch_Status <> -1 BEGIN
IF @.x <> @.col1 BEGIN
SET @.x = @.x + 1
SET @.i = 1
END

UPDATE @.myTable
SET col2 = @.i
WHERE id = @.id

SET @.i = @.i + 1
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
END

CLOSE theIckyCursor
DEALLOCATE theIckyCursor

SELECT col1
, col2
FROM @.myTable

SET NOCOUNT OFF

You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.

(Basically, what you're trying to do sounds very wrong!)|||thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.|||a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution

export your data to excel, use a macro to apply the sequence numbers

otherwise, tell us your real situation, not this fantasy of only one column|||Ok, let's go back to the real world scenario.
Can you provide proper column headings etc and describe WHY you want to add a sequence number?
If you explain your justification for this design; perhaps we can suggest a better solution :)
EDIT: Sniped!|||Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.

Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.

Thanks,
Rahul Jha|||can you use an ORDER BY to get the rows into the necessary sequence for numbering them?|||select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1|||row_number?

Can you make it more clear?

Thanks,
Rahul Jha|||row_number() (http://www.databasejournal.com/features/mssql/article.php/3572301)|||pdreyer's solution actually works. I have learned something new today :)|||IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.

I'll be obliged if any one let me know on the query that pdreyer has written.

Thanks,
Rahul Jha|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.

Thanks,
Rahul Jha|||please answer the question in post #7|||This one ought to leave a mark!SELECT 1 AS ick
INTO #junque
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7

SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junque

UPDATE #foo
SET bat = (SELECT Count(*)
FROM #foo AS baz
WHERE baz.ick = #foo.ick
AND baz.bar <= #foo.bar)

SELECT ick, bat
FROM #foo

DROP TABLE #foo
DROP TABLE #junque-PatP|||ORDER BY on COL1 can be done......... Let me know if i hv cleared ur doubt or not............|||have you seen pats post on page 1|||This one ought to leave a mark!ouch!

[applies mark remover]|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000.
True, true.
And my DB is in SS 2000.
We didn't know that.|||ORDER BY on COL1 can be doneexcellent

here is your solution:

select ( select count(*) + 1
from daTable
where Col1 < T.Col1 ) as rownum
, Col1
from daTable as T
order
by Col1

rownum Col1
1 1
1 1
1 1
1 1
5 2
5 2
7 3
7 3
7 3
7 3
7 3
12 4
13 5
14 6
14 6
16 7
here is pdreyer's ROW_NUMBER() solution for comparison --

select row_number() over
( partition by Col1
order by Col1 ) as rownum
, Col1
from daTable
order
by Col1

rownum Col1
1 1
2 1
3 1
4 1
1 2
2 2
1 3
2 3
3 3
4 3
5 3
1 4
1 5
1 6
2 6
1 7
if you remove the PARTITION BY clause, then the result is

rownum Col1
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 4
13 5
14 6
15 6
16 7|||You guys rock............

Thanks for the solution.......... Thanks patty, r937 and pdreyer......... gr8 help that was.

Thanks,
Rahul Jha|||SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junqueI had no idea you could insert identity values that way.|||Thanks patty

*laughs out loud*|||:) :) Don Laugh Georgy............... :) :)

I really learnt few things thru this thread.........

Thanks guys once again...... You all are awesome......... Nothing to fear Rahul till the time you guys are there......... :) :) :)

Thanks,
Rahul Jha|||I had no idea you could insert identity values that way.Identity function - I think it is new to 2005. The devs here love it because they love select into too.|||that's a negative poots. the identity function is in SQL 2k. I have used it before.|||My mistake. I came across it about the time we migrated so probably got my wires crossed there.|||well, be careful for what you wish for

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx|||-- Prepare sample data
CREATE TABLE #Sample
(
Col1 INT,
Col2 INT
)

INSERT #Sample
(
Col1
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 6 UNION ALL
SELECT 7

-- Check precondition
SELECT * FROM #Sample

-- Setup staging mechanism
CREATE CLUSTERED INDEX IX_Sample ON #Sample (Col1)

DECLARE @.Index INT,
@.Col INT

SELECT TOP 1 @.Index = 0,
@.Col = Col1
FROM #Sample
ORDER BY Col1

UPDATE #Sample
SET @.Index = Col2 = CASE WHEN Col1 = @.Col THEN @.Index + 1 ELSE 1 END,
@.Col = Col1

DROP INDEX #Sample.IX_Sample

-- Check postcondition
SELECT * FROM #Sample

-- Clean up
DROP TABLE #Sample

Monday, March 19, 2012

generating insert scripts

Hi,

I have a table with 25 columns and 100 rows of data. I want to generate Insert scripts with data. Sql 2005 does not provide any option out of the box to do so.

Has anyone tried this/ used any other tool for the same.

Please share the solution.

Thanks.

hi,
you can use a third party tool.. there's a lot of them.. or you just write a query something like

select 'insert into tblTable (col1, col2, col3.......)'
select 'select '''+ col1 + ''',''' + col2 + ''''.......... + ' union all' from tblTable

- clintz|||

you can use this script:

declare @.Columns varchar(5000),@.TableName varchar(50),@.Condition varchar(500), @.ColumnVal varchar(8000)
set @.TableName='table1'
set @.Condition='1=1'
set @.Columns=''
set @.ColumnVal=''
select @.Columns =@.Columns + c.Column_name + ',' ,@.ColumnVal=@.ColumnVal +
case c.data_type
when 'varchar' then '''''''+'
when 'datetime' then '''''''+ cast('
else '+ cast(' end +
c.Column_name +
case c.data_type
when 'varchar' then '+ '''''
when 'datetime' then ' as varchar(50)) + '''''
else ' as varchar(50)) +' end + ''','
from INFORMATION_SCHEMA.columns c where c.table_name=@.TableName
set @.Columns= substring(@.Columns,1,len(@.Columns)-1)
set @.ColumnVal= substring(@.ColumnVal,1,len(@.ColumnVal)-1)

exec( 'select ''insert into ' + @.TableName + '('+ @.Columns + ') values (''' + @.ColumnVal + ')'' from ' + @.TableName)

please note that all datatypes are not handled . you will have to modify the case part for other datatypes

|||

You can use one of these excellent options:

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en

|||Thanks Arnie, clintz, mobin

Monday, March 12, 2012

Generating a table from a log for past 12 months

I would like to show a datagrid where the columns are the last 12 months from whatever today is. I am drawing from what could be a rather large tagle in the DB. The Table looks like this:

LogIDLogTallyLogDateItem
1 2 3/28/03 apples
2 1 3/29/03 apples
3 2 4/01/03 oranges
4 3 4/01/03 apples
5 1 4/01/03 grapes

......

293 1 3/17/04 oranges
294 1 3/17/04 apples

And if someone wants to see a report on apples, they would see something like:

3/034/035/03..............3/04
3 3 - 3

I can select one month (this one) like this:

SELECT SUM(LogTally) AS Expr1
FROM TABLE
WHERE (MONTH(GETDATE()) = MONTH(LogDate)) AND (YEAR(GETDATE()) = YEAR(LogDate)) AND (item = 'apples')
GROUP BY item

but, I just don't know how to do it for the past 12 months. Do I play with the dates and do 12 seperate SELECTS?
THANK YOU!you could do something like:


SELECT
 SUM(LogTally),
 DATEPART(mm, LogDate)
FROM
 YourTable
WHERE
 <input criteria for last year here>
GROUP BY
 DATEPART(mm, LogDate)

which should sum the values for each month.

Cheer
Ken|||Thanks for the reply.
What I need is to have each column in my datagrid to be a different month. There will always be 12 months, but there can be any amount of items(rows)
Can I do this with what you showed me?
Thanks again

Friday, March 9, 2012

Generate SQL script and columns order

I am using Generate SQL script or scptxft tool for generartion creation
script for my database.
But unfortunally this script generates order for columns in creating
table not saticfying me. I need to have order exactly like I see in
Enterprise Manager, but the default order is other...
Can anybode give an advise how solve this problem?
Hi,
I think you can not.
Try doing a reverse engineering if you have Erwin data modeller or else you
have to arrange manually.
Thanks
Hari
SQL Server MVP
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1126006864.707536.128230@.o13g2000cwo.googlegr oups.com...
>I am using Generate SQL script or scptxft tool for generartion creation
> script for my database.
> But unfortunally this script generates order for columns in creating
> table not saticfying me. I need to have order exactly like I see in
> Enterprise Manager, but the default order is other...
> Can anybode give an advise how solve this problem?
>
|||Sergi Adamchuk wrote:
> I am using Generate SQL script or scptxft tool for generartion
> creation script for my database.
> But unfortunally this script generates order for columns in creating
> table not saticfying me. I need to have order exactly like I see in
> Enterprise Manager, but the default order is other...
> Can anybode give an advise how solve this problem?
I just did a little digging into the SQL EM Generate SQL Scripts and the
Design Table functions to see how they were querying the list of columns
in the tables for display.
It appears they are both using the sp_MShelpcolumns system procedure
with the OrderBy column specified as 'id'. With that parameter, you
should be seeing the order of the columns as they are ordered by their
ColID in syscolumns.
I tested on SQL Server 2000 SP4 (Server and Client Tools). If you are
not seeing the same result from those two functions, can you tell me:
1- Are you running SP4 on the server and/or the client
2- What do you see as the column order when you query the syscolumns
table for the table in question and order by the ColID column? Which
version of the output is correct and which is incorrect?
You can use:
Select name from syscolumns where id = object_id('<table_name>') order
by colid
David Gugick
Quest Software
www.imceda.com
www.quest.com

Wednesday, March 7, 2012

Generate Sequence Number based on other columns

Hi,
Please help me to find a solution.

My requirement is similar to this.
Example:

TABLEA(pk_id, pol_mas_id,pol_seq_id)

values are
pk_id pol_mas_id pol_seq_id
1 1
2 1
3 2
4 2
5 3
6 3
7 3

Now i need to update the column "pol_seq_id" as below.

pk_id pol_mas_id pol_seq_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 3 2
7 3 3

Currently i am using cursor and you know performance sucks.

Is there any way to increment the data in the column(pol_seq_id) based on other column (pol_mas_id).

Thanks in Advance!!

Try:

select

pk_id, pol_mas_id, row_number() over(partition by pol_mas_id order by pk_id) as pol_seq_id

from

dbo.t1

It is helpful to have an index by (pol_mas_id, pk_id)

AMB

|||Thanks a lot for the response !!!
It works for select as when i am trying to update the same i am getting this error
"Windowed functions can only appear in the SELECT or ORDER BY clauses."
so i have populated the same into #temp table and then updated with join statement.

It works
|||

Try using a CTE.

;with cte

(

select

pk_id, pol_mas_id, pol_seq_id, row_number() over(partition by pol_mas_id order by pk_id) as rn

from

dbo.t1

)

update cte

set pol_seq_id = rn;

AMB

Friday, February 24, 2012

Generate documentation of tables and columns?

Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.

yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template

Cathal|||Yes, if you enter a value in the the Description column (using Enterprise Manager), then the value becomes a property of the table or column. These descriptions are called extended properties and almost all objects in SQL Server (including the database itself) can have one or more descriptions.

In SQL Server Books 2000 Books Online, see the topic "Using Extended Properties on Database Objects" for more information. There are also 3rd party tools that can be used to document database objects as well.

Regards,|||You can also try using SqlSpec - it will generate data dictionaries against any sql 2000 or 2005 database. www.elsasoft.org for more info.|||

Here i can execute document on my local sever but when i tried to execute network servers from my work station It is throwing below error

8/28/2006 2:30:17 PM

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy

everthing you see here and send it to sqlspec@.elsasoft.org. If

you can, also attach a SQL script to generate the database that

reproduces this bug, it will help us reproduce the issue. Thanks!

SqlSpec v2.0.19

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at n.b(String A_0)

at n.f()

at p.d()

when i connected network server and running this command local to that server that time it is asking me to install htmlhelpworking file and I installed still it is asking same .how can i do this what things i need to keep in mind to successful execution.

|||

I think your connection string is wrong:

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

should be:

Executing query to: server = AHADMD01; database = AHAWEEKLY ; Trusted_Connection = yes ;

that is, get rid of the parens.

|||

It is working great and thankyou ,

again i have annother quastion how can i change its path because it is default creating document in documents and settings how could i change it .

|||I think if you run it from the cmd line, you can specify where the output goes. try SqlSpec /? to see all the switches. I think the /o switch allows you to specify the output directory.|||nice it is working fine|||Hi,

I just tried using ElsaSoft SqlSpec. It worked on one local database but won't work for another one.
I hope you can help me out. Here's the error I got:

9/7/2006 2:50:48 PM
Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy
everthing you see here and send it to sqlspec@.elsasoft.org. If
you can, also attach a SQL script to generate the database that
reproduces this bug, it will help us reproduce the issue. Thanks!
SqlSpec v2.0.20
9/7/2006 2:50:48 PM

Cannot resolve collation conflict for concatenation operation.

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()
at n.b(String A_0)
at n.f()
at o.d()|||I think this is fixed in the latest version on the elsasoft.org site, although the build number is the same at 2.0.20. Can you download the version on the site and see if this still repros?|||

I think your connection string is wrong:

Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

should be:

Executing query to: server = networkservername ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

that is, get rid of the parens.

when you are calling net work srever from local workstation should use string without paren

i hope it woks

|||Hi,

I was actually trying this on a database located on my PC that's why I kept the (local). I also tried connecting to a database on a server and changed the (local) to the server name but got the same error as well. Our IT Ops guy fixed this just now by setting an login/password to the database and executing the line from the command prompt, adding the parameters for login and password. Not sure if this will work for others.

Thanks!|||May i know the string which you are calling from command line|||You might want to consider SchemaToDoc (http://www.schematodoc.com). It creates Word or HTML output that documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, and stored procedures. It also has an interface that lets you annotate your tables and fields; you can include these extended property descriptions in your output documents.

Generate documentation of tables and columns?

Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template