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

I have two SQL server which I need to determine their SQL versions.  Here is what I got using the sql@@version command.  

Can someone suggest a good weblink to look them up?


Microsoft SQL Server 2008 (SP3) - 10.0.5538.0 (X64)
                Apr  3 2015 14:50:02
                Copyright (c) 1988-2008 Microsoft Corporation
                Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)
 

Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)
                Jan  5 2018 22:11:56
                Copyright (c) Microsoft Corporation
                Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
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
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
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
Hello

In order to backup properly my SQL Database I need to create 2 batch files which will run on 2 scheduled task and will do the following tasks

1)      Daily at 23h PM : STOPSQL.BAT : Stop SQL SERVER Service and Stop SQL Server Agent Service
2)      Daily at 6h AM: STARTSQL.BAT: Start SQL SERVER Service and Start SQL Server Agent Service

What is the recommended solution for the content of these 2 batch files?
Thank you
0
i'm using SQL Server 2008.

I have this table called Players which looks like this.
It's a list of players that play in either major league baseball, national football league or national basketball league.

Players table
Notice that it has a column called CoachLeague. That determines what league each player plays in.
Then, if you also notice the column called CoachID, that is the ID of that player's coach.

This is the script to create the Players table:

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Players](
	[PlayerID] [int] NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[CoachID] [int] NULL,
	[CoachLeague] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (1, N'Bryce', N'Harper', 15, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (2, N'Madison', N'Bumgarner', 13, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (3, N'Pablo', N'Sandoval', 13, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (4, N'Clayton', N'Kershaw', 6, N'MLB')
INSERT [dbo].[Players] ([PlayerID], [FirstName], [LastName], [CoachID], [CoachLeague]) VALUES (5, N'Lebron', N'James', 14, N'NBA')
INSERT [dbo].[Players] ([PlayerID], [FirstName], 

Open in new window

0
Hi EE,

How do I write the SSIS logging output to a DB on my server, there is someone wrong in the connection string I am using:

I am using SQL server 2008 R2 SSIS.

Any assistance is welcome.

Thank you.
Capture.PNG
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.