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

Hello,
I have a stored Procedure.
Need to change the query
DECLARE @SQL VARCHAR(MAX) = ''

 SET @SQL = ' SELECT * FROM  table1 WHERE'

		
SET @SQL = @SQL+ '  [FinishDate] <= ''' + today() +''''	

SET @SQL = @SQL + ' ORDER BY TrDate ASC'

  

Open in new window


query condition is   [FinishDate] <= ''' + today() +''
Any suggestions are welcome.
0
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

We have many dynamic sql stored procedures in our system

I am finding that on "search style" stored procedures I am running into issues where the DATA has a single quote in it
The parameter has an apostrophe has a single quote in it
But the dynamic SQL does an escape on the EXEC (@SQL)
Is there a "good way" to handle this?


Example...
Here is some code
DECLARE @SQL VARCHAR(MAX) = '';
DECLARE @param VARCHAR(10) = 'a''.com';
--SELECT @param;

SET @SQL = @SQL + 'select * from users where Username = ''' + @param + ''' order by username ';
PRINT @SQL;
EXEC ( @SQL );

Open in new window


And here is the message on execute
Screenprint
0
Update Stats with Fullscan is taking too much time to complete (more than 400 Mins) for a DB Size of 150GB.
How can I resolve this issue ?
0
How would I select records from a table that are NOT the first of the month?


--I want the records that are not the first of the month for daterecord
Select Name, DateRecord
FROM Table
0
I need get the list of instructors who do not have any classes between a given date and time .

I got the code but it does not work

SELECT DISTINCT U.userKey , U.firstName , U.lastName , U.email 
FROM users U WITH (NOLOCK) 
INNER JOIN user_securityRole USR WITH (NOLOCK) ON U.userKey = USR.userKey 
INNER JOIN securityRole_lkup_permission SRLP WITH (NOLOCK) ON USR.securityRoleKey = SRLP.securityRoleKey 
INNER JOIN lkup_permission LP WITH (NOLOCK) ON SRLP.permissionKey = LP.permissionKey 
WHERE U.enable = 'Y' 
AND LP.permissionCd = 'INS' 
AND EXISTS (SELECT 1 from instructors I WITH (NOLOCK) where deleted = 0 and I.instructorKey=u.userKey) 
AND EXISTS (SELECT 1 from userInstructorLicense UIL WITH (NOLOCK) 
where UIL.userKey = u.userKey and DATEDIFF(DAY,UIL.expirationdt,'2018-07-19 14:00:00.0')< 0 
and UIL.availStateKey = 1) 

and NOT  EXISTS ( 
	SELECT 1 FROM Session S WITH (NOLOCK) 
	INNER JOIN SessionUnit SU WITH (NOLOCK) ON S.SessionKey = SU.SessionKey 
	inner join sessionMap SM WITH (NOLOCK) on SM.sessionKey= S.sessionKey 
	WHERE su.instructorKey = U.userKey AND  (('2018-07-19 14:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) 
OR ('2018-07-19 16:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) ) ) 

ORDER BY U.lastname , U.firstname

Open in new window

0
As part of our failover process in SQL Server 2008 R2 we need to change the registry on our sharepoint servers so they are pointed to the right SQL box on the mirror. This has worked fine until we created 2 vm sharepoint boxes.  The step that changes the registry is in a text doc for each sharepoint box and is accessed using  Operating System (CmdExec) with 'regini -m \\SHP01 d:\scripts\SwitchToAppN1_SHP01.txt '  . With the VMs  the command is regini -m \\VMPRDWEBAPP01 d:\scripts\SwitchToAppN1_VMPRDWEBAPP01.txt. Error is
Executed as user: ########. REGINI: SetValueKey (CLSQLN1\Applications) failed (5)  REGINI: Failed to load from file 'd:\scripts\SwitchToAppN1_VMPRDWEBAPP01.txt' (5).  Process Exit Code 1.  The step failed.

Searches said this has to The problem was w ith permissions on the remote server.  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurePipeServers\winreg so we have added permission to the reg key for the user/svc-account but are still getting the error.

Any help would be appreciated.
0
We have a SQL and Windows Server 2008 R2, 16GB RAM and 16383 MB for total paging file size in total drives.
C drive is 100 GB
D Drive is 200 GB

Takes forever to login in. It is a virtual vm.

Do you think the paging file is set wrong ?

Thanks for your help
0
Why does this return 13 records?  I just want it to return the first 12.  I can I make that happen?

 WITH _DateList ([DATE])
 AS (SELECT      DATEADD(MONTH, -12, DATEADD(month, DATEDIFF(month, -1, GETDATE()), 1)) [DATE]
     UNION ALL
     SELECT DATEADD(MONTH, 1, [DATE])
     FROM _DateList
     WHERE [DATE] < CAST(GETDATE() as DATE)
    )

SELECT * FROM _DateList

Open in new window

0
How do I format a date to look like this?
0
Sql server 2012 running on
node1, node2 running on fail over cluster manager windows 2012 and shares the same disk, node1 is active..


1. How do i plan to install new patches(kb,Sservice packs,etc) of SQL for the both nodes?
as some sql patches requires restart? I need to patch on both nodes?

2. If change any database settings in node2 or node1, will it replicate each other?

3. what precautions and planning i need to, during changes on the Sql servers. Pls suggest

4. what about if any windows updates or any windows patches needs to restart?
0
Free Tool: Path Explorer
LVL 12
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

This tutorial is a great guide for newbies in SQL Server who never backup the database and for people with some experience, because you will learn some tips that you may not know.
1
a Job is continously failing, it is transcational log backup

atabase Task Execute SQL Task     Description: Executing the query "BACKUP LOG [xxx] TO  DISK = N'F:\MSSQL11.MSSQLSE..." failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information.  BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  Warning: 2018-05-12 11:00:19.53     Code: 0x80019002     Source: User DB Transaction log backup      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:00:00 AM  Finished: 11:00:19 AM  Elapsed:  19.125 seconds.  The package execution failed.  The step failed.

any idea, what needs to be done


USE [msdb]
GO

/****** Object:  Job [IBM DBA.User DB Transaction log backup]    Script Date: 12/05/2018 8:07:19 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 12/05/2018 8:07:19 PM ******/
IF NOT EXISTS (SELECT …
0
My current logshipping configuration is as below

Primary  DB Server - Sql 2008R2

Secondary DB server - Sql 2012   -   ( tried first with stand by mode  & also with  Restoring mode)

logshipping backup job is working fine -  trn logs are getting created in shared folder
logshipping copy job is  working fine -- trn logs are getting copied to local folder in secondary server  from the shared folder in primary
logshiipping restore job is running without any error but trn logs are not getting restored.

I am getting the following message in view history of  restore job

Message
2018-05-12 14:15:01.53      Searching through log backup files for first log backup to restore. Secondary DB: 'M_SHARED'
2018-05-12 14:15:01.60      Skipped log backup file. Secondary DB: 'M_SHARED', File: 'E:\logshipping\m_shared\M_SHARED_20180512081500.trn'
2018-05-12 14:15:01.61      Could not find a log backup file that could be applied to secondary database 'M_SHARED'.
2018-05-12 14:15:01.61      The restore operation was successful. Secondary Database: 'M_SHARED', Number of log backup files restored: 0

need expert advise to resolve the issue.
0
Jobs failure errors..
One job in one database is continuously failling.. any idea, what is this?

USER DB MaintenancePlan.Stop Shrink Index job



Executed as user: xxx\SYSTEM. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6000.34 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  2:50:02 PM  Progress: 2018-05-11 14:50:05.05     Source: {392FDF17-CBA2-418D-B8E0-0AC06FE9F63E}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Error: 2018-05-11 14:50:05.28     Code: 0xC002F210     Source: Execute T-SQL Statement Task Execute SQL Task     Description: Executing the query "EXEC msdb.dbo.sp_stop_job @job_id=N'a919ec54-e2f7-..." failed with the following error: "SQLServerAgent Error: Request to stop job Shrink SEMP5 Datafiles INDEX (from User NT AUTHORITY\SYSTEM) refused because the job is not currently running.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:50:02 PM  Finished: 2:50:06 PM  Elapsed:  3.947 seconds.  The package execution failed.  The step failed.
0
Hi,
    We are facing some strange issues on our SQL server 2008 R2 environment where suddenly SQL Agent job which was executing DTS packages weren't returning any error or successful status . When we checked in back-end the file which were supposed to get processed by DTS weren't processed. we haven't changed any configuration change or package upgrade done. the same package was copied to other environment which ran successfully.
To test whether DTS tool are having any issue we created some test package which was executed properly without any issues.
 No error logs are getting logged either on SQL Agent Job-->Steps-->Advanced->Error log file or on package logging as well.
Even running the DTSRUN command on command prompt doesn't show up anything.

Any help would be much appreciated!!
0
Hello,
I have query which works perfect, just need a small modification. Please refer to he expected result attachement.

DECLARE @EndDate AS DATE = '20180222';
DECLARE @StartDate AS DATE = '20180212';

WITH Unpivoted (GuestName, Property, ArrivalType, ArrivalDate, DapatureDate, RoomNo, RoomType)
AS ( SELECT GuestName ,
            SentTo,
            'A1',
            ArrivalDate ,
            ISNULL(Extendedto, DepartureDate),
            Roomno ,
            RoomTypeRouting
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A2',
            ArrivalDate2 ,
            ISNULL(Extendedto2, DepartureDate2),
            Roomno2,
            RoomTypeRouting2
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A3',
            ArrivalDate3,
            ISNULL(Extendedto3, DepartureDate3),
            Roomno3,
            RoomTypeRouting3
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0
     UNION ALL
     SELECT GuestName ,
            SentTo,
            'A4',
            ArrivalDate4,
            ISNULL(Extendedto4, DepartureDate4) ,
            Roomno4,
            RoomTypeRouting4
     FROM   CORR
     WHERE  DeleteRecord IS NULL
            OR DeleteRecord = 0 
UNION ALL
     SELECT GuestName ,
            SentTo,
            'A5',

Open in new window

0
I have a value like this:

RERE1000000000

I want to check in a number exists  in a db field if it doesn't I want to set the value to RERE1000000000 otherwise I want to add 1 to the max value.

RERE1000000001
RERE1000000002
etc.
0
Trying to create a view with all the values of a field for the same ID into one text field.

I know how to do this in vb but need to know how to do something like this in a SqlServer view.

basically i want to return 1 record with all the information from a field spanning multiple records that have the same criteria.

this is the vb code that pulls the data into 1 variable called cTrackingInfo.

   SSQL2 = "SELECT TRACKING_INFO FROM CT_PACKLIST_TRACKING_INFO WHERE PACKLIST_ID = '" & rs("PACKLIST_ID") & "' ORDER BY ID"
   SET RS2 = CREATEOBJECT("ADODB.RECORDSET")
   RS2.OPEN SSQL2, SCONN
   Do Until RS2.eof
      If RS2("TRACKING_INFO") <> "" Then
         If Trim(cTrackingInfo & "") = "" Then
            cTrackingInfo = RS2("TRACKING_INFO")
         Else
            cTrackingInfo = cTrackingInfo  & "," & RS2("TRACKING_INFO")
         End If
      End If
   rs2.MoveNext
   Loop
   RS2.CLOSE

Open in new window


Any help would be greatly appreciated.
0
Why does this query return 13 records? I want it only to return 12 ... the one between the dates

DECLARE @END DATE = '12/31/2017';
DECLARE @Start DATE = DATEADD(MONTH, -11, @END);


WITH _DateList ([DATE])
AS (SELECT  @Start [DATE]
    UNION ALL
    SELECT DATEADD(MONTH, 1, [DATE])
    FROM _DateList
    WHERE [DATE] <=  @END
   )
SELECT  * FROM _DateList

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server
LVL 12
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

i have a simple 3 field sql table
ID , ID_AutoReport , Limit

ID is (PK, Int, not null)
ID_AutoReport is (int, null)
Limit is (nvchar(200), not null)

i need a simple sql query to add new rows with new IDs the number of rows i will be adding will be equal to the number of entries for  ID_AutoReport that match 349730 , and will duplicate the data in the Limit field. (I hope I haven't made this confusing)
i think i've gotten myself a little confused as to the way parts of my query relate to the data i am replicating.

SET IDENTITY_INSERT DAAutoReportsLimit ON;

INSERT INTO [SYSTEM SETUP].dbo.[DAAutoReportsLimit] ( 
                                 ID_AutoReport ,
                                 Limit )
            SELECT 
                   350163 ,
                   Limit
            FROM   [SYSTEM SETUP].dbo.[DAAutoReportsLimit]
            WHERE  ID_AutoReport = 349730;
SET IDENTITY_INSERT DAAutoReportsLimit OFF

Open in new window


example:
table before:
ID , ID_AutoReport , Limit
1,349730,555-555-1234
2,349730,555-555-4321
3,349730,bob@gmail.com
4,349731,tom@elseware.net

table after:
1,349730,555-555-1234
2,349730,555-555-4321
3,349730,bob@gmail.com
4,349731,tom@elseware.net
5,350163,555-555-1234
6,350163,555-555-4321
7,350163,bob@gmail.com
0
Hello, all;
(SQL Server 2016)

I need to get the top record for all duplicate numbers.
When I try a distinct on the itemID, it gives me the bottom record and skips a lot of rows for some reason.
(And yes, I have also tried MIN and MAX on this as well, and it still does not work.)

Here is a sample of the data. I need to return the TOP rows, from all the duplicates.
As it is it Front cover.
I could do  
caption='Front Cover'
However, there are some entries that do not have a cover, and their entry is NULL.

ItemNo	CloserLookID	Caption
2	00000002	Front cover
2	00000002	Back cover
118	00000118	Front cover
118	00000118	Back cover
130	00000130	FRONT COVER
130	00000130	BACK COVER
130	00000130	P2
130	00000130	P6
202	00000202	Front cover
202	00000202	Back cover
202	00000202	Preface
202	00000202	Color page 1
202	00000202	Color page 2
220	00000220	Front Cover
220	00000220	Table of Contents
220	00000220	Introduction
220	00000220	Sample Page 1
220	00000220	Sample Page 2
220	00000220	Back Cover
284	00000284	Front cover
284	00000284	Back cover
290	00000290	FRONT COVER
290	00000290	BACK COVER
290	00000290	MUSIC SAMPLE
384	00000384	Front Cover
384	00000384	Back Cover
384	00000384	Contents
384	00000384	Performer credits
385	00000385	Front Cover
385	00000385	Back Cover

Open in new window


Thanks
Carrkiss
0
i want to download adventure works database 2008. Please share me the link to download the (.BAK) file
0
Hi

I got the error from the application side, as they are upgrading the application and database is in simple recovery model

Could not allocate space for object 'dbo.AGENT_TRAFFIC_LOG_1' in database 'sem5' because the 'FG_LOGINFO' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup

I have plenty of D drive

I did the following

      1. Shrink-Database
      It was taking lot of timing I killed the process and restarted the Sql Server
      
      Again same above error
      
      2. Shrink -> Files-> Transcations logs and I did the all file groups one by one..

release unused space > 
Again same above error

and

reorganize pages before releasing unused space

      Again same above error
      
  3. I increasee Autogrowthy by 10000MB for all file groups, unrestricted growth
       
        Again same above error

 4. I manaully increase the Initial Size roughly adding 20 to 30% more see the free space in the screen2.png image and restarted the sql server
   Again same above error

Even I tired unrestricted growth for all file groups, still it did not work

Im not sure, what is the issue, is what ever the steps I did is the correct? Is anything im missing


Attached the screenshots..

screen1.pngscreen2.png
0
What is the best way to make a date appear like this:

01/18
0
I have a test_file.txt

------------------------------------------test_file.txt------------------------------------------------
3
ID | Name | Gender
x1 | A | M
x2 | B | F
x3 | C | M
------------------------------------------------------------------------------------------


3 is our record count.

I need to write a batch script where I have to store the record count value in one variable(X), need to store the data values ( 3 row) in to another variable (Y)
and have to compare both the variables if X == Y then execute a SQL job (ABC) and if they are not equal fail the script raising an ERROR.

Please help folks as I think this is the only forum which can help me solving my problem.
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.