Microsoft SQL Server

163K

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

I simply created a stored procedure that inserts data from an ASP page. I keep getting an error message "Failed to convert parameter value from a string to a boolean".
 
My Stored Procedure is
ALTER PROCEDURE [Merch].[MyStoredProcedure]
	@Suppint Varchar(50),
	@VendorCode Varchar(10),
	@classgroupAllocID Varchar(10),
	@Season Varchar(4),
	@quarter Varchar(50),
	@Month Varchar(50),
	@DateAllocated Date,
	@StyleCreated Varchar(5),
	@EDIT BIT,
	@Category VARCHAR(50)
	
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @POID INT;
	INSERT INTO [Merch].[PurchasOrder] (Suppint,VendorCode,classgroupAllocID,Season,[quarter],CreateDate)
	VALUES (@Suppint,@VendorCode,@classgroupAllocID,@Season,@quarter,GETDATE());
	Select @POID = SCOPE_IDENTITY();
	
	INSERT INTO [Merch].[purchasOrder2](POID,[Month],DateAllocated,StyleCreated,EDIT,Category,CreatDate) VALUES (@POID,@Month,@DateAllocated,@StyleCreated,@EDIT,@Category);

Open in new window


My vb code to take my web page objects into my sql server is the following.
        Dim strConnString As String = ConfigurationManager.ConnectionStrings("MerchData").ConnectionString
        Dim con As New SqlConnection(strConnString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "[Merch].[MyStoredProcedure]"
        cmd.Parameters.Add("@Suppint", SqlDbType.VarChar).Value = ddlSupplier.Text.Trim()
        cmd.Parameters.Add("@VendorCode", SqlDbType.VarChar).Value = tbsuppliercode.Text.Trim()
 

Open in new window

0
Learn SQL Server Core 2016
LVL 13
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

I need to join 2 distincts

 SELECT distinct pv,pl from mlbscores  where ev='WAS' or el='WAS'

the result is this

pv                             pl
DEGROM.J            SCHERZER.M
EFLIN.Z                     SCHERZER.M
SYNDERGARD.N      STRASBURG.S
WEELER.Z              CORBIN.P


i need all the distincts in 1 column
is that posible dear experts?

regards
0
Hi,
I am using Visual Studio on my local windows 10 workstation to test an SSIS package which was not created on this machine.
There is a Connection Manager  which connects to an Oracle database as part of the package.
When I click Test Connection I get the error displayed .
ora
Is there some oracle  component I need to install locally?
Any guidance appreciated.
0
Hi All,

I have below data.

SeqNo   Brand      SubSeqNo      Type              Other data..
1	       AUDI	1	                A4 (New)
2	       AUDI	NULL	
3	       AUDI	NULL	
4	       AUDI	NULL	
5	       AUDI	NULL	
6	       AUDI	NULL	
7	       AUDI	NULL	
10	       AUDI	2	               A4 Cabiolet
11	       AUDI	NULL	
12	       AUDI	NULL	
13	       AUDI	NULL	
14	       AUDI	NULL	
15	       AUDI	NULL	
16	      AUDI	NULL	
19	      AUDI	3	               A - 6

Open in new window


I want to fill column SubSeqNo  and Type has data in row above it.

Thank you.
0
I have a table called #Data and i wish to summerise its contents. See the code below.

 
select 
  msr_Q1Total_Change,
  avg( cast( Has_Act_BasalaBehov as real ) ) as BasalaBehov,
  avg( cast( Has_Act_KognitivtStöd as real ) ) as KognitivtStöd
from #Data 
where msr_Q1Total_Change = 1
group by msr_Q1Total_Change

Open in new window


However i really need to produce the same summarisation for msr_Q1Total_Change  in the range 1 to 12.  So i am looking for 12 seperate results.

Is there a way to write this with a join that returns the values 1-12 ?  Thereby saving me a lot of cut and pasting and 12 seperate expressions.
0
Hi All,

I have below data :

1      AUDI
55      AUDI
109      ALFA ROMEO
127      BMW
181      BMW

Open in new window

I want to convert it into :

1  AUDI
2  AUDI
..
55  
56 AUDI
.
.
108 AUDI
109 ALFA ROMEO
110 ALFA ROMEO
.
.
 126

And etc.

How could I do it ?
0
Hi All,

I have below data :

1	AUDI
55	AUDI
109	ALFA ROMEO
127	BMW
181	BMW

Open in new window


I want to convert it into :

1        54  AUDI
55     108 AUDI
109   126 ALFA ROMEO
127   180 BMW
181   181 BMW

How could I do it ?

Thank you.
0
I have SQL query that calculates some totals based upon entries between two tables. The problem I have is that the query takes a very long time to run.  One of the tables is the rate_deck table and the other one is the sip_calls table.  The rate_deck table has about 180,000 records and the sip_calls table has about 140,000 records. In this scenario it took over 3 hours for the query to run. I have tried creating indexes on both tables but it did not help. I am importing the data for both tables from csv files so I have full control over the tables schema. Below I have the current schema for both tables and some sample data and a copy of the query. What I need is some advice on what can be done to speed up the query.

CREATE TABLE sip_calls(
  CalledNumber       BIGINT  NOT NULL
  ,Duration           BIGINT  NOT NULL  
);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (13178268627,265);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (8884254800,24);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (7088505327,66);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (13132311580,0);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (4373413793,7);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (8004254800,24);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (19991234560,123);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (9991234567,12);
INSERT INTO sip_calls(CalledNumber,Duration) VALUES (13132311580,48);

CREATE TABLE rate_deck(
   NPANXX

Open in new window

0
I need a little advice on how to calculate the difference in the value of a column rows in the table.
I have a table that has patientID, VisitDate, and a measured value.  A patient can make a number of visits on different dates and I need to know the difference between the measured values.
PatientID	 VisitDate	     Value	    Difference
1             2012-01-01           10               2
1             2012-02-01           12              -7
1             2012-03-01           5                NULL
2             2015-02-01           0                6	
2             2015-04-01           6                NULL

Open in new window

     
You can see from the example above I need the Difference to be given as the change to the next value (rather than the change that has occurred).
I can order the rows using partition by PatientID and ordering by VisitDate so I get a row number but what strategy would anyone recommend for getting the difference? We are talking about 60000 patients and maybe 500 000 rows.
0
Hi,
I have what should be a simple question to answer as it is common with many softwares and that is
How to Browse the data in a table in SQL Server Express 2017 ?
I can't seem to find a browse command even online.
I know I can browse the top 1,000 records but I want to browse the whole records.
Can someone please put me out of my misery
Many thanks
Ian
0
Ensure Business Longevity with As-A-Service
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

If you had an old Asp.net web forms c# application using sql server for back end that needed to be re-written to use modern technologies , what would you use?

But you must use sql server 2016.

Would you use asp.net web forms?
Asp.net mvc 5?
Asp.net core?
Angular front end?

Also what technology would you use to connect to sql server?
Web API? Entity Framework dB first? Entity Framework code first? Any other way?
0
This code below looks up the number of sales per day, based on similar dates.  How can I add up the TotalSales_Cnt from TABLE1 and TABLE2?  I've tried INNER JOINS with no success.

<%

Dim rs
Set rs = Server.CreateObject("ADODB.recordset")

SQL1 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE1] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

SQL2 = "Select " & _ 
      "cast(PurchaseDate as date) PurchaseDate , count(*) TotalSales_Cnt " & _ 
      "from [DAL.ProductContext].[dbo].[SoldItemsTABLE2] " & _ 
      "Group By cast(PurchaseDate as date) " & _ 
      "order by 1 desc "

rs.open SQL1, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE1</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
    rs.movenext
loop

response.write "</table>"

rs.Close 

rs.open SQL2, "DSN=dashboard"

response.write "<table border=1><tr><td><b>Sales Date</b></td><td><b>Total Sales TABLE2</b></td></tr>"
    
do while rs.eof = false
    response.Write("<tr><td>" & rs("PurchaseDate") & "</td><td>" & rs("TotalSales_Cnt") & "</td></tr" & vbcrlf)
    rs.movenext
loop

response.write "</table>"

%>

Open in new window

0
Hi,

I need to write a SQL server stored procedure for below scenario.

Select the 3 key columns(ex : id,name,create date) from Table A and then seek the record in table B
 if the records is exist in table B then
    select id,name, create date and resource id from table B insert and then into Table c if not exists in table c
else
 do nothing

Much appreciate your support
0
I have some sales data, and each record has a purchase date.  I have multiple sales on each day, and want to count up how many sales per day based on the purchase date.  What's the best approach to be able to count them up, then print the results to the page?

Dim conn
Set conn = Server.CreateObject("ADODB.recordset")
conn.open "select * from [DAL.ProductContext].[dbo].[SoldItems] ORDER BY PurchaseDate DESC", "DSN=dashboard"

Open in new window

0
I have an SSIS package I set up to export csv files from a database.   I can't seem to figure out how to go back and view or edit the query in the .dtsx package.  

Can you view and edit an SSIS package in MSSQL Management Studio?
0
Hi

In SQL Server 2017 I am trying to convert a column to an integer and have used following command  
select convert(int,Prize)
from [NH Flat_2012]
Where Prize is the column which contains example data  £1,200 which I want to convert to  1200
The error message I receive is....
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '"£1' to data type int.

Can someone kindly assist with correct syntax
Many thanks
Ian
0
I am getting this error when running a simply query.

dates are saved in DB in this format yyyy-mm-dd. query string being sent to database is..

SELECT count(WoID) as Pending FROM WorkOrders WHERE ( (Status='Approved') OR (Status='Assigned') or (Status = 'Rejected') )

running this direct in SSMS works and return the correct result, there is no date involved in this query its just getting a record count.


Microsoft SQL Server Native Client 10.0 error '80040e07'

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

/cmms/common/db_obj.asp, line 94
0
Microsft SQL Server 2017 - Attaching to 2014 database files.

I need to attach the DB data and log files from a former server (no longer avail).  The files are SQL 2014.  When attempting to attach to the database, I can specify the Data file, but the option to specify the existing log file is not available.  If I proceed it attempts to create a new log file.  What steps should be done to include the old log file to get the entire DB?

Clarification: Scripting works fine.  I'm wondering about the newer interface on SSMS not having the section to specify the log file location.
0
I have an array of bib numbers (AsgndBibs) on our local machine and I want to find any of those that are not in the db on the server so I can insert them.  I have the insert down.  I just want to create an array of bibs that are not on the server (BibsToInsert).  The approach I am using requires opening a recordset for each bib in the array to see if it exists in the server.  There must be a better way.  I would appreciate any help I can get.  Thanks.  Below is how I am populating the BibsToInsert right now.

        j = 0
        ReDim BibsToInsert(0)
        For i = 0 To UBound(AsgndBibs) - 1
            Set rs = New ADODB.Recordset
            sql = "SELECT Bib FROM PartRace WHERE RaceID IN (" & sEventRaces & ") AND Bib = " & AsgndBibs(i)
            rs.Open sql, srvr_conn, 1, 2
            If Not rs.RecordCount > 0 Then
               BibsToInsert(j) = AsgndBibs(i)
               j = j + 1
               ReDim Preserve BibsToInsert(j)
            End If
            rs.Close
            Set rs = Nothing
        Next i

Open in new window

0
Acronis Global Cyber Summit 2019 in Miami
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

I created 3 tables
Roles
- IdRole Primary Key
- UserId Foreign Key
- Role Field

Employee
- IdEmp PrimaryKey
- Other fields

RolesEmps
- IdRoleEmp PrimaryKey
- IdEmp Foreign Key
- IdRole - I want this to be a foreign key and connect to Roles.IdRole but it is identifying it as a PK.  When I try to create a relationship I get "THE COLUMNS IN TABLE ROLES DO NOT MATCH AN EXISTING PRIMARY KEY OR UNIQUE CONSTRAINT".

How can I create two FKs in RolesEmps to point to appropriate tables to set relationships.  I have IdEmp relationship set but I can't set IdRole
CREATE TABLE [dbo].[Roles](
	[IdRole] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[Role] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_dbo.Roles] PRIMARY KEY CLUSTERED 
(
	[IdRole] ASC,
	[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Roles]  WITH CHECK ADD  CONSTRAINT [FK_Roles_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO

ALTER TABLE [dbo].[Roles] CHECK CONSTRAINT [FK_Roles_Users]
GO

Open in new window

CREATE TABLE [dbo].[Employees](
	[IdEmp] [int] IDENTITY(1,1) NOT NULL,
	[EmplId] [nvarchar](max) NULL,
	[FirstName] [nvarchar](max) NULL,
	[MI] [nvarchar](max) NULL,
	[LastName] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED 
(
	[IdEmp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Open in new window

I WANT TWO FK's IN THE BELOW TABLE
CREATE TABLE [dbo].[RolesEmps](
	[IdRoleEmp] [int] IDENTITY(1,1) NOT NULL,
	[IdEmp] [int] NOT NULL,
	[IdRole] [int] NOT NULL,
 CONSTRAINT [PK_RolesEmps] PRIMARY KEY CLUSTERED 
(
	[IdRoleEmp] ASC,
	[IdEmp] ASC,
	[IdRole] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RolesEmps]  WITH CHECK ADD  CONSTRAINT [FK_RolesEmps_Employees] FOREIGN KEY([IdEmp])
REFERENCES [dbo].[Employees] ([IdEmp])
GO

ALTER TABLE [dbo].[RolesEmps] CHECK CONSTRAINT [FK_RolesEmps_Employees]
GO

Open in new window

0
sum fields after being separated into new fields from query

I have a query:
That takes a field that looks like :
I have a field called
PICKCOUNTS   that looks like  

PICKCOUNTS
123-43-456-56-564-65-0
The query belows  separates the data like below
Exact     Function     DM       Match       OUT_OF_SYSTEM          Xtra            DO_NOT_COUNT
123        43                 456        56                 564                             65                    0

Now i need to sum all the new columns/fields

Exact:Function:dm:match
123+43+456+56

SELECT     
[AUTOMATCH]
      ,[POSTCOUNTS]
      ,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 1, '-') AS EXACT
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 2, '-') AS FUNCTIONAL
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 3, '-') AS DM
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 4, '-') AS MATCH
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 5, '-') AS OUT_OF_SYSTEM
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 6, '-') AS X
,dbo.SEPARATES_COLUMNS(POSTCOUNTS, 7, '-') AS DO_NOT_COUNT
      ,[MANUALMATCH]
      ,[EXCPETION_DIRECT]
      ,[AUDITEDBY]

FROM  [Metrics].[dbo].XREF_HEADER

Open in new window


FUNCTION

fUNCTION:
FUNCTION [dbo].[SEPARATES_COLUMNS](
@TEXT      varchar(8000)
,@COLUMN    tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
  BEGIN
       DECLARE @POS_START  int = 1
       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

       WHILE (@COLUMN >1 AND @POS_END> 0)
         BEGIN
             SET @POS_START = @POS_END + 1
             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
             SET @COLUMN = @COLUMN - 1
         END 

       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1 

       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
  END

Open in new window





thanks
fordraiders
0
Hi Experts!

Need your help with the following data pull in SQL 2012.

The data looks like this:

PerId   StateType     StatusType  StatusDate
01      Home          Active      01/01/2012
01      Home          Closed      12/31/2012

02      Home          Active      01/01/2013
02      Home          Closed      12/31/2015
02      Home          Active      10/01/2016

03      Away          Closed      01/01/2013
03      Away          Active      01/01/2015
03      Away          Active      12/31/2015

03      Home          Active      02/01/2013
03      Home          Closed      12/31/2013
03      Home          Closed      12/31/2018

DECLARE @PersonHistory TABLE
(PerId VARCHAR(50), StateType varchar(50), StatusType varchar(20), StatusDate datetime)

INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'01',N'Home',N'Active', N'01/01/2012')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'01',N'Home',N'Closed', N'12/31/2012')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'02',N'Home',N'Active', N'01/01/2013')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'02',N'Home',N'Closed', N'12/31/2015')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES (N'02',N'Home',N'Active', N'10/01/2016')
INSERT INTO @PersonHistory ([PerId], [StateType], [StatusType], [StatusDate]) VALUES 

Open in new window

0
Hi all

My current employer has a number of SQL Server 2008R2* tales whose clustered primary key is a uniqueidentifier column (usually with FILLFACTOR = 90) that either our front-end randomly generates, or is INSERTed via a stored procedure with passed parameters that generates a NEWID() value.  This all happened way before my time.

Potentially stupid question:  Since clustered primary keys physically store the data in order, and the uniqueidentifiers are randomly generated, should I change the clustered primary key to a non-clustered, and save all the time spent on page splits inserting values when the order truly does not matter?

EE-question-uniqueidentifer-clustere.jpg
Thanks in advance.
Jim

*  We're in the middle of a 2016 conversion.
0
Folks,
I would like to learn MSSQL. Can anyone recommend a site that takes someone from a beginner to master?
0
Hi Experts

Could you point a way to sumarize to just one line similar correlated data that stays at different columns by using a MSSQLServer query?

These 02 lines:
img001
Are resulted from this query:
SELECT * FROM  v_telefones_01 AS v01
  WHERE v01.cpf = '16147383868'
UNION ALL
SELECT * FROM v_telefones_02  AS v02
   WHERE v02.cpf = '16147383868'
UNION ALL
SELECT * FROM v_telefones_03  AS v03
     WHERE v03.cpf = '16147383868'
  UNION ALL
SELECT * FROM v_telefones_04  AS v04
   WHERE v04.cpf = '16147383868'

Open in new window


Is there a way to make them result in just one line, with two columns filled, like:
img002
Thanks in advance!
0

Microsoft SQL Server

163K

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.