Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Share tech news, updates, or what's on your mind.

Sign up to Post

I've been looking at examples on how to changes rows into columns and still can't get this:

1. This is an example

CREATE table #final2
(
 whatmonth VARCHAR(10),
 
  col decimal
)

INSERT INTO #final2
(
    whatmonth,
    col
)
SELECT 'July',194


INSERT INTO #final2
(
    whatmonth,
    col
)
SELECT 'June',123

INSERT INTO #final2
(
    whatmonth,
    col
)
SELECT 'total',317

SELECT * FROM #final2

Open in new window


I want it  to be:

total    June   July
317     123   194


I was playing around with this example. I actually want it to be dynamic because I don't know how many rows there will be:

      
select *
from 
(
  select whatmonth, col
  from #final2
) src
pivot
(
  sum(col)
  for whatmonth in ([1],[2],[3])
) piv;

Open in new window

0
JavaScript Best Practices
LVL 13
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Hello,

I have these two queries

This one returns 353 rows

select c.store,a.vitem as OrderCode,a.auditid,b.invoicenum, sum(a.quan)as QTY, a.descript as Description, a.Upc, b.quanship as SVInvoice
from nh_svauditdetail a, nh_svauditinvoice b, nh_svaudithdr c
where a.auditid =5896 And b.invoicenum in(499211) And a.auditid=c.id And b.store=10 And a.vitem=b.itemcode group by c.store,a.auditid,b.invoicenum,a.vitem, a.upc, a.descript,b.quanship order by a.vitem

This one when i add the d.pack returns  384 rows.

select c.store,a.vitem as OrderCode,a.auditid,b.invoicenum, sum(a.quan)as QTY, a.descript as Description, a.Upc, b.quanship as SVInvoice, d.pack
from nh_svauditdetail a, nh_svauditinvoice b, nh_svaudithdr c , sh_item d, sh_upc e
where a.auditid =5896 And b.invoicenum in(499211) And a.auditid=c.id And b.store=10 And a.vitem=b.itemcode and a.upc=e.upc and e.id=d.upc group by c.store,a.auditid,b.invoicenum,a.vitem, a.upc, a.descript,b.quanship,d.pack order by a.vitem

I belive i need to move to a "Join" statement instead so the d.pack doesn't get included in the group by and cause the inflation of results.  Just not sure how to structure a multiple table join.  Thanks for any help provided and Happy 4th.

Microsoft SQL server 2005

Nick
0
This is SQL 2012. I know the data is not correct but my manager wants somehow to get the display of these rows correct. I think we (or ask the client) to run their test again but my manager says no.

I want to see if there's a way to fix this before I tell him there's no way to do this.

I created a sample  data:
create table #test
(
  tag varchar(20),
  dateandtime datetime,
  recieptNumber int

)

insert into #test
 select 'FSS', '2019-06-30 17:47:11.153', 0
 
 insert into #test
 select 'ABC', '2019-06-30 17:47:10.153', 123


insert into #test
 select 'XYZ', '2019-06-30 17:47:12.153', 123

select * from #test
order by dateandtime desc

Open in new window


1. You see DateAndTime column. We want to order by that column desc.
2. You see the "tag" column. I have "FSS" and 2 other ones
3. recieptNumber for XYZ and ABC will always be the same. FSS will always have zero as the recieptNumber.
4. We order by DateAndTime desc and it looks like this:
sql-problem.png
5. I want FSS row to show up first. I tried "group by" but it's not "group by'. Ordering by DateAndTime will always  display the rows like that. Anyway to have it display like this?

FSS	2019-06-30 17:47:11.153	0     -- then this one but datetime is between ABC and XYZ but I still want it to show up first.
XYZ	2019-06-30 17:47:12.153	123  -- this this one because it came after teh ABC row below
ABC	2019-06-30 17:47:10.153	123   -- this row last because of the datetime stamp

Open in new window

sql-problem.png
0
I have set up a SQL job to send the Deadlock alert in SQL server 2012. Whenever Deadlock occurs in the database it will send an alert email. This has been configured based on the SQL error 1205. I have been receiving 5 to 6 deadlocks every day but the application or in reports, we did not see any issues. Is it something I need to investigate or it's common in getting SQL deadlock in SQL Server? SQL Server is designed to detect and resolve deadlocks automatically right?
0
Hello Guys

I have a table with 1.000 rows, this table doesn't have an identity column and I'd like to alter my table and add an identity column for this table.

How can I do this ?

regards
0
How can I get better performance with my sql query in a SP? if you look at below my execution pan you will see that :

IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)

Open in new window


has alot of memory usage. How  can I reduce that?

ALTER PROCEDURE [Common].[SaveCustomerEntityIds] 
(
	@EntityType	NVARCHAR(128),
	@CustomerId	INT,
	@EntityId	INT OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)
	BEGIN
		INSERT INTO Common.[CustomerEntityIds]
					([EntityId]
					,[CustomerId]
					,[EntityType])
				VALUES
					(0
					,@CustomerId
					,@EntityType)
	END

	UPDATE	Common.[CustomerEntityIds]
	SET		[EntityId] = ([EntityId]) + 1
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId

	SELECT	@EntityId = EntityId
	FROM	Common.[CustomerEntityIds]
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId
END

Open in new window





picture of execution planpicture of execution planExecutionPlan2.sqlplan
0
Hi,
I have an error when trying to create a view in SQL
SELECT [Date],Age,Alarms,CardNumber
FROM dbo.Everything

and the error message

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.Everything'.

I tried typing Everything
and dbo.Everything
and Everything.dbo
But none of them worked.

Yet Everything is a valid table name

Please tell me what I'm doing wrong
Thanks
Ian
0
Need some help to create a view.

SELECT     A.Datum, A.ABNr, C.Name1, C.Ort, C.DebitorenNr, B.Einheitbez14wert AS AuftragsGewicht, B.Einheitbez6wert AS AuftragsStck, A.WaagenNr, 
                      A.WiegekartenNr, A.Netto AS NettoFertigm, A.Brutto, A.Stück, A.Abweichung, CAST(A.StückGewicht AS VARCHAR(10)) AS StückGewicht, A.Mitarbeiter, 
                      A.theoretischeGewicht, A.theoretischeStückzahl, C.Fertigmeldungdatum, C.Fertigmeldunggewicht, C.Fertigmeldungstückzahl, B.Einheitbez1wert AS Typ, 
                      B.Einheitbez3wert AS Breite, B.Einheitbez4wert AS Breite2, B.Einheitbez5wert AS Länge, B.Einheitbez2wert AS Stärke, 
                      B.Artikelkurzbezeichnung AS Güte, B.Kommission, B.Bestellinfo AS BestNr, B.Preiseinheittext AS Preiseinheit, 
                      B.VKPreisBruttoproVertriebseinheitgesamt AS VKPreis, B.Einheitbez9wert AS Paketgewicht, B.Sonderbemerkung, C.Lieferzeit, A.IDNr
FROM         dbo.Waagendaten A INNER JOIN
                      dbo.Auftragsbestätigungspositionen B ON A.ABNr = B.ABNr INNER JOIN
                      dbo.Auftragsbestätigungen C ON A.ABNr = C.ABNr
WHERE     (B.KalkulationMetallwarenIDNr <> '') AND (B.Einheitbez1wert <> 50)
GROUP BY A.ABNr, C.DebitorenNr, C.Name1, C.Ort, C.Lieferzeit, B.Einheitbez1wert, B.Einheitbez3wert, B.Einheitbez4wert, B.Einheitbez5wert, B.Einheitbez2wert, 
                      B.Einheitbez14wert, B.Einheitbez6wert, A.WaagenNr, A.WiegekartenNr, B.Artikelkurzbezeichnung, 

Open in new window

0
Hi

I still have one more issue on the opening stock, below is my query in ms access

SELECT Qry28000StockReportOpening.GrnDate, Qry28000StockReportOpening.ProductID, Qry28000StockReportOpening.ProductCode, Qry28000StockReportOpening.WHID, Qry28000StockReportOpening.ProductName, Qry28000StockReportOpening.OpStock, Qry28000StockReportOpening.Production, Qry28000StockReportOpening.Grn, Qry28000StockReportOpening.Sales, Qry28000StockReportOpening.Issue, Qry28000StockReportOpening.Diff, ((IIf(IsNull([OpStock]),0,[OpStock])+IIf(IsNull([Production]),0,[Production])+IIf(IsNull([Grn]),0,[Grn]))-(IIf(IsNull([Sales]),0,[Sales])+IIf(IsNull([Issue]),0,[Issue])+IIf(IsNull([Diff]),0,[Diff]))) AS Net
FROM Qry28000StockReportOpening

I want the result of the above query to come into the report by referencing the report stock code called ProductID
=IIf(IsNull(DSum("Net","QrystockSummary","[ProductID] =" & [ProductID] & "AND [GrnDate]<#" & Format(([Forms]![frmStockReportPrinting]![txtStartDateStocksg]),"yyyy\/mm\/dd") & "#")),0,DSum("Net","QrystockSummary","[ProductID] =" & [ProductID] & "AND [GrnDate]<#" & Format(([Forms]![frmStockReportPrinting]![txtStartDateStocksg]),"yyyy\/mm\/dd") & "#"))

Now for whatever reasons it’s  pulling  only one product instead of all how do I fix it
0
This is SQL 2012. I'll go step by step and show what I need done. I'm thinking I need a loop but also somehow to see the next row

1. Sample data
  Create Table #test
(
 Device VARCHAR(250),
 CashCredited Money,
 PlayType VarChar(50),
 DateAndTime DateTime,
 [Transaction ID] BIGINT,
 accunulated money)

--inserts

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX',-2.50,'Debit','2019-02-05 11:12:21.920',1211793,0 --last column needs to be -2.50


INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', 0,'Credit','2019-02-05 11:12:45.000',1211794,0

 INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated

 )
 SELECT 'MX', -1,'Debit','2019-02-05 11:30:26.383',1211795,0 -- last column needs to be -3.50

  INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'FSS',0 ,'Posting','2019-02-05 11:30:27.383',8888,0 --second column needs to be 3.50

   INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 accunulated
 )
 SELECT 'MX',0,'credit','2019-02-05 11:31:01.000',1211796,0

    INSERT INTO #test
 (
     Device,
     CashCredited,
     PlayType,
     DateAndTime,
     [Transaction ID],
	 

Open in new window

0
Become a Certified Penetration Testing Engineer
LVL 13
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Hello,  I have a query that is aggregating incorrectly. I'm no SQL expert and have been unable to get it to work correct.  
I have the following data sets.
Table 1
select * from nh_svauditdetail where auditid=949 and vitem=7211197

id        auditID                  upc            vitem                      descript                      cupc                quan  
257031      949      0004130301175      7211197           EE WAFFLE HMSTYL           1004130301175      1      
257110      949      0004130301175      7211197           EE WAFFLE HMSTYL           1004130301175      1      
257113      949      0004130301175      7211197           EE WAFFLE HMSTYL           1004130301175      1      

Table 2
select * from nh_svauditinvoice where invoicenum=383560 and itemcode=7211197
id      store      custnum      invoicenum            itemcode      dept      upc                     quanorder         quanship                 oos                        brand                      description      
126168      26      453426          383560                     7211197         FR      0004130301175             2                               2                   0                            ee                    WAFFLE HMSTYL 10CT      
I'm using the following query to aggregate the data

select a.vitem as OrderCode,a.auditid,b.invoicenum, sum(a.quan)as QTY, a.descript as Description, sum(b.quanship)as SVInvoice
from nh_svauditdetail a, nh_svauditinvoice b
where a.auditid =949 and b.invoicenum in(383560) and  
b.store=26 and a.vitem=b.itemcode and a.vitem=7211197 group by a.vitem,a.auditid,b.invoicenum, a.descript order by a.vitem
 
and get this result
OrderCode      auditid          invoicenum          QTY                       …
0
Hi I have 2 customers in a table.

custid  type
1       deposit
1       payment
2       deposit
2       credit
3       deposit
3       payment

now I only want the customers who has BOTH type as Deposit and Payment. So i want to select custid 1
and 3 in this case .

can someone give me that tsql please ?

Many Thanks
0
SELECT        COUNT(Receipt_Detail.Receipt_Number) AS totalcountreceipt, SUM(Receipt_Detail.Amount_Applied) AS receiptamount, Invoice_Header.Invoice_Number,?
FROM            Invoice_Detail INNER JOIN
                         Invoice_Header ON Invoice_Detail.Invoice_Number = Invoice_Header.Invoice_Number INNER JOIN
                         Member_Association ON Invoice_Header.Member_Number = Member_Association.Member_Number INNER JOIN
                         Member ON Invoice_Header.Member_Number = Member.Member_Number INNER JOIN
                         Receipt_Detail ON Invoice_Detail.Invoice_Number = Receipt_Detail.Invoice_Number INNER JOIN
                         Receipt_Header ON Member_Association.Member_Number = Receipt_Header.Member_Number AND Receipt_Detail.Receipt_Number = Receipt_Header.Receipt_Number
WHERE        (Invoice_Detail.Charge_Code = 'D' OR
                         Invoice_Detail.Charge_Code = 'T' OR
                         Invoice_Detail.Charge_Code = 'N')  (Invoice_Header.Invoice_Date BETWEEN '20181201' AND '20190131') AND (Receipt_Header.Deposit_Date BETWEEN 
                         '20181201' AND '20190131')  and (Invoice_Header.Invoice_Amount > 0)
GROUP BY Invoice_Header.Invoice_Number
ORDER BY totalcountreceipt DESC

Open in new window

Inside of "?" if I want to add like below, is it possible? Please show me how.

select top 1 (Receipt_Detail.Amount_Applied) where Receipt_Header.Deposit_Date BETWEEN '20181201' AND '20190131' order by Receipt_Header.Deposit_Date asc
0
rrrplease take a look of the attached file. It is a simple invoice to receipt (1 to m) relationship. and my question is.

1. assume I give you receipt received to date range 01/01/2019 to 01/14/2019. how can get the following in SQL query.

Invoice ID Total Receipt Count Receipt Amount
1                 25.00              1
2                 0                      0
3                 0                      0
0
SQL Server Configuration Manager question

I installed SSMS last week but did not install SQL Server. I never tried to connect to any database.

Today, I tried to install SQL Server but got a network connections error. When I opened SSMS and tried to connect to either of the two databases, I got the same error.

What step(s) do I need to follow in SQL Server Configuration Manager?

Thanks

SQL Server installation error
SSMS error
Clearly, critical services are Stopped, but which ones do I turn On?

Services Are Off
0
creating Indexes to boost query performance

Please provide me some examples where indexes and clustered indexes would help to boost performance of a database query.

Thanks
0
What is Spectrum as it relates to SQL Server?

I was given this name about improving performance on database queries but can not find a software product.

Please advise...

Thanks
0
Need a sample SQL Server database I can use to play around with

I have SQL Server 2014 installed and hope to goof around with a sample database which has lots of different object types already installed. In fact, I hope that

sys.objects table is filled with every possible permutation. I want this database for the sole purpose of raising questions in me about what things I may have not seen before.

Do you have a link so such a free database for me to download?

Thanks.
0
There are some questions I have regarding creating a summary query in a Microsoft Access database:

Question 1:  Please note sample below provided:

With the query:  qQuestion1a - I put in it to show me all time that is not considered "ST" - which is standard time.  My criteria is <> "ST"
However I'm missing data as qQuestion2a shows there are some where the TimeType hasn't been filled out - how come those don't show in qQuestion1a?
Seems like a bug on Microsoft Access' part....

Question 2:

Basically I'm building a query that will give me all non-billable time for each employee.  Some employees have non-billable time - others do not.  My attempt with the qSummaryOfNonBillableperDisney  only shows the employees that have non-billable time.  However I want a query that lists each and every employee regardless if they have non-billable time or not.  

How do I make such a query that would do this:

UserName           NBT
Donald Duck              29
Mickey Mouse      27
Minnie Mouse      13
"George Geef ""Goofy"""
Daisy Duck

instead of this:

UserName      SumOfWorkHours
Donald Duck              29
Mickey Mouse      27
Minnie Mouse      13

Just to explain further - how I would attempt to do this...is creating a query that made a table of all Disney employess with the name and another field that has the total NBT hours.  Then I would run an update query that would load in the values.   Is this the correct method of doing this?  Do you have to take these series of steps to create …
0
Exploring SharePoint 2016
LVL 13
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

How to grant user permission to access sql agent & sql jobs ?
0
Hi,

I need to store pdf documents and images in a sql server database.
It is possible to calculate the approximate space for which eg. 1 Gb of documents, which size matches the database.

Best regards
0
A little history before I get crucified. Not my circus and not my monkeys.. I have a soft spot for the down trodden.  

Have a client that is still break\fix. 4 years ago I warned them I was no longer supporting Windows 2003r2. 2 years ago I warned them the server (Dell T300) could die. Offisite backup no… still using a 2011 version of Symantec Backup and backup had stopped working a year previously. However, I do have good System State backup
RAID 5 failed and hot spare flaked out, so had to send the drives out for data recovery. Data was recovered. All user folders and other files I put on a temp server so they could access them. Now the remaining problem is that they had a program that used SQL 2005. I have got SQL 2005 to load on 2008R2 and run as long as I put all the SQL service packs on it. The database will not restore. Their previous server was also the domain controller.

The software company said they would help, but wanted $15,000 upfront. The main reason this place was still on this system was that the company only supported server 2003R2 and SQL 2005 which was why they were still on it. The client was moving away from this company, but needs their accounting from the old software.

I have a client that has a T300 in the closet that was a domain controller with server 2003R2. It never got demoted because the access to old accounting software wouldn’t run on newer version of server. I got it and have it in the shop and it still runs 😊 So I was wondering if…
0
This is SQL 2014.

1. I have this where clause that works

DECLARE @ManagedOrNon VARCHAR(100) = N'N/A - Non-Managed'

where
...
AND 
      (
          s.LesseeCode IS NULL
          OR (NOT (
                      s.LesseeCode = @ManagedOrNon
                      AND (ISNULL(s.LesseeCode, 1) = ISNULL(@ManagedOrNon, 1))
                  )
             )
      )

Open in new window


2. I want to duplicate what the C# code has and add and Else statement

if (nonManaged == NonManaged.No)
		q = q.Where(x => x.Schedule.LesseeCode == null || x.Schedule.LesseeCode != Constants.NonManagedAssetScheduleCode);
	else
	if (nonManaged == NonManaged.Yes)
	q = q.Where(x => x.Schedule.LesseeCode == Constants.NonManagedAssetScheduleCode);

Open in new window


3. I think I can use a CASE statement but this didn't work

Where
   ....

 s.LesseeCode = CASE WHEN @ManagedOrNon = N'N/A - Non-Managed'
	                      THEN
                              s.LesseeCode IS NULL
					  OR 
					 (NOT (
						  s.LesseeCode = @ManagedOrNon
							  AND (ISNULL(s.LesseeCode, 1) = ISNULL(@ManagedOrNon, 1))
							  )
						 )
						  ELSE
                                                       s.LesseeCode = 'Managed'
						  END 

Open in new window


I'll work on it. If I figure it out, I'll post back.
1
This is related to this question https://www.experts-exchange.com/questions/29129555/I-have-a-solution-Inner-Join-but-I-also-need-all-the-rows.html
Scott and Kyle helped me with it but now, my moody manager changed things around on me and I'm again stuck on coming up with a similar solution to the question above. My manager says to use if-else but I think there's a better way of doing this (the link above has the solution but I don't know how to apply it to below)

I know
1. I need to start with #AuthUser table
2. I need to look at FilterML and FilterPortfolio columns in #AuthUser

The change I need
User 14307 has FilterML and FilterPortfolio set to 0. This means there's no filter, so if I run the query below, I should get rows back but I don't. I know it's because I do inner join and look at the tables to see if user is in there or not and that's not correct.

It works fine for user 88. run it for SET @userid = 88 -- 14307 and then change it to 14307 and run it again and you don't see any rows but I should get rows back because MLFilter and MLPOrtfolio are false for user 14307...this user has access to everything, no filtering should be applied to it.

  CREATE TABLE #Authuser -- all the users
(
  ID INT,
  FirstName VARCHAR(100),
  FilterML BIT,
  FilterPortfolio bit
)

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterML,
	FilterPortfolio
)
SELECT 88, 'Edward', 1,1 -- has filter and will get data with the INNER JOIN I 

Open in new window

0
I had this question after viewing Is this correct: first % in "Like" ignores indexes?.

I asked the question above the other day and I understand it but hoping for a miracle, workaround something :)

From Scott:
A prefix of % does not make SQL "ignore" the index, it means that SQL can't do a seek on the index.  It could still scan the index to satisfy the query

I think I came across a link that had a workaround and now I can't find it. I can't use FTS and can't have word% search.

Anyway to speed up a search using %word% by adding indexes? I've already broken down the stored proc and get chunks of data but it would be great if I could add an index to 5 columns in the "where" clause that I'm searching on.

If not doable, then it is what it is.
0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.