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

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
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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
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
OWASP Proactive Controls
LVL 13
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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
This is a related question that Dustin and Ste5an helped me with
https://www.experts-exchange.com/questions/29129110/Replace-works-but-is-there-a-better-way.html?headerLink=workspace_answered_questions

I'll go step by step

1. Please run the attached script. It creates the sample data

2. This works. 1265 rows. However, you see I have "@seachtext" and "@searchtext2".

DECLARE @searchtext NVARCHAR(4000) = '%019%'

declare @searchtext2 nvarchar(4000) = '%O19%'

DECLARE @results TABLE(SearchText NVARCHAR(4000))

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

	 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )

 INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

[dbo].[camillaforEE] AS u
	-- INNER JOIN @results r ON u.SerialNo LIKE @searchtext
where

		  (u.[SerialNo] LIKE @searchtext)
		  OR
           (u.[SerialNo] LIKE @searchtext2)
        
          OR
		   (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )

DROP TABLE #unitsearch

Open in new window


3. My question above helped with replacing zero with O and vice versa. So, I removed @searchText2 and use the @result table to join. But, now I get an error that there's a duplicate for row 1518. I don't want to use "distinct" or remove "primary key". I think this isn't the right solution.

Any ideas why this doesn't work?


Open in new window

0
I don't know if there's another way of doing this besides what I have in mind and I'll note it below...

Some users have access to all Scheds and some users only to specific ones. When FilterSched is false, it means user has access to everything and has to get all the rows. If FilterSched is true, means user has to get all the rows.

I'll go step by step with an example.

1. I create the temp tables
CREATE TABLE #Main -- main table that has all the rows
(
  SchedID int
)

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

CREATE TABLE #AuthuserSched -- users and the scheds they have access to
(
  ID INT,
  SchedID int
)

 CREATE TABLE #FilterScheds -- holds list of scheudles user has access to
 (
   SchedId int
 )

Open in new window


2. Populate tables

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 88, 'Edward', 1 -- has filter and will get data with the INNER JOIN I have because #FilterScheds table holds whatever Sched this user has access to

INSERT INTO #Authuser
(
    ID,
    FirstName,
    FilterSched
)
SELECT 14307, 'Camillia', 0 -- doesn't have filter so this user gets all the data (*** but that INNER Join is going to fail because #FilterScheds doesn't get populated)

----

INSERT INTO #Main -- all my data
(
    SchedId
)
SELECT 9999

INSERT INTO #Main
(
    SchedId
)
SELECT 555

INSERT INTO #Main
(
    SchedId
)
SELECT 4567

---

INSERT INTO #AuthuserSched -- user 88 has access to Sched 9999
 SELECT 88,9999

Open in new window


4. Now I join to get the data for user (I have this in a stored proc )

 --1. see if this user has a filter
 DECLARE @filter BIT
 DECLARE @userId INT = 88 --- run it for user 88 first, then user 14307
 
 SELECT @filter = filtersched FROM #Authuser
 WHERE id = @userId

 -- 2. now that user has filter, get their Scheds
 IF @filter = 1 --user 88 needs filtering
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
  
  ---3. now get the results. I have all Scheds user has access to. So, I'm doing an inner join

  SELECT * FROM #Main
    INNER JOIN #FilterScheds ON #FilterScheds.SchedId = #Main.SchedID --ISSUE is here --*** for user 88, I only need what Scheds he has access to. For user 14307...users that don't have filter...all the rows should come back
	                                                                      -- 
  
  TRUNCATE TABLE #FilterScheds -- clear the table and run it again for user 14307

Open in new window


My solution is to do this. A better way of doing this?

IF @filter = 1
  BEGIN
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #AuthuserSched  WHERE id = @userId
  END
else
 begin
  INSERT INTO #FilterScheds
  (
      SchedId
  )
    SELECT SchedID FROM #Main --- get all the scheds that MAIN has for users that have access to everything. So, #FilterScheds is always populated
end 

Open in new window

0
In SQL Server 2012, Is there a way to create a table variable that references a table already in the database?  Note: I have a string variable that contains the name of the table.

DECLARE @tablename As varchar(MAX)
DECLARE @TB As Table

SET @tableName = 'table1'
SET @TB = table with name 'table1'
SELECT * FROM @TB

Note: I'm asking if I could do this without dynamic sql,  I already know that I could do 'EXEC sp_executesql 'SELECT * FROM ' + @tableName.
0
I can't figure out why this SQL brings back the same amount of rows with and without the where caluse (been at it for a day...took joins out, etc and I still can't figure it out). I'll keep at it and if I figure it out, I'll post back.

1. I can't attach the SQL here. It's too big to attach here. You can download it from here and create the table with the data. https://drive.google.com/open?id=1WVbU7EeC-r748RM8dVa74guQvR-DAxbk
   It's 55645 rows of data inserted into a table.

2. This works fine. Brings back 53678 rows with the "where" clause

DECLARE  @AssetType nvarchar(4000),
 @searchtext nvarchar(4000),
 @searchtext2 nvarchar(4000),
 @p__linq__0 VARCHAR(100) = N'N/A - Non-Managed'
 
 SET @AssetType =2
 SET @searchtext ='%019%' --'%MX7T11243364%'
 SET @searchtext2 ='%O19%'

 SELECT * 
--INTO camillaNoFTS
 FROM dbo.BigAssetSearch
 WHERE
 

  (
       ([Sched] LIKE @searchtext)
		 OR 
		  ([SerialNo] LIKE @searchtext)
		  or
		  ([SerialNo] LIKE @searchtext2)
	
        
        OR ([CustomerRef] LIKE @searchtext )
        OR ([Model] LIKE @searchtext )
        OR ([Manufacturer] LIKE @searchtext)
        OR ([SiteAddress] LIKE @searchtext )
        OR 
		(customername LIKE @searchtext )
        OR ([LesseeName] LIKE @searchtext )
         
	)
	AND
    (
              ([LesseeCode] IS NULL)
              OR (NOT (
                          ([LesseeCode] = @p__linq__0)
                          AND ((CASE
                                    WHEN 

Open in new window

0
I have a string that can have zero or O in it. If it's zero, I replace it with O. If O, I replace it with zero. I need both strings ... the original one and the new one.

I did this and I repeat it for O, but is there a better way of not repeating it?

DECLARE  @searchtext nvarchar(4000) 

if CHARINDEX('0',@searchtext) > 0 -- I want to do the same check for O
	begin
		SET @searchtext2 = REPLACE(@searchtext,'0','O')
	END
    ELSE
	begin
      SET  @searchtext2 =  @searchtext -- duplicate what code does
 	END

Open in new window

0
CompTIA Network+
LVL 13
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

when we use exists in a query

what is meaning of select x means

select count(order_nr) from xyz where ord_cd="10"
exists(

select 'x' from ABC a where ef=ge
and
a.some_cd='zz'
and
((datediff(...)or (datediff.....))


i checked below link
https://www.geeksforgeeks.org/sql-exists/

how to validate my results are correct or not

how to break above query to see if difference of time is actually correct?
please advise
0
Msg 1013, Level 16, State 1, Line 1
The objects "XYZ" and "ABC" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

wondering what it means by above error

please advise
0
We have a client that has ACT 2011 on Windows Server 2012 R2.  They have somehow managed to damaged all of their ADF files by messing with ACT 2011 and SQL server.  We have no idea what was done except that it is not working.

1. We are getting this error message with all the current database/files when we try to open them:

This database cannot be opened because the SQLServer version of this database cannot be identified.

2. When we restore backup files from before this issue occurred and try to open them we get a dialog box that say it will verify the database.  However, the next moment. we get an error message:

The log scan number (1201:656:1) passed the log scan in database 'Sales' is not valid,  The error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf).  ......


3. Finally, when we try to attach the file (that the client "messed with") to the database (via ACTDiag) I get this error message:

"The database was created with a version of SQL Server that is neither SQL Server 2005 or Server 2008.  Attaching a non-SQL 2005 or 2008 database is not supported."  However, I do not believe SQL version about 2008 R2 has been on this machine.

We are doing what seems to be endless research and multiple solutions for SQL and/or ACT and nothing seems to be working.  Any thoughts or advice?
0
i am trying below kind of query which should give count less than 30 minutes of difference of time. not able to execute below. How to modify to make it work

select count(order_nbr) from order INNER JOIN timings ON order.order_id=timings.order_id WHERE LEN(order_nbr) = 11 AND order_cd=02  AND order.time_ins- timings.time_upd <30 minutes
please advise
0
Hi,

I need to be able to compare Rowversion to be able to detect data changes for an ETL process. I am using SQL Server 2005. The process needs to convert to varchar for comparison. I have trawled the net for some suggestions but I think there is a problem with case sensitivity:

When I run select min_active_rowversion() this is retuned - 0x0000000002514EB9

When I run SELECT sys.fn_sqlvarbasetostr(min_active_rowversion())
or
SELECT master.dbo.fn_varbintohexstr(min_active_rowversion())
0x0000000002514eb9 is returned which is a different value to that returned by min_active_rowversion()

Is there a process that I can use to be able to return the value of min_active_rowversion() as a varchar?

Many thanks
0
we have hundreds of flows like registration, sales, cancellation from front end java applicatio to SQL database.

Microsoft SQL database has hundreds of stored procedures, tables, columns, joins, primary keys, foreign keys etc.

it is getting hard to check database stored procedures, tables if issue come on say sales flow to check quickly as no proper documention on database side.

what kind of documentation is good to have on database side.

i think like one excel spreadsheet  with columns like stored procedures, then next column relevant tables then next column table 1 columns, table 2 columns and then highight primary key as yellow color and then next column joins and other related tables

please advise any other better ways and best practices and links resources on it
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.