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

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
Python 3 Fundamentals
LVL 13
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
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
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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
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
Bootstrap 4: Exploring New Features
LVL 13
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

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

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.