How to Insert Summary Info in a table at midnight everyday?

I have to populate a summary table everyday.
Need to count the number of (STATUS =)'Opened', 'Testing', 'Closed' and
'Pending' from the Cases table and INSERT these values into the
'Summary' Table at 10:00 PM everyday.
How do I schedule an Auto INSERT? Any help will be greatly appriciated
You can simply schedule a job using the SQL Agent job scheduler. Look in
BooksOnLine for more details on scheduling or SQL Agent.
Andrew J. Kelly SQL MVP
"rabig" <rabig@.yahoo.com> wrote in message
news:1161137144.561928.202420@.i3g2000cwc.googlegro ups.com...
>I have to populate a summary table everyday.
> Need to count the number of (STATUS =)'Opened', 'Testing', 'Closed' and
> 'Pending' from the Cases table and INSERT these values into the
> 'Summary' Table at 10:00 PM everyday.
> How do I schedule an Auto INSERT? Any help will be greatly appriciated
>
sql

How to insert numbers from a text box to a Sql database table column’s type numeric?

Hi,

I am getting an error when I try to insert a number typed in a text box control into a Sql database table column's type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:

System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.decimal, "Measure"))

A piece of the SP inside Sql server:

USE market26
GO
ALTER PROC new_offer
@.Offer_id bigint, @.Measure numeric(6,2) = null, …

What is wrong? Why it doesn' t accept the number typed in my text box?

Thank you,
CesarThis is not correct:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

This would probably work (not tested):

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal);
mParm.Size = 13; // Max size in Bytes of the Decimal number
mParm.Precision = 8; // Total number of digits allowed (right + left of decimal point
mParm.Scale = 2; // Set the number of decimal places the Parameter value is resolved
mParm.set_IsNullable(true);
mParm.Value = decimal.Parse(myTextBox.Text.Trim());
CmdInsert.Parameters.Add(mParm)

|||Hi,

I always write my ASP.NET commands to talk with Sql Stored Procedures thus:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

I think that it is correct, and it works fine.

I have never used a text field to insert numeric data into Sql database, but, I have to define all these parameter properties in order to pass a numeric data type to the database?

Thanks|||That's interesting. There is no Constructor attributed to the SqlParameter Class which accepts a Constructor looking like this:

New SqlParameter(<string>,<SqlDbType Enum>, <string>)

as far as I know...But hey, go for it!|||Take a look at this piece of an article and let me know what do you think about it:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' Set up parameter for stored procedure
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@.CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This example makes all the parameter settings explicit. Some developers like this style, and it's good for instructional purposes. However, some developers prefer an equivalent alternative that has fewer lines of code:

Dim sConnectionString As String = _
 "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
cmdOrders.Parameters.Add(New _
 SqlParameter("@.CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@.CustomerID").Value = "ALFKI"
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

This code behaves exactly the same as the previous example. However, it only needs two lines of code for each parameter rather than six. When a stored procedure has a lot of parameters (as some of our later examples do), this can be quite a difference on lines of code required, so we'll use that form from this point onward.

The complete article is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp|||Hey! Perhaps you have misunderstood me. I meant that I always use this structure:
(Which I knew that something was wrong)

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal, "Measure"))

Instead of this:

SqlParameter mParm = new SqlParameter("@.measure", SqlDbType.Decimal)
mParm.SourceColumn = "Measure"
CmdInsert.Parameters.Add(mParm)

I knew that something was wrong, I suspected that something it lacked in my SqlParameter, and you helped me with your example!, it lacked only the size property which in my case is 5. I am sorry, the only thing that I saw strange is the structure you wrote, and the amount of parameters which I don' t need to use (in this case). I always use the shorter alternative ;-).

So, the correct SqlParameter in my case is:

CmdInsert.Parameters.Add(New SqlParameter("@.Measure", SqlDbType.Decimal,5, "Measure"))

And now works fine.
Thank you very much!
Cesar

How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans

How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans

sql

How to insert into a table with a function?

I want to add a record into a table, using a function.

This function needs to give back the number of records of a given table.

Function ImportResults

@.NumberOfRecords int

@.TableName varchar(60)

@.ImportDate datetime

@.Result bit

@.Result = 1

@.ImportDate =getdate()

--Open Table with tablenames

select TableName from Tbl_ImportTables

For each record in Tbl_ImportTables

@.TableName = Tbl_ImportTables.TableName

@.NumberOfRecords = select count(*) from @.TableName

-- add to Import Results

Insert into Tbl_Import_Results

(ImportDate

,TableName

,NumberOfRecords

)

VALUES

(

@.ImportDate

, @.TableName

, @.NumberOfRecords

)

-- Check 0 records (I only want to know if there is a table with 0 records)

IF @.NumberOfRecords=0 then @.Result=0

Next record

RETURN @.Result

End function

Can somebody help me with the right syntax (I am new to functions and sp's)?

Is this a scalar valued function?

Thanks,

Frans

DML operations (except local table variables) are not allowed in SQL Functions, you will have to use a stored procedure for that.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Yes, this is really a stored procedure.

As you are dynamically selecting the tablename you may want to look at the following topics in Books Online:

1) Cursors - to replace your For Each Loop
2) EXEC or sp_executesql - to dynamically execute a sql string built.

You may want to consider inserting the importdate and tablename first and then just updating the numberofrecords value in your cursor:

ie INSERT INTO Tbl_Import_Results (ImportDate, TableName) SELECT GETDATE(), TableName FROM tbl_ImportTables

HTH!

|||

OK,

I have started to built a basic function in which a sp is called.

ALTER FUNCTION [dbo].[fnImportResult]

RETURNS bit

AS

BEGIN

EXEC dbo.[TestInsert]

@.ControleDatum= '12-10-2007'

,@.BronDatabase = N'aaa'

,@.Tabelnaam = N'bbb'

,@.AantalRecords = 3333

RETURN 0

END

If I run this function I get the following message:

Only functions and extended stored procedures can be executed from within a function.

When only the EXEC part is run then no error message

What am I doing wrong?

Thanks again,

Frans

|||As the message indicates, you cannot call Stored Procedures from a UDF.

|||

OK, so no DML operation or calling a Stored Procedure are allowed in a user defined function. Get it now

Is it correct that a sp cannot return a result?

If so, how can I make it to work? I need to know if one of the records which has been inserted intoTbl_Import_Results has 0 records (for the right ImportDate).

Please advise how you would do this (headlines are fine).

Thanks,

Frans

|||

Stored Procedures can return a results set. They can also return values to indicate success or failure or some other integer value.

Check out OUTPUT parameters and Return Codes in Books Online.

Good Luck!

|||

Slowly I am getting there. The Fetch is working

Now syntax is the problem:

This works:

set @.NumberOfRecords = select count(*) from [1_Exact_DS].[dbo].artbst

But not this (Error converting data type varchar to bigint.)

declare @.TableName as varchar(110)

set @.TableName= '[1_Exact_DS].[dbo].artbst'

set @.NumberOfRecords = (select count(*) from @.TableName)

This is not allowed:

set @.NumberOfRecords = EXEC(select count(*) from @.TableName)

How can I take care that first the @.TableName is resolved and than the select string?

Thanks again,

Frans

|||

You need to dynamically build the sql string to use EXEC

EXEC('SELECT COUNT(*) FROM ' + @.TableName)

|||

Thanks for your quick response

This gives 'incorrect syntax' :

set @.NumberOfRecords = EXEC('select count(*) from ' + @.TableName)

The result of the EXEC should go into the variable @.NumberOfRecords. But how?

Thanks,

Frans

|||

In this case, you won't be able to use EXEC to set the result of a query to a variable. You can however use sp_executesql instead of EXEC:

Code Snippet

declare @.TableName varchar(110)
declare @.NumberOfRecords int
declare @.sql nvarchar(200)

set @.TableName = 'dbo.sysobjects'

set @.sql = 'SELECT @.out = COUNT(*) FROM ' + @.TableName

exec sp_executesql @.SQL, N'@.out INT OUTPUT', @.NumberOfRecords output

select @.NumberOfRecords

HTH!

|||

This works great!!!! Exactly what I wanted

I even start to understand the syntax now.....

Thanks a lot for your help.

Frans

how to insert characters in existing field

Hi,
I have a sql table with a field name model. In the model column, i have
model number that start with FX%.
I want to add WM before FX%. After FX can be anything, characters or
numbers. The model data type is varchar.
How can i do it. I tried using the syntax below but not successful.
update test2 set model = 'WM%' where model = 'FX%'
Can anyone help?
Tiffany,
It sounds like what you want is
update test2 set
model = 'WM' + model
where model = 'FX%'
This will paste WM on the beginning of all the FX... model names.
Steve Kass
Drew University
Tiffany wrote:

>Hi,
>I have a sql table with a field name model. In the model column, i have
>model number that start with FX%.
>I want to add WM before FX%. After FX can be anything, characters or
>numbers. The model data type is varchar.
>How can i do it. I tried using the syntax below but not successful.
>update test2 set model = 'WM%' where model = 'FX%'
>Can anyone help?
>
|||Steve!
I have a similar problem where I want to replace the domain part in a column
with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
tip you gave Tiffany but nothing happens.
Any clues?
Regards,
Bosse
"Steve Kass" wrote:

> Tiffany,
> It sounds like what you want is
> update test2 set
> model = 'WM' + model
> where model = 'FX%'
> This will paste WM on the beginning of all the FX... model names.
> Steve Kass
> Drew University
> Tiffany wrote:
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8241
On Wed, 16 Feb 2005 07:09:06 -0800, Bosse wrote:

>Steve!
>I have a similar problem where I want to replace the domain part in a column
>with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
>tip you gave Tiffany but nothing happens.
>Any clues?
>Regards,
>Bosse
Hi Bosse,
Steve's message to Tiffany was about how to put some extra characters in
front of existing string data. To replace a part of existing string data,
you use the REPLACE function instead.
UPDATE MyTable
SET Email = REPLACE (Email, '@.xxx.se', '@.yyy.se')
WHERE Email LIKE '%@.xxx.se'
Note the extra @. and .se inserted in the replace, to make sure that an
address like aaxxxt@.xxx.se is not accidentaly changed to aayyyt@.yyy.se.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

how to initialize package variables?

I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.

thanks

jpsR1 wrote:

I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.

thanks

The best way would be to do it in a script task. There's lots of material around to help you do this. Search for "ReadWriteVariables".

-Jamie

|||

Thanks Jamie. I did find some info but it helped when i searched for "ReadWriteVariables".

this is what I used. I had found a similar sample elsewhere and had tried it with no luck. The difference being, I originally had not put the prefix "User::" in front of the variable.

Phil / Mike, thanks for the help...

Now I'm actually confused (seems to happen alot since I delved into SQL 2005!). I actually placed the "User::" tag in front of my variables and it worked. Oh well, I have enough to play with for now (I especially like the functions for the read / write capabilities). I'm really just trying to start refreshing my scripting skills (or obviously lack thereof)...

You actually can put the "User::" in on the Script Task Editor and it works...

If I define the variables "User::varFTPDestPath" and "User::varFTPFileName" as ReadOnly and "User::varFTPDestPathFileName" as ReadWrite in the Script Task Editor, the following works:

Public Sub Main()

Dim strFTPDestPath, strFTPFileName As String
strFTPDestPath = Dts.Variables("User::varFTPSourcePath").Value.ToString
strFTPFileName = Dts.Variables("User::varFTPFileName").Value.ToString
Dts.Variables("User::varSourcePathFileName").Value = strFTPDestPath + strFTPFileName
MsgBox("varSourcePathFileName = " + Dts.Variables("User::varSourcePathFileName").Value.ToString)
Dts.TaskResult = Dts.Results.Success

End Sub

verbatim:

Dts.Variables("User::rcSourceFile").Value = 0

Dts.Variables("User::rcCleanInserts").Value = 0

thanks again!!!

sql

How to increment a field for a Line Number?

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

HI ,
Can you tell me clearly.please send how you tried it|||What is the relationship between the two columns ?

I don't think SQL Server 2000 will allow you to use the result of a Stored/Procedure / User Defined Function as the default value for a column.

SQL Server 2005 might.|||Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END|||

Quote:

Originally Posted by TrentC

Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END

It looks like you are trying to replicate the functionality of an identity column.

Unless the value in your line_number column is directly related to the information in that record just make your line_number column an int column with identity turned on and seed at +1.|||

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Hi,

I recently had to do the same thing, if your table has an identity column (i.e. primary key that is incremented automatically when you insert a new row), which all tables should, then you can use the following method:

First of all insert all your DocNum records into the table so that the identity column (ID say) is updated automatically. Then update the LineNum field as follows:

UPDATE
[TABLE_NAME]
SET
LineNum = (SELECT
COUNT(*)
FROM
[TABLE_NAME] t1
WHERE
t1.ID <= [TABLE_NAME].ID
AND
t1.DocNum = [TABLE_NAME].DocNum
)

How to Increase the number of Seats and Processors after Installation.

Hi,
While installing SQL Server(2000) we will be asked to give the info
regarding
the Number of Seats(per seat) and Processors(per processor). If there
are more
licenses bought after the installation how to increase these values or
configure
the server to accept more clients/users?
thanx
Hi,
We can increase the count; but we can not change directly the licensing mode
ie; from Per seat to Per processor or vice versa.
Increase the license count using "Control Panel -- SQL Serevr Licensiong
OPTIONS...
THanks
Hari
SQL Server MVP
"sasiraj" <vardhansasi@.gmail.com> wrote in message
news:1130432522.930497.324510@.g14g2000cwa.googlegr oups.com...
> Hi,
> While installing SQL Server(2000) we will be asked to give the info
> regarding
> the Number of Seats(per seat) and Processors(per processor). If there
> are more
> licenses bought after the installation how to increase these values or
> configure
> the server to accept more clients/users?
> thanx
>

How to Increase the number of Seats and Processors after Installation.

Hi,
While installing SQL Server(2000) we will be asked to give the info
regarding
the Number of Seats(per seat) and Processors(per processor). If there
are more
licenses bought after the installation how to increase these values or
configure
the server to accept more clients/users?
thanxHi,
We can increase the count; but we can not change directly the licensing mode
ie; from Per seat to Per processor or vice versa.
Increase the license count using "Control Panel -- SQL Serevr Licensiong
OPTIONS...
THanks
Hari
SQL Server MVP
"sasiraj" <vardhansasi@.gmail.com> wrote in message
news:1130432522.930497.324510@.g14g2000cwa.googlegroups.com...
> Hi,
> While installing SQL Server(2000) we will be asked to give the info
> regarding
> the Number of Seats(per seat) and Processors(per processor). If there
> are more
> licenses bought after the installation how to increase these values or
> configure
> the server to accept more clients/users?
> thanx
>

How to Increase the number of Seats and Processors after Installation.

Hi,
While installing SQL Server(2000) we will be asked to give the info
regarding
the Number of Seats(per seat) and Processors(per processor). If there
are more
licenses bought after the installation how to increase these values or
configure
the server to accept more clients/users?
thanxHi,
We can increase the count; but we can not change directly the licensing mode
ie; from Per seat to Per processor or vice versa.
Increase the license count using "Control Panel -- SQL Serevr Licensiong
OPTIONS...
THanks
Hari
SQL Server MVP
"sasiraj" <vardhansasi@.gmail.com> wrote in message
news:1130432522.930497.324510@.g14g2000cwa.googlegroups.com...
> Hi,
> While installing SQL Server(2000) we will be asked to give the info
> regarding
> the Number of Seats(per seat) and Processors(per processor). If there
> are more
> licenses bought after the installation how to increase these values or
> configure
> the server to accept more clients/users?
> thanx
>

How to increase the number of allowed queries?

Hi there,
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nico
sp_configure 'user connections', 0
go
reconfigure
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico
|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
concurrent queries, since they are basically free software. If you want to get rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico
|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:

> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>
>
|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:

> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico
|||See my other reply. You need to buy and install "a proper" SQL Server to get rid of this performance
throttling.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...[vbcol=seagreen]
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimized
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
|||ok thanks. You are right, the server is installed with the personnal edition.
We have the standard edition, I should reinstall it with the standard edition.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:

> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
> concurrent queries, since they are basically free software. If you want to get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>
|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex. Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...[vbcol=seagreen]
> ok thanks. You are right, the server is installed with the personnal edition.
> We have the standard edition, I should reinstall it with the standard edition.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
|||Thank you
"Tibor Karaszi" wrote:

> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more complex. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
>
sql

How to increase the number of allowed queries?

Hi there,
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nicosp_configure 'user connections', 0
go
reconfigure
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. The
se are constrained to 8
concurrent queries, since they are basically free software. If you want to g
et rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edi
tion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:

> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>
>|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:

> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||See my other reply. You need to buy and install "a proper" SQL Server to get
rid of this performance
throttling.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...[vbcol=seagreen]
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimiz
ed
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
>|||ok thanks. You are right, the server is installed with the personnal edition
.
We have the standard edition, I should reinstall it with the standard editio
n.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:

> Seems you are running either MSDE (Desktop Engine) or Personal Editions. T
hese are constrained to 8
> concurrent queries, since they are basically free software. If you want to
get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise E
dition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files
(safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex.
Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...[vbcol=seagreen]
> ok thanks. You are right, the server is installed with the personnal editi
on.
> We have the standard edition, I should reinstall it with the standard edit
ion.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
>|||Thank you
"Tibor Karaszi" wrote:

> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database file
s (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more comple
x. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Serv
ers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
>

How to increase the number of allowed queries?

Hi there,
our SQL server 2000 running on Windows 2000 server is optimised for 8
queries, how can I increase the number of simultaneous queries?
Thanks
Nicosp_configure 'user connections', 0
go
reconfigure
go
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
concurrent queries, since they are basically free software. If you want to get rid of the
constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||Thanks for the answer
I'll go check in the help file what it does.
"Adam Machanic" wrote:
> sp_configure 'user connections', 0
> go
> reconfigure
> go
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> > Hi there,
> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> > queries, how can I increase the number of simultaneous queries?
> >
> > Thanks
> > Nico
>
>|||Maybe I didn't write the right questions, here it is again
I get a message in the event viewer saying that the SQL server was optimized
for 8 queries and that there was 10 queries more.
How can I optimize the server for more queries?
Thanks
"Nico" wrote:
> Hi there,
> our SQL server 2000 running on Windows 2000 server is optimised for 8
> queries, how can I increase the number of simultaneous queries?
> Thanks
> Nico|||See my other reply. You need to buy and install "a proper" SQL Server to get rid of this performance
throttling.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:84290A0D-D078-4892-81F8-CB8F96E8D345@.microsoft.com...
> Maybe I didn't write the right questions, here it is again
> I get a message in the event viewer saying that the SQL server was optimized
> for 8 queries and that there was 10 queries more.
> How can I optimize the server for more queries?
> Thanks
> "Nico" wrote:
>> Hi there,
>> our SQL server 2000 running on Windows 2000 server is optimised for 8
>> queries, how can I increase the number of simultaneous queries?
>> Thanks
>> Nico|||ok thanks. You are right, the server is installed with the personnal edition.
We have the standard edition, I should reinstall it with the standard edition.
Is there anything I should do before uninstalling the personnal edition et
reinstalling with the Standard edition?
Thank you
"Tibor Karaszi" wrote:
> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to 8
> concurrent queries, since they are basically free software. If you want to get rid of the
> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> > Hi there,
> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> > queries, how can I increase the number of simultaneous queries?
> >
> > Thanks
> > Nico
>|||If you don't need to save anything in the system databases, it is easy:
Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
SQL Server. Install SE. Restore the user databases.
If you want to retain the stuff in the system databases, it is more complex. Some of below links
should help you:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
> ok thanks. You are right, the server is installed with the personnal edition.
> We have the standard edition, I should reinstall it with the standard edition.
> Is there anything I should do before uninstalling the personnal edition et
> reinstalling with the Standard edition?
> Thank you
> "Tibor Karaszi" wrote:
>> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to
>> 8
>> concurrent queries, since they are basically free software. If you want to get rid of the
>> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Nico" <Nico@.discussions.microsoft.com> wrote in message
>> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
>> > Hi there,
>> > our SQL server 2000 running on Windows 2000 server is optimised for 8
>> > queries, how can I increase the number of simultaneous queries?
>> >
>> > Thanks
>> > Nico
>>|||Thank you
"Tibor Karaszi" wrote:
> If you don't need to save anything in the system databases, it is easy:
> Backup all user databases. Stop SQL Server and also save the database files (safety measure). Remove
> SQL Server. Install SE. Restore the user databases.
> If you want to retain the stuff in the system databases, it is more complex. Some of below links
> should help you:
> Moving SQL Server Databases
> http://www.support.microsoft.com/?id=224071
> Moving Databases between Servers
> http://www.support.microsoft.com/?id=314546
> Using WITH MOVE in a Restore to a New Location with Detach/Attach
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
> http://www.support.microsoft.com/?id=240872
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> news:C3C00883-884D-498A-995B-4CC38495C880@.microsoft.com...
> > ok thanks. You are right, the server is installed with the personnal edition.
> > We have the standard edition, I should reinstall it with the standard edition.
> >
> > Is there anything I should do before uninstalling the personnal edition et
> > reinstalling with the Standard edition?
> >
> > Thank you
> >
> > "Tibor Karaszi" wrote:
> >
> >> Seems you are running either MSDE (Desktop Engine) or Personal Editions. These are constrained to
> >> 8
> >> concurrent queries, since they are basically free software. If you want to get rid of the
> >> constraint, buy either Workgroup Edition, Standard Edition or Enterprise Edition.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Nico" <Nico@.discussions.microsoft.com> wrote in message
> >> news:BA32307B-D9DD-4FD5-92BB-DEC8DA03BDB1@.microsoft.com...
> >> > Hi there,
> >> > our SQL server 2000 running on Windows 2000 server is optimised for 8
> >> > queries, how can I increase the number of simultaneous queries?
> >> >
> >> > Thanks
> >> > Nico
> >>
> >>
>

How to import a bunch of files?

I have a large number of text files that I want to import into a database. I
have created a DTS package that knows how to import and transform the data
so that it can be imported but that seems to be hard coded to a particular
file. Doing this by hand 18 thousand times is not something I want to do. Is
there a command line interface that I can call from either DOS or WSH to
import all of this data? I'm thinking that something like
for %%f in (*.txt) do <import %%f>
would be great. Anyone have a suggestion on how this could be done?
Richard Lewis Haggard
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
For SQL2005 you can use the ForEach Loop file enumerator
Looping over files with the Foreach Loop
http://www.sqlis.com/default.aspx?55
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>I have a large number of text files that I want to import into a database.
>I have created a DTS package that knows how to import and transform the
>data so that it can be imported but that seems to be hard coded to a
>particular file. Doing this by hand 18 thousand times is not something I
>want to do. Is there a command line interface that I can call from either
>DOS or WSH to import all of this data? I'm thinking that something like
> for %%f in (*.txt) do <import %%f>
> would be great. Anyone have a suggestion on how this could be done?
> --
> Richard Lewis Haggard
>
|||I ended up doing it with a dtsrun package called from a pair of batch files.
In this case, c:\StockWizPro98\OUTPUT is the destination directory that
receives periodic updates and data drops. I decided to make a temp directory
that will have a single file in it and to create a DTS package that knows
how to import data from it. The
first batch file iterates through all of the input files, copies and renames
them to what the DTS package is looking for and calls the package to import
the data. Simple.
1.bat
pushd c:\StockWizPro98\OUTPUT
for %%f in (*.txt) do call 2 %%f
popd
2.bat
pushd c:\StockWizPro98\OUTPUT\temp
copy /y ..\%1 temp.txt
dtsrun /s (local) /n StockImport /e
popd
Richard Lewis Haggard
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23aBh2ff%23FHA.740@.TK2MSFTNGP12.phx.gbl...
> Looping, Importing and Archiving
> http://www.sqldts.com/default.aspx?246
> For SQL2005 you can use the ForEach Loop file enumerator
> Looping over files with the Foreach Loop
> http://www.sqlis.com/default.aspx?55
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
> news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>

How to implement?

Hi all,

I had many groups of data which column A store number & column B store location. My syntax as below :-

select count(columnA) as no, columnB from table group by columnB

No columnB

20 A

10 B

5 C

How can I select 5 rows for Each ColumnB? (total 15 rows of record)

Thanks!

check out the docs about using TOP.|||TOP is part of it, but it can't get you what you're looking for. I've done it with a cursor before. Create a temp table or table variable. Create a cursor that is each unique value of B. Loop through the cursor inserting into the temp table the top 5 for each B. When the cursor is done, select from your temp table.|||I think that a temp table and a cursor are probably overkill in thissituation. I'd bet that a corrolated subquery would be moreefficient.
Something like this:

SELECT t1.columnA, t1.columnB
FROM table t1
WHERE t1.columnA IN (SELECT TOP 5 t2.columnA FROM table t2 WHERE t2.columnB = t1.columnB)

how to implement unique key on multiple field

hello guys,

I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?

thank you.

Sure, i you wnt to check them only per column you can put a UNIQUE Constraint on those columns, that will check like the primary key for uniqueness.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How to implement floating headers and/or columns

Hello,
I have read in a number of Powerpoint presentation from MS folks that we
have the ability to implement floating headers and/or column, similar to
freezing panes in Excel.
Could somebody please point me to the article/link that explains how to
implement this?
Thanks in advance for your help,
Ashok GThis is only available in SQL Reporting Services 2005.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/5b282ff53c37b672/a5fcf4b22a3766f7?q=fixed+header&rnum=1#a5fcf4b22a3766f7
Andy Potter|||Thanks Andy. Appreciate your response. I was also looking for floating
columns. Please do let me know if youhave further information.
--
Thank you,
Ashok G
"Potter" wrote:
> This is only available in SQL Reporting Services 2005.
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/5b282ff53c37b672/a5fcf4b22a3766f7?q=fixed+header&rnum=1#a5fcf4b22a3766f7
> Andy Potter
>|||Does this work the same when a report renders to Excel?
I am wondering if this freezes the table header in Excel? so that it prints
out the table header row in every page in Excel and the table header stays
when the user scroll down to the bottom of the report.
Thanks!
"Potter" wrote:
> This is only available in SQL Reporting Services 2005.
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/5b282ff53c37b672/a5fcf4b22a3766f7?q=fixed+header&rnum=1#a5fcf4b22a3766f7
> Andy Potter
>

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.

How to identify all parameter values selected

I have the SQL query. If the user is selecting all the vendor Numbers available in the vendor number parameter drop down then, I will not include the vendor Number condition in the where portion of the sql query. For that I want to know whether the user has selected all the values available in the drop down. How to identify this?

Have a Default Value in your DropDown, for instance: SELECT ALL with the value of "" VALUE=""

which is basically null. In your where cause use an ISNULL

For instance:

@.COMPANY_ID INT = NULL

Select ID, [NAME], COMPANY_ID FROM EMPLOYEES WHERE COMPANY_ID = ISNULL(@.COMPANY_ID, COMPANY_ID)

Hope this helps.