SQL Server 2012 :: Adding 2 COUNT Statements Results In Heavy Query

Jan 28, 2014

These separate COUNT queries are very fast:

SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357

But I want to add the COUNT statements, so this is what I did:

select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)

Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?

View 7 Replies


ADVERTISEMENT

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Adding Count Statements

May 22, 2008



I have two queries:

select count(*)1 from TABLE1
where Date1 < x

and

select count(*)2 from TABLE1
where Date1 < x

and Date2 > x

what I want is to get the result as a single number in other words Count(*)1+Count(*)2 = ?

I am having difficulty adding these two statements

Thanks

View 4 Replies View Related

SQL Statement, Adding Two COUNT/CASE Statements

Dec 12, 2007



SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors],
COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors]
FROM content_hits_tbl
WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())

=======================

How do I add up both COUNT/CASE columns? Would it be:
SUM([New Visitors] + [Returning Visitors]) AS Total


I tried this and it doesn't work. I get invalid column names error for both.

I have even tried:
SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total

You would think that there would be some gui functionality in VS08 that would do this...


Thoughts are greatly appreciated!

TT

View 8 Replies View Related

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

Oct 23, 2015

I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.

Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)

[Code] ...

View 2 Replies View Related

SQL Server 2012 :: Adding RowID To Existing Table - Inconsistent Results

May 6, 2015

I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.

We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:

AAAA.1234 /* (account number)*/
1/1/2015 $150 First Transaction
1/3/2015 $24.233 Second Transaction
BBBB.5678
1/1/2015 $350 Third Transaction
1/3/2015 $24.233 Fourth Transaction

My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.

Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.

I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:

AAAA.1234 1/1/2015 $150 First Purchase
AAAA.1234 1/3/2015 $24.233 Second Purchase
BBBB.5678 1/1/2015 $350 Third Purchase
BBBB.5678 1/3/2015 $24.233 Fourth Purchase

My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:

SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericTable
WHERE RowHeader LIKE '____.____%'

Results look like this:

But every time I run the routine, I get different numbers!

Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.

TRUNCATE TABLE GenericImportTable;
ALTER TABLE GenericImportTable DROP COLUMN RowID;
BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6)
ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericImportTable
WHERE RowHeader LIKE '____.____%'

View 3 Replies View Related

SQL Server 2012 :: Tab Character In Query Results

Jul 9, 2015

I am getting a tab character at the end of my query fields, been trying various things to fix, such as using the replace function below but still i get the tabs!

select CAST(REPLACE(NAMEALIAS,CHAR(9),'')AS CHAR(40)) + ',' as PRODNAME,
CAST(REPLACE(ISNULL(GLOBALTRADEITEMNUMBER,0),CHAR(9),'')AS CHAR(18))as EANNO,LTRIM(cast(ISNULL(GLOBALTRADEITEMNUMBER,0) as char(18))) as KONSEAN
,LTRIM(CAST(I.ITEMID AS CHAR(8))) AS PRODCODE,'00' from INVENTTABLE I
LEFT JOIN INVENTITEMGROUPITEM IG ON I.ITEMID = IG.ITEMID

[Code] ....

Results:
Gießanzeiger groß ,04260376730475 04260376730475 730475 00
Gießanzeiger klein , 04260376730352 04260376730352 730468 00
Gießanzeiger klein , 04260376730468 04260376730468 730468 00

View 9 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

SQL Server 2012 :: Query To Get Count Of All Fields Named LX Where X Is A Number

Jan 29, 2015

For example in a table with this fields "field1, L1,L3,L100" field2 the count is 3

it would be better to match a number into the like but i thinks it cannot be done in the like so i've to add another condition to ensure all the text after L is a number.

is this the best way to do it?

Select count(*) from Information_Schema.Columns Where Table_Name = @Table
AND column_name like 'L%' and ISNUMERIC(SUBSTRING(column_name,2, len(column_name)-1))=1

View 6 Replies View Related

Adding Query Results To A Tbale

Jan 13, 2004

hello everybody,

I have a small problem and was hipeing someone could help. Want to run a query, where I sellect results from two tables. Once this is done I want to takes those results and update a table that is already created with those results. How would I do this? Thw eonly way I know how to do this is by writing this:

select CompanyName, ContactName, Address, City, Region, ProductName, UnitPrice INTO TABLE2
From Suppliers, Products

But the problem with this is that I can only input the query results into a new table that I would name above. I can't insert it into a table that has already been created.

This is the erroe that I receive:
There is already an object named 'TABLE2' in the database.

Please Help!

thanks

View 8 Replies View Related

Adding Columns To A Query From Results

Apr 14, 2014

I have a table called approval_levels in which I have 2 columns:

|LEVEL_ID |NAME |
|1 |app_level_1 |
|2 |app_level_2 |
|3 |app_level_3 |

I have a second table called requests, in which I have 3 columns:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |
|1 |wd-40 |Acme |
|2 |Windex |Acme |
|3 |Propane |Acme |

I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.

|REQUEST_ID |LEVEL_ID |
|1 |1 |
|1 |2 |
|1 |3 |
|2 |1 |
|3 |3 |

Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |app_level_1 |app_level_2 |app_level_3 |
|1 |wd-40 |Acme |X |X |X |
|2 |Windex |Acme |X | | |
|3 |Propane |Acme | | |X |

Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?

View 1 Replies View Related

Adding A Count Clause To A Query

Jun 5, 2008

I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING:
SELECT Active_Activities_temp.*
FROM Active_Activities_temp LEFT JOIN
ACTIVITY_CORE_LISTING ON
Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv
WHEREACTIVITY_CORE_LISTING.cde_actv is null
ORDER BY prtcpnt_id
So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.

The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.

What code can I implement to do what I need to do? Thanks so much.

View 5 Replies View Related

SQL Server 2012 :: Saving Query Text / Execution Time And Rows Count

Jun 3, 2014

I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.

View 5 Replies View Related

Need Help With Adding A Duplicate Record Count Column To Query

Jul 23, 2005

I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn

View 2 Replies View Related

Inconsistant Query Results With Count(*)... Bad Index?

Jul 20, 2005

I have a table that seems to have a bad index. When I do the followingquery I get inconsistant and needless to say incorrect results.select count(*) from mytable where mycolumn = 1If I remove the index from "mycolumn" the query works correctly. If Iadd the index back (even with a new name etc...) it doesn't workright.Has anyone ran into this? or does anyone know how I can fix thisproblem?It seems that removing the index is not really removing everythingbecause when I add a new one I get this same problem... btw, this isisolated to this column on this table. all other indexes within thedatabase are fine.Any help would be appreciated.Thanks,dharper

View 1 Replies View Related

SQL Server 2012 :: Query To Search Full-text Indexed Table And Return Results

Apr 19, 2014

I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)

Query

DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);

SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');

SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result

[Code] ....

View 2 Replies View Related

Count Of Occurrence In Given Period Of Time - Query Has Unexpected Results

Sep 12, 2013

I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.

SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder
HAVING COUNT(x.[StreeLine1])>1

Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?

SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
AND x.[StreeLine1]='599 Ships Landing Way'

I use Microsoft SQL 2008

View 12 Replies View Related

SQL Server 2012 :: Query To Count How Many Sessions Are Active And Remain Active Per Hour

Jan 22, 2015

I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.

TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707

[code]....

can see how many sessions logged in per hour with the following statement:

SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041

[code]....

Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.

View 5 Replies View Related

SQL 2012 :: Pasting Query Results Into XL From SSMS

Mar 13, 2014

Previous to 2012, grid results from SSMS queries pasted into Excel beautifully and were easy to format.in 2008, The results from this query pasted into a spreadsheet with the query contained in a single Excel cell:

(sample - not the whole script)
select top 300
Avg_CPU_Time
,Total_Physical_Reads
,convert(datetime,Last_execution_time) as Timestamp
,Stored_Procedure
,Query_text
from dbadmin.dbo.History_CPU_IO_ByQueryAndSP

This was wonderful. I could deliver to developers wonderfully tidy reports on query resource usage with timestamps along with which stored procedure the queries came from.

Can't do that in SSMS 2012. Try it. It's a disaster. The Query_text, when pasted, spreads across multiple Excel columns, including the ones designated for other data. The result is totally unreadable.how to make the query_text stay in it's own cell? I've tried converting query_text to varchar and ntext. Same results.

View 9 Replies View Related

SQL 2012 :: Group By In Sub Query To Get Desired Results

Jun 15, 2015

I have to use a Group By in Sub query that will give me my desired results.

SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W

The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.

View 8 Replies View Related

SQL 2012 :: Query To Match Multiple Results And Average

Mar 3, 2014

I work for a school district and new requirement we were just given for scoring some student scores.

Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.

What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.

View 9 Replies View Related

SQL 2012 :: Send Emails With Query Results As Attachment?

Jul 16, 2014

What is the best way to send emails with query results as attachment?

There is a stored proc sp_send_dbmail but the formatting is not very good.

View 3 Replies View Related

SQL 2012 :: Limiting Query Results With 3 Criteria For Each Record

Mar 20, 2015

I am trying to write a query that gives me the personal records from speed skaters on e.g. the 500 mtrs. I do this with the query:

SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, min(crtFinalTime) AS MinTime
FROM tb....... INNER JOIN etc..
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)

In itself this works fine. However, there are complicating factors. Sometimes a speed skater has multiple PRs, meaning the he/she has the same fastest time more than once.

If these times are achieved on multple days, the 1st date is the official PR. (meaning: "Min of racedate")
If they are raced on the same day the 1st race is the PR (meaning: "Min of distancenumber")

Changing the code to:

SELECT cdsDistance AS Distance
, prsFirstName
, prsLastName
, MIN(crtFinalTime) AS MinTime
, MIN(cdsStartDate) AS RaceDate
, MIN(cdsDistanceNumber) AS DistanceNumber

FROM tb.......
GROUP BY cdsDistance, prsFirstName, prsLastName
ORDER BY min(crtFinalTime)

This gives me the wrong outcome because it gives me the "MIN" of every field, and they are not necessarily on the same row.

An option would be to calculate min(crtFinalTime), if for a person there is more than 1 result, calculate min of date, and then (if there is still more than 1 row) min of distancenumber.

Seems complicated, and I have the feeling there must be a better way (apart from: how to get this code)

Stacking subqueries in the FROM statement seems like a option be costly (time wise). There are more than 10 million rows (and growing) to run through.

As an example a few times:

DistanceFirst nameLast name Time Date Distance nr.
500 Yuya Oikawa 34.49 201311155
500 Yuya Oikawa 34.49 201311153
500 Yuya Oikawa 34.49 201311172

Yuya has 3 best times (34.49), 15-11-2013 is the 1st date, then distance nr 3 is the 1st distance raced. Therefore the 2nd row is the only row I would like to get in my endresult.

View 4 Replies View Related

SQL Search :: 2012 / How To Update The Results Into Select Query Table

Oct 28, 2015

I have  created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.

Below is my Query:

 ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROM 
(SELECT a.meu, a.mep2, SUM(a.mest) as excst                
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''      

[code]....

View 2 Replies View Related

SQL 2012 :: Possible To Send Pivot Query Results As Automated Database Email?

Nov 26, 2014

possible to send Pivot query results as automated database email ?

View 3 Replies View Related

SQL 2012 :: Save Query Results Directly Using Variables And Date / Time

Sep 1, 2015

I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory

As an example I have created a (simple) stored procedure:

USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)

[Code] ....

What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)

The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"

The directory: D:SQLServerResults

View 9 Replies View Related

SQL Server 2012 :: Order Of Execution Of Statement With GO Statements?

Jan 16, 2014

Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?

UPDATE tbl1 SET col1 = 1

GO

DELETE FROM tbl1 where col2 = 2

View 2 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

SQL Server 2012 :: Use Unique Custom Error Numbers For THROW Statements?

Apr 20, 2015

My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".

We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.

Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?

View 2 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

SELECT Statements Not Providing Results Wanted

Apr 10, 2002

I have three tables, Employer, Customer and CustomerEmployer. The employer table contains all employers and the customeremployer table contains customer records for an employer. For example

Employer Table
EmployerID Name Address
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street
4 ZZZ Company 2 Rust Street

Customer Employer Table
ID EmployerID CustomerID
1 3 1
2 3 2

Customer Table
ID Name
1 Joe Smith
2 Jane Thomas
3 Tim James

I would like to run a select statement which lists all the employers and whether the employer has already been assigned to a customer. For example, for Joe Smith I would like to see all the employers listed AND some marker indicating that Joe is already assigned to EmployerID 3, JJJ Company.
Results
EmployerID Name Address CustomerID
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street 3
4 ZZZ Company 2 Rust Street

I have tried unions and many types of joins but none seem to work correctly. I was using the following select statement however only employers that are contained in the customeremployer table appear.

SELECT Employer.EmployerID, Employer.Name, Address, Employer.City,
CustomerEmployer.CustomerID
FROM CustomerEmployer RIGHT OUTER JOIN
Employer ON CustomerEmployer.EmployerID = Employer.EmployerID
WHERE (CustomerEmployer.CustomerID = @customerid) OR (CustomerEmployer.CustomerID IS NULL)
ORDER BY Name

Any ideas?
Thanks.

View 1 Replies View Related

Queries With Different Statements - Show Results In Datagridview

Oct 19, 2015

I have made a couple of queries I want to use into a Visual studio project, Now is my problem:

All my queries have different statements. I believe the best is to show the results in a Datagridview

How to view them there? I know how to do it... but I have got about 30 queries

Here is my code so far:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"

[Code] ....

View 2 Replies View Related

SQL Server 2012 :: Adding Certain Rows And Minus Value With A Row

Nov 25, 2014

I got a table where i need to add certain rows and minus the valu with a row.

IDC1C2C3C4C5C6
1551557775
266201452
345222266
441727582
532556951
6022022
7182344142
8

Result in the 8 Row

Sum(id(2,3,4)-sum(id(6,7))

View 2 Replies View Related







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