Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Microsoft SQL Server

159K

Solutions

49K

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

Hi All,

I wonder if there is a quick way to check which data that has the problem ?

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Thank you.
0
Free Tool: Port Scanner
LVL 10
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

In this code how would I split it out so that it returns the first and last name?

SELECT('JIM, SMTIH K')

NOTE: not all of the last names will have the middle initial.
0
Hello,

I search how to check f an index is partitionned on a table partitionned because the following erreor is returned when I try to do switch :
Msg 7733, Level 16, State 3, Line 10. 'ALTER TABLE SWITCH' statement failed

Thanks

regards
0
Using SQL Server 2014.

I need to parse off the last two segments of a variable length string from a column in a customers database.  In Access, I would simply use either Instrrev() or the Split() functions to perform this, but would like to do it in a SQL query.

Is there an equivalent to the Access instrrev() function in TSQL, which allows you to search for the character postion from right to left in a string as in:

?instrRev("ABC DEF GHI", " ") => 8

without having to work from left to right?

How about an equivalent to the SPLIT() function which parses a string into an array based on the delimiter provided:
Arr() = SPLIT("ABC DEF GHI", " ")
yields
Arr(0) = "ABC"
Arr(1) = "DEF"
Arr(2) = "GHI"

Thanks,
Dale
0
I am relatively new to SQL Server, using Developer 14.

I have payment records stored in a table and I want to pull values for those that are fully paid and those that are partially paid.

The field 'FullOrPartialPayID ' =1 if the pay rec fully paid the balance.

I have two fields in my result set 'FullPayAmt' and 'PartialPayAmt'.  

If  'FullOrPartialPayID ' =1 I want the full amount of  field 'PayAmt' to be in 'FullPayAmt', otherwise it should be zero and 'PartialPayAmt' will get the full amount of 'PayAmt'.

Conversely  If  'FullOrPartialPayID ' <>1 I want to full amount of 'PayAmt' to be in 'PartialPayAmt' and 'FullPayAmt' to be zero.


If I was coding this in MS Access query builder I would have fields 'FullPayAmt' defined like this:

SELECT tblPaymentsYears.PayHdrID, tblPaymentsYears.PayTaxAuthID, tblPaymentsYears.[FullOrPartialPayID], tblPaymentsYears.[PayAmt],
IIf([FullorPartialPayID]=1,[PayAmt],0) AS FullPayAmt,
IIf([FullorPartialPayID]<>1,[PayAmt],0) AS PartialPayAmt
FROM tblPaymentsYears;

How can I code this in a SQL View?
0
When i check DB options :
Auto Create Statistics: True
Auto Update Statistics: True

It means that if i created any indexes in this DB will automatically update statistics? am i correct?

When i used below query:
The results are:
auto_created  user_created
           0                    0
It should be 1 becasue detabase level is True.

USE TestDB
SELECT o.name, i.name AS [Index Name],  
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC; 

Open in new window


I got confusion when we make true at database level, it should automatically set true to index level?  am i correct
0
I have an MSSQL table, tblTimeZone, which has fields ZipStart, ZipEnd, TimeZone and State.  The values for ZipStart and ZipEnd are the first 3 digits of a Zip Code.  The TimeZone value is a digit from 1 to 6 representing the 5 US time zones plus an "Other" (#6) as a catch-all for un-found zipcodes.  There are 69 entries in this table

I have a second table, tblNames, which has names and addresses (including a zipcode field) of people. This table can have over 5,000 names.

What I need to do is using the zipcode from the tblNames look up the timezone in the tblTimeZone and create a sum of names for each timezone.

The values in the tblTimeZone are not contiguous for a timezone - hence, one can't say that all the zipcodes between 010 and 349 are in the Eastern timezone.  As an example, 374 to 377 is Eastern timezone - state TN, but 370 to 373 is Central timezone - state also TN,
as are zipcodes 378 to 385 also Central and TN.
0
Dear Experts,

I'm trying to make it so I can connect to my sql server on one machine to another, so I can copy the databases over. I am trying to connect to TCP.

The actual error I am getting is:
Can't connect to tcp:<our external ip>,1433\SQLSERVER2014
A network-related or instance-specific error occurred while establishing a connection to SQL Server... -The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)
The remote computer refused the network connection.


I've followed guides for how to set it up, and as a matter of fact, following such a guide, had no issue setting it up so I can log into our new comper's sql server 2014 remotely. But some setting on the old computer must be preventing it from working and I just can't figure out what it is.

I have enabled TCP/IP and set it to 1433 (the default port). My sql server is named SQLSERVER2014.
I have restarted the sql server browser service and the sql server service.
I have also opened up the 1433 port through TCP on the firewall for private,public,domain (so all).
I also added program exceptions to allow the sql server and browser through the firewall.
I checked to see if there was a group policy that might be preventing it.

For the TCP/IP Properties, I set all IP1 through IPAll, TCP Dynamic Ports to empty (not 0) and I set the tcp port for all of them to 1433.

The computer is the main computer that faces the internet and the ip address of our computer goes …
0
Is there a way to write a script that will add a SQl database to another machine automatically without a hard wired connection string.  I have a vb.net program that has a SQL Server database that will be installed via download to 10 different independent computers in 10 different homes with 10 different users.  They will have different machines with different windows versions  etc.... They will also have different info on  each database with no connection to each other.

I would like to keep the database independent so that if i update the UI they can download it and run the install(setup) and find the database.  

I know this can be done with a local DB but then i would not be able to do UI updates.
Can someone steer me with this.
0
I have a couple queries wrapped in an SQL transaction like below. If one or more of the sql statements fails or doesn't complete properly, I'd like to rollback everything automatically. Is this possible? Thank you.

BEGIN TRANSACTION;

INSERT INTO.....

UPDATE tblBlah...

INSERT INTO....

COMMIT TRANSACTION;

Open in new window

0
Important Lessons on Recovering from Petya
LVL 10
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Hello:

Below is my query, followed by the sample data and the results.  

I'm trying to SUM the ReceivedQTY column, so that only one row appears.

How can I accomplish this?

Thank you!

John

;WITH CTE AS
(
		select MO.MANUFACTUREORDER_I as MONumber, MO.ITEMNMBR as MOItem, 
	CASE WHEN MO.MANUFACTUREORDERST_I = 0 THEN 'New'
		   WHEN MO.MANUFACTUREORDERST_I = 1 THEN 'Quote'
		   WHEN MO.MANUFACTUREORDERST_I = 2 THEN 'Open'
		   WHEN MO.MANUFACTUREORDERST_I = 3 and ISS.DATERECD > 0 THEN 'Issued'
		   WHEN MO.MANUFACTUREORDERST_I = 3 THEN 'Released'
		   WHEN MO.MANUFACTUREORDERST_I = 4 THEN 'Hold'
		   WHEN MO.MANUFACTUREORDERST_I = 5 THEN 'Canceled'
		   WHEN MO.MANUFACTUREORDERST_I = 6 THEN 'Complete'
		   WHEN MO.MANUFACTUREORDERST_I = 7 THEN 'Partial Complete'
		   WHEN MO.MANUFACTUREORDERST_I = 8 THEN 'Closed'
		   END as MOStatus,
	MO.STARTQTY_I as StartQty, 
	ISS.QTYRECVD/BOM.QUANTITY_I as IssuedQuantity,
	REC.QTYRECVD as ReceivedQTY,
	(REC.QTYRECVD) - (ISS.QTYRECVD/BOM.QUANTITY_I) as UnitVariance,
	100*(1 + (REC.QTYRECVD - (ISS.QTYRECVD/BOM.QUANTITY_I))/(ISS.QTYRECVD/BOM.QUANTITY_I)) as Yield,
	MO.STRTDATE as PlannedStart, MO.ENDDATE as PlannedEnd, ISS.DATERECD as IssueDate, REC.DATERECD as ReceivedDate, 
	MO.USERID as CreatedBy, REC.USERID as ReceivedBy,
	MO.MATPROJCOSTI_1 as PlannedMaterial, MO.LABPROJCOSTI_1 as PlannedLabor, MO.LABFIXOHDPROJCOSTI_1 as PlannedOH,
	IT.ITEM_COST as IssuedMaterial,
	LISS.Cost_Array_1 as IssuedLabor, LISS.Cost_Array_2 

Open in new window

0
I have function which returns a  SMALLINT, and I have a stored procedure which calls the function.  When I execute the stored procedure, everything appears to work just fine.  However, the stored procedure create statement has an intellisense error when I reference the function.  Here is the error:  “Cannot find either column dbo or the user defined function or aggregate, or name is ambiguous”

Should I be concerned?  How do I fix this intellisense error?

Here is the function, after this I list the stored procedure statement which has the intellisense error:

SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfGetLineNumber]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfGetLineNumber]
GO
CREATE FUNCTION [dbo].[udfGetLineNumber]
(
      -- Add the parameters for the function here
      @PERSON_ID INT,
      @JUMP_MANIFEST_ID INT,
      @JUMP_DOOR_CD CHAR (2)
)


RETURNS SMALLINT
      
WITH ENCRYPTION
AS
BEGIN

DECLARE @lineNrCount SMALLINT

IF (SELECT COUNT (*) FROM PERSON_JUMP_MANIFEST_T WHERE PERSON_ID = @PERSON_ID AND JUMP_MANIFEST_ID = @JUMP_MANIFEST_ID) = 0
      BEGIN
            SET @lineNrCount = 1
            WHILE 1=1
                  BEGIN
                        IF (SELECT COUNT (*) FROM PERSON_JUMP_MANIFEST_T
                        WHERE  JUMP_MANIFEST_ID = @JUMP_MANIFEST_ID
                        AND JUMP_DOOR_CD = @JUMP_DOOR_CD
                        AND LINE_NR = @lineNrCount ) = 1
                        BEGIN
                              SET @lineNrCount = @lineNrCount + 1;      
                        END
                        ELSE
                        BEGIN
                          BREAK
                        END
                  END      …
0
I am a Senior .NET Full Stack Developer but want to have a sharper eye on the new technologies that are coming and the current technologies which are passing into the dustbin of history. What on-line industry publications are good for this?

Thanks
0
I am refreshing my memory about the various things which can cause a long response time on .NET MVC web pages and the tools to fix them.

A badly written stored procedure is the most obvious and this kind of bug can be uncovered first by looking at the Network tab of the browser and when the response time's too high, you may need to debug into the code to the point where the stored procedure is executed. You can also use SSMS to execute and fix it until the overall execution time becomes acceptable.

What other things can cause slow response and what methods uncover the problem?

Thanks
0
I am fairly new to SQL Server.  I'm working in Developer 14.

I've created a stored procedure that will select records from a view based on the passed parameters.  One of the parameters passed is TaxTypeID.  There are 4 tax type (1, 2, 3, and 4) I want to group the returned record set depending on which tax type was passed.

If the taxtype is 1 I want to
group by CountyID, PayYear, PayMonth

if tax type is 2
group by SchoolDistrictNum, payYear, PayMonth

if taxtype is 3 or 4
group by MuniCode, PayYear, PayMOnth

Is there any way to code the conditional grouping an sorting?

This is the current version of the SP
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spRptJerrysMonthlySummary]    Script Date: 10/17/2017 11:34:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Jerrys Report
-- =============================================
ALTER PROCEDURE [dbo].[spRptJerrysMonthlySummary] 
	-- Add the parameters for the stored procedure here
	@FromDate    datetime = null, 
	@ThruDate    datetime = null,
	@MuniCode    int      = Null,
	@SchoolDist  int      = Null, 
	@TaxTypeID   int      = Null 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT SUm(FaceAmt)              as SumFace, 
	       Sum(PenaltyAmt)           as SumPenaltyAmt, 
		   

Open in new window

0
microsoft sql

I have a table with sources, destinations and ports

image01
I want to concatenate (for xml):
all sources that have identical destination and port
all destinations that have identical source and port
all ports that have identical source and destination

image02
my query so far:
With finalresults([ROWID], [Serveur Source], [Serveur Destination], [Port]) 
AS ( 
select 
ROW_NUMBER() OVER (ORDER BY (SELECT null)) AS rowid, 
stuff((
select '__;',replace(c.[Serveur Source],' ','') as [text()] 
from [firewallrules].dbo.[outter-to-rionet] c WHERE c.[Port] = a.[Port] 
and c.[Serveur Destination] = a.[Serveur Destination] order by c.[Serveur Source]
FOR XML PATH('')
), 1,1,'') as source01, 
stuff((
select '__;',replace(c.[Serveur Destination],' ','') as [text()] 
from [firewallrules].dbo.[outter-to-rionet] c WHERE c.[Port] = a.[Port] 
and c.[Serveur Source] = a.[Serveur Source] order by c.[Serveur Destination]
FOR XML PATH('')
), 1,1,'') as destination01, 
stuff((
select '__;',replace(c.[Port],' ','') as [text()] 
from [firewallrules].dbo.[outter-to-rionet] c WHERE c.[Serveur Source] = a.[Serveur Source] 
and c.[Serveur Destination] = a.[Serveur Destination] order by c.[Port]
FOR XML PATH('')
), 1,1,'') as port01 
from [firewallrules].dbo.[outter-to-rionet] a 
)
select [Serveur Source], [serveur destination], [port] from finalresults f1 

Open in new window


The problem:
The concatenation work fine, but all sources, destinations and ports appear in every line
So, if i have 500 row before, i still have 500 rows after

Example:
this concatenation is ok:
source01 | destination01 | port01, port02, port03

This line is considered as a duplicate, because the source, destination and port are included in another line
source01 | destination01 | port02

I do not want this firewall rule to be defined twice for the same source, destination and port
0
Hi everyone,

I'm trying to attach the AdventureWorks Database to my Microsoft SQL 2012. I keep getting the message "An error occurred when attaching the database(s). Click the hyperlink in the Message column for details."

Problem is, there is no message in the message column.

A few things,
I'm running it as Administrator
I changed the permissions of the mdf file to "Everyone"
I moved the file into the DATA folder found in the Microsoft SQL tree.

Can someone help me out?
0
Is there a while statement to execute a stored procedure for each row in the returned data below?

SELECT   ReservationID
FROM     dbo.everyware_t_reservation
WHERE    DateModifiedEW > DATEADD(HH, -24, GETDATE())
         AND LEN(Phone1) + LEN(Email) > 0
         AND ISNULL(IndividualID, 0) + ISNULL(AltIndividualID, 0) = 0
ORDER BY DateModifiedEW DESC;


EXEC dbo.brkCreateLeadFromReservations  4631496

Open in new window

0
Hi, I have Frieght chargers in the FactInvoiceHeader Table.
But I think better insert Freight Chargers in the FactInvoceLine Table.
Just wondering how do that.
0
New feature and membership benefit!
LVL 10
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Hello,

I'm trying to convert 'horizontal' rows into 'vertical' rows, but I'm not quite sure how to approach this.

Let's say I have the following data:

-----------------
CompoundSchedule   (table name)
-----------------

CompoundName		RouteName	Schedule 	Dosage
-----------------------------------------------------------------------------------
CompoundA 		IP		QDx3		125.5
CompoundB		IV		QDx4		110.0
CompoundC 		IP		QDx7		115.0

Open in new window


I'm trying to get a more vertical 'key/value' type of result. For example (and ordering doesn't really matter at this point)


Key 		Value
----------------------------
CompoundName	CompoundA
CompoundName	CompoundB
CompoundName	CompoundC
RouteName		IP
RouteName		IV
RouteName		IP
Schedule 		QDx3
Schedule 		QDx4
Schedule 		QDx7
Dosage 		125.5
Dosage 		110.0
Dosage 		115.0

Open in new window


How would I do this, if it's possible? Keep in mind that ordering doesn't matter at all in this case, I'm just looking for the simplest possible way to get a key/value type of results from 'horizontal' records.

Thanks!
0
Hello,

I've got 2 Windows Server 2016 systems.  One is 12 core, the other 8.  The minimum number of cores to run SQL Server 2016 is 4...which means I have to get 2 licenses of "SQL Server Standard Edition (Core-Based Licensing)" at the very least.

The purpose of having SQL server is to have it integrated with Internet Information Server for dynamic database generated web pages.  The Server/CAL will not apply here and I just need SQL Server on 1 (one) machine.

My question is, do I need 4 (or 6) licenses for either the 8 (or 12) core servers to run or can I get away with purchasing just 2 licenses?

Thank you in advance.
0
Even though I have only two print statements it still returns all the output parameters but returns all 0's. When I run the select statement in the SP I get values other than 0 returned. The SP is shown below.
declare @txt_dairy_nmbr	INT
declare @txt_fats_nmbr		INT   
declare @txt_fruit_nmbr		INT
declare @txt_grain_nmbr		INT
declare @txt_protein_nmbr	INT
declare @txt_vegetable_nmbr	INT
exec SVCHC_cpsp_dietary_totals '4F3A3CD5-832B-46AA-9D5B-87165798767A', '1', 'Initial' 
@txt_dairy_nmbr OUTPUT,
@txt_fats_nmbr	OUTPUT,   
@txt_fruit_nmbr	OUTPUT,
@txt_grain_nmbr	OUTPUT,
@txt_protein_nmbr OUTPUT,
@txt_vegetable_nmbr OUTPUT
print @txt_dairy_nmbr
print  @txt_fats_nmbr

Open in new window


Stored Procedure:
CREATE PROCEDURE [dbo].[SVCHC_cpsp_dietary_totals]
(
--@Design Bit,
@pi_cur_person_id  UNIQUEIDENTIFIER,
@pregnum			INT,
@txt_visit			VARCHAR (15),
@txt_dairy_nmbr		INT	OUTPUT, 
@txt_fats_nmbr		INT	OUTPUT,   
@txt_fruit_nmbr		INT	OUTPUT,
@txt_grain_nmbr		INT	OUTPUT,
@txt_protein_nmbr	INT	OUTPUT,
@txt_vegetable_nmbr	INT	OUTPUT
)
AS
 select 
sum(ISNULL(@txt_dairy_nmbr,'')) as txt_dairy_nmbr,
sum(ISNULL(@txt_fats_nmbr,'')) as txt_fats_nmbr,
sum(ISNULL(@txt_fruit_nmbr,'')) as txt_fruit_nmbr,
sum(ISNULL(@txt_grain_nmbr,'')) as txt_grain_nmbr,
sum(ISNULL(@txt_protein_nmbr,'')) as txt_protein_nmbr,
sum(ISNULL(@txt_vegetable_nmbr,'')) as txt_vegetable_nmbr
FROM SVCHC_CPSP_Dietary_ext_
where person_id =  @pi_cur_person_id 
and pregNum = @pregnum
and txt_visit = @txt_visit

Open in new window

1
I have an Access application that uses linked SQL Server tables.  When the app is launched I need to determine how many users are already using the database.  How can I do that from within my access app?
0
I have a database table that has comma separated keywords column (i.e. "apple, green, sky"), I want to find all the other rows with matching keywords, ordered by the most matching keywords.

What would be the best way to do this pro-grammatically?
0
Hi experts,

I have 2 questions on my app which I put at the bottom of this post.

I'm have a vb6 application. Yes you heard right a vb6 application.
I'm using sql server 2008.  I'm using the Employees table from the Northwind sql server database.
I'm learning to work with Recordsets.

I have a sql server stored procedure that looks as shown on this script:

USE [Northwind]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetEmployeeByID]
	-- parameter
	@EmployeeID as varchar(50)

AS
BEGIN					

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [Northwind].[dbo].[Employees]
WHERE [EmployeeID] = @EmployeeID 

END

GO

Open in new window


So when I execute this stored procedure like this on sql server management studio this is what the result set of the stored procedure looks like:

-- execute stored procedure and pass value of 2 to EmployeeID parameter
[Northwind].[dbo].[GetEmployeeByID] 2

Sql1.PNG
This is my code for my application. It works fine.

Option Explicit On
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim strconnect As String
Dim VarEmployeeID As Long

Private Sub btnGetEmployee_Click()

    ' get value in EmployeeID textbox and save in variable
    VarEmployeeID = txtEmployeeID.Text

    'Call the Function - pass the argument to the function called GetAnswer
    'the result is stored in a variable called the answer
    Set rs = 

Open in new window

0

Microsoft SQL Server

159K

Solutions

49K

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.