How To Make A Certain Log During SP Execution?
Sep 28, 2001hi all,
Now i want to log some information (e.g.time,count...)during SP execution,how can i do it in Sql?
Thanks
ADVERTISEMENT
Static Variables In A SQLCLR Stored Proc Seem To Get Reused From Execution To Execution Of The Sp
Aug 23, 2007after moving off VS debugger and into management studio to exercise our SQLCLR sp, we notice that the 2nd execution gets an error suggesting that our static SqlCommand object is getting reused from the 1st execution (of the sp under mgt studio). If this is expected behavior, we have no problem limiting our statics to only completely reusable objects but would first like to know if this is expected? Is the fact that debugger doesnt show this behavior also expected?
View 4 Replies View RelatedExecution Plans &<&> Proportionate Execution Times
Dec 7, 2005Hi I am slowly getting to grips with SQL Server. As a part of this, I have been attempting to work on producing more efficient queries. This post is regarding what appears to be a discrepancy between the SQL Server execution plan and the actual time taken by a query to run. My brief is to produce an attendance system for an education establishment (I presume you know I'm not an A-Level student completing a project :p ). Circa 1.5m rows per annum, testing with ~3m rows currently. College_Year could strictly be inferred from the AttDateTime however it is included as a field because it a part of just about every PK this table is ever likely to be linked to. Indexes are not fully optimised yet. Table:CREATE TABLE [dbo].[AttendanceDets] ([College_Year] [smallint] NOT NULL ,[Group_Code] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Student_ID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Session_Date] [datetime] NOT NULL ,[Start_Time] [datetime] NOT NULL ,[Att_Code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [IX_AltPK_Clust_AttendanceDets] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [All] ON [dbo].[AttendanceDets]([College_Year], [Group_Code], [Student_ID], [Session_Date], [Start_Time], [Att_Code]) ON [PRIMARY]GO CREATE INDEX [IX_AttendanceDets] ON [dbo].[AttendanceDets]([Att_Code]) ON [PRIMARY]GOALL inserts are via an overnight sproc - data comes from a third party system. Group_Code is 12 chars (no more no less), student_ID 8 chars (no more no less). I have created a simple sproc. I am using this as a benchmark against which I am testing my options. I appreciate that this sproc is an inefficient jack of all trades - it has been designed as such so I can compare its performance to more specific sprocs and possibly some dynamic SQL. Sproc:CREATE PROCEDURE [dbo].[CAMsp_Att] @College_Year AS SmallInt,@Student_ID AS VarChar(8) = '________', @Group_Code AS VarChar(12) = '____________', @Start_Date AS DateTime = '1950/01/01', @End_Date as DateTime = '2020/01/01', @Att_Code AS VarChar(1) = '_' AS IF @Start_Date = '1950/01/01'SET @Start_Date = CAST(CAST(@College_Year AS Char(4)) + '/08/31' AS DateTime) IF @End_Date = '2020/01/01'SET @End_Date = CAST(CAST(@College_Year +1 AS Char(4)) + '/07/31' AS DateTime) SELECT College_Year, Group_Code, Student_ID, Session_Date, Start_Time, Att_Code FROM dbo.AttendanceDets WHERE College_Year = @College_YearAND Group_Code LIKE @Group_CodeAND Student_ID LIKE @Student_IDAND Session_Date <= @End_DateAND Session_Date >=@Start_DateAND Att_Code LIKE @Att_CodeGOMy confusion lies with running the below script with Show Execution Plan:--SET SHOWPLAN_TEXT ON--Go DECLARE @Time as DateTime Set @Time = GetDate() select College_Year, group_code, Student_ID, Session_Date, Start_Time, Att_Code from attendanceDetswhere College_Year = 2005 AND group_code LIKE '____________' AND Student_ID LIKE '________'AND Session_Date <= '2005-11-16' AND Session_Date >= '2005-11-16' AND Att_Code LIKE '_' Print 'First query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds' Set @Time = GetDate() EXEC CAMsp_Att @College_Year = 2005, @Start_Date = '2005-11-16', @End_Date = '2005-11-16' Print 'Second query took: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS VarCHar(5)) + ' milli-Seconds'GO --SET SHOWPLAN_TEXT OFF--GOThe execution plan for the first query appears miles more costly than the sproc yet it is effectively the same query with no parameters. However, my understanding is the cached plan substitutes literals for parameters anyway. In any case - the first query cost is listed as 99.52% of the batch, the sproc 0.48% (comparing the IO, cpu costs etc support this). BUT the text output is:(10639 row(s) affected) First query took: 596 milli-Seconds (10639 row(s) affected) Second query took: 2856 milli-SecondsI appreciate that logical and physical performance are not one and the same but can why is there such a huge discrepancy between the two? They are tested on a dedicated test server, and repeated running and switching the order of the queries elicits the same results. Sample data can be provided if requested but I assumed it would not shed much light. BTW - I know that additional indexes can bring the plans and execution time closer together - my question is more about the concept. If you've made it this far - many thanks.If you can enlighten me - infinite thanks.
View 10 Replies View RelatedExecution Procedure Stored During Execution Of The Report .
Aug 3, 2007
Hello :
How to execute a procedure stored during execution of the report, that is before the poster the data.
Thnak you.
Execution In Scheduled Job Vs Direct Execution
Nov 29, 2004Here's my case, I have written a stored procedure which will perform the following:
1. Grab data from a table using cursor,
2. Process data,
3. Write the result into another table
If I execute the stored procedure directly (thru VS.NET, or Query Analyser), it will run, but when I tried to execute it via a scheduled job, it fails.
I used the same record, same parameters, and the same statements to call the stored procedure.
Any idea?
Actual Execution Plan Vs Estimated Execution Plan
Jul 7, 2006The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View 1 Replies View RelatedCan I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net
Mar 1, 2007Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Make C#
Jun 30, 2006Can somebody help me convert this SQL2000 Function TO C# Function pleaseCREATE Function dbo.CalculateNextRentDate ( @Rent_Payment_Date datetime, @Frequency varchar(50) , @Number int)RETURNS DATETIMEASbeginDECLARE @NextPaymentDate DatetimeSET @Number = @Number - 1.IF @Frequency = 'month' IF @rent_payment_date = dateadd(month, datediff(month, 0+@Number, @rent_payment_date) + 1, -1) BEGIN SET @NextPaymentDate = dateadd(month, datediff(month, 0, @rent_payment_date) + 2, -1) END ELSE BEGIN SET @NextPaymentDate = dateadd(month, 1+@Number, @rent_payment_date) END return @NextPaymentDateend
View 1 Replies View RelatedMake 2 Or 1
Aug 16, 2007Our company has its Departments And Services.
Now We are making it online.
Both have separate email list, phone numbers, and more.
Will I make one table and adds the field Type (Values: D or S).
Or make them separate.
Remember one thing If we merge them then Email And PhoneNumber Table will also me merge
other wise they will also separate.
What is better.
DTS How Make Our Own?
Mar 29, 2004How to make our own DTS package I have to split the data on my own.. Data is very biig almost 30 to 40 million. I need to splitt them into 10k chunks in database and with my desired table name.
Waiting for a +tive reply.
Regards
Shani ;)
How Can I Make This And Or
Feb 12, 2008
hi i have a tabel in my database i tray to generat report for this tabel
this tabel have all this fields:
company_id
emp_no
seq_no
interval_date
in_time
in_type
out_time
out_type
wage_code
status
i want all his colums can be search
but with company_id
like if he enter company_id and emp_no okay give him result if he enter Company_id and interval_date okay give him result
i write this
Code Snippet
SELECT company_id, emp_no, seq_no, interval_date, in_time, in_type, out_time, out_type, wage_code, status
FROM interval
WHERE (company_id LIKE @CompanyID) AND (emp_no LIKE @EmployeeID) OR
(company_id = @CompanyID) AND (interval_date = @IntervalDate) OR
(company_id = @CompanyID) AND (in_time = @InTime) OR
(company_id = @CompanyID) AND (in_type = @InType) OR
(company_id = @CompanyID) AND (out_time = @OutTime) OR
(company_id = @CompanyID) AND (out_type = @OutType) OR
(company_id = @CompanyID) AND (wage_code = @WageCode) OR
(company_id = @CompanyID) AND (status = @Status)
but in report preview it tell me i must enter intrevalDate ?
How Can I Make This...
Apr 5, 2006Hi have have this problem, I have a table called PABX that has all the callings registry and what I need to do is for each client(PABX.cod_client) I have 2 types of calls (VC1, VC2) , and for these types I need to select all the registries chaging the dialed number(PABX.NRTELEFONE) for the new one (TROCAR.NRTELEFONE) and for those client that doesn't need to change select the PABX.NRTELEFONE
is it possible through SQL Server 2000(via stored procedure) or I'll need to do it by my application using a vector ?
Thanks
How To Make Class
Jan 25, 2008hiiiiiiiiii I am creating a web application using vb.net in which i m using the concept of classes. now i am done all the code for inserting the values in the database using the class but it is difficult to fetch the values from the database using select command and sending them to a WebForm . i want to know how i send send the values coming from the select command to a datagrid or another web controlif possible provide me a sample code thanks for your help
View 2 Replies View RelatedHow Can I Make Checkbox
Feb 2, 2006hello all i have a 2 questions hope that u can help me my first question is: in ms access there was a data type named yes/no and it was a checkbox is there a checkbox data type in sql server 2005?
my second question is i need the connection code between asp.net 2005 and sql server 2005 i searched in here for that code but i got more confuse i found two codes and both are not working so hope u can give me the right connecting code. that's all thanks
Make SQL Available Online
Sep 28, 2005Can anyone tell me how I would go about making my SQL server accessable from the Internet, or know of any good tutorials to get me started, I haven't had much luck looking on google.
I need to access an SQL database from one server on another server for a web application.
Thanks
Need To Make This Query
Jun 28, 2004Hi guys, I have a car_race table which has these fields
car_id int
race_id int
b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 2
b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
car_id 1
race_id 4
b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?
How Do I Make It Use My Index?
Dec 15, 2004Hello!
I have two tables
users and pictures.
table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid
when I do this query:
"select userid from pictures where userid=123"
then It will do a clustered index seek
But If I do any of those:
"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"
It will do a clustered index scan.
How can I force it to seek my index instead of scan?
Thanks!
I Want To Make Project
May 26, 2006i want to make a database for pharmacy using sql server and VB.NET
so any body can give me illuminations
thanks in advance
george albert
Any Way To Make This Shorter?
Aug 1, 2006I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?SELECT DISTINCT u.userId,u.lastName,u.firstName,u.address1,u.address2,u.city,u.state,u.zip,CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0 THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))END CPGkeyFROM [...] JOIN [...]I'm hoping there is something in a way of....If userClass Between 1 and 3 ThenIF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))End IF as CPGKey,Thanks in advace.
View 1 Replies View RelatedMake A Copy Of A Db
May 29, 2007I need to restore a copy of database to a new db name
i'm trying by creating a new db and restoring by doing
RESTORE DATABASE [userr] FROM DISK = N'D:sqlbackupsuser.bak' WITH FILE = 4, NOUNLOAD, STATS = 10
GO
i'm getting an error
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'userr' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What's the best way to do this?
T-SQL (SS2K8) :: IF 0.00 Then Make It .00
Jan 28, 2015I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.
Here's the statement:
Select
CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",
WORKTYPE,
[Code]....
How To Make @str Global?
Jan 31, 2007If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.
I guess I have to execute part1 and Q2 at same time.
Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank
/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';
/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str
/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname
Make A Backup With T-sql
May 5, 2007Hello!
I would like to backup a database through t-sql into a file.
I do this :
EXEC sp_addumpdevice 'disk', 'mydb','c:mydb.bak'
it tells "Command(s) completed successfully."
but I can not find the file c:mydb.bak ...
but if I do it again I get :
"Msg 15026, Level 16, State 1, Procedure sp_addumpdevice, Line 74
Logical device 'mealmanager' already exists."
:-(
Any idea? I simply want to get a .bak file of the db, that's all.
Thanks a lot for any feedback!
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
Which Collation To Make That œ &<&> Oe
May 29, 2007hello !
Could someone tell me which collation I have to use for a text field with a unique index, but which should make the diffrence between œ and oe. (Latin1_General_CS_AS does not make a diffrence between them)
Thanks a lot for any feedback !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
How To Make Query Look Better
Aug 28, 2007select contractnbr,affcontractnbr,tdd,affrtramount,affturn,
pt=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate),ratio=datediff(d,afffirstfundingdate,affRTRBalanceZeroDate)/afftrun
from #targetfinishedaff
datediff(d,afffirstfundingdate,affRTRBalanceZeroDate) appears two times in this code, Is there a way to make it appear only once?
Thx
How Can I Use Sp_executesql Make This.
Jan 31, 2008I have this code:
USE BDPrincipal
GO
IF OBJECT_ID(N'aquery') is not null
DROP FUNCTION aquery
GO
CREATE FUNCTION aquery()
Returns nvarchar(500)
as
Begin
Declare @var nvarchar(500);
Set @var = 'Select Distinct Description from dbo.tblScanners';
Return @var
end
GO
exec sp_executesql aquery;
I created that code to prove if it works.
But the result doesn´t appear, and the message is:
Command(s) completed successfully.
I need it to work.
Because I need to create a very dinamic query.
Please help me!
How To Make This Query?
Feb 14, 2008I want to make query for average sales price, the records are like this
item, Sales_qty, UnitPrice
A, 10, 1000
A, 20, 1100
A, 5, 1500
B, 1, 1000
B, 2, 1200
and display like this
A, 35, 1128 (35 FROM total qty, 1128 from average sales price)
B, 3, 1133
Thanks in advance
Make This More Effecent?
Feb 26, 2008i was wondering if anyone could help me write all this into one or two quries...
alter table testdata add areacode varchar(3),
countystate varchar(35),
citystate varchar(30),
county_name varchar(30),
ltv float
go
UPDATE t1
SET t1.county_name=t2.county
FROM testdata t1
INNER JOIN master.dbo.zip t2
ON t1.statecode+t1.countycode=t2.fips
go
update t1 set
maritalstatus = case maritalstatus
when '1' then 'M'
when '2' then 'S' end,
homeowner = case homeowner
when '1' then 'R'
when '2' then 'T'
when '3' then '9'
when '4' then 'H' end,
areacode = substring(phone,1,3),
citystate = rtrim(city)+' '+state_abbrv,
countystate = rtrim(county_name)+' '+state_abbrv,
ltv = case when estcurhomeval >0 then cast(oo_mtg_amnt as float)/cast(estcurhomeval as float) end
from testdata t1
go
basically, i have to run this against some fairly large tables, so i want to try and get it down to one pass,
instead of three.
Thanks in advance!
How To Make A SQL Run Longer?
Jun 27, 2007Hell All,To reproduce one of our cusotmer's probem, I need to make the SQL torun for more than a minutes before it returns the result set. I do nothave large amount of data in the database to simulate the dealy.Is there a way in SQL to cause the delay while returning the resultsetThanks for the help.RegardsRaj
View 7 Replies View RelatedMake Two Query In Too One
Jul 20, 2005I have two SQL query that I would like to make in to one, if possibleHere is the first one: (Query4)SELECT rooms.DESCRIPTIO, rooms.ID, bookings1.DATEFROM bookings1 INNER JOIN rooms ON bookings1.ROOMID = rooms.IDWHERE (((bookings1.DATE) Between #4/3/2004# And #4/9/2004#));And the second one:SELECT rooms.ID, rooms.DESCRIPTIO, Query4.ROOMIDFROM rooms LEFT JOIN Query4 ON rooms.ID = Query4.ROOMIDWHERE (((Query4.ROOMID) Is Null));Is it possible to make one query of this two?With the same result.
View 3 Replies View RelatedMake Duplicate Db?
Apr 12, 2007I am wanting to be able to take my work home with me, problem is I can access the server from there.
Is it possible to work with a local instance on my laptop, then when completed transfer it to another instance on a different pc?
Meaning, talbes,queries,etc.
Thanks
Davids Learning
Does This Make Any Sense?
Mar 18, 2008I have inherited a SQL 2005 server with a few small databases on it. There's a maintenance plan here that doesn't seem to make a lot of sense to me. Can anyone comment:
Every Sunday at 4:00 AM
1. Reorganize index on All user database Tables and Views - compact large objects.
2. Rebuild index on local server connection, All user databases, Tables and view, Original amount of free space.
3. Shrink database. All user databases. Limit 100MB.
I'm confused a little about item 3. Won't a shrink be kind of useless after all of the work that goes on in steps 1 and 2. When I ran this manually, the transaction logs jumped significantly.
Thanks in advance for your help.
Dean
How Can I Make Such Query?
Jan 9, 2008I havea textbox for searching by patient name but now and though I am searching with LIKE% i can only search for exact string, for example if user typed JASSIM it will search for field full_name LIKE '%JASSIM%" ad if user typed JASSIM RAHMA it will search for full_name LIKE '%JASSIM RAHMA%' but i want to search if the field CONTAINS '%JASSIM% AND %RAHMA% when the user types JASSIM RAHAM in the textbox
View 4 Replies View Related