Transact-SQL (SP_Trace_Create)
Nov 9, 2007I'm trying to find sample coding were the Sp_trace_create procedure is being used.
Can anyone provide some assistance?
Thanks
Ken
ADVERTISEMENT
Problems With SP_TRACE_CREATE
May 12, 2008Hi All,
I'm trying to create a trace on my SQL 2005 Express.
I used the following code:
DECLARE @TraceID INT
DECLARE @MaxSize bigint
SET @MaxSize = 15
EXECUTE sp_trace_create @TraceID output,
@options = 2, --//rollover the file when max size is reached
@tracefile = N'C:Documents and SettingsdmanbuhnikMy DocumentsTestTrace.trc', --//trace file name
@maxfilesize = @MaxSize --//maximum file size
SELECT @TraceID --//output the traceid create for viewing
When I run it, I keep getting an error:
Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070005(error not found).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
Any idea what's wrong??
thanks a lot,
Yoav
File Not Created From Sp_trace_create
Jun 11, 2006I am getting a return code of 12 (""File not created"") from sp_trace_create. I have checked to ensure that the path is valid. I am using Windows Authentication and the Windows Administrator account. SQL Server is using the Windows userid that I created for SQL Server. I have verified (at least tried to verify) that the user for SQL Server has access to the folder containing it. The following is the relevant portion of what I am attempting. DECLARE @RC int, @TraceId intExec @RC = sp_trace_create @TraceId OUTPUT, 0, N'C:TraceFile' I have tried other paths that also do not work.
View 1 Replies View RelatedUsing Sp_trace_create (SQL Server 2005)
Nov 12, 2007
I have been given the task in using T-Sql procedures.
The objective is to create audit logs usibg the sp_trace_create procedure.
Can you provide some guidance in where can I find some sample coding using the above subject command?
Please note that I'm using SQL Server 2005
Sp_trace_create And File Exists Error
Feb 19, 2007My goal is to monitor all failed login attempts to server. I have:
DECLARE @traceidnum INT, @on BIT, @file_path NVARCHAR(50), @maxsize bigint;
SET @on = 1; SET @maxsize =5; SET @file_path = 'c: race'; --Create trace EXEC sp_trace_create @traceid = @traceidnum OUTPUT, @options = 2, @tracefile = @file_path, @maxfilesize = @maxsize, @stoptime=null, @filecount =0; --trace stop EXEC sp_trace_setstatus @traceidnum, 0 --Set events EXEC sp_trace_setevent @traceidnum, 14, 1, @on --trace start EXEC sp_trace_setstatus @traceidnum, 1
this works fine, but the problem is when sql server restarts I loose trace but trace-file remains. When I want to create new trace with the same parameters I receive Error = 0x80070050(File Exists) .
Is there any way to reuse trace-file? Or to change trace's file path to indicate file already created?RegardsKonRi
OLE DB Error: OLE DB Or ODBC Error: You Do Not Have Permission To Run 'SP_TRACE_CREATE'.; 42000
Dec 12, 2007I keep getting this error:
OLE DB error: OLE DB or ODBC error: You do not have permission to run 'SP_TRACE_CREATE'.; 42000
I do not want to add the login account to sysadmin role on my production server and giving the logon account db_reader rights to my database is not working. Is there another way around it.
Transact SQL
Jan 7, 2008I have the following SELECT statement. It returns the first record from EVENT_TEACHERS. I want the last record. 'iID' is the unique identifier so I want to select this stuff where iID is max. How can I add this to my string? I can't get the block out of my head of wanting to put the aggregate in the WHERE clause.
str = "SELECT vwStaffList4Assessment.*, EVENT_TEACHERS.iID, EVENT_TEACHERS.iAsmtID, ASMT.* FROM EVENT_TEACHERS INNER JOIN ASMT ON EVENT_TEACHERS.iAsmtID = ASMT.iAsmtID INNER JOIN vwStaffList4Assessment ON ASMT.DVN=vwStaffList4Assessment.DVN"
Thanks
Help With Transact
Feb 28, 2008I am trying to insert to our DB. Here is my insert statement.
str = "INSERT INTO SURVEY (q3) VALUES (" & q3 & ") WHERE iID="&iID
Here is my debug:
DEBUG: INSERT INTO SURVEY (q3) VALUES (1) WHERE iID=23
And here is my error:
Incorrect syntax near the keyword 'WHERE'.
Can someone please tell me what I am doing wrong? Thanks in advance.
Transact SQL
Mar 15, 2006I have the following string in Access as a field
SpecCmplDate: IIf([SaleStatus]="Spec",[Close],"")
How do I create this field in SQL Server?
Transact SQL
Jul 27, 2001I am new to SQL and need some help. Would anyone be able to tell how to write a statmement, that would bring back several columns with specific amounts of spaces in it? I don't want to change the sizes of the columns forever, just for this one query.
Thanx in advance.
sql amateur
Transact SQL
Jul 29, 1998Hello all,
I`m trying to do a "order by" on a datefield in a query (month only), and I would like my result set to be sorted started with a value that I provide.
For example, I would like the result set to be sorted begining with october:
October ...
November ...
December ...
January ..
February ...
March ...
But of course it gave me:
January ..
February ...
March ...
November ...
December ...
I attempted to do it in 2 selects with a union but it wouldn`t let me do an "order by" in both select statements with the union operator.
-skye
Vb6, Transact Sql, Asp
Sep 5, 2004CALL CENTER IT SUPPORT
I START A NEW JOB WITH A COMPANY PROVIDES IT SUPPORT TO CALL CENTERS. WE USE VB6 CONNECTING
VIA ADO TO SQL SERVER 2000 STORED PROCEDURES. OUR ONLINE REPORTING TOOL USES CRYSTAL REPORTS &
ASP (LITTLE VB6). CAN ANYONE RECOMMEND GOOD BOOKS AND WEBSITES TO PREPARE ME FOR MY NEW POSITION
WITH THIS COMPANY?
ALSO, HOW DOES ONE GO ABOUT BUYING RELEVANT CODE IN VB6, TRANSACT SQL, ASP?
WITH CALL CENTERS (OUR CLIENTS) MOVING OVERSEAS-OUTSOURCING, WHAT ABOUT ENRICHING OUR
POTENTIAL CLIENT BASE...FOR EXAMPLE, RECOMMENDING MARKETING EFFORTS TO 'NAILED DOWN' CLIENTS
LIKE PHARMACEUTICAL COMPANIES WHERE THE GOVERNMENT DEMANDS SUCH WORK BE DONE IN AMERICA. OR
EXPANDING OUR ROLE TO ALSO DO...?
ANY IDEAS TO ABOVE THREE ISSUES PLEASE-PLEASE EMAIL mikelynn@comcast.net
Transact SQL
Jan 9, 2007Hi: Can any one please tell me the difference b/w TSQL and simple SQL. Also there is a a TSQL use in my VB6 Program, how to i find that SQL in SQL Server?
cnADOSQL.Execute "DBCC CHECKIDENT ('tbl_distlist_balrange', RESEED, 0)"
Thanks.
TRANSACT-SQl
May 14, 2004Hi,
What is the sql syntaxe to specify which database can be accessed by a login.
LIKE (Transact-SQL)
May 30, 2008I am trying to use LIKE in a statement but I can't seem to pull all the rows for Age of Empire. I have at least 50 rows with the word Age of Empire but I only get two rows. What is the best way to use LIKE in the statement below so that it pulls all rows with the words Age of Empire?
Select * from dbo.proddesc
where codeabbreviation Like '%Age of empire%'
Transact SQL
Mar 12, 2006Hi Im reading about active and temporal databases. I have a quesiton though.
In which category does SQL server falls into?
Also
What is the difference between Transact SQL and TSQL2?
thanks
Transact-SQL SUM Help!
Jul 11, 2006I am trying to tally up information from two tables, but I am runningin to trouble and I can't seem to figure this out. I have aCreditsTable and a DebitsTable and I am trying to create a querry thatsums up the info from each per UserID and returns TotalCredits,TotalDebits, and a Ballance.CreditsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 192 | 2 | True || 207 | 1 | True || 207 | 1 | True || 207 | 2 | True || 212 | 3 | True |DebitsTableUserID Ammount Applied+----------+----------+----------+| 192 | 1 | True || 207 | 1 | True || 207 | 1 | True |***This is the Function I have tried, but it doesn't return the correctresultsALTER FUNCTION [dbo].[BallanceTotals]()RETURNS TABLEASRETURN(SELECT DISTINCTdbo.CreditsTable.UserID, SUM(dbo.CreditsTable.Ammount) AS TotalCredits,SUM(dbo.DebitsTable.Ammount) AS TotalDebits,SUM(dbo.CreditsTable.Ammount - dbo.DebitsTable.Ammount) AS BallanceFROMdbo.CreditsTable FULL OUTER JOINdbo.DebitsTable ON dbo.CreditsTable.UserID = dbo.DebitsTable.UserIDWHERE(dbo.CreditsTable.Applied = 1) OR (dbo.DebitsTable.Applied = 1)GROUP BYdbo.CreditsTable.UserID)*** This is what it returns, it is not adding things up correctly (itlooks like it is adding NULL values as 1 instead of 0 or something)BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 2 | 1 || 207 | 4 | 3 | 1 || 212 | 3 | | |*** This is what I want it to return!BallanceTotalsTotal TotalUserID Credits Debits Ballance+----------+----------+----------+----------+| 192 | 3 | 1 | 2 || 207 | 4 | 2 | 2 || 212 | 3 | 0 | 3 |I would really appreciate some help in getting this to work correctly!-Daniel
View 5 Replies View RelatedTransact SQL :: Add Where To CTE
Aug 10, 2015Why is my where skewing data? This 1st statement shows everything perfectly, but this go around Ineed to add one more stipulation and adding in the where returns 0 results. But if I run a simple select with the where added, I get the intended result set.
--works
with cte as(select *, row_number() over(partition by time1, time2, userID
order by userID ) as rn from timetable)
delete from cte where rn > 1
--no work
with cte as(select *, row_number() over(partition by time1, time2, userID
order by userID ) as rn from timetable where timeadded > 0)
delete from cte where rn > 1
Transact SQL :: Can't Get SUM Value
Jul 10, 2015Below is my SQL code and after that output i get. For result i would like to get SUM of column Expr1 (which is 60.668,66) so that means only 1 row not 5. I know it's not that hard but i've been searching around and trying but no luck...
SELECT INVENTTABLE_1.ITEMID AS MA, INVENTDIM_1.INVENTLOCATIONID, SUM(INVENTSUM_1.PHYSICALINVENT) AS Expr1
FROM dbo.INVENTDIM AS INVENTDIM_1 INNER JOIN
dbo.INVENTSUM AS INVENTSUM_1 ON INVENTDIM_1.INVENTDIMID = INVENTSUM_1.INVENTDIMID INNER JOIN
dbo.ECORESPRODUCTTRANSLATION AS ECORESPRODUCTTRANSLATION_1 INNER JOIN
dbo.INVENTTABLE AS INVENTTABLE_1 ON ECORESPRODUCTTRANSLATION_1.PRODUCT = INVENTTABLE_1.PRODUCT ON
[Code] ....
Transact-SQL Help
Feb 28, 2008I have 3 tables in SQL 2005. I have dumbed them down significantly, to illustrate my question.
1) Item Table - Contains a list of 2 items, "Coca-Cola" and "Pepsi-Cola".
2) Market Share - Contains the 'market share' information for the items in "Item Table". 1 to 1 relationship.
3) Retail Price - Contains the 'average retail' price data for the items in "Item Table". 1 to 1 relationship.
All three tables are linked by "iItemID".
I am trying to build a simply query that generates the following results:
----------------------------------------------------------
Item Name Market Share Retail Price
-----------------------------------------------------------
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
If, I first focus on retrieving the Market Share data, using the following query, it retrieves what I want.
SELECT tblItems.sItemName, tblMarketShare.sYTD
FROM tblItems INNER JOIN
tblMarketShare ON tblMarketShare.iItemID = tblItems.iItemID
----------------------------------
Item Name Market Share
----------------------------------
Coca-Cola mm.mm%
Pepsi-Cola mm.mm%
However, if I then add the RetailPrice price table (using the following query), I get the following incorrect results.
SELECT tblItems.sItemName, tblMarketShare.sYTD, tblRetailPrice.sYTD
FROM tblItems INNER JOIN
tblMarketShare ON tblMarketShare.iItemID = tblItems.iItemID INNER JOIN
tblRetailPrice ON tblRetailPrice.iItemID = tblItems.iItemID
----------------------------------------------------------
Item Name Market Share Retail Price
-----------------------------------------------------------
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
Where the first two values for Market Share are repeated again for row 3 and 4.
How do I syntax my SQL statement to return a single data set in the following fashion?
----------------------------------------------------------
Item Name Market Share Retail Price
-----------------------------------------------------------
Coca-Cola mm.mm% $xx.xx
Pepsi-Cola mm.mm% $xx.xx
I know this is simple for someone who write queries all the time. But this is driving me crazy. I have tried various JOINS (LEFT OUTER, RIGHT, etc.), but I am missing something.
Anyone's help greatly appreciated.
Thanks,
Vance
Transact SQL
Mar 18, 2008Is it possible to create a Server Role without have the Drop Database option
View 2 Replies View RelatedHow To Write This In Transact-SQL
Dec 23, 2004I need to return a computed value and have no idea how to do it.
I have a table of dates:
Column DataType
RowID int
Person nvarchar(20)
Percent real
Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult
John D - 2001 - .58
John D - 2002 - .52
John D - 2003 - .50
Jane D - 2001 - .58
Jane D - 2002 - .52
Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
Thanks,
Roger
Transact-SQL Programming
Aug 3, 2000I have a table containing almost 700000 records. The table contains the following fields:
table_id, member#, car_eff_dt, car_term_dt, mbr_occur.
A member can occur more than one time and his effective date/termination date are sorted sequentially.
An example would look like this:
table_id member# car_eff_dt car_term_dt mbr_occur
1 12345678901 01/01/1995 12/31/1995 null
2 12345678901 01/01/1996 12/31/1996 null
3 12345678901 01/01/1997 12/31/1997 null
4 10987654321 07/15/1998 02/16/1999 null
5 10987654321 04/01/2000 12/31/9999 null
Using Transact-SQL, is it possible to generate a unique number for each occurance of a particular member#?
table_id member# car_eff_dt car_term_dt mbr_occur
1 12345678901 01/01/1995 12/31/1995 1
2 12345678901 01/01/1996 12/31/1996 2
3 12345678901 01/01/1997 12/31/1997 3
4 10987654321 07/15/1998 02/16/1999 1
5 10987654321 04/01/2000 12/31/9999 2
Currently, I am running the code in an Access/VBA module through an ODBC connection, but I would like to be able to run all my code directly on the server.
Thanks in advance for any help you might be able to provide.
SQL 6.5 - Transact SQL Possibilities...
Aug 28, 2002Dear SQLGuru,
I came to know one of the better option in Oracle SQL for TOP-N Analysis-To find the top 5 usage accounts, top 10 users, etc. Is there any such way in SQL server ?
Is there any ways of using JOINS ( using keyword INNER & OUTER ) ? Some of the scripts that is generated automatically in Microsoft access with Join condition is not even working in MSSQL server 6.5. Is it true ? Or is there any other way that I can use these scripts in SQL 6.5.
If possible, pls. help me with some sample scripts ?
Thanks a lot in advance.
Naga
Transact.log Is Full
Sep 2, 1999Hi!
I need help!
We have 98% transact log full.
After run
DUMP TRANSACTION WITH TRUNCATE ONLY it still doesn't purge the log.
How can we resolve problem.
Thank you,
sev
Transact-SQL And XML Help Needed.
Jul 4, 2004Ok here is the situation I need a little help with.
I am passing an XML document to a stored procedure. My XML document looks like this:
<ROOT>
<customer ID="10" Address="1234 Somewhere"></customer>
<customer ID="20" Address=""></customer>
</ROOT>
Now I use sp_xml_preparedocument and OpenXML to Insert the XML ID and Address attributes into a table in my database. The table has 2 fields (ID int) and (Address varchar(100) nullable)
The result of the insertion is 2 rows in my table with the ID field containing (10 and 20) and the Address field containing (1234 Somewhere and an empty string).
My first question is, why isn't the second row Address field in my database null instead of an empty string since thats what I would like happen. Is it because the XML Address atribute = "".
If that is the case then that should bring up my second question. Since the OpenXML inserts Address="" as an empty string instead of NULL, why doesn't a regular TransactSQL INSERT statement called from ASP does the same thing?? If I lost you here then let me give a quick example.
An html web form with a text field (txtAddress). The user leaves the text box empty and submits the form. My ASP takes the value of the form, Request.Form("txtAddress") and passes it to stored proceudre through an ASP parameter object. My stored procedure simply does an INSERT into tablename (ID, Address) VALUES (@passedID, @PassedAddress). Now the outcome in the database is that the Address field is NULL and not an empty string.
That is why im confused. Why arent the OpenXML and the straight-forward insertion yield the same result (either null or empty string). It seems to me that an empty text box is an empty string and not null. ASP's ISNull(txtAddress) agrees and returns false.
Any insight ot help on this issue is greatly appreciated.
HELP! Transact SQL Cursors
Aug 19, 1999What I am trying to do is iterate through a set of records from a table, and setting one field to a common value on each record, and setting another to an incremented value. Here it is:
Table contains columns A, B, and C.
A is a unique ID.
B is an ID.
C is another value.
I want to, for all records where field B = "AAA", Set B to "AAB", and set A on each record to the next unique ID that I get from somewhere else. Here's my SQL Stored Proc:
DECLARE MYCURSOR SCROLL Cursor FOR SELECT A, B FROM MyTable where B = 1 FOR UPDATE.
Loop:
FETCH NEXT INTO @vcA, @vcB FROM MYCURSOR.
UPDATE MYTABLE SET A = @nctr, B = "AAB" WHERE CURRENT OF MYCURSOR
SELECT @nctr = @nctr + 1
If @@FETCH_STATUS = 0 goto Loop:
I hit an error as soon as I get to the Update -
"Cursorfetch: Number of variables declared in the INTO list must match that of selected columns"
Go Figure. I really need an answer on this ASAP! Help!
Transact SQL -- How Do I Case &>?
Dec 8, 2005Hello, I am having some transact SQL problems.
ALLOWED:
Column1,
CASE FOO
WHEN 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2
NOT ALLOWED:
Column1,
CASE FOO
WHEN > 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2
The problem seems to be the > symbol. Apparantly the case statement is only for equality checks, and nothing else.
So, what can I do to fix this? I have these kinds of comparisons all over.
~Le
Transact-SQL Script
Mar 21, 2007I have a table named conversion. In this table there is not a field for "first name", but only a field for "last name" and "Contact Name". The "Contact Name" holds the full name (First and Last), and the "Last Name" holds the last name.
In theory I was thinking that I could take the "Last Name" field and subtract the amount of characters + 1 additional (to account for space in between Contact Name Field - FirstName LastName) and then I would have the first name?
I can find the amount of characters in a field name by using this:
SELECT LEN(lastname_column) AS 'Length', lastname_column
FROM conv
Transact SQL Puzzle
Jan 17, 2008Here's a fun little puzzle that I thought up based on some rather gnarly problems I faced today. It might brighten up your morning. See if you can figure out what it does and how it does it.
How does it work? When will it fail as written (this example does not fail)? Why will it fail? How can you change it to increae its capacity?-- ptp 20080117 SQL puzzle
DECLARE @iINT
, @cVARCHAR(360)
SET @i = 90
SET @c = ''
WHILE 64 < @i
BEGIN
SET @c = Char(@i) + Space(@i - 64) + @c
SET @i = @i - 1
END
SELECT Replace(Replace(Replace(Replace(@c
, ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')
UNION SELECT @c-PatP
SQL-Transact Statement
May 30, 2006I need an SQL statement that will allow me to strip out duplicate records based upon a data condition.
Table
PPN_I CPN_I Qty_Required ECN_02
1. ItemA ItemB 1 4506
2. ItemA ItemB 1 3209
3. ItemA ItemB 1 901
4. ItemA ItemD 1 null
5. ItemA ItemD 0 null
6. ItemA ItemF 3 5609
7. ItemA ItemG 1 null
8. ItemB ItemA 1 1725
Okay - I have two conditions I need to enforce
1. A duplicate record is defined as a record where PPN_I and CPN_I have identical ID's. My new DB doesn't allow duplicate records. In order to 'strip' out the unwanted records - I need to enforce a couple of data conditions:
I delete both records for this case only - if qty required =1 and a duplicate record has qty required = 0 then both get deleted. In the above example - records 4 & 5 both get deleted. In all other duplicate record scenarios - I want to keep the record with the highest ECN_02 number. So, records 2 & 3 get deleted because they are duplicate (same PPN_I and CPN_I id's) and their ECN_02 is less than the highest value (4506). If no duplicate record exists - no action taken (records 6,7,8).
So, records 4 & 5 - both get deleted. Records 1,2 & 3 become one record (highest ECN_02) records 1 stays - records 2 & 3 deleted.
8 records become 4.
Any thoughts? How do I 'loop thru the table and identify duplicate records and then enforce some kind of rule?
Any assistance would be appreciated,
Thanks in advance.
Transact-SQL Tutorial
Jul 23, 2005Are there any good free online tutorials for T-SQL?
View 2 Replies View RelatedUsing The Transact-SQL Debugger
Jul 20, 2005I'm using the Transact-SQL Debugger for the first time here.I go to Object Browser, find my stored procedure, right click andchoose "Debug".The "Debug Procedure" window comes up, and I click on the Executebutton.The Debug environment comes up and automatically executes myprocedure. It doesn't allow me to single step through the code.What am I missing here?All of the "step" buttons, "restart", and "stop debugging" buttonsare dimmed out.Thanks.Ken
View 3 Replies View Related