how to increment an integer

HI ALL
i am a newbie in SQL.tell me how to increment an integer variable, i dont want autoincrement.Where is this integer? In a table in a variable, ...?
In a table: UPDATE MYTAB SET INT1=INT1+1 WHERE <some condition>;
In a variable: v_int:=v_int+1;
In a loop: FOR i1 IN 1..n LOOP...
In a query: SELECT INT1 + 1 INTO v_int from MYTAB...;

:confused:sql

How to improve speed of query MSDE 2000

Hi All
I am having trouble with query timeouts in a VB6 program using MSDE 2000 (on
local PC)
It only happens when querying between certain dates ( surprisingly it occurs
when the dates are closer together and hence less records exists)
e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
if date range 25/8/05 to 1/9/05 it does?
code.....
sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as myprice
from [idetail] where [inv_num] in (Select inv_num from Invoice where
inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") & "' and '" &
Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code order by prod_code"
rs.Open sql, cn, adOpenKeyset, adLockReadOnly
I have no primary keys or indexes on the idetail table
Any ideas
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am having trouble with query timeouts in a VB6 program using MSDE
> 2000 (on local PC)
> It only happens when querying between certain dates ( surprisingly it
> occurs when the dates are closer together and hence less records
> exists)
> e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
> if date range 25/8/05 to 1/9/05 it does?
> code.....
> sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as
> myprice from [idetail] where [inv_num] in (Select inv_num from
> Invoice where inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") &
> "' and '" & Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code
> order by prod_code"
> rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> I have no primary keys or indexes on the idetail table
>
a proper indexing schema (as long as a proper primary key, which is always
usefull :D) should always help... but this should not be a related
problem... your actual plan should always be a table scan, followed by a
sort operation, but again, should not be related...
do you have actual lock pending on the table during the failing projections?
try executing
EXEC sp_lock
http://msdn.microsoft.com/library/de...la-lz_6cdn.asp
and
EXEC sp_who
http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
to determin eventual lock conditions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to import Data strcture from .mdf and .ldf, not Data.

Hi, All
I have database files of SQL Server 2000: .mdf and .ldf. I want to
import data structure of this database into the other Microsoft SQL SERVER.
Would you tell me the way how to do it? Thanks!
Hi,
Copy the MDF and LDF files to destination server and use the below command
in query analyzer:-
sp_attach_db
'dbname','physical_mdf_name_with_path','physical_l df_name_with_path'
Thanks
Hari
MCDBA
"Terry" <terry@.gz-hyundai-motor.com.cn> wrote in message
news:2ne0imFv5rmgU1@.uni-berlin.de...
> Hi, All
> I have database files of SQL Server 2000: .mdf and .ldf. I want to
> import data structure of this database into the other Microsoft SQL
SERVER.
> Would you tell me the way how to do it? Thanks!
>
|||Terry,
Refer to this article, it's very useful.
Microsoft Knowledge Base Article - 224071
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/?id=224071
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Terry wrote:
> Hi, All
> I have database files of SQL Server 2000: .mdf and .ldf. I want to
> import data structure of this database into the other Microsoft SQL SERVER.
> Would you tell me the way how to do it? Thanks!
>
|||If you are talking about just the structure of a database (and not structure
and data) then you could use Enterprise Manager to script the database
(right click db/ All Tasks/ Generate Script) and you could execute that
against the new server OR you could use DTS.
If you wanted both however, the aforementioned method (by Mark Allison) is
probably easiest.
Br,
Mark.
"Terry" <terry@.gz-hyundai-motor.com.cn> wrote in message
news:2ne0imFv5rmgU1@.uni-berlin.de...
> Hi, All
> I have database files of SQL Server 2000: .mdf and .ldf. I want to
> import data structure of this database into the other Microsoft SQL
SERVER.
> Would you tell me the way how to do it? Thanks!
>
|||Thank you very much!
"Mark Allison" <marka@.no.tinned.meat.mvps.org>
?:#Bs1Q2reEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Terry,
> Refer to this article, it's very useful.
> Microsoft Knowledge Base Article - 224071
> Moving SQL Server databases to a new location with Detach/Attach
> http://support.microsoft.com/?id=224071
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Terry wrote:
SERVER.[vbcol=seagreen]

How to import Data strcture from .mdf and .ldf, not Data.

Hi, All
I have database files of SQL Server 2000: .mdf and .ldf. I want to
import data structure of this database into the other Microsoft SQL SERVER.
Would you tell me the way how to do it? Thanks!Hi,
Copy the MDF and LDF files to destination server and use the below command
in query analyzer:-
sp_attach_db
'dbname','physical_mdf_name_with_path','
physical_ldf_name_with_path'
Thanks
Hari
MCDBA
"Terry" <terry@.gz-hyundai-motor.com.cn> wrote in message
news:2ne0imFv5rmgU1@.uni-berlin.de...
> Hi, All
> I have database files of SQL Server 2000: .mdf and .ldf. I want to
> import data structure of this database into the other Microsoft SQL
SERVER.
> Would you tell me the way how to do it? Thanks!
>|||Terry,
Refer to this article, it's very useful.
Microsoft Knowledge Base Article - 224071
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/?id=224071
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Terry wrote:
> Hi, All
> I have database files of SQL Server 2000: .mdf and .ldf. I want to
> import data structure of this database into the other Microsoft SQL SERVER
.
> Would you tell me the way how to do it? Thanks!
>|||If you are talking about just the structure of a database (and not structure
and data) then you could use Enterprise Manager to script the database
(right click db/ All Tasks/ Generate Script) and you could execute that
against the new server OR you could use DTS.
If you wanted both however, the aforementioned method (by Mark Allison) is
probably easiest.
Br,
Mark.
"Terry" <terry@.gz-hyundai-motor.com.cn> wrote in message
news:2ne0imFv5rmgU1@.uni-berlin.de...
> Hi, All
> I have database files of SQL Server 2000: .mdf and .ldf. I want to
> import data structure of this database into the other Microsoft SQL
SERVER.
> Would you tell me the way how to do it? Thanks!
>|||Thank you very much!
"Mark Allison" <marka@.no.tinned.meat.mvps.org>
':#Bs1Q2reEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Terry,
> Refer to this article, it's very useful.
> Microsoft Knowledge Base Article - 224071
> Moving SQL Server databases to a new location with Detach/Attach
> http://support.microsoft.com/?id=224071
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Terry wrote:
SERVER.[vbcol=seagreen]

How to import a text file with transac-sql

Hi allI am looking for examples of scripts that will help me doing these things:

- import a text file delimited with the character "*", representing a new month of data, for example data from march 2007

- create a new table with the structure of an existing one to import the data, for example Data_March_2007

- alter an existing totals table adding a new column for the new moth imported, adding a new colum for the month of March 2007.

This looks more like a job for DTS (SQL2000) or SSIS (SQL2005) than a pure TSQL job.

how to identify what consumes cpu in sql server

Hi all
I use 64 bit 2005 server with 8cpu and 8G of memory.
This server is accessed by large number of intensive or not so intensive programs.
I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good.
But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it.
If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good.
Task manager shows that all of it 75% attributed to sql server.
So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.

Thank you, Gene.

You could have other issues besides CPU that are causing CPU pressure, or it could be that your queries are very efficient, but they are being run so frequently as to cause CPU pressure. Try running these DMV queries to get a better handle on what is going on.

-- Check CPU Pressure

-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)

SELECT signal_wait_time_ms=SUM(signal_wait_time_ms)

,'%signal (cpu) waits' = CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

,resource_wait_time_ms=SUM(wait_time_ms - signal_wait_time_ms)

,'%resource waits'= CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))

FROM sys.dm_os_wait_stats

-- Check SQL Server Schedulers to see if they are waiting on CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

-- 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

-- Get Top 50 executed SP's ordered by calls/sec

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',

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

qs.total_worker_time AS 'TotalWorkerTime',

qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',

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

DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',

--qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',

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

, qt.dbid

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.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) DESC

|||

Or try the SQL 2005 Performance Dashboard, it's pretty powerful

http://www.sql-server-performance.com/bm_performance_dashboard_2005.asp

|||

Hi Glen

It's very interesting material you sent. Thank you so much. i will need time on Monday to analyze it.

Great technical stuff! Gene.