Microsoft SQL Server

158K

Solutions

187

Articles & Videos

48K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello,
How can I modify this query to have filter like' starts with '
  IF  @PatientName <> '' 	       
			 SET @SQL = @SQL + '  AND PatientName LIKE  ''%' + CAST(@PatientName AS  Nvarchar(MAX)) +  '%'''
   

Open in new window



Regards
0
Use Case: Protecting a Hybrid Cloud Infrastructure
LVL 4
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Hi All,

I have query below:

DECLARE @intMaxLength AS INTEGER
SET @intMaxLength  = (SELECT MAX(LEN(RTRIM(Name))) FROM TMJENIS)
SELECT
RTRIM(JenisCode) As JenisCode
 
,LEFT(Name, @intMaxLength) + SPACE(@intMaxLength  - LEN(RTRIM(Name))) + ' - ' + RTRIM(JenisCode) As Name
 
, LEN(Name) as lenname
, @intMaxLength as MAXLEN
FROM TMJENIS
WHERE 1=1
ORDER BY Name

Adding the SPACE(@intMaxLength  - LEN(RTRIM(Name)))  does not help.

What's wrong ?

Thank you.
0
It would appear that SSIS is now called SSDT. Is that correct?

I need to learn as much simple functionality as I can of SSIS by Friday for an interview opportunity. The largest one I have ever had.
Then I need to brush up on my SQL Server Query abilities. Which are old and rusty. Any thoughts on how I can get caught up quickly?
I understand the concepts of both products and have used SQL Server in the past just need any input so that I can snag a job. Please advise. Thank you.
0
Hi EE,

I am trying to add a security group to a SQL Server I can find the group without issue when I search via Windows -> Computer Management but I can't that same group to display in SQL server. See screenshot I have triple checked the names and locations where I am searching.

Any assistance is welcome.


Thank you.
Search-Screen.PNG
0
I have a table (User_Values) where I need to create 613 rows of data into columns "User_ValueKey" (primary key) and User_Values (int).  The values are sequential, from 333 to 948.  The data would look like this in the table itself

User_ValueKey      User_Value
333                          333
334                          334
335                          335

And so forth.

Is there a quick way to do this?  I have SQL Server 2008 R2 Express, if that helps.

Thank you!
0
You think the title sounds confusing, just wait.  :-)

I have a table with several fields.  I want to create a stored procedure where I can provide a parameter and retrieve a specific field based on the parameter submitted.

For example...

Here's an example of the table:
StudentID
MorningClassGrade
AfternoonClassGrade
EveningClassGrade

What I want is something along these lines:
CASE @FieldType
    WHEN 1 THEN
        SELECT
            StudentID,
            MorningClassGrade
        FROM
            MyTable
    WHEN 2 THEN
        SELECT
            StudentID,
            AfternoonClassGrade
        FROM
            MyTable
    WHEN 3 THEN
        SELECT
            StudentID,
            EveningClassGrade
        FROM
            MyTable
    END

Open in new window

Or
SELECT
    StudentID,
        CASE @FieldType
            WHEN 1 THEN
                MorningClassGrade
            WHEN 2 THEN
                AfternoonClassGrade
            WHEN 3 THEN
                EveningClassGrade
        END AS ClassGrade
    FROM
        MyTable
WHEN

Open in new window


Hopefully you understand what I'm trying to do.

Ideas?
TIA
0
The code below is not working.

This line of code in particular:
,ROW_NUMBER() OVER(PARTITION BY [business_unit_id], [employee_id], a.[job_id]  ORDER BY a.[job_id]) AS EmployeeCount

I want the row_number() function to count each employee by job_id. So the Store Manager result would be 1, the cashier would return a 1 and 2 differing by the employee_id.

SELECT 
business_unit_id
,a.job_id
,name
,employee_id
,scheduled_shift_id
,DATEADD(dd, DATEDIFF(dd, 0, in_timestamp), 0) as in_business_date
,in_timestamp
,out_timestamp
,ROW_NUMBER() OVER(PARTITION BY [business_unit_id], [employee_id], a.[job_id]  ORDER BY a.[job_id]) AS EmployeeCount
,ROW_NUMBER() OVER(PARTITION BY [business_unit_id], [employee_id], DATEADD(dd, DATEDIFF(dd, 0, in_timestamp), 0) ORDER BY DATEADD(dd, DATEDIFF(dd, 0, in_timestamp), 0)) AS ShiftCount
FROM [murphyusa].[dbo].[lab_sched_shift_view] a
INNER JOIN [murphyusa].[dbo].[Lab_Job] b
on a.job_id = b.job_id
WHERE employee_id IS NOT NULL AND in_timestamp BETWEEN '06/10/2017 00:00' AND '06/16/2017 23:59' AND business_unit_id = 1001196

Open in new window



I have attached some sample data as well as the desired result.
SampleData1.xlsx
0
Hi,

I'm using PHP and dblib to connect tp a MS SQL server data base, i can connect and talk to the database fine but I'm finding it hard to pull a single row of data or a single entry

$con = new PDO("dblib:host=****;dbname=nursery",'pcbuddy','****');

Open in new window


foreach($_POST['selectedinvoices'] as $ids)
{
				
				$query_today = $con->prepare("SELECT * FROM Invoices WHERE invoice_no = :ID");
				$query_today->bindParam(':ID', $ids, PDO::PARAM_INT);
				$query_today->execute();
									
				echo $stmt["invoice_no"];

Open in new window


No matter what I try I cant do a single select query.

Please help
0
I've been so "lucky" to inherit the stored procedure shown below. How can I make sure that it runs with "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" ? - and can anyone give me som advice on how to optimize the procedure, as it runs quite slowly at the moment ?

Thanks

CREATE PROCEDURE [dbo].[getPeriodStatsAlternative]
	@periodFrom nvarchar(8),
	@periodTo nvarchar(8),
	@uRole as nvarchar(10),
	@channels as nvarchar(10) = '%'

AS


/****** Total ******/
SELECT	vGroup, vType, count(vDetail) AS vCount
FROM (
SELECT        'OverallWeekDay' AS vGroup, cast(visitTime  as nvarchar(10)) AS vType,  visitTime  as vDate, v_Visits.id AS vDetail
FROM            v_Visits
WHERE        visitTime  between @periodFrom and @periodTo AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
) resSub
GROUP BY  vGroup, vType

UNION ALL
SELECT	vGroup, vType, count(vDetail) AS vCount
FROM (
SELECT        'Overall' AS vGroup, 'Visits' AS vType, visitTime  as vDate, v_Visits.id AS vDetail
FROM            v_Visits
WHERE        visitTime  between @periodFrom and @periodTo AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
) resSub
GROUP BY  vGroup, vType

UNION ALL
SELECT	vGroup, vType, count(vDetail) AS vCount
FROM (
SELECT        'Overall' AS vGroup, 'Pictures' AS vType, visitTime  as vDate, Visit_Pictures.id AS vDetail
FROM             v_Visits INNER JOIN Visit_Pictures ON v_Visits.id = Visit_Pictures.visitId
WHERE        visitTime  between 

Open in new window

0
"Select * from [TableEditor].[dbo].[teFields] ORDER BY TableID where TableID = " + Convert.ToInt32(ddlTable.SelectedValue) + ""; statement
"Select * from [TableEditor].[dbo].[teFields] ORDER BY TableID where TableID = "39"; Correct
0
Transaction Monitoring Vs. Real User Monitoring
LVL 1
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Hi experts
I have a Windows Server 2012 R2 - 64 bit, 32 GB RAM.
On this runs a SQL Server 2008 R2 Enterprise Edition 64 bit

The issue is that the sqlservr.exe service consumes almost 95% of memory (approx 28 GB).
I was checking and the configurations of min and max memory are not configured, I see the values like this:

Min server memory = 0
Max server memory = 2147483647
These are the default values.

My question is: what is the correct way or what should I take into account to give the appropriate values to my BD in relation to memory. Or how to tell if these 32GB of RAM I have is what I really need or are already running short.
0
I have a VB.NET application that connects with a SQL Server DB, and inserts a new record of data into a table that is an auto-increment ID column.  When the user enters the data, and decides that they do not want to save the information, the application is supposed to rollback the data and not insert it into the DB.  However, if the user does not save the data for ID number 150, for example, the code works as it should and removes the information from the DB, however upon the next entry of data into the DB, the ID number is now 151, and not 150 as that should be the next number in the sequence, since it was not utilized.

Can someone tell me code wise, how to ensure that SQL Server does not increment to the next number if the user does not save the record into the DB?  Below is the VB code that I am using for this functionality:


Private Sub frmWizard_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        If Not hasProfileBeenSaved Then
            If MsgBox("Save changes to the new profile?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                e.Cancel = True  
            Else
                'delete profile
                DbUtil.Provider.DeleteRecord(profile.ID)
            End If
        End If
    End Sub

Open in new window



Thanks in advance for your feedback.

Regards,
aj85
0
I have an Update statment that was working fine and I was updating the ApprovedBy column every time I changed the status. Now, my boss, against my better judgement, want me to add a DirectorApprovedBy column so that we know when the Director and Exec Director have both the Approved the document. I was just over writing the previous ApprovedBy and you could just assume that if the ExecDirector Approved, then the Director Approved it as well. Plus, I have a running History of the document and when and who has approved it but he wants both Approvals in this table.
That being said I want to use my existing Update statement but if its the Director(UserRoleID = 3) I want to update the DirectorApprovedBy column and if not update the ApprovedBy column. Im thinking something like this

            UPDATE dbo.WorkWeekTracker
            SET StatusID = @SetStatus,
            UpdateStatusDate = GETDATE(),                              
    IF @UserRoleID = 3
            ApprovedBy = @ApprovedBy
            ApprovedDate = @ApprovedBy
    ELSE
               DirectorApprovedBy = @ApprovedBy
               DirectorApprovedDate = @ApprovedBy
         WHERE WeekID = @WeekID AND UserID = @UserID and WorkWeekID = @WorkWeekKey      

Thanks
0
( An extension of this question.. )

Given the below SQL Server 2008  T-SQL statement and XML fragment, how would I return the value 'Sammy Hagar' WHERE boo i:type="rockstar" ?
DECLARE @x XML 
SET @x = '<goo xmlns="http://underdog.com" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <foo>
   <boo i:type="fruit">banana</boo>
	<boo i:type="rockstar">Sammy Hagar</boo>
	<boo i:type="planet">Mars</boo>
	<boo i:type="movie">Heathers</boo>
	<boo i:type="practicaljoke">Rick Roll</boo>
  </foo>
</goo>'
;
WITH XMLNAMESPACES ('http://underdog.com' as d) 
SELECT @x.value('(/d:goo/d:foo/d:boo)[1]', 'varchar(64)') AS the_amount

Open in new window

0
< Highly mocked up SQL Server 2008R2 T-SQL >
DECLARE @x XML 
SET @x = '<goo xmlns="http://underdog.com" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <foo>
    <boo>banana</boo>
  </foo>
</goo>'
SELECT @x.value('(/goo/foo/boo)[1]', 'varchar(64)') AS the_amount

Open in new window


I'm expecting to return a the_amount value of 'banana', but I'm always getting NULL.  What's the fix?
0
Hi,

I would like to update a column in one table (Target) from the contents of on column in another table 9Source).

The value in the source table is unique, but needs to be added to the target table multiple times.  Something like this:

UPDATE a
  SET a.[StockNumber] = (SELECT h.[StockNumber]
                                          FROM Stock h
                                          JOIN Sales a
                                          ON a.OrderNumber = h.OrderNumber)
FROM [Stock] a

So OrderNumber appears multiple times in the Stock table and needs to be updated with the StockNumber.

When I run the above, I get the following: query returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help would be appreciated.

Thanks
0
Hi All,

Let's say I've created some rules inside SQL database.
Under Programmability, Rules.

I want to read it from my application to validate user input.

How could I do it?

Thank you.
0
Would it be possible to write a trigger at the database level that looks at every table on the server and gathers the user name, Table name and action(select,Update,Insert Delete)?

I would like to collect this data and store it in a table.
0
Hello,
Can't figure out what is wrong with this SP.
The first condition does not fire at all.
 @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null,
	 @ColumnName2 varchar(100) = null,
	 @ColumnName3 varchar(100) = null,
	 @Value varchar(100) = null,	
	 @Value2 varchar(100) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]

	
          
			 	
			if  @ColumnName3 ='' 
				SET @SQL = 'SELECT DITINCT '  
			   
				SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'  +  ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName2 + ']  LIKER ' +   '''' +'%' +  @Value + '%'+''''
	
	        if @ColumnName3 <> '' 
				SET @SQL = 'SELECT DISTINCT '  
	         

				SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'  +  ' FROM  [' + @tableName + '] '   + ' WHERE   CAST(CollectionDateTime AS Date) =  ' +   '''' + @value  +''''  +
				 ' and   [' +  @ColumnName3 + ']   like ' +   '''' +'%'+  @Value2 +'%' +''''
	
	  SET @SQL = @SQL  	
	  
	  EXEC (@SQL)	
	  PRINT(@SQL)	
   

Open in new window


Cheers
0
Comparison of Amazon Drive, Google Drive, OneDrive
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

I have this SELECT statement in a stored procedure :

 SELECT
       eng_name AS Name,
       DATEPART(YEAR,TimexDate) AS 'Year',
        DATEPART(MONTH,TimexDate) AS 'Month',
       EngineerID,
       SUM(Minutes_Elapsed) AS Minutes_Elapsed_Total,
       SUM(Minutes_RW) AS Minutes_RW,
       SUM(NoOfBreakdown) AS NoOfBreakdown,
       SUM(CAST(Minutes_RW AS Decimal(7,2)))/ SUM(CAST(NoOfBreakdown AS Decimal(7,2))) AS Minutes_RW_Average

What I am trying to workout the average time spend on each breakdown. However I am getting this error message when I execute the stored procedure :

Msg 8134, Level 16, State 1, Procedure prc_TimeXActivity_GROUP, Line 21
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

The procedure only started to fail when I added the final line of the SELECT statement.
0
Is there a way (table, log, anything else) to tell whether a trigger has executed?
0
I have been tasked with importing some data into Excel from a "Cloud" database.  I was only given an IP address with user name and password.  I have never queried from Excel using only an IP address.  I tied to setup an ODBC connection using the IP address, but it doesn't work.  Can someone point me in the right direction?  I think the database is a SQL Server database but still trying to confirm this.
0
Hello!

I've written a simple query (in Microsoft Access) with an inner join but for some reason the results table of the query shows double results with one field missing from the second record.

Here's the code:
SELECT [Bibliographic Information].ISSN, [Bibliographic Information].Title, [Bibliographic Information].[Ulrichs Discipline], [Google Scholar].[Google Scholar H-index]
FROM [Bibliographic Information] INNER JOIN [Google Scholar] ON [Bibliographic Information].ISSN = [Google Scholar].ISSN
WHERE ((([Bibliographic Information].ISSN) Like [Forms]![Search Form]![ISSN]));

Open in new window


How do I narrow the results to only the result record with all queried fields?
0
Can you please tell me where the error is?
screenshot.PNG
INSERT INTO `coffee` (`id`, `name`, `type`, `price`, `roast`,

`country`, `image`, `review`) VALUES
(1, 'Cafe au Lait', 'Classic', 2.25, 'Medium', 'France',

'Images/Coffee/Cafe-Au-Lait.jpg', 'A coffee beverage consisting strong

or bold coffee (sometimes espresso) mixed with scalded milk in

approximately a 1:1 ratio.'')'
0
I have a web application with a MS SQL 2012R2 Server in the back end.
If we connect to the server with our connection string using "SQL Server" then the system works fine, but we cannot disable TLS 1.1 because apparently this driver uses that version.
If we connect to the server using SQL Server Native Client 11.0  then we can disable TLS 1.1 and use TLS 1.2 only, BUT then certain fields from our database such as text fields, nvarchar(max), and apparently any long strings won't work anymore. The data is not displayed in the ASP files and some other errors are displayed in the application.

What is causing this with the 11.0 connection and how can we fix it?
0

Microsoft SQL Server

158K

Solutions

187

Articles & Videos

48K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.