Transact SQL :: Find Actual Statements With Wildcards

Jun 17, 2015

STEP1:
CREATE TABLE Trace(Statement VARCHAR(MAX))
INSERT INTO Trace 
VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted  from History'),
('Insert into Account  Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')

[code]....

now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.

View 12 Replies


ADVERTISEMENT

Transact SQL :: How To Find Stored Procedures Without NOLOCK Statements In Database

Jun 15, 2015

I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.

View 8 Replies View Related

Transact SQL :: How To Concatenate Year / Month And Day Into Actual Date

Jan 10, 2012

I am working with a SQL Server database that was set up to store the year, month, and day in separate columns, rather than use a single column for the date. I plan to change this so that we store dates and times using the datetime data type. Until then, for now, I need to write transact-SQL select statements to concatenate the year, month and day to create a date that can be displayed in the results window in the format yyyy/mm/dd.

I can't seem to find any built-in function in SQL Server that will let me do this. Of course, using Excel or Access, I can use the DATE() function to reconstruct the date into yyyy/mm/dd. Is there a way to reconstruct the date into yyyy/mm/dd in SQL Server? 

View 7 Replies View Related

How Can I Get Actual Operation Cost From Actual Execution Plan?

Jul 25, 2006

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

View 2 Replies View Related

Find Out Active Sql Statements

Jul 23, 2005

Hi,Where does Sql server store the most current active T-SQLstatements? Is there a system table or stored procedure(documented orundocumented)that will show you the active sql statements beingexecuted. For eg: in oracle you can query v$sql to see all the sqlstatements executed by each session.I know that you can use Enterprise manager, but was wondering if youcan run a query via query analyzer to look at it.Any help is appreciated.ThanksGeetha

View 3 Replies View Related

Need Help With Some SQL Statements, Find Most Frequent Value In Column

Mar 11, 2008



How can I make a statement that will return the 10 most frequently occuring values in a column?

I have no idea if that is even possible, if you have an idea on how I could do that I would really appreciate it.

Im trying to make a page that would show some statistics on a table I have.

Im also trying to make something that would show the count of the number of records inserted in the last 24 hours, week, month, year etc. The table has a column called "DateInserted" as SmallDate, right now i can use a Where DateInserted > '20080310' to get the count, but its not dynamic, is there anyone to merge all these results into one row with each column being a diffrent time period?

I know this a lot of questions, but I would really appreciate any pointers.

View 4 Replies View Related

Transact SQL :: Printing Report - Capture Actual Pages In A Duplex Printing Job

Aug 10, 2015

So I have been asked by our sustainability person to create report from our printing data that actually shows the number of pieces of paper used. This is easy enough for single-sided printing, but when printing in duplex the software does not take into account that 3 printing pages actually equates to 2 pieces of paper. I know this sounds simple, but say I have a print job record that looks like this:

Submitted printed total_pages duplex
8/10/2015 8/10/2015 42 1                       

This is a print jobs that if done correctly is actually 21 pages( duplex printing). If the job is say total_pages =5 I cant just divide by 2 because its actually using 3 pieces of paper ( yes they are wanting this data don't ask why). How can I adjust some sql to accurately depict 5 pages, front and back, as 3 pieces of paper?

View 2 Replies View Related

How To Use Query Analyzer To Find The Most Efficient One In Many Select Statements?

Nov 8, 2006

Hi, everyone.

I have read a lot of topics about execution plan for query, but I got little.
Please give me some help with examples for comparing different select statements to find the best efficient select statement.

Thank you very much.

View 4 Replies View Related

Transact SQL :: How To Use More Than 10 Case Statements

May 12, 2015

I need to breakdown some information with just initials (sometimes 2, sometimes 3 if the initials are already used) SQL Server throws the error of:

Msg 125, Level 15, State 4, Line 1

Case expressions may only be nested to level 10.

What should I alter, or how should I write this query so I can use all of the needed case statements? (their are actually about 24 when statements, but this is just to get a working example to display)

Select
case
when employee_name Like 'Jorge Jones' Then 'JJ'
when employee_name Like 'Mike Mikes' Then 'MM'
when employee_name Like 'Albert Alvarez' Then 'AA'
when employee_name Like 'Hernandez-Sotata' Then 'HS'

[code]....

View 5 Replies View Related

SQL Server 2008 :: How To Find Statements That Cause Large Memory Paging

Apr 22, 2015

I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).

How to find particular queries/stored procedures that causing this?

View 5 Replies View Related

Transact SQL :: Select (sum) Statements With Two Table?

Jun 5, 2015

I have a database with two tables (Table1 and Table2)

looking for something like:

Table1:
+----+-------+----------+
| id   | Name | email    |
+----+-------+----------+
|  1 | name1 | mail1    |
|  2 | name2 | mail2    |
|  3 | name3 | mail3    |
|  4 | name4 | mail4    |
|  5 | name5 | mail5    |
|  6 | name6 | mail6    |
+-- +-------+----------+

Table2:
+----+------------+---------+------+
| id   | table1_ID | fee       | type |
+---+-------------+---------+------+
|  1 |     1           | 20000  |     1 |
|  2 |     3           | 1000    |     1 |
|  3 |     1           | 10000  |     1 |
|  4 |     3           | 1000    |     1 |
|  5 |     5           | 500      |     1 |
|  6 |     5           | 500      |     2 |
|  7 |     1           | 60000  |     2 |
|  8 |     2           | 1000    |     2 |
+----+------------+---------+-------+

i want the query that return:

+--------+-------+---------------------------------+---------------------------------+--------+
| name  | email | a:sum(fee) where type=1   | b:sum(fee) where type=2   |  b/10  |
+--------+-------+---------------------------------+---------------------------------+--------+
|name1 | mail1 |         30000                        |       60000                         |  6000|
|name2 | mail2 |           0                              |        1000                          |  100    |
|name3 | mail3 |          2000                         |          0                              |   0       | 
|name4 | mail4 |           0                              |          0                              |   0       |
|name5 | mail5 |          500                           |         500                           |   50     |
|name6 | mail6 |           0                              |          0                              |   0       |
+--------+-------+---------------------------+---------------------------------------+---------+

View 4 Replies View Related

SQL Express Import Using Transact SQL Statements

Aug 2, 2006

I have SQL server Express. How can you import a database, using transact sql code, onto a server from your local PC.

View 11 Replies View Related

Transact SQL :: Can Have Multiple Statements Under CASE-THEN

Jun 3, 2015

So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....

CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]

View 3 Replies View Related

SQL Server 2012 :: Find Subset Of Records From A Table - Multiple Except Statements

May 13, 2015

I created a CTE which finds a subset of records from a table

I then ran a SELECT statement against the same table as

SELECT * FROM TABLE
EXCEPT (SELECT * FROM CTE)

Is it possible to add another EXCEPT statement after the CTE EXCEPT statement to cover a condition not incorporated in the CTE definition?

View 9 Replies View Related

Transact SQL :: Generate Drop Table Statements

Oct 13, 2015

I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?

View 3 Replies View Related

Transact SQL :: Deadlocks On Server From Select Statements?

Feb 13, 2014

I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed

CREATE TABLE [dbo].[CMS_LOCKS7](
[PARENTID] [int] NOT NU,  --we have a non clustered index on this column
[CHILDID] [int] NOT NULL, --we have a non clustered index on this column
[ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column
[ORDINAL] [int] NULL,-- we have a non clustered index on this column

[Code] ....

View 12 Replies View Related

Transact SQL :: Check For NULL In CASE / WHEN Statements?

Aug 5, 2015

I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.

What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.

This is an example of the table:

UpdateColumn
Old Value
New Value
ReceivedDate
 7/8/2015 5:00:00 AM
ReceivedDate
7/8/2015 12:00:00 AM
7/9/2015 5:00:00 AM
ReceivedDate
7/9/2015 12:00:00 AM

So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying

This is the query:

CASE UpdateColumn
... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
I tried adding
CASE UpdateColumn
...
WHEN 'ReceivedDate' IS NOT NULL
THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...

But it did not like the "IS NOT NULL".

View 9 Replies View Related

Transact SQL :: Not Allowed To Write Delete Statements In A Function?

Jun 25, 2015

We are not allowed to write delete statement in a function. It will give the below error.

"Invalid use of a side-effecting operator 'DELETE' within a function."

We will get the same error for truncate/drop statements.

What is the reason behind this error?

View 8 Replies View Related

Transact SQL :: Reducing Deadlocks From Conflicting Delete Statements

Jun 15, 2015

I am working on an app that getting quite a few deadlocks due to delete statements.  I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks.  I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.

What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.  

Open this url in new tab [URL] ....

View 7 Replies View Related

Transact SQL :: Converting Union Statements Into Census Row Totals

Aug 20, 2015

I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code.  Each block is the same except for the date.  How could I rewrite these 30 union statements into a more concise query?

Each client (people_id) has a distinct agency_id_number.  So the code below totals the number of distinct clients/day and groups them by programs and facilities.

Sample data that you can use to test this report:

IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census

create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)

[Code] ....

View 3 Replies View Related

Transact SQL :: GO Statements To Execute Large Stored Procedure In Batches

Jun 19, 2015

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

View 9 Replies View Related

Transact SQL :: Method To Generate Insert Statements For Data In A Table

May 30, 2013

I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)

View 7 Replies View Related

Transact SQL :: Generate Insert With Column List And Select With Columnlist Statements

Oct 22, 2015

I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?

View 7 Replies View Related

Transact SQL :: Transaction Count After EXECUTE Indicates Mismatching Number Of BEGIN And COMMIT Statements

Aug 19, 2015

I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."

View 2 Replies View Related

Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?

Sep 29, 2015

I cannot find the problem with this function.

ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;

[code]....

I am getting the following error on line 75:

Select statements included within a function cannot return data to a client.

This is happening when declaring TrackUpdateCursor

The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?

View 20 Replies View Related

Transact SQL :: How To Find A Node

Nov 8, 2015

i have a table below like :
Id    ,    Request   

int     nvarchar(100)   
and in Request field i put below data :
1 <request><F3>353535</F3><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F3>353535</F3><F6></F6></request>

now i need to a query that i can find records that exists <F3> and if exists , remove just the <F3> tag

 below like :
1 <request><F6></F6></request> 2 <request><F5>353535</F5><F6></F6></request>3 <request><F6></F6></request>

View 18 Replies View Related

Transact SQL :: How To Find Duplicates For Different Fields

Oct 27, 2015

I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.

What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.

So If I have in the table

Serial          MSDSID          Date          Size...
001                 20            1/1/2015        5
002                 21            1/1/2015        3
001                 22            2/1/2015        1
003                 21            3/1/2015        1
004                 23            1/15/2015      5
003                 22            1/20/2015      6
004                 23            2/21/2015      5
002                 21            4/25/2015      4

I would like the results to show:

Serial            Count
001                  2           the count is 2 because Serial 001 has an MSDSID of 20 and 22
002                  1           the count is 1 because Serial 002 only has MSDSID 21
003                  2           the count is 2 because Serial 003 has an MSDSID of 21 and 22
004                  1           the count is 1 because Serial 002 only has MSDSID 23

It would be even better if the results just showed where the count is greater than 1.

View 5 Replies View Related

Transact SQL :: How To Find Mail Is Queued

Jul 19, 2015

I want to send an email twice a day, from database. So I have planned to make a storedproce which will be called by a job (which will select some record from one table and put it in other table based on a flag) but I want to run it in a transaction so that if email is send successfully then only it should commit else it should rollback.

How  can i find that "Mail queued" now i should commit.

View 4 Replies View Related

Transact SQL :: How To Find If A Column Is Used Anywhere In Database

Oct 20, 2015

I have looked around quite a bit, but mostly what I have found is looking to see if a table is used or if a column is in a stored procedure and honestly most of what I have seen does not work.

I want to reduce our nightly import by removing any columns that are not being used. We insert into our staging tables, Stage1 for example. And say Stage1 has column1 and column2. If those columns are not being used, then I want to remove them from Stage1. The only catch is that every Stage1 table has a v_Stage1. v_Stage1 should have all the columns from Stage1, but doesn't always. So I need to know what columns from Stage1 are used somewhere other than v_Stage1 and what columns from v_Stage1 are not used. 

View 14 Replies View Related

Transact SQL :: How To Find Alphanumeric Formats

Jun 12, 2015

create table example (f varchar(10))

insert into example values('fd')
insert into example values('fd')
insert into example values('fd1')
insert into example values('fd23')
insert into example values('fda23')
insert into example values('fd23g')

output:-

[A-Z][A-Z]
[A-Z][A-Z]
[A-Z][A-Z][0-9]
[A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][A-Z][0-9][0-9]
[A-Z][A-Z][0-9][0-9][A-Z]

How to write SQl query to fetch this type of output.

View 6 Replies View Related

Transact SQL :: Find Max Of 2 Columns In One Table?

Jul 23, 2015

I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision

Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative

Expected

Customer 1 had a max of refnum of 2 and 1st one on number

IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative

View 21 Replies View Related

Transact SQL :: Find Duration From Two Records

Oct 26, 2015

I am working on one logic. I have a table as below.

Declare @Table table (ID int, StartDate datetime, EndDate datetime)
Insert Into @Table (ID, StartDate, EndDate)
Values (1, '2013-01-01', '2013-12-31') 
, (1, '2014-01-01', '2014-06-30') 
, (2, '2014-01-01', '2014-07-31') 
, (2, '2014-08-02', '2014-08-30') 
select * from @Table 

I need as below.

ID, StartDate, EndDate
1, '2013-01-01', '2014-06-30'
2, '2014-01-01', '2014-07-31'
2, '2014-08-02', '2014-08-30'

Means If ID is same for two or more than two records then difference between first row's EndDate and second row's StartDate is 1 day then we should get one record as output. How can we built this logic in T-SQL ?

View 12 Replies View Related

Transact SQL :: Find Combination Of Rows?

May 10, 2015

I have this data as below. I need to find out the combination from the data and take a count of them

CREATE TABLE A
(    nRef INT,
     nOrd     INT,
     Token     INT,
     nML              INT,
     nNode            INT,
 sSymbol          VARCHAR(50),
 nMessageCode     INT
)
INSERT INTO A
(   nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER',13073),

[code]....

if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved