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

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
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
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
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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
i have below query

select * from xyz_table where item_id='12345';

i got results with 10 columns

3rd column is purchase_activities


now i need to sort or order based on ascending date to see first activity at top to last activity at bottom

how to do it
please advise
0
Working on a SBS2011 experiencing the usual SBS WSUS errors that create disk space issues. I decided to clean up the WSUS database by following two articles.

Reinstall WSUS on SBS tried removing from add and remove programs and it errors out. As mentioned in this article I proceeded to the document below to remove the role but have a database question prior to deleting.  

WSUS uninstall is failing I made the changes to the registry mentioned in this article and removed the role which completed successfully. Step 10 states, once WSUS is uninstalled, manually remove the WSUS content and/or database files from the hard drive. This is where I would like to clarify before deleting data. For obvious reasons I want to make sure what I’m deleting will not cause an instability.

My understanding of SQL is it’s installed on the C: partition below where the system databases are displayed. Rather than tamper with the SQL installation the only data I removed here was the log files which grew over 12G. FYI I copied them for further review.

SQL system files located C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\ with the following folders:
Backup
Binn
Data
Install
LOG (12.8G) cleaned up the log files
SchemaSig

On the D: partition there is D:\WSUS with the following folders:
UpdateServicesPackages
WsusConent
SUSDB.mdb (26.5G)
SUSDB_log.ldf (2.6G)

I opened SQL Studio and see the following SUSDB database
sql
0
This is SQL 2014.

I'm doing a search query using Like %whatever%

Is this correct:
First %-sign will make any index useless, but one at the end will use the index.

I got it from here columns in Like
0
This is a related question to this Related question

I created a sample. I'll go step by step. Please scroll down to my 'Edit' note below. I figured something on this that I don't understand.

1. This is the table with 2 rows and the Full Text Search. You can run it to create the sample table with data

/****** Object:  Table [dbo].[camillasearchtest]    Script Date: 11/18/2018 6:59:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[camillasearchtest](
	[RowId] [int] IDENTITY(1,1) NOT NULL,
	[ScheduleID] [int] NOT NULL,
	[ID] [bigint] NOT NULL,
	[SerialNo] [nvarchar](64) NULL,
	[Manufacturer] [nvarchar](64) NULL,
	[Model] [nvarchar](250) NULL,
	[Vendor] [nvarchar](64) NULL,
	[Quantity] [int] NULL,
	[DateAccepted] [date] NULL,
	[DateReturned] [date] NULL,
	[SiteAddress] [nvarchar](256) NULL,
	[SiteAddress2] [nvarchar](256) NULL,
	[SiteCity] [nvarchar](256) NULL,
	[SiteState] [nvarchar](256) NULL,
	[SiteZip] [nvarchar](256) NULL,
	[SiteCountryCode] [nchar](3) NULL,
	[BillName] [nvarchar](256) NULL,
	[BillToAttn] [nvarchar](40) NULL,
	[BillAddress] [nvarchar](256) NULL,
	[BillAddress2] [nvarchar](256) NULL,
	[BillCity] [nvarchar](256) NULL,
	[BillState] [nvarchar](256) NULL,
	[BillZip] [nvarchar](256) NULL,
	[BillCountryCode] [nchar](3) NULL,
	[RemitName] 

Open in new window

0
This is SQL 2014.

I setup Full Text Search on 8 columns. The search is fast but I get different results when I use "Like" and when I use FTS's "Contains".  

I'll go step by step below.

1. This is how setup FTS
FTS1.png
FTS2.png
FTS3.png
FTS4.png
FTS5.png
2. This SQL brings back 28,243 rows and takes 1 min and 30 seconds. The data is correct. It uses Like

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 camillaresult
 FROM dbo.camillasearchtest
 WHERE
 
--  id = 4379297 AND --***remove this
  (
       ([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 ([LesseeCode] IS NULL) THEN
                          

Open in new window

0
OWASP: Forgery and Phishing
LVL 12
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

This is SQL 2014.

I have a table with 3,000,000 rows something. It has lots of columns and we're thinking about using this flat file as our search table.

Can I have Full Text Search applied to this table only and not affect the rest of the database? My manager says something about FTS is not portable and will be a hassle to port to production database.

We do wildcard search on 6 of the columns
0
sql server 2012 when i login as below it shows some values which are unclear to me under server name

how to make sure to put server names some meaningful names to understand

instead of say
CXXXXX1234.xy.LMP.com,99999
i like to give alias name like
Production Database
or
System Test
etc

so that i do not need to refer every time when i login to check documentation to see where i am making changes etc

also how to connect multiple environments same time paralleled like production and System Test, integration etc
do i need to open microsoft sql mgmt tool multiple times or can i open separate tabs in one instance one underneath other?
Where can i refer for good usage tips and learning? any good vidoes or resources around this

what is difference between
windows authentication
and
sql server authentication
when to use which one

please advise
SQLServer2012_NAmeEnv.png
0
I upgraded my computer to a clean install of windows 10 and cannot install sql server 2005. I keep getting the message that  sql will not start...

I am using Visual studio 2005.

Thanks for your help!
0
Hi All,

I have below code:
	 --IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL 
	 if OBJECT_ID('tempdb..#TEMP') is not null
	 BEGIN
DROP TABLE  #TEMP
END

 

	if 'YASMINE 1017 1548' = ''
	BEGIN
	 
	   SELECT  
	  DISTINCT
	  BarangCode
	  INTO #TEMP
	  FROM TMSTOKBARANGDETIL2018 A
	  WHERE NoTransaksi = 'BS 20180101' 

	END
	ELSE
	BEGIN

	 

	   SELECT  
	  'YASMINE 1017 1548' AS BarangCode
	  INTO #TEMP
	   
	   		 DROP TABLE #TEMP
  
		

Open in new window


I get :

Msg 2714, Level 16, State 1, Line 31
There is already an object named '#TEMP' in the database.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '#TEMP'.

What is the problem ?

Thank you.
0
I wanted to use a substitution variable to list only three departments. I am using the IN clause to list the departments.  I want to only display 3 departments. Meaning select 3 departments from the list to display on the report.

select d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name,
sum(e.SALARY) Total
from employees e left outer join departments d
on(e.department_id = d.department_id)
where e.department_id IN(10, 20, 30, 40, 50, 60, 70, 80, 90, 10, 110, 220, 330, 440, 450, 455, 456)
group by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name
order by d.department_id, d.department_name, d.manager_id,d.location_id, e.first_name, e.last_name
0
hi experts

i have two query's
--query UNO
SELECT [ERDAT     ]
      ,A.[TRDAT     ]
	  ,[Tipo usuario contractual     ]
      ,[Texto       ]
FROM [SMI].[dbo].[01102018_01_USR02] A 
		JOIN [SMI].[dbo].[01102018_USMM] B	ON A.[BNAME       ] = B.[Usuarios    ]
		JOIN [dbo].[02102018_USR05] C ON B.[Usuarios    ] = C.[BNAME       ]
WHERE  [UFLAG] NOT IN (192,64 ) AND
		[PARID                 ] like '%BUK%'
		and [Texto       ] = 'Diálogo     '
		AND try_convert(datetime, [ERDAT     ], 103) > try_convert(datetime, '31.10.2017', 103)
order by try_convert(datetime, [ERDAT     ], 103) 

--QUERY DOS
SELECT BNAME, [BUK], [BZPNR]
FROM (
		SELECT * FROM [dbo].[02102018_USR05]
	 ) AS Data
PIVOT
(
	MAX(PARVA)
	FOR PARID IN ([BUK], [BZPNR])
) AS PVT_Data

Open in new window


How can I join the queries to have a single result?
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.