produce SQL statement for a database

hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)(for SQL Server 2000...and maybe 7.0)

In the SQL Query Analyzer Object Browser, drill down to your table, right-click on the table name and select "Script Object...As" and it will generate the SQL for you.

Additionally, in Enterprise Manager, drill down to your "Tables" and select the table you want to query. Right-click on the table, Select Open Table --> Query. You SQL statement is generated dynamically as you select fields. A Select statement is the default. In the gray area where you see the table and all of it's fields, Right-click and there is a selection for "Change Type." In there you can change the query to an Insert, Create, etc...|||I prefer to build my own..

USE NorthWind

DECLARE @.TBName sysname, @.SQL varchar(8000)

SELECT @.TBName = 'Cust', @.SQL = ''

SELECT @.SQL = @.SQL + RTRIM(SQL) FROM (
--SELECT SQL FROM (
SELECT RTRIM(' SELECT ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(', ' + RTRIM(COLUMN_NAME)) As SQL, TABLE_NAME, 3 As SQL_Group, ORDINAL_POSITION As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION <> 1
UNION ALL
SELECT RTRIM(' FROM [' + RTRIM(TABLE_NAME) + ']') As SQL, TABLE_NAME, 4 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT RTRIM(' GO ') As SQL, TABLE_NAME, 5 As SQL_Group, 1 As Row_Order
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @.TBName+'%'
AND ORDINAL_POSITION = 1
) AS XXX
Order By TABLE_NAME, SQL_Group, Row_Order

SELECT @.SQL

--EXEC(@.SQL)

Processor

How should I know if I need to add new processor to my Server?

During Submission of our Records every 24th day of the month the cpu usage of the server is steady 100% can you please help me what alternative can I do? or how can i check if need to add new processor.

Please help me guys.

thanks

Any sustained CPU usage exceeding 90% indicates a need for more 'processing' power.

If this ONLY occurs on the 24th day of the month, and the remainder of the time CPU utilization is lower, you have a couple of options.

Change the workload on the 24th, spread it out into several smaller 'batches'

OR

Get additional processor power (add one or more CPU(s)).

|||

You should also try to investigate what part of your workload is using the most CPU and see if you can do something about it. For example, you might be missing an index for a frequently run query that is causing more CPU pressure (among other things).

-- Get the most CPU intensive queries

SET NOCOUNT ON;

DECLARE @.SpID smallint

DECLARE spID_Cursor CURSOR

FORWARD_ONLY READ_ONLY FOR

SELECT TOP 25 spid

FROM master..sysprocesses

WHERE status = 'runnable'

AND spid > 50 -- Eliminate system SPIDs

AND spid <> 102 -- Replace with your SPID

ORDER BY CPU DESC

OPEN spID_Cursor

FETCH NEXT FROM spID_Cursor

INTO @.spID

WHILE @.@.FETCH_STATUS = 0

BEGIN

PRINT 'Spid #: ' + STR(@.spID)

EXEC ('DBCC INPUTBUFFER (' + @.spID + ')')

FETCH NEXT FROM spID_Cursor

INTO @.spID

END

-- Close and deallocate the cursor

CLOSE spID_Cursor

DEALLOCATE spID_Cursor

-- Get Top 50 executed SP's ordered by avg worker time

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age,

ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

--WHERE qt.dbid = 5 -- Filter by database

ORDER BY qs.total_worker_time/qs.execution_count DESC

-- Missing Indexes

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,

migs.*, mid.*

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid

ON mig.index_handle = mid.index_handle

--WHERE statement = '[ngservices].[dbo].[UserFeedUnreadCountRollup]' -- Specify one table

ORDER BY index_advantage DESC;

|||

Yeah, I guest we need a new server after all . . . this is really makes me difficult . . thanks for the INFO

|||

Hey, I might try to use this script . . . I'll keep you update . . . thanks a lot

Processing SCDs in bulk

I read another post. I'm hoping I'm just doing something wrong, and that the SSIS team wouldn't have done this:

I read in a batch of records that should cause changes in an SCD in some cases. The table is empty originally. In the wizard, its handling changing attibutes ,fixed and historical.

3 records in ( all with the same business key) = 3 records out?

In this case, it should only produce 1 record.

Are you kidding me? There must be a way to set the batch size to 1, right?

Are they all going down the "new" output?

I see your point. Not all of them are new are they and I assume they all valid to be in the pipeline. It'd be nice if the LOOKUP cahce (for that is what it is under the covers) could be updated as a row comes in - a dynamic cache if you will. I haven't much (any in fact) experience with the SCD component.

It could be that this is a hole in the product. Can you post a repro?

-Jamie

|||

For better or worse, this is by design. The data in the pipeline does not update the lookup table when the lookup is performed. Our data pipeline is buffered not single row so the initial row doesn't make it to the destination before the next row is compared and the SCD doesn't have a dynamic cache. If your data is like this then you would need to aggregate it for your initial insert case and then run it through again for your update cases.

Thanks,

Matt

|||thanks for responding. I found a work around by having a for each loop container that holds the dataflow component, and then passing through the set that way in some fashion, its just more complicated.|||

HI, I am very interested in this. Can you tell me what have been your solution? Something like doing inserts first (1st pass) and thn processing updates in another pass.

I need to imlement something like this in my project and it is the only way I found I could do it. If your solution is better that what I just described, can you share it please?

Thank you very much,

Ccote

|||

I put the dataflow inside a for each loop container.

In my case, I can identify ordered subsets of the original set, which I use as my pass criteria

In my case, we're processing policy transactions

e.g.

Policy# Transaction#

ABC 1

ABC 2

XYZ 1

XYZ 2

I want to process all the 1s first, then all the 2s, etc. so that if any SCD in the transaction occurs, it will be reflected.

For each transaction # , I make a pass through the dataflow, so I send through sets, and force the SCD to work. The beginning on my dataflow has a stored procedure call. I pass in the parameter of the transaction # I want it to process.

My SCD still isn't working, but its not related to the above.

hope that helps. good luck.

Processing SCDs in bulk

I read another post. I'm hoping I'm just doing something wrong, and that the SSIS team wouldn't have done this:

I read in a batch of records that should cause changes in an SCD in some cases. The table is empty originally. In the wizard, its handling changing attibutes ,fixed and historical.

3 records in ( all with the same business key) = 3 records out?

In this case, it should only produce 1 record.

Are you kidding me? There must be a way to set the batch size to 1, right?

Are they all going down the "new" output?

I see your point. Not all of them are new are they and I assume they all valid to be in the pipeline. It'd be nice if the LOOKUP cahce (for that is what it is under the covers) could be updated as a row comes in - a dynamic cache if you will. I haven't much (any in fact) experience with the SCD component.

It could be that this is a hole in the product. Can you post a repro?

-Jamie

|||

For better or worse, this is by design. The data in the pipeline does not update the lookup table when the lookup is performed. Our data pipeline is buffered not single row so the initial row doesn't make it to the destination before the next row is compared and the SCD doesn't have a dynamic cache. If your data is like this then you would need to aggregate it for your initial insert case and then run it through again for your update cases.

Thanks,

Matt

|||thanks for responding. I found a work around by having a for each loop container that holds the dataflow component, and then passing through the set that way in some fashion, its just more complicated.|||

HI, I am very interested in this. Can you tell me what have been your solution? Something like doing inserts first (1st pass) and thn processing updates in another pass.

I need to imlement something like this in my project and it is the only way I found I could do it. If your solution is better that what I just described, can you share it please?

Thank you very much,

Ccote

|||

I put the dataflow inside a for each loop container.

In my case, I can identify ordered subsets of the original set, which I use as my pass criteria

In my case, we're processing policy transactions

e.g.

Policy# Transaction#

ABC 1

ABC 2

XYZ 1

XYZ 2

I want to process all the 1s first, then all the 2s, etc. so that if any SCD in the transaction occurs, it will be reflected.

For each transaction # , I make a pass through the dataflow, so I send through sets, and force the SCD to work. The beginning on my dataflow has a stored procedure call. I pass in the parameter of the transaction # I want it to process.

My SCD still isn't working, but its not related to the above.

hope that helps. good luck.

Processing Cube hangs

Hi,

I have a problem with processing my cube. My fact table (with telephone data) contains about 400,000 records... which is increasing rapidly (400,000 records is about 8 months of data)...
I have a few dimensions:
Dimension User: about 200 records
Dimension Line: about 200 records
Dimension Direction: 4 records
Dimension Date: 365 records for each year
Dimension TimeInterval: with 24 intervals

So far so good... when I process this dimension I have no problem....
However, when I add a dimension (CalledNumber, with exactly 101 records) the processing hangs as soon as it starts...

The SQL performed when processing the cube looks like this:
[CODE]
SELECT field1, field2,... fieldn
FROM table1, table2,.... tablem
WHERE
(table1.id=table2.table1id)
AND
(table2.id=table3.table2id)
...
[/CODE]

When I execute above SQL in the Query Analyser from SQL Server Enterprise Manager, it ALSO hangs...

I am not really suprised by that, because this SQL first create a huge table of 400,000 x 200 x 200 x 4 x 365 x 24 x 101 records and after that works through the WHERE statements to filter out the appropriate records.

for me it would be more logical to use the following code to process the cube, but that cannot be changed in Analysis Manager:
[CODE]
SELECT field1, field2,... fieldn
FROM table1
LEFT JOIN table2 ON (table1.id=table2.table1id)
....
LEFT JOIN tablem ON (tablem.id = tablem-1.tablemid)
[/CODE]

When I execute above SQL in the Query Analyser from SQL Servel Enterprise Manager, it does NOT hang, but performs the query in about 35 seconds....
But Analysis Manager does not allow me to change the SQL used for processing the cube...

What can I do to add more dimensions to my cube... (It will be more anyway after adding the CalledNumber dimension)?
any suggestions?

PS. forgot to mention: I use Sql Server 2000

For people who have the same problem... on another forum I got an answer (Thanks Darren Gosbell) which gave me the solution:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agoptimizing_1qr7.asp

processing comma-delimited strings (redux)

Hello,

I have no trouble converting a comma-delimited string of values into multiple records, bur have recently encountered a problem that's giving me a headache - hopefully someone on the forum already has some experience doing this task:

create table tester (col1 int, col2 varchar(1000), col2 varchar(1000))
insert into tester values(1, '1,3,5,7', 'a,c,e,g,')
insert into tester values(2, '11,13,15,17', 'aa,ac,ae,ag,')

There is no correlation between rows, but between the 2 varchar columns is a positional relationship - in the first record, the '1' in col2 relates to the 'a' in col 3, same for the '3' and 'c', on and on. The values within each of the comma-delimited strings of the 2 columns are positionally related. Say they could be time and temperature values, with a string of time values in col1 and a string of related temps in col2. This is data from an external system that I have no control over, but must load the data into my system

I need to write a select statement that will return the contents like so:

1, 1, a
1, 3, c
1, 5, e
1, 7, g
2, 11, aa
2, 13, ac
2, 15, ae
2, 17, ag

Has anyone encountered such as this? Any clues or code snippets?

Thanks for any ideas,

saWhat split function do you use? If it uses a tally table you could return the numbers and link on those.
Not used but how about Kristen's here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2|||your "relationships" are based on offsets...so what value or meaning does that infer??

what the hell...use a cursor

actually a simple loop and udf will do

Procedure/Cursor question about returning results

I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?

To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.

DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor

WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end

Close Authors_cursor
deallocate authors_cursor

Thanks in advance

Tony Murunion

If you only need the previous value, the best thing would be to store the previous data in a local variable. I think that is a common approach.

HTH, jens Suessmeyer.

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

Thanks for the reply

The actual script I was working with used local variables to get the data I wanted. I was just stuck with getting the results back in one set instead of one for each record.

I was able to resolve my problem by dumping the data into a table in a function (decided to try that instead of a proc)

Tony

Procedure/cursor question about returning results

I'm working on a procedure that needs to cycle through the records of some
raw data and combine the the current record with the datetime field of the
prior record. I have been able to write a script to do that with cursors
and variables but my problem is it returns each record separately. How do I
go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass
through the data as a seperate query. Since I can't do a select *, what
approach should I take? If you want the actual script I have so far, I
would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony MurunionTony Murnion (remove) wrote:
> I'm working on a procedure that needs to cycle through the records of some
> raw data and combine the the current record with the datetime field of the
> prior record. I have been able to write a script to do that with cursors
> and variables but my problem is it returns each record separately. How do
I
> go about getting the procedure to return all the records as one set of dat
a?
> To see what I mean, the following script for the Pubs DB returns each pass
> through the data as a seperate query. Since I can't do a select *, what
> approach should I take? If you want the actual script I have so far, I
> would be happy to provide it.
> DECLARE authors_cursor CURSOR
> FOR SELECT * FROM authors
> OPEN authors_cursor
> FETCH NEXT FROM authors_cursor
> WHILE @.@.FETCH_STATUS = 0
> begin
> FETCH NEXT FROM authors_cursor
> end
> Close Authors_cursor
> deallocate authors_cursor
> Thanks in advance
> Tony Murunion
Your cursor wouldn't give predictable results anyway because you
haven't specified ORDER BY.
Cursors are rarely a good way to get results out of data. In this case
you can possibly use a query. To take another example from Pubs:
SELECT T1.title_id, T1.title,
T1.pubdate AS current_pubdate,
MAX(T2.pubdate) AS previous_pubdate
FROM titles AS T1
LEFT JOIN titles AS T2
ON T1.pubdate > T2.pubdate
GROUP BY T1.title, T1.title_id, T1.pubdate
ORDER BY current_pubdate, previous_pubdate ;
To do that with a cursor you could insert each row to a table variable
and then SELECT from the variable. Don't forget ORDER BY though!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply. I just started looking into the tables option. I'm
fairly new to advanced script writing. I've done a lot of select and
updates over the years but most of my data manipulation\retrieval has been
through Crystal. In this current project, I need to do the manipulaton
before the ending result.
With my cursor testing, I do have the order by clause for just the reasons
you stated. The results I'm getting are valid - I just need them to come
over as one record set. The tables option may do that for me. I was also
just looking at the user definde functions. Since this database I am reading
will be generating a few thousand records a day, what do you think will
ultimately give me the best performance? The join example you gave seems
like it would bog down with larger volumes of data.
I did not mention before but this is on sql 2000
Thanks again.
Tony
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1149798283.293874.324730@.j55g2000cwa.googlegroups.com...
> Tony Murnion (remove) wrote:
> Your cursor wouldn't give predictable results anyway because you
> haven't specified ORDER BY.
> Cursors are rarely a good way to get results out of data. In this case
> you can possibly use a query. To take another example from Pubs:
> SELECT T1.title_id, T1.title,
> T1.pubdate AS current_pubdate,
> MAX(T2.pubdate) AS previous_pubdate
> FROM titles AS T1
> LEFT JOIN titles AS T2
> ON T1.pubdate > T2.pubdate
> GROUP BY T1.title, T1.title_id, T1.pubdate
> ORDER BY current_pubdate, previous_pubdate ;
> To do that with a cursor you could insert each row to a table variable
> and then SELECT from the variable. Don't forget ORDER BY though!
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>