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

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
My manager is really good at SQL, so I want to explore another way of writing this SQL before I show him what I have.

This is SQL 2014

This works with temp tables:

--create the temp tables
CREATE TABLE #unitsearch
 (
   rowid int
 )


  CREATE TABLE #schedsearch
 (
   rowid int
 )

 -- insert into first temp table

INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
  rowid

FROM

	 dbo.camillasearchtest AS u

WHERE ....

--insert into second temp table
INSERT INTO #schedsearch
(
    rowid
)
SELECT rowid
FROM dbo.camillasearchtest s
WHERE ......

-- delete any dups
DELETE FROM #schedsearch
WHERE rowid  IN (SELECT rowid FROM #unitsearch) 

---- get the result by using UNION

SELECT 
*

FROM dbo.camillasearchtest
WHERE rowid IN (SELECT rowid FROM #unitsearch)

UNION
*

 FROM dbo.camillasearchtest
WHERE rowid IN (SELECT rowid FROM #schedsearch)

Open in new window


With CTE, I can do the select but how can I do the insert and delete and the union join?

-- first table
 WITH UnitSearch (rowid)
AS
(SELECT 
  rowid

FROM

	 dbo.camillasearchtest AS u

WHERE .....

SELECT  rowid
FROM   UnitSearch

----
WITH SchedSearch (rowid)
AS
(SELECT 
  rowid

FROM

	 dbo.camillasearchtest AS u

WHERE .....

SELECT  rowid
FROM   SchedSearch

--- now how do I delete the dups?

--now how I use Union?

Open in new window

0
I am filtering a table with a SQL view, my date column is datetime the data looks like this: 2018-11-19 00:00:00.000

How do I filter for Yesterdays date?

= GETDATE() - 1 returns no records.
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
Hi,

using SQL server 2017:

Basically what I am trying to build is a base query that I have, but I need it to output 1 line of data per month - going back for a a set date in time.

ie this is what I am hoping to have in one query somehow:

query 1/line 1
select count(de_owing), sum(de_owing), count(de_paid), sum(de_paid) from debtors where de_listeddate between '2017-11-01' and '2017-11-30'

query 2/line2
select count(de_owing), sum(de_owing), count(de_paid), sum(de_paid) from debtors where de_listeddate between '2017-12-01' and '2017-12-31'

right through to current month.

is there a way that it could group by the month and year, or another more elegant way of doing it so that i wouldn't have to keep adding a new query for each new month?

any help you could provide would be greatly appreciated.

many thanks!
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
in excel how to represent below date and time stamp as it is like in database cell

2018-11-13 06:13:15.891


when i copy past it shows as 13 or something

how to import sql server results value including heading to excel without loosing date formatting etc
any tips or tutorial videoes around this?

Please advise
0
I need to revoke select on all tables in a database (about 400 tables) but only for certain users.  I have a table that lists the usernames that need to be processed.

Currently I have to do it one at a time like this:

SELECT 'REVOKE select ON ' + SYSOBJECTS.NAME + ' FROM ' + SYSUSERS.NAME
FROM SYSOBJECTS, SYSUSERS
WHERE SYSOBJECTS.XTYPE in('u','v') AND SYSUSERS.NAME = 'one_username'
ORDER BY SYSOBJECTS.NAME
go


How would I loop through a list of usernames stored in a table?
0
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
Has anybody used Azure Site Recovery to protect a SQL Server 2008 standard edition cluster?

I am just trying to wrap my head around how the data gets sync'ed. Typically a hot data synchronization is an enterprise feature however this article states that ASR can be used to protect a SQL Server 2008 SE fail over cluster.

I don't see any details on how the data sync works though.
- https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-sql

Can anybody share their experience with Azure Site Recovery specific to SQL Server 2008 standard edition.

Thanks experts!
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
Guys,
I have a situation where  I need to migrate / copy existing  logins from windows authentication to - sql authentication. Every windows  Logins  has specific server role, database role and attach to  schema. So  in order to copy this login and again create them as sql logins, do you guys able to recommend a method of doing this ? currently this sql server has around 2000 actives windows login that I must convert them to sql authentication.
0
I need help with the code below.

Business Need - for any procedure greater than or equal to 7, I need to look back at the procedures 1 - 6 and only use the values used in this result set for the procedures greater than or equal to 7. I am only concerned with the PatientVisitDiags1 through PatientVisitDiags9 fields.

If you look at my screenshot, it helps visualize my need better. In this screenshot, CPTCode 76000 was used and is in the 'ListOrder' = 7, so it applies for this rule. For this CPTCode, the doctor used the following:

PatientVisitDiags1 = 3
PatientVisitDiags2 = 4
PatientVisitDiags3 = 5
PatientVisitDiags4 = 8
PatientVisitDiags5 = NULL
PatientVisitDiags6 = NULL
PatientVisitDiags7 = NULL
PatientVisitDiags8 = NULL
PatientVisitDiags9 = NULL

Because "5" was not used in any of these fields in ListOrder 1 through 6, I need to eliminate it on the procedures 7 and greater.

I need 76000 to have this:

PatientVisitDiags1 = 3
PatientVisitDiags2 = 4
PatientVisitDiags3 = 8
PatientVisitDiags4 = NULL
PatientVisitDiags5 = NULL
PatientVisitDiags6 = NULL
PatientVisitDiags7 = NULL
PatientVisitDiags8 = NULL
PatientVisitDiags9 = NULL


SELECT
     pvp.ProceduresId
   , pvp.CPTCode
   , pvp.ListOrder
   , pvp.PatientVisitDiags1
   , pvp.PatientVisitDiags2
   , pvp.PatientVisitDiags3
   , pvp.PatientVisitDiags4
   , pvp.PatientVisitDiags5
   , pvp.PatientVisitDiags6
   , pvp.PatientVisitDiags7
   , pvp.PatientVisitDiags8
   , 

Open in new window

0
Hi All, I am running replication in one of my environment. I am facing blocking issue. I observed table size for MSmerge_tombstone is too much large. Table count is 3184806.
Is there anyway we can purge this table. Is there any dependency?

Regards
Abdul Wahab
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.