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

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
Acronis True Image 2019 just released!
LVL 1
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

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
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
CompTIA Network+
LVL 12
CompTIA Network+

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

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
Hi,

Thank you for taking a look at my query.

Hopefully it is an easy one!!  basically this part of the select clause from below ",dateadd(month, datediff(month, 0, debtor.de_listed_date), 0) as listed_month"

it is coming out correctly, but what I would like on the next select clause is the same thing but the date for the end of the month rather than the start, is this possible?  I tried
adding:

dateadd(day,-1,dateadd(month,datediff(month,0,debtor.de_listed_date)+1,0))

but it barks saying it needs to be part of a grouping or aggregate but I can't see how I would do that.

;
with cte as (
select tr.tr_rowid_debtor, (tr_to_agency+tr_to_client) as tr_amnt, tr_commission_amount, tr_posted_date
from de_transaction as TR
where tr.tr_account = 1
)
select cl_alt_number,
cl_number
,dateadd(month, datediff(month, 0, debtor.de_listed_date), 0) as listed_month
,sum(debtor.de_principal+debtor.de_fees) as listed_amt
,sum(case when debtor.de_status in ('PAID','TPAI') then 1 else 0 END) as Closed_count
,sum(case when debtor.de_status in ('PAID','TPAI') then debtor.de_paid else 0 END) as Closed_Paid
,count(debtor.de_rowid) as Debtor_count
,sum(case when debtor.de_active = 'A' then 1 else 0 END) as Active_count
,sum(case when debtor.de_active = 'A' then debtor.de_owing else 0 END) as Active_owing
,sum(case when debtor.de_status in ('WITH','CANC','REVR','WOFF','EXP') then 1 else 0 END) as Withdrawn_count
,sum(case when debtor.de_status in 

Open in new window

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
I am looking at whether my update below is the most effecient way of running the update
Both are populated from live data
@tableROllAPoll will generally have 500-2500 records
@tableActiveRecords will have 300,000+ records

I have the @table structures included as well
DECLARE @tableROllAPoll TABLE
(
    IndividualID BIGINT,
    matchname VARCHAR(1000),
    matchaddres VARCHAR(1000),
    matchEmail VARCHAR(1000),
    HomeNumber BIGINT,
    Mobile BIGINT,
    matchOP VARCHAR(1000),
    MatchAll_IndividualID BIGINT,
    NamePhoneEmail_IndividualID BIGINT,
    NamePhoneAddress_IndividualID BIGINT,
    NameAddressEmail_IndividualID BIGINT,
    PhoneAddressEmail_IndividualID BIGINT,
    NamePhone_IndividualID BIGINT,
    NameEmail_IndividualID BIGINT,
    PhoneEmail_IndividualID BIGINT
);


DECLARE @tableActiveRecords TABLE
(
    OriginID INT,
    OriginName VARCHAR(1000),
    IndividualID BIGINT,
    matchname VARCHAR(1000),
    matchaddres VARCHAR(1000),
    matchEmail VARCHAR(1000),
    HomeNumber BIGINT,
    Mobile BIGINT
);

Open in new window



UPDATE rp
SET rp.NamePhoneEmail_IndividualID = namephoneemail.IndividualID
FROM @tableROllAPoll rp
    JOIN @tableActiveRecords namephoneemail ON rp.matchname = namephoneemail.matchname
                                               AND rp.matchEmail = namephoneemail.matchEmail
                                               AND rp.Mobile = namephoneemail.Mobile
                                               AND rp.Mobile > 0;

Open in new window

0
Hi Experts,

Can anyone tweak this code, below, to work with a negative date input to subtract business days from a given date?

Thanks,
Steve

ALTER function [dbo].[DateAddWorkDay]
(@days int,@FromDate Date)
returns Date
as
begin
declare @result date
set @result = (
select b
from
(
    SELECT
    b,
       (DATEDIFF(dd, a, b))
      -(DATEDIFF(wk, a, b) * 2)
      -(CASE WHEN DATENAME(dw, a) = 'Sunday' THEN 1 ELSE 0 END)
      -(CASE WHEN DATENAME(dw, b) = 'Saturday' THEN 1 ELSE 0 END)
      -COUNT(o.Holiday) 
      as workday
    from
    (
    select 
    @FromDate as a,
    dateadd(DAY,num +@days,@FromDate) as b
    from (select row_number() over (order by (select NULL)) as num
          from Information_Schema.columns
         ) t
    where num <= 100 
    ) dt
    left join Holiday o on o.Holiday between a and b and DATENAME(dw, o.Holiday) not in('Saturday','Sunday') 
    where DATENAME(dw, b) not in('Saturday','Sunday')
          and b not in (select Holiday from Holiday where Holiday between a and b) 

    group by a,b
) du
where workday =@days 


)
return @result 
end

Open in new window

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.