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 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
Scenario:

Table : Supp
Columns:
AppUser
SQLUser
DateCreated
DateModified
SuppCode
SuppDesc
Address
SuppType

Whenever application changing 'SuppDesc' column data in 'Supp' Table, we need details include hostname (user modifying application from his desktop) through email alert.

Version : SQL Server 2008 R2.

Please help me with solution.

Thanks,
Chandra
0
Hi,
  Does anyone have experience in HP ALM Quality Center version 11?  The previous tech left so there is little info. about this setup with no support contract.
 Currently everything for Quality Center (JBOSS, Microsoft SQL Server 2008 R2, Win 2008 R2 Ent. ) is installed in one VM and it's one serves this function.

Any recommendations on a backup and restore plan should anything goes wrong with this server or on the HP ALM Quality Center version 11 application?
qc.jpg
0
I Need to alter an extended stored procedure in SQL 2008R2.  Pretty much exactly like here: https://www.experts-exchange.com/questions/21081340/Microsoft-SQL-System-Extendend-Stored-Procedure-Overwrite-sp-executesql.html 
Though that ended up without a solution.

I need to use the same name as an Existing stored procedure though.

I want to be able to test for a parameter to the Stored procedure and Exit if it contains a keyword. If it does not, then I want to execute the original SP as usual.

So something like:

sys.sp_executesql  -> sp_origexecutesql

New sys.sp_executesql  
      if @P1 = "Select * from NoNoTable" return NULL
      Else exec sp_origexecutesql @P1

I have SysAdmin Access to the SQL Server, so I can make any changes there. I can Create my Own DLL Code if needed too. I just don't have access to all of the Code that is running against the SQL Server and I want to Intercept some of the sp_executesql Commands.

Thanks,
0
I had to change my login in active directory.  After doing that all of my SSRS (2008 R2) subscriptions wouldn't run because the previous AD entry didn't exist.  I had a tool to make those changes, but I still have the same issue when I create a new subscription.  It says my AD login isn't recognized.  How do I change that?  I've googled it and found some hits, but nothing quite explains how to fix the issue for new subscriptions.  Can I just go to the users table and change the old login to the new login?
0
we have sccm and scom 2012 r2 running on windows 2008 r2 as the OS. database on a different server running windows 2008 r2 and sql 2008.

we want to upgrade the OS of sccm, scom and sql servers

we want to upgrade sccm and scom to system center cb 1802 or later.

it is best to install fresh deployments of all and try and migrate data.

or look at the option of inplace upgrade.
0
Database Migration from HP Storage to DELL EMC:
After renaming of SQL Server 2008 R2 Default instance, We are unable to connect Management Studio from remotely for Windows Authentication users. It is working fine for SQL Server Authentication users.

Error: The target principal name is incorrect. Cannot generate SSPI context.

Meantime for SQL Server 2014 it is working fine.

Scenario 1:
Server : SQLCL (SQL Server 2008 R2) - We have build new server with the name as SQL2 then migrated the databases, added alias name as SQLCL. In this scenario, we faced same mentioned above error.
Server:  DBCL (SQL Server 2014): We have build new server with the name as SQL1 then migrated the databases, added alias name as DBCL. In this scenario, we have no issues.

Scenario 2:
Again We have renamed the server from SQL2 to SQLCL and SQL1 to DBCL but Still we are getting same error for SQL Server 2008 R2 version installed.

But we have no issues jobs and every thing fine. Please help us.

Thanks,
Chandra
0
How would one pull out the numeric text from within a varchar field?  For example, I need to write a formula to determine overall dosage a patient could take in a given day.  The instructions for the medication is stored as one long string like;

take 1 tablet by oral route  every 4 hours as needed

Open in new window


I'd like to pull out the first numeric entry from the left and place it into a column named 'Quantity' and the first numeric entry from the right and put it in a column named 'Frequency'.
I'd then multiply this by the dose to determine the overall usage of a medication.  Here is my current query;

select distinct p.person_id as [PID], p.last_name as [Last Name], p.first_name as [First Name], CAST(p.date_of_birth as DATE) as [Date of Birth], 
pm.medication_name as [Medication],pm.sig_desc as [Sig],  pm.start_date as [Start Date] from patient_medication pm
JOIN person p ON p.person_id = pm.person_id
JOIN fdb_medication f ON f.medid = pm.medid
where medication_name  like '%hydromorphone%' and p.expired_ind <> 'Y' and  (pm.date_stopped > GETDATE() or pm.date_stopped = '')
order by p.person_id 

Open in new window


Thanks for any help!
0
Hello,
How to set a column which is [Ref] [int] IDENTITY(1,1) NOT NULL to 1 . I tried to delete all rows in the table but still
when I insert a row in the table , theRef value is 33.

Regards
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
Help with SQL SERVER 2008R2 view or stored procedure.

I have a view (CT_Work_Order_Warehouse_Headers_Pass_02) that the output looks like this:
BASE		LOT		SPLIT		SUB		SEQ		WAREHOUSE
CC181137.3		21		0		0		5		HOUMA
CC181137.3		21		0		0		10		TBD
CC181137.3		21		0		0		20		TBD
CC181137.3		21		0		0		30		PORT_ALLEN
CC181137.3		21		0		0		40		TBD
CC181137.3		21		0		0		50		HOUMA
CC181137.3		21		0		0		60		TBD
CC189999.3		1		0		1		10		PORT_ALLEN
CC189999.3		1		0		1		20		TBD
CC189999.3		1		0		1		30		TBD
CC189999.3		1		0		1		40		HOUMA
CC189999.3		1		0		1		50		TBD
CC181234		1		0		1		5		PORT_ALLEN
CC181234		1		0		1		10		TBD
CC181234		1		0		1		20		TBD

Open in new window


I need a view or stored procedure to make the data look like this:
BASE		LOT		SPLIT		SUB		SEQ		WAREHOUSE
CC181137.3		21		0		0		5		HOUMA
CC181137.3		21		0		0		10		HOUMA
CC181137.3		21		0		0		20		HOUMA
CC181137.3		21		0		0		30		PORT_ALLEN
CC181137.3		21		0		0		40		PORT_ALLEN
CC181137.3		21		0		0		50		HOUMA
CC181137.3		21		0		0		60		HOUMA
CC189999.3		1		0		1		10		PORT_ALLEN
CC189999.3		1		0		1		20		PORT_ALLEN
CC189999.3		1		0		1		30		PORT_ALLEN
CC189999.3		1		0		1		40		HOUMA
CC189999.3		1		0		1		50		HOUMA
CC181234		1		0		1		5		PORT_ALLEN
CC181234		1		0		1		10		PORT_ALLEN
CC181234		1		0		1		20		PORT_ALLEN

Open in new window


Note: Each starting SEQ will have a non 'TBD' warehouse for each different Base/Lot/Split/Sub
I know i need to go get the WAREHOUSE from the SEQ <= the current SEQ where it is not = 'TBD'

any help would be greatly appreciated.
0
hi experts

i have this query
SELECT
    [BNAME       ]
,      [PARID                 ]
      ,[PARVA                                   ]
  FROM [SMI].[dbo].[02102018_USR05]
  WHERE [BNAME       ] LIKE '%RLARA  %'

this is the results
BNAME             PARID                       PARVA                                  
RLARA             BUK                         1000                                    
RLARA             BZPNR                       13271                                  
RLARA             FIT_ALV_AR                  /CLIENTE EXT                            
RLARA             LE_SHP_DEL_MON_LISTT        HC                                      
RLARA             MOL                         PE                                      
RLARA             UGR                         PE                                      

but i need this
BNAME             BUK                         BZPNR                
RLARA             1000                      13271
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.