Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 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. Major improvements include the  Always On technologies and support for unstructured data types.

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

Sign up to Post

Using SQL Server 2008

This is a follow up to this example:
https://www.experts-exchange.com/questions/29134468/t-sql-display-query-result-as-list-of-comma-separated-values.html?anchor=a42789277¬ificationFollowed=222867761#a42789277

In that example

I had this table:

Use [Northwind]
create table Employee (EmployeeID int, LastName varchar(50))
insert into Employee values (1,'Davolio'), (2,'Fuller'), (3,'Leverling'), (4, 'Peacock'), (5,'Buchanan'), (6, 'Suyama'),(7,'King'),(8,'John'),(9,'Peter')

Open in new window



The input was a comma separated string list of LastName

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio,Fuller,Leverling,Peacock,Buchanan,Suyama,King,Callahan,Dodsworth'
------------- Input -------------


The solution was this query. The output of this query is a list of EmployeeIDs of those last names.

------------- Input -------------
DECLARE @LastName varchar(2500)
SET @LastName = 'Davolio,Fuller,Leverling,Peacock,Buchanan,Suyama,King,Callahan,Dodsworth'
------------- Input -------------
------------- Query -------------
;with cte1 as (
select ltrim(SUBSTRING(LastNames, n, CHARINDEX(',', LastNames + ',',n) - n)) AS LastName
  from (select @LastName as LastNames) t1
  CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING(',' + LastNames, n, 1) = ','
  AND n < LEN(LastNames) + 1) --select * from cte1
select distinct 

Open in new window

1
I'm using sql server 2008.

I'm using the Employees table from the Northwind database.

Right now i have this query where I input a comma separated string of last nams as input and it returns those employees.


Query 1

-- input is comma delimited list
DECLARE @EmployeeName varchar(2500)
SET @EmployeeName = 'Davolio,Fuller,King,'
-------------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[LastName] + ' ' + [FirstName] AS FullName
FROM [Northwind].[dbo].[Employees]
WHERE( nullif( @EmployeeName, '') is null or (',' + @EmployeeName +',' LIKE '%,' + [LastName] + ',%'))
ORDER BY [LastName]

Open in new window


When I run this query i get this:

Query 1 output

Query 2

How do i fix the Query 2 below, so my input could be part of the last name?

So if I input this I should get the same result as Query 1.

-- input is comma delimited list of partial last names
DECLARE @EmployeeName varchar(2500)
SET @EmployeeName = 'Dav,Ful,Kin,'
-------------
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[LastName] + ' ' + [FirstName] AS FullName
FROM [Northwind].[dbo].[Employees]
WHERE( nullif( @EmployeeName, '') is null or (',' + @EmployeeName +',' LIKE '%,' + [LastName] + ',%'))
ORDER BY [LastName]

Open in new window

0
Hello Experts,
I am trying to create table which should contain Column Name, Data Type and Length from another Table.  Please see the example below:

Table - 1(Employee): Record Count (10)
---------------------------------------------------------
1. Employee_Name Varchar(50)
2. DateOfBirth Date
3. Salary Int
4. Active char(1)

Table - 2 (Temp)
--------------------------
1. Column_Name - any text data type say Varchar(50)
2. Data_Type - any text data type say Varchar(20)
3. Length - any int data type say Int

Data (in Table -2)
---------------------------
Employee_Name, Varchar, 50
DateOfBirth, Date, 8
Salary, Int, 10,2
Active, Char, 1

Basically I am just getting the structure from Table-1 in Table-2 in the form of columns.  Please let me know if this something possible.

Thank you in advance.

Thank you.
0
Hi
SQL Server - I have a table with over 70 million records. It will be queried mainly by date (non unique) and then by Company and maybe another category. What is the most efficient way to index this table. This table has no primary key and is queried without any joins to other tables.
Regards
0
I’m using sql server 2008.

In a table I have a smallmoney datatype column.

Values in that column look like this:

Column1
1.54
0.00
8.76
6.87
0.00
9.87


If I use this if a value is null it displays it as blank
ISNULL(Column1, ‘’) as column

What’s the syntax for if a value in that column is 0.00 then display it as blank?
0
I'm using the Northwind database  in sql server 2008.

-- Janet Leverling
UPDATE [Northwind].[dbo].[Employees]
SET [Title] = 'Lawyer'
WHERE [EmployeeID] = 3

-- Robert King
UPDATE [Northwind].[dbo].[Employees]
SET [Title] = 'Doctor'
WHERE [EmployeeID] = 7

I have two update statements like this.
When I run them individually they work fine.

I want to put these in a stored procedure, so when I run the stored procedure both records get updated.

Is there a syntax to combine both these statements into one statement?
0
I have set up the server 2017 mirroring. And it does not have a witness server.
I have seen the below youtube video to know how to recover the DB during failover scenarios. Regarding this, I have gone through some articles too.

https://www.youtube.com/watch?v=HyGzgzRQCcI

If you want to use the mirror database you need to have transaction log backup from the primary server.  If you have multiple transaction log backups then you need to restore one by one in mirror server to activate.   How to start the endpoint (mirror DB)  if not able to recover transaction log backup from the primary?
0
Hello,

Need a suggestion on how to use the join in the following example.

Thanks in advance.
Example5.xlsx
0
I'm using SQL Server 2008

I was looking at the RAISERROR documentation on this page:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017

So the syntax is this:

syntax
the first part is string message, then severity, then state

Then towards the bottom of the page it has this example with severity of 10 and state of 1:

example
What does a severity of 10 mean?
What does a severity of 16 mean?
What does a state of 1 mean?
0
This is SQL 2014 and it's related to the question here
https://www.experts-exchange.com/questions/29129110/Replace-works-but-is-there-a-better-way.html

And this https://www.experts-exchange.com/questions/29129724/Result-of-these-2-SQLs.html  (I got the "exists" line of code from this solution)

I'm using the solution in that question but wondering why I'm not getting the correct number of rows.

1. Please see attached for a sample data. It creates a table with some data in it.

2. This is the code I'm using. I've noted in it which one is correct and which one is not correct. I don't know what I'm missing.

DECLARE @results TABLE(SearchText NVARCHAR(4000))
DECLARE  @searchtext nvarchar(4000)
DECLARE  @searchtext2 nvarchar(4000)

SET @searchtext = '%019%'
SET @searchtext2 = '%O19%'

INSERT INTO @results --*************  0 and O C# code has for SerialNo. tfs892
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

	SELECT * FROM @results

SELECT * FROM dbo.camillatest u
WHERE 
exists (Select 1 from @results r WHERE u.SerialNo LIKE @searchtext) --**** the solution I'm using. It brings back 27277 but it should be 27340
--(u.SerialNo LIKE @searchtext OR u.SerialNo LIKE @searchtext2) -- 27340 correct rows 
--(u.SerialNo LIKE '%019%' OR u.SerialNo LIKE '%O19%') -- 27340 correct row

Open in new window

sample-data-for-ee.txt
0
I have the below Purchase receipt table with these rows

Order #    Receipt #          DateTime
1233           2345                2018-04-04 13:21:55.153
1233           2346                2018-04-04 13:22:01.410
1067           2834                2018-04-03 12:18:01.410
1067           2835                2018-04-03 12:18:52.410
1067           2839                2018-04-03 12:21:52.410

I need to find out the receipt numbers using the SQL query to list the rows generated less than the one-minute time difference for the same purchase orders.
The output should be like this
Order #   Receipt #
1233         2345
1233         2346
1067         2834
1067         2835

the above receipts generated  for the same order # less than one-minute time difference. Please let me know how to find out using the SQL query.
0
I have set the sal server agent job to 10:00 PM Monday or any other schedule but the job is not triggering. The agent schedule is enabled but the job is not starting at all. If I run the job manually the job works fine.
0
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
Hi,

I have two SQL Server 2008 R2 running under database mirroring Synchronized mood. Mirroring is working fine. I noticed the log file is growing big although TLog backup is working and there are no any open active transaction. I have 21 GB left out of 100 GB in the log drive.  And also 99.9 % log space is unused and 0.1% is used.

Please advise what necessary steps should I take in order to release the log.

Thanks

Zahid
0
SQL server agent log reports:  [355] The mail system failed to initialize; check configuration settings and

107: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database. And 8194: the client-side extension could not remove computer policy setting for ' ' because it failed with error code '0x8007000d The data is invalid.' See trace file for more details. This is a production server and cannot be rebooted during working hours.
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
What is the difference between press and hold shift key and click on SSMS executable and directly clicking on SSMS executable and enter the same credential?
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 inherited a very old and poorly written stored procedure with nested fetch statements. This code processes a large amount of data by copying from one database into another. This process was working fine until updates were made to indexes. This past weekend the process was deadlocks on tables and failed to insert data. Added to this bad stored procedure the database we are inserting into is loaded with multiple triggers on just about every table.

I have reverted the indexes back to where they were but the inner fetch stays in an infinite loop. We have also tried rebooting the server to clear out any data stuck in memory. We are running SQL Server 2008R2

I can provide code if needed. Any help is greatly appreciated. We are impacting production at this point.
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
What do i need to do to download a free version of SSIS?
0
MS SQL Server 2008 R2

I am helping a friend with some database issues and one of the issues is duplicate indexes.  Many of the tables have indexes like this...

PK_Index = ID (CLUSTERED)
IDX_1 = ID with City, State as included fields
IDX_2 = ID with City,State,Zip as included fields

I say you can delete IDX_1 since IDX_2 is the same with an added field in the included list.  He says no, he needs to keep all three.

What is the best practice when you have indexes that are very similar but vary by the included fields?

Thanks!

Jim
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

Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 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. Major improvements include the  Always On technologies and support for unstructured data types.