Showing posts with label code. Show all posts
Showing posts with label code. Show all posts

Tuesday, March 27, 2012

DELETE and UPDATE Trigger question

Hello
I have a Trigger on a table. Here is the code
ALTER TRIGGER [dbo].[OnOrderDelete]ON [dbo].[orders] AFTERDELETE,UPDATEAS BEGINSET NOCOUNT ON;DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;END

Actually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:
When orders table is updated, the

"SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;"

part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @.ids =(SELECT idfromUPDATED) ")

Is this right? And how can I part UPDATED and DELETED ?

Thanks
Artashes

Because an update is logically a delete and insert combined into one atomic transaction.

And just an FYI - your trigger will fail if you ever try to delete multiple rows with a single statement. You should try:

DELETE FROM files where OrderID IN (SELECT ID FROM deleted)

|||

Thanks for information!

Your way of deleting seems to be more correct!

But you know, my query works!!

Is there any explanation?

|||

Motley has given the explanation. In SQL Server there is no actually 'UPDATE' as you may expect just some modification on a row, instead an UPDATE consists an INSERT followed by a DELETE, so that's why you felt that DELETED is treated as UPDATED.

|||

Iori_Jay, about DELETE and INSERT everything is clear.

I mean why works my query

DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;

Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must write

DELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);
Confused|||

Try executing DELETE FROM files.

With your trigger, it will fail.

|||

Motley, I don't understand you!

And what am I executing now?

Or you mean only just "DELETE FROM files;" (delete all rows in files?)

|||Yes, if you used the trigger you originally had and tried to delete all the rows in files, the trigger would throw an error. Any delete that caused multiple rows to be deleted would fail.|||

I don't know about all files, but there were situations, when my query deleted 3 rows!

|||

Not within a single statement.

DELETE from files where fileid=1

DELETE from files where fileid=2

would work, but...

DELETE from files where fileid=1 or fileid=2

would fail.

|||

Thanks for info!

But there is no statement like "DELETE from files where fileid=1 or fileid=2" in my query? Am I right?

|||

artashes:

Iori_Jay, about DELETE and INSERT everything is clear.

I mean why works my query

DECLARE @.idsint;SELECT @.ids =(SELECT idfrom DELETED);DELETE FROM filesWHERE OrderId = @.ids;

Which as Motley said, should not work? (Because I try to delete multiple rows with a single statement)
As he said, I must write

DELETE FROM fileswhere OrderIDIN (SELECT IDFROM deleted);

Confused

I can't understand why such query works even when you're trying to delete multiple rows--the subquery will return multiple rows and "SELECT @.ids =(SELECT idfrom DELETED)" command will fail as it is trying to assign multiple values to a single variable.However the following query will work as it assigns the last id from deleted table to the variable:

DECLARE @.idsint;
SELECT @.ids =idfrom DELETED;
DELETE FROM filesWHERE OrderId = @.ids;

|||Smile If you wan't I can send you a project where it works, and deletes NOT only the last deleted id.

Sunday, March 25, 2012

delete a text file

I have a Job which imports from a text file. The second step is to delete th
e
text file. I have use the following code and it reports success but the file
is still there. Any ideas?
EXEC master..xp_cmdshell 'Del D:\Documents and
Settings\Administrator\Desktop\testextra
ct.txt'Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Who is SQL Server service starting as? My guess is that the account does
not have write/modify permissions on a user's folder.
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>I have a Job which imports from a text file. The second step is to delete
>the
> text file. I have use the following code and it reports success but the
> file
> is still there. Any ideas?
> EXEC master..xp_cmdshell 'Del D:\Documents and
> Settings\Administrator\Desktop\testextra
ct.txt'|||Windows logon permission. I am always in as admin
"Aaron Bertrand [SQL Server MVP]" wrote:

> Who is SQL Server service starting as? My guess is that the account does
> not have write/modify permissions on a user's folder.
>
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:08D9D12B-26A1-4F4C-889C-8F32F274E0C9@.microsoft.com...
>
>|||> Windows logon permission. I am always in as admin
In every single installation I have ever seen, *YOU* are not the user
account SQL Server is running as.
Go to the control panel on the SQL Server machine, open up the services
control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
the Log On tab. Dollars to donuts says that account is not your account.
But it is a given that *that* account needs permissions on the folder where
xp_cmdshell is attempting to play.|||You are right!! Placed the folder on another drive and it worked perfectly.
Thanks so much
"Aaron Bertrand [SQL Server MVP]" wrote:

> In every single installation I have ever seen, *YOU* are not the user
> account SQL Server is running as.
> Go to the control panel on the SQL Server machine, open up the services
> control panel applet, go to MSSQLServer (or MSSQL$<instance> ) and look at
> the Log On tab. Dollars to donuts says that account is not your account.
> But it is a given that *that* account needs permissions on the folder wher
e
> xp_cmdshell is attempting to play.
>
>

Thursday, March 22, 2012

delete 30 days from todays date?

I have a column that is to be updated to todays date - 30 days if it is older than that.

I tried this code but I get error for the conversion to datetime because it is out of range. Anyone have another solution?

update DATAFILE
set [Effective Date] = [Effective Date] - CAST('yyyy-mm-30' as datetime)
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30update DATAFILE
set [Effective Date] = [Effective Date] - 30
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30|||Ok, it was so simple?

I found this code worked also.

update DATAFILE
set [Effective Date] = DATEADD(dd, -30, GetDate())
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30

The date is supposed to be updated to todays date minus 30 days.
Its ok now. Thanks...|||Do not use addition or subtraction operations on dates. SQL Server dates or not so simple as dates in other Microsoft products where you can just add or subtract whole numbers. I have gotten spurious results this way in the past.

Use the DATEADD and DATEDIFF functions instead.

blindman

Friday, March 9, 2012

Defining Variables in Date fields within a Trigger?

Hi All,

I am creating an Insert Trigger with following example of code for you to go off(just an example)

DECLARE @.CREATIONDATE VARCHAR(12)
SET @.CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
Values ('Name', 'Code', 'Description', {d @.CREATIONDATE})

Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

When checking the Syntax for the trigger it errors saying that - 'Error Syntax near '@.CREATIONDATE'

It works fine if I just insert a static value such as
{d '2002-10-10'}. Am I able to replace the static value with a variable and if so what will my syntax be? How would it look?

Thanks
Anthonyhow about:

Insert into fintest.dbo.glf_chart_acct
(chart_name, accnbri, descr1, date)
select 'Name', 'Code', 'Description', Creation_Date
from Inserted

SQL Server will automatically convert a string to a date and you have the advantage of handeling one or more records at a time!|||DECLARE @.CREATIONDATE VARCHAR(12)
SET @.CREATIONDATE = (Select Inserted.Creation_Date from Inserted)

Insert into fintest.dbo.glf_chart_acct(fintest.dbo.chart_name, fintest.dbo.accnbri, fintest.dbo.descr1, fintest.dbo.date)
Values ('Name', 'Code', 'Description', {d @.CREATIONDATE})

Inserted.CreationDate is Varchar and the fintest.dbo.date colunm is a datetime field

How about instead of the {d @.CREATIONDATE} you either put just @.CREATIONDATE or try a CAST(@.CREATIONDATE as datetime)

defining command,commandtype and connectionstring for SELECT command is not similar to INS

i am using visual web developer 2005 and SQL 2005 with VB as the code behind

i am usingINSERT command like this

Dim test As New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()
test.InsertCommandType = SqlDataSourceCommandType.Text
test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@.roll,@.name, @.age, @.email) "

test.InsertParameters.Add("roll", TextBox1.Text)

test.InsertParameters.Add("name", TextBox2.Text)
test.InsertParameters.Add("age", TextBox3.Text)
test.InsertParameters.Add("email", TextBox4.Text)
test.Insert()

i am usingUPDATE command like this

Dim test As New SqlDataSource()
test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
test.UpdateCommandType = SqlDataSourceCommandType.Text
test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 "
test.Update()

but i have to use theSELECT command like this which is completely different from INSERT and UPDATE commands

Dim tblData As New Data.DataTable()
Dim conn As New Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)
Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)
conn.Close()

TextBox4.Text = tblData.Rows(1).Item("name").ToString()
TextBox5.Text = tblData.Rows(1).Item("age").ToString()
TextBox6.Text = tblData.Rows(1).Item("email").ToString()


for INSERT and UPDATE commands defining the command,commandtype and connectionstring is same

but for the SELECT command it is completely different. why ?

can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?

if its possible how to do ?

please help me

Hi you can use:

Insert:

Dim

conAsNew SqlConnection("server=.;database=test;uid=sa")Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="insert into t1(name,birth) values (@.n,@.b)"

cmd.Parameters.Add(

New SqlParameter("@.n", Data.SqlDbType.NVarChar)).Value =TextBox1.Text

cmd.Parameters.Add(

New SqlParameter("@.b", Data.SqlDbType.DateTime)).Value =TextBox2.Text

cmd.ExecuteNonQuery()

con.Close()

Update:

Dim conAsNew SqlConnection("server=.;database=test;uid=sa")

Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="updae t1 setname= @.n , birth= @.b)"

cmd.Parameters.Add(

New SqlParameter("@.n", Data.SqlDbType.NVarChar)).Value =TextBox1.Text

cmd.Parameters.Add(

New SqlParameter("@.b", Data.SqlDbType.DateTime)).Value =TextBox2.Text

cmd.ExecuteNonQuery()

con.Close()

Select:

Dim conAsNew SqlConnection("server=.;database=test;uid=sa")

Dim cmdAsNew SqlCommand

con.Open()

cmd.Connection =con

cmd.CommandType =Data.CommandType.Text

cmd.CommandText ="updae t1 setname= @.n , birth= @.b)"

Dim da As New Data.SqlClient.SqlDataAdapter(Command)
da.Fill(tblData)

con.Close()

|||Sorry the third query is "select .........." not "update ...."

Defining code for better performance

Hi below is a SP that I am using to build a temp table and send back
paginated results to ASP. The prcedure itself works fine and does the
job although a little slow, I am sure the is a way that I can further
define it so that it runs faster but SQL isn't my strong point and I was
wondering if someone could help out.
Thanks in advance
Peter
CREATE PROCEDURE dbo.cnms_employee_page
(
@.Page INT,
@.RecsPerPage INT,
@.pagenumbers INT = NULL OUTPUT
)
AS
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
myID INT IDENTITY,
row_id INT,
NETWORK_ID VARCHAR(3),
CORP_ID VARCHAR(10),
EMP_ID VARCHAR(10),
LEV1_ID VARCHAR(10),
LEV2_ID VARCHAR(10),
LEV3_ID VARCHAR(10),
EMP_LAST_NAME VARCHAR(25),
EMP_FIRST_NAME VARCHAR(15),
EMP_TITLE VARCHAR(30),
CUSTOMER_ID VARCHAR(20),
LOCATION_ID VARCHAR(10),
START_DATE datetime,
END_DATE datetime,
username VARCHAR(50),
action_date datetime,
user_action CHAR(1),
NETWORK_NAME VARCHAR(40),
LEV1_NAME VARCHAR(20),
LEV2_NAME VARCHAR(20),
LEV3_NAME VARCHAR(20),
CORP_NAME VARCHAR(40)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
FROM Employee INNER JOIN
Network ON Employee.NETWORK_ID = Network.NETWORK_ID INNER JOIN
Corporation ON Employee.NETWORK_ID = Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
JOIN
CORPLVL1 ON Employee.NETWORK_ID = CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
Corplvl2 ON Employee.NETWORK_ID = Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
Employee.LEV1_ID = Corplvl2.LEV1_ID AND
Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
Corplvl3 ON Employee.NETWORK_ID = Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
Employee.LEV1_ID = Corplvl3.LEV1_ID AND
Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID = Corplvl3.LEV3_ID
GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
ORDER BY Employee.row_id DESC
SELECT pagenumbers = COUNT(row_id) FROM #TempItems
-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords = (
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.myID >= @.LastRec
)
FROM #TempItems
WHERE myID > @.FirstRec AND myID < @.LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Looking through your SQL, there are a lot of joins.
It might be worth executing the SELECT statement within query analyzer &
checking out the execution plan, as this might give you some insight as to
whether there are any bottlenecks in the statement.
If this is run frequently enough, it might also be worth running the index
tuning wizard against this statement to see if it has any suggestions...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Peter
What is amount of data you are inserting into the temp table?
I'd create clustered index on myID column (very useful with range
searching )
Try to a add with recompile to stored procedure and look at query optimyzer
output whether or not there are some differences.
Try to avoid using = NULL with parameters instead use =0
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uCRzU2N4DHA.3104@.TK2MSFTNGP11.phx.gbl...
> Hi below is a SP that I am using to build a temp table and send back
> paginated results to ASP. The prcedure itself works fine and does the
> job although a little slow, I am sure the is a way that I can further
> define it so that it runs faster but SQL isn't my strong point and I was
> wondering if someone could help out.
> Thanks in advance
> Peter
>
> CREATE PROCEDURE dbo.cnms_employee_page
> (
> @.Page INT,
> @.RecsPerPage INT,
> @.pagenumbers INT = NULL OUTPUT
> )
> AS
> SET NOCOUNT ON
> --Create a temporary table
> CREATE TABLE #TempItems
> (
> myID INT IDENTITY,
> row_id INT,
> NETWORK_ID VARCHAR(3),
> CORP_ID VARCHAR(10),
> EMP_ID VARCHAR(10),
> LEV1_ID VARCHAR(10),
> LEV2_ID VARCHAR(10),
> LEV3_ID VARCHAR(10),
> EMP_LAST_NAME VARCHAR(25),
> EMP_FIRST_NAME VARCHAR(15),
> EMP_TITLE VARCHAR(30),
> CUSTOMER_ID VARCHAR(20),
> LOCATION_ID VARCHAR(10),
> START_DATE datetime,
> END_DATE datetime,
> username VARCHAR(50),
> action_date datetime,
> user_action CHAR(1),
> NETWORK_NAME VARCHAR(40),
> LEV1_NAME VARCHAR(20),
> LEV2_NAME VARCHAR(20),
> LEV3_NAME VARCHAR(20),
> CORP_NAME VARCHAR(40)
>
> )
>
> -- Insert the rows from tblItems into the temp. table
>
> INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
> LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
> LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
> NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
> SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> FROM Employee INNER JOIN
> Network ON Employee.NETWORK_ID => Network.NETWORK_ID INNER JOIN
> Corporation ON Employee.NETWORK_ID => Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
> JOIN
> CORPLVL1 ON Employee.NETWORK_ID => CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
> Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
> Corplvl2 ON Employee.NETWORK_ID => Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
> Employee.LEV1_ID = Corplvl2.LEV1_ID AND
> Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
> Corplvl3 ON Employee.NETWORK_ID => Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
> Employee.LEV1_ID = Corplvl3.LEV1_ID AND
> Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID => Corplvl3.LEV3_ID
> GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> ORDER BY Employee.row_id DESC
> SELECT pagenumbers = COUNT(row_id) FROM #TempItems
>
> -- Find out the first and last record we want
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
> SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
> -- Now, return the set of paged records, plus, an indiciation of we
> -- have more records or not!
> SELECT *,
> MoreRecords => (
> SELECT COUNT(*)
> FROM #TempItems TI
> WHERE TI.myID >= @.LastRec
> )
> FROM #TempItems
> WHERE myID > @.FirstRec AND myID < @.LastRec
>
> -- Turn NOCOUNT back OFF
> SET NOCOUNT OFF
> GO
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You might also be better simplifying the insert. You should not need to
insert all of the columns you require to be output, just the key ones
(hopefully a single column). You could then join this to your source tables,
& save a potentially huge amount of insert activity...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

Defining code for better performance

Hi below is a SP that I am using to build a temp table and send back
paginated results to ASP. The prcedure itself works fine and does the
job although a little slow, I am sure the is a way that I can further
define it so that it runs faster but SQL isn't my strong point and I was
wondering if someone could help out.
Thanks in advance
Peter
CREATE PROCEDURE dbo.cnms_employee_page
(
@.Page INT,
@.RecsPerPage INT,
@.pagenumbers INT = NULL OUTPUT
)
AS
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
myID INT IDENTITY,
row_id INT,
NETWORK_ID VARCHAR(3),
CORP_ID VARCHAR(10),
EMP_ID VARCHAR(10),
LEV1_ID VARCHAR(10),
LEV2_ID VARCHAR(10),
LEV3_ID VARCHAR(10),
EMP_LAST_NAME VARCHAR(25),
EMP_FIRST_NAME VARCHAR(15),
EMP_TITLE VARCHAR(30),
CUSTOMER_ID VARCHAR(20),
LOCATION_ID VARCHAR(10),
START_DATE datetime,
END_DATE datetime,
username VARCHAR(50),
action_date datetime,
user_action CHAR(1),
NETWORK_NAME VARCHAR(40),
LEV1_NAME VARCHAR(20),
LEV2_NAME VARCHAR(20),
LEV3_NAME VARCHAR(20),
CORP_NAME VARCHAR(40)
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
FROM Employee INNER JOIN
Network ON Employee.NETWORK_ID =
Network.NETWORK_ID INNER JOIN
Corporation ON Employee.NETWORK_ID =
Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
JOIN
CORPLVL1 ON Employee.NETWORK_ID =
CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
Corplvl2 ON Employee.NETWORK_ID =
Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
Employee.LEV1_ID = Corplvl2.LEV1_ID AND
Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
Corplvl3 ON Employee.NETWORK_ID =
Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
Employee.LEV1_ID = Corplvl3.LEV1_ID AND
Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID =
Corplvl3.LEV3_ID
GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
Employee.EMP_ID, Employee.LEV1_ID,
Employee.LEV2_ID, Employee.LEV3_ID,
Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
Employee.CUSTOMER_ID, Employee.LOCATION_ID,
Employee.START_DATE, Employee.END_DATE, Employee.username,
Employee.action_date,
Employee.user_action, Network.NETWORK_NAME,
Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
Corplvl3.LEV3_NAME
ORDER BY Employee.row_id DESC
SELECT pagenumbers = COUNT(row_id) FROM #TempItems
-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.myID >= @.LastRec
)
FROM #TempItems
WHERE myID > @.FirstRec AND myID < @.LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Looking through your SQL, there are a lot of joins.
It might be worth executing the SELECT statement within query analyzer &
checking out the execution plan, as this might give you some insight as to
whether there are any bottlenecks in the statement.
If this is run frequently enough, it might also be worth running the index
tuning wizard against this statement to see if it has any suggestions...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Peter
What is amount of data you are inserting into the temp table?
I'd create clustered index on myID column (very useful with range
searching )
Try to a add with recompile to stored procedure and look at query optimyzer
output whether or not there are some differences.
Try to avoid using = NULL with parameters instead use =0
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uCRzU2N4DHA.3104@.TK2MSFTNGP11.phx.gbl...
quote:

> Hi below is a SP that I am using to build a temp table and send back
> paginated results to ASP. The prcedure itself works fine and does the
> job although a little slow, I am sure the is a way that I can further
> define it so that it runs faster but SQL isn't my strong point and I was
> wondering if someone could help out.
> Thanks in advance
> Peter
>
> CREATE PROCEDURE dbo.cnms_employee_page
> (
> @.Page INT,
> @.RecsPerPage INT,
> @.pagenumbers INT = NULL OUTPUT
> )
> AS
> SET NOCOUNT ON
> --Create a temporary table
> CREATE TABLE #TempItems
> (
> myID INT IDENTITY,
> row_id INT,
> NETWORK_ID VARCHAR(3),
> CORP_ID VARCHAR(10),
> EMP_ID VARCHAR(10),
> LEV1_ID VARCHAR(10),
> LEV2_ID VARCHAR(10),
> LEV3_ID VARCHAR(10),
> EMP_LAST_NAME VARCHAR(25),
> EMP_FIRST_NAME VARCHAR(15),
> EMP_TITLE VARCHAR(30),
> CUSTOMER_ID VARCHAR(20),
> LOCATION_ID VARCHAR(10),
> START_DATE datetime,
> END_DATE datetime,
> username VARCHAR(50),
> action_date datetime,
> user_action CHAR(1),
> NETWORK_NAME VARCHAR(40),
> LEV1_NAME VARCHAR(20),
> LEV2_NAME VARCHAR(20),
> LEV3_NAME VARCHAR(20),
> CORP_NAME VARCHAR(40)
>
> )
>
> -- Insert the rows from tblItems into the temp. table
>
> INSERT INTO #TempItems (row_id, NETWORK_ID, CORP_ID, EMP_ID, LEV1_ID,
> LEV2_ID, LEV3_ID, EMP_LAST_NAME, EMP_FIRST_NAME, EMP_TITLE, CUSTOMER_ID,
> LOCATION_ID, START_DATE, END_DATE, username, action_date, user_action,
> NETWORK_NAME, CORP_NAME,LEV1_NAME,LEV2_NAME,LEV3_NAME)
> SELECT Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> FROM Employee INNER JOIN
> Network ON Employee.NETWORK_ID =
> Network.NETWORK_ID INNER JOIN
> Corporation ON Employee.NETWORK_ID =
> Corporation.NETWORK_ID AND Employee.CORP_ID = Corporation.CORP_ID INNER
> JOIN
> CORPLVL1 ON Employee.NETWORK_ID =
> CORPLVL1.NETWORK_ID AND Employee.CORP_ID = CORPLVL1.CORP_ID AND
> Employee.LEV1_ID = CORPLVL1.LEV1_ID INNER JOIN
> Corplvl2 ON Employee.NETWORK_ID =
> Corplvl2.NETWORK_ID AND Employee.CORP_ID = Corplvl2.CORP_ID AND
> Employee.LEV1_ID = Corplvl2.LEV1_ID AND
> Employee.LEV2_ID = Corplvl2.LEV2_ID INNER JOIN
> Corplvl3 ON Employee.NETWORK_ID =
> Corplvl3.NETWORK_ID AND Employee.CORP_ID = Corplvl3.CORP_ID AND
> Employee.LEV1_ID = Corplvl3.LEV1_ID AND
> Employee.LEV2_ID = Corplvl3.LEV2_ID AND Employee.LEV3_ID =
> Corplvl3.LEV3_ID
> GROUP BY Employee.row_id, Employee.NETWORK_ID, Employee.CORP_ID,
> Employee.EMP_ID, Employee.LEV1_ID,
> Employee.LEV2_ID, Employee.LEV3_ID,
> Employee.EMP_LAST_NAME, Employee.EMP_FIRST_NAME, Employee.EMP_TITLE,
> Employee.CUSTOMER_ID, Employee.LOCATION_ID,
> Employee.START_DATE, Employee.END_DATE, Employee.username,
> Employee.action_date,
> Employee.user_action, Network.NETWORK_NAME,
> Corporation.CORP_NAME, CORPLVL1.LEV1_NAME, Corplvl2.LEV2_NAME,
> Corplvl3.LEV3_NAME
> ORDER BY Employee.row_id DESC
> SELECT pagenumbers = COUNT(row_id) FROM #TempItems
>
> -- Find out the first and last record we want
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
> SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)
> -- Now, return the set of paged records, plus, an indiciation of we
> -- have more records or not!
> SELECT *,
> MoreRecords =
> (
> SELECT COUNT(*)
> FROM #TempItems TI
> WHERE TI.myID >= @.LastRec
> )
> FROM #TempItems
> WHERE myID > @.FirstRec AND myID < @.LastRec
>
> -- Turn NOCOUNT back OFF
> SET NOCOUNT OFF
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
|||You might also be better simplifying the insert. You should not need to
insert all of the columns you require to be output, just the key ones
(hopefully a single column). You could then join this to your source tables,
& save a potentially huge amount of insert activity...
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||Thanks for your suggestions guys, I did a test as James suggested
firstly as the select stood, I then ran another test just requesting the
row_id, I think the best thing to do would be to retrieve the just the
row_id as James suggested and then use multiple selects to return the
other values removing the joins which seemed to be causing the heavy
cost.
If you have any better approaches then please let me know
Many thanks again
Peter
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Wednesday, March 7, 2012

Defining a Select Command

I'm trying to populate a DropDownList from my SQL database. I'm using C# 2005 and when I compile my code I get an error.Compiler Error Message:CS0103: The name 'myConnection' does not exist in the current context

using

System;

using

System.Data;

using

System.Data.SqlClient;

using

System.Configuration;

using

System.Collections;

using

System.Web;

using

System.Web.Security;

using

System.Web.UI;

using

System.Web.UI.WebControls;

using

System.Web.UI.WebControls.WebParts;

using

System.Web.UI.HtmlControls;

public

partialclassDefault3 : System.Web.UI.Page

{

privatestring connectionString = WebConfigurationManager.ConnectionStrings["mewconsultingConnectionString"].ConnectionString;protectedvoid Page_Load(object sender,EventArgs e)

{

SqlCommand myCommand =newSqlCommand();

myCommand.Connection = myConnection;

myCommand.CommandText =

"SELECT * FROM tblPau";

myConnection.Open();

SqlDataReader myReader;

myReader = myCommand.ExecuteReader();

myReader.Read();

// The first row in the result set is now available.

lstPau.Items.Add(myReader[

"PauSiteName"]);

myReader.Close();

myConnection.Close();

}

}

Exactly as the error stated: in your code, you did not delcare myConnection.

You need something like this before you reference it:

SqlConnection

myConnection =newSqlConnection(connectionString);

Defining a "partial" foreign Key..how could I ?

I have a table A, which has 3 fields:

- code, subcode and description (code and subcode are PK)

and a table B which has 2 fields:

- date, code, value (date and code are PK)

So far, no problem. It comes up when I try to define code in table B as foreign key pointing to code in table A, because table A has code and subcode as its PK, not only code...but, I need only code to be in table B. How could I overcome this?

Well this is kind of design issue...

You can't do it directly.. Your table design is wrong.

Try to use the following design:

CodeMaster :: code, date, value , codedescription
Here,
Code is Primary Key

SubCodeMaster :: code, subcode, subcodedescription
Here,
Code references CodeMaster::Code
Code, Subcode is Primary Key

|||

Well, unless the code column is unique in an of itself (thus making it the PK), you can't (and you shouldn't). If it is, you could apply a unique constraint to the code column, but that would be silly.

In all likelihood, you actually need a table for a code itself that relates to both tables. This would be the proper solution, since a code is a different thing than a subcode, and any code info would have to be repeate on each row

You can enforce this with triggers too, but I wouldn't suggest that as the "best" case.

Friday, February 24, 2012

default value through Code

Hello,

I set some default values though Code for a few parameters in my report, is there any way I can relate parameters to each other such as once I change one parameter I want to pass it to the code and define default value for another parameter and refresh the screen.

Thanks,

Hi,
Cascading Parameters is the solution for you, try it out. Hope U've found the answer.

Tuesday, February 14, 2012

Default Server Name

I have MSSQLServer version 8 installed. I'm using it to code with .NET. I
somehow ended up with MSSQLServer using an incorrevt server name when it
starts up. It gives me a dropdown box to select the correct servername, but
that's annoying.
My question is how can I get it to use the correct servername as the default
when it loads?
Any help is really appreciated!!
Bob
"Bob" <whoopiea@.whoopie.com> wrote in message
news:%230uT7NxjEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I have MSSQLServer version 8 installed. I'm using it to code with .NET.
I
> somehow ended up with MSSQLServer using an incorrevt server name when it
> starts up. It gives me a dropdown box to select the correct servername,
but
> that's annoying.
Did you change the server name after SQL Server was installed?

> My question is how can I get it to use the correct servername as the
default
> when it loads?
If yes to the above question, use the following (from BOL):
-- substitute the previous name and new name of your servers as appropriate
sp_dropserver old_name
GO
sp_addserver new_name, local
GO
Steve
|||"Steve Thompson" <stevethompson@.nomail.please> wrote in message
news:uP4Nfd4jEHA.3844@.TK2MSFTNGP12.phx.gbl...
> "Bob" <whoopiea@.whoopie.com> wrote in message
> news:%230uT7NxjEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I
> but
> Did you change the server name after SQL Server was installed?
> default
> If yes to the above question, use the following (from BOL):
> -- substitute the previous name and new name of your servers as
appropriate
> sp_dropserver old_name
> GO
> sp_addserver new_name, local
> GO
>
> Steve
>
Steve:
Thanks.
I inadvertantantly installed the sample databases to the wrong place, and
could not access them (ie: Northwind, PUBS, etc...) After alot of
troubleshooting, I got them installed from "\\computername" to
"\\computername\NetSDK". Then I deleted the databases from the data file in
"\\computername". The NetSDK folder is where they are and when I select
"\\computername\NetSDK" from SQL Server Service Manager's screen's dropdown
menu, all is fine. However, "\\computername" always comes up in the
dropdown as the default.
I'm kindof new with programming with SQL Server and not sure how to execute
the commands you suggested (sp_dropserver old_name ... GO
sp_addserver new_name, local ... GO)
All I know is how to code to the database.. creating databases, connection
strings within VS.NET and SQL queries, etc. from the programming environment
(I'm used to DB2, Access and some others).
Any additional insight is appreciated.
Thanks again,
Bob
|||Bob,
Sorry, I misunderstood your original question, it sounded as though you had
renamed your server after the SQL Server install. Since that is not the
case -- disregard my advice.
The dropdown default is something likely stored in your computers
registry...
Steve
"Bob" <whoopiea@.whoopie.com> wrote in message
news:eMn0Pa$jEHA.2680@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> "Steve Thompson" <stevethompson@.nomail.please> wrote in message
> news:uP4Nfd4jEHA.3844@.TK2MSFTNGP12.phx.gbl...
..NET.[vbcol=seagreen]
it[vbcol=seagreen]
servername,
> appropriate
> Steve:
> Thanks.
> I inadvertantantly installed the sample databases to the wrong place, and
> could not access them (ie: Northwind, PUBS, etc...) After alot of
> troubleshooting, I got them installed from "\\computername" to
> "\\computername\NetSDK". Then I deleted the databases from the data file
in
> "\\computername". The NetSDK folder is where they are and when I select
> "\\computername\NetSDK" from SQL Server Service Manager's screen's
dropdown
> menu, all is fine. However, "\\computername" always comes up in the
> dropdown as the default.
> I'm kindof new with programming with SQL Server and not sure how to
execute
> the commands you suggested (sp_dropserver old_name ... GO
> sp_addserver new_name, local ... GO)
> All I know is how to code to the database.. creating databases, connection
> strings within VS.NET and SQL queries, etc. from the programming
environment
> (I'm used to DB2, Access and some others).
> Any additional insight is appreciated.
> Thanks again,
> Bob
>