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

Hello,

I have a SQL Replication problem. I added some tables in an existing replication and I don't know what tables they are. Is there any way to see the history of tables I have added? (SQL 2008 R2)

I hope u can help me!
JN
0
Exploring SQL Server 2016: Fundamentals
LVL 13
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hi  I have  a table  TB-Docs in Sql Server 2016  with a filed Name ( and other fields)  . There are some names ending  like Doc1VOL01 , Doc1VOL11  , Doc1Vol123

Eg.
    Doc1Vol01
    Doc1Vol11    
    Doc3Vol123
 
   I need result with Volume rows and the substring of the  Document name  ( All I need to know is how to get the substring like Doc1 from a string of Doc1Vol01)
        Name                 Doc
        -----                     ----
     Doc1Vol01           Doc1
    Doc1Vol11            Doc1
    Doc3Vol123          Doc3


Edit : Name can be of any length , for example, there can be a Doooooc1Vol01
0
Hi, my below SELECT is in a stored proc and when I execute it its taking 9 seconds . Is there anyway to fine tune the query to bring the time down and improve the performance ?
Thanks


SELECT
      LatestStatuses.SiteKey,
      LatestStatuses.component,
      LatestStatuses.[Status],
      LatestStatuses.StatusDetail,
      LatestStatuses.[Timestamp]
 FROM (
      SELECT    
            SiteKey,
            Component,
            Timestamp as [Timestamp],
            RANK () OVER (PARTITION BY sitekey, component ORDER BY Timestamp DESC) AS StatusResult
            ,status as [Status]
            ,StatusDetail
      FROM StatusLog) AS LatestStatuses
WHERE StatusResult = 1
ORDER BY SiteKey, Component
0
Hi,
when I run the following query against my 2008 SQL Server
 select 2.13 as Control_Number,
'Ensure the ''sa'' Login Account is set to ''Disabled'' (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01
AND is_disabled = 0
UNION 
select 2.14 as Control_Number,
' Ensure the ''sa'' Login Account has been renamed (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01

Open in new window

i get the following output
sis u see it
when I add another UNION below
 select 2.13 as Control_Number,
'Ensure the ''sa'' Login Account is set to ''Disabled'' (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01
AND is_disabled = 0
UNION 
select 2.14 as Control_Number,
' Ensure the ''sa'' Login Account has been renamed (Scored)' As Control_Name, name,sid, is_disabled from sys.server_principals
WHERE sid = 0x01
UNION 
select  2.17 as Control_Number,
' Ensure no login exists with the name ''sa'' (Scored)' As Control_Name, ' ' as name, ' ' as sid, ' ' as is_disabled from sys.server_principals
WHERE sid = 0x01

Open in new window

i get
sid y dontI dont understand what happened to the sid values above...
any guidance appreciated.
Thanks
0
I have an issue that just started happening with an ODBC Connection on a Windows 2003 Server (VP1) failing to connect to a database on remote Windows 2008 R2 SQL Server (SQL1).

I received the following error:
ERROR ODBC Database Datasource 'D4TN' [][Named Pipes Provider: Could not open a connection to SQL Server[2], while SQLConnect 08001
Failed to connect to Database using DSN 'D4TN' Err -1


I also noticed that the network shares of the SQL Server are not accessible anymore from 'VP1', I receive the following errors when trying to browse or clicking on the mapped drives:
An error occurred while reconnecting J: to \\SQL1\share
Microsoft Windows Network: The local device name is already in use
This connection has not been restored:


I am still able to browse network shares on 'SQL1' from all other Servers and also have successful ODBC connections to the database D4TN.

I can't ping the 'SQL1' server from 'VP1' and can't ping 'VP1' from 'SQL1', but I can ping 'VP1' and 'SQL1' successfully from other servers.

Please advise on how I can resolve this issue.
0
Hello,
In current Production environment hosted on Microsoft SQL Server 2008, we have expensive sql query that is killing the database. We wish to add additional index to existing table.
SQL Query. This database is currently monitored by Solar wind and it has recommended to add index to the existing table. This table has currently 20 million records.

SELECT PVT_CHG_LOG_SEQ,  
   "Table_Name",  
   PK_Column_Name,  
   PK_Value,  
   ADP_SYNC_REQ_FLAG,  
   SYNC_STATUS,  
   SIBL_SYNC_STATUS,  
   ADP_SYNC_STATUS,  
   PRIORITY,  
   RETRY_COUNT,  
   SIBL_RESPONSEDESC,  
   ADP_RESPONSEDESC,  
   SIBL_COMPL_TIME,  
   ADP_COMPL_TIME,  
   Created_Date,  
   9,1  
FROM dbo.INT_PVT_DATA_CHANGE_LOG  
WITH  
   (
      UPDLOCK,  
      NOWAIT
   )  
WHERE (SYNC_STATUS = @P0)  
ORDER BY 9,1 ASC  

Recommendation from Solar wind to add inded
Clustered Index Scan (OBJECT:([CPMSPROD].[dbo].[INT_PVT_DATA_CHANGE_LOG].[PK__INT_PVT___61283AF276F68FE1]), WHERE:(CONVERT_IMPLICIT(nchar(1),[CPMSPROD].[dbo].[INT_PVT_DATA_CHANGE_LOG].[SYNC_STATUS],0)=[@P0]))

Question: a) Do we need to create new clustered or non clustered index for column SYNC_STATUS  to the table INT_PVT_DATA_CHANGE_LOG or modify existing index PK__INT_PVT___61283AF276F68FE1 to incorpate the column SYNC_STATUS

b) Any other guidelines to be followed while adding index

Regards
Tom
0
We had a requirement to find which strategy is working by giving scoring for each promotions based on customers who received promotions in last 24 months but we wanted to exclude customers who opened promotions which were  send earlier then last 24 months.
so basically the requirement(Emails sent out in the last 24 months by excluding clicks in the last 24 months)Please tell me how  I can query in SQL server.

Thanks
0
In my SSIS for each loop...
It is grabbing each file in the folder and running the processes
Then moving the file just completed into an archive folder
My problem is that it is running the processes nN times depending on number of files remaining

So... if I start with 8 files...
It inserts the first file 8 times and runs the processes that many times
Moves that file
And then does the next file 7 times
And so in

Any ideas?

SSIS
For Each Loop Properties
0
Keep receiving "Error converting data type nvarchar to float" when dividing two columns. QUOTE_AMOUNT column is set to Float and IC_UDF_ is set to NVARCHAR> when I attempt to convert IC_UDF_001 to float, numeric or INT, I receive the error. The data for both

SELECT        dbo.BA_VIEW_WO_QUOTE.WO_NUMBER, dbo.BA_VIEW_WO_QUOTE.WQ_NUMBER, dbo.PARTS_MASTER.IC_UDF_002 AS FMV, 
                         dbo.BA_VIEW_WO_QUOTE.QUOTE_AMOUNT, CAST(dbo.BA_VIEW_WO_QUOTE.QUOTE_AMOUNT / dbo.PARTS_MASTER.IC_UDF_002 AS NUMERIC)*100 AS FMV1
FROM            dbo.BA_VIEW_WO_QUOTE INNER JOIN
                         dbo.PARTS_MASTER ON dbo.BA_VIEW_WO_QUOTE.PNM_AUTO_KEY = dbo.PARTS_MASTER.PNM_AUTO_KEY

Open in new window

data.csv
Capture.JPG
0
hi,

any one know if SQL 2017/2019 support microservice platform ?

fully supported or any feature doesn't work on top of it?

any URL for it?
0
Price Your IT Services for Profit
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Hi,

In current production database of Mcirosoft sql server 2008, we recently performed maintenance activity of truncating log table that consumed 110 gb of storage space.
After truncate job is complete, we ran reorganize task for tables and indexes plus update statistics job on the table and indexes (full scan). The job was successfully complete
Even after performing the above task, the application performance had degraded.  
New to the Microsoft sql server world, please recommend any pointers to improve the performance.

Regards
0
I'm experiencing a rather bazar issue I've never run into previously.

I'm inserting data into an Access 2003 text field (255 char) where several rows are null or blank but when I test the length len([Field]), they all come out as over 100 characters wide.

The source of this field (from a customer) is a SQL Server table with the same characteristic.


How can I transfer this field and end up in Access with a zero length value?
0
I have created Five action queries in Ms Access 2016 for inserting data that require further manipulation to give correct information , one of the INSERT QUERY see below:

INSERT INTO tblPosAccounts ( QtySold, UnitCost, CostOfSales, SoldID, POSDate, CosAcc, BSIDCos )
SELECT tblPosLineDetails.QtySold, tblPosLineDetails.UnitCost, tblPosLineDetails.CostOfSales, tblPosLineDetails.ItemSoldID, tblPosLineDetails.POSDate, tblPosLineDetails.CosAcc, tblPosLineDetails.BSIDCos
FROM tblPosLineDetails;

The above query works very well if used against Ms Access 2016 table BUT fail to append in MS SQL Server 2016, kindly assist me on this all the links are just super no errors whatsoever. I do not understand this. The SQL Server has the same tables as MS Access.

See what you can do.

Regards

Chris
0
I seem to be struggling with the SQL code that I created and needs a few pointers on where I am going wrong. It seems to be caught in an infinite loop. I know it will be obvious once pointed out but its still a learning curve at the moment.

 DECLARE @TOTAL as int
DECLARE @CUSTOMER varchar(8)
DECLARE @PARTNO varchar(20)
DECLARE @CASE_DISCOUNT numeric(18, 2)
DECLARE @OVERRIDE_DISCOUNT numeric(18, 2)


DECLARE @COUNTER as int
SET @COUNTER = 1

SELECT        dbo.MBG110.CUSTOMER, dbo.MBG110.CUSTNAME, dbo.MBG130.SORDNO, dbo.MBG160.CINVOICE, dbo.MBG130.CUSTORDATE, dbo.MBG130.CUSTREQDTE_SOR, dbo.MBG160.CINVDATE,
                         dbo.MBG160.PARTNO_DS4, dbo.MBB010.DESCRIPTION, dbo.MBG160.DESQTY, dbo.MBB010.WEIGHT_ITM, dbo.MBG160.DESQTY * dbo.MBB010.WEIGHT_ITM AS ORDER_WEIGHT,
                         dbo.MBG130.SIINVVAL_SOR
                                     INTO #TempSales
FROM            dbo.MBG110 RIGHT OUTER JOIN
                         dbo.MBG130 ON dbo.MBG110.CUSTOMERLONG = dbo.MBG130.CUSTOMER_SOR LEFT OUTER JOIN
                         dbo.MBG160 ON dbo.MBG130.SORDNO = dbo.MBG160.SORDNO_DES LEFT OUTER JOIN
                         dbo.MBB010 ON dbo.MBG160.PARTNO_DS4 = dbo.MBB010.PARTNO

ALTER TABLE #TempSales ADD Case_Discount numeric(18, 2)
ALTER TABLE #TempSales ADD Override_Discount numeric(18, 2)
ALTER TABLE #TempSales ADD RowId int identity(1,1)

SELECT @TOTAL = COUNT(CUSTOMER) FROM #TempSales

SELECT        dbo.MBN510.PROMOCODE_N51, dbo.MBN510.CUSTOMER_N51, …
0
I need help with those dates in my Store procedure  in SQL Server :
I am doing calculation based on time ranges of rolling month, rolling  3 months and rolling  a whole year.

Rolling over 2 year
To determine those dates  I know   I Should use DateAdd() function but I need to be sure how they looks like for particular date , just that I will not make mistakes.
In procedure I am passing Start_Date that user will select.
--Range_Month:

Declare @Month_Range_Start_Date Date                           e.g. if start_Date is 03/15/2019 this date should be 02/15/2019  
Declare @Month_Range_End_Date Date                             e.g  if Start_Date is 03/15/2019 this date should be the same 03/15/2019

Declare @Previous_Month_Range_Start_Date Date            e.g. if  Start_Date is 03/15/2019 this date should be 02/15/2018
Declare @Previous_Month_Range_End_Date Date              e.g. if  Stard_Date is 03/15/2019 this date should be 03/15/2018


--Range_Quarter:
Declare @3M_Range_Start_Date Date                         e.g. if Start_Date is 03/15/2019 this date should be three months back 12/15/2018
Declare @3M_Range_End_Date Date                          e.g. if Start_Date is 03/15/2019 this Date should be the same 03/15/2019

Declare @Previous_3M_Range_Start_Date Date       e.g. if  Start_Date is 03/15/2019 this date should be 12/15/2017
Declare @Previous_3M_Range_End_Date Date         e.g. if Start_Date is 03/15/2019 this date should be 03/15/2018


0
Access 2010
Linked sql server tables

Let me start by saying this question has nothing to do with duplicate data.
DATETIME fields repeating data for entries.


I have something strange that started recently.
When I view the Linked tables in Access.
It displays the data : as shown below...for a repeated  ID

repeated rows of data
in sql server  this is correct...( second entry)

sql server entry
not so concerned about the military time..but...the entries in Access are repeating...
if i had 5 entries..
they would all be the same ?

or maybe military time is causing the problem ?




Thanks
fordraiders
0
I have created a SQL Server Job that runs a query which I then will email in HTML format. The issue I am experiencing is that when the SQL statement returns a NULL value the resulting table in HTML does not render the cell and the data shifts. Table-Exampes-of-Now-versus-Intende.docx

My goal is to have display a "Blank" in the cell of the table I am emailing when the corresponding value in SQL is NULL. In the embed Word .doc the third employee (Smith) just joined the company and Termination date is NULL in SQL. This is causing the shift of data to the left.






Open in new window

DECLARE @Body NVARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)


SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black;border-width: 1px;padding-left:1px;padding-right:1px;padding-top:1px;padding-bottom:1px;font: 9px arial} '
    + '</style>' + '</head>' + '<body>' + 'Date and Time: '
    + CONVERT(VARCHAR(50), GETDATE(), 120)
    + ' <br> <table cellpadding=0 cellspacing=0 border=0;>'
       + '<td bgcolor=#669900><b><font size="1" color="white">Employee</font></b></td>'
       + '<td bgcolor=#669900><b><font size="1" color="white">Hire Date</font></b></td>'
         + '<td bgcolor=#669900><b><font size="1" color="white">Termination Date</font></b></td>'
       + '<td bgcolor=#669900><b><font size="1" color="white">Job Title</font></b></td>'
       + '<td bgcolor=#669900><b><font size="1" …
0
working with Oracle Linked server in SSMS 2008R2

do i need a terminator(or something else)  between  OPENQUERY/dynamic sql statements

I have this line of code

TRUNCATE TABLE [dbo].[XREF_HEADER_DETAIL_ORACLE_EXTRACT_2019_STAGING]
begin try

DECLARE @StartDate AS VARCHAR(10) = '07-JAN-19';
DECLARE @EndDate AS VARCHAR(10) = convert(varchar, getdate(), 101);
DECLARE @Statement NVARCHAR(MAX) = N'
    SELECT *
    FROM OPENQUERY(
        XREF_PROD,
        ''SELECT


etcx......  code


EXECUTE sys.sp_executesql @Statement;



end try
BEGIN CATCH  
   select
   ERROR_NUMBER() AS ErrorNumber,
		ERROR_STATE() AS ErrorState,
		ERROR_SEVERITY() AS ErrorSeverity,
		ERROR_PROCEDURE() AS ErrorProcedure,
		ERROR_LINE() AS ErrorLine,
		ERROR_MESSAGE() AS ErrorMessage;
END CATCH



--- do i need additional code here between statements/dynamic sql statements  ?

--- next dynamic sql statement  

TRUNCATE TABLE [dbo].[ORACLE_CUST]


    SELECT *
    FROM OPENQUERY(
        XREF_PROD,
        ''SELECT reportsTor,


code etcccc...

Open in new window



Thanks
fordraiders
0
I have a table dc_split in which I split a bill total of $2000 three ways.
First and second person's share is 33.3333
The last person's share is 33.3334

create table dc_split (fullname varchar(70), billpercent numeric(7,4), billtotal numeric(12,2), theirshare numeric(12,2))

insert into dc_split (fullname, billpercent, billtotal)
values('Lori', 33.3333, 2000)
insert into dc_split (fullname, billpercent, billtotal)
values('Pete', 33.3333, 2000)
insert into dc_split (fullname, billpercent, billtotal)
values('Joey', 33.3334, 2000)

After updating the table to get their total share:
update dc_split set theirshare=billpercent * billtotal/100

The results are that the share for each person is 666.67 which sums up to a total of 2000.01

I need a way of getting their split values instead to be 666.66 for the first two people
and 666.68 for the last person.  So basically for the first two people, theirshare should not
round up and the last person's share should make up the extra 2 cents.

Is there a way to do this in the sql?
0
5 Ways Acronis Skyrockets Your Data Protection
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Hello experts,

I call the following .sql through a  SQLCMD.

DECLARE @vThreshold_PRC NUMERIC(18,2) = $(V_THRESHOLD_PRC),
        @vCurr_PRC NUMERIC(18,2),
		@vMessage NVARCHAR(500),
		@vCpt_sync INT,
		@vCpt_all INT

BEGIN 
	SELECT @vCurr_PRC = CAST(100.00 * (cpt_sync / cpt_all ) AS numeric(18,2)), @vCpt_sync = cpt_sync, @vCpt_all = cpt_all
	FROM (SELECT COUNT(*) * 1.00 cpt_sync FROM $(V_LNKSERVER)[$(V_DBNAME)].[$(V_SCHNAME)].[$(V_TBLNAME)]) S,
		 (SELECT COUNT(*) * 1.00 cpt_all  FROM $(V_LNKSERVER)[$(V_DBNAME)].[$(V_SCHNAME)].[$(V_TBLNAME)_INTERNAL]) I

	IF @vCurr_PRC > @vThreshold_PRC
	BEGIN
		SET @vMessage =  ': Le nombre de projets a transmettre (' + CAST(COALESCE(@vCpt_sync, 0) AS NVARCHAR(50)) + 
		                ') sur un total de ' + CAST(COALESCE(@vCpt_all, 0) AS NVARCHAR(50)) + ' projets, est superieur au pourcentage de projets maximum pouvant etre transmis (' + 
						CAST(COALESCE(@vThreshold_PRC, 0) AS NVARCHAR(50)) + ') : pourcentage courant calcule = ' + CAST(COALESCE(@vCurr_PRC, 0) AS NVARCHAR(50))
		  --PRINT @vMessage
		  RAISERROR(@vMessage,16,1)
	END
	
END
GO

Open in new window


I would like to add at the beginning of @message getdate with with the following format:  2018-06-15 23:54:42.013

SET @vMessage = getdate () +  ':

How should I proceed?

If you have questions, please contact me.
0
I have SQL Server 2017 running on a Windows Azure Virtual Machine.
I have an issue with connectivity.
I am getting the following error when attempting to connect using HTTP://serverip/OLAP/msmdump.dll
The error message is
Either a connection cannot be made to the XXX.XXX.XX.XXX server, or Analysis Services is not running on the computer specified.
Client access is over the Internet connections.
SSAS and IIS are on the same server.
The Azure VM was deployed using Resource Manager.
0
using REPLACE and RIGHT to delete anything to the right of a decimal. including the decimal
or something else ?


in a Function i have

Declare @Result varchar(Max)
SET @Result = Replace(@Input,'!','')
Return @Result

I have data in a field that looks like  "1.00"
or
1.45677

I need to strip anything to the Right of the decimal.

Thanks
fordraiders
0
call an UPDATE statement
SELECT CASE based on 2  input variables

-- current code --
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[ReplaceSpecialCharacter]
(
@Input varchar(max),
@Input_Field varchar(100)
)
RETURNS varchar(Max)
AS
BEGIN
Declare @Result varchar(Max)
SET @Result = Replace(@Input,'!','')
SET @Result = Replace(@Result,'@','')
SET @Result = Replace(@Result,'#','')
SET @Result = Replace(@Result,'$','')
SET @Result = Replace(@Result,'%','')
SET @Result = Replace(@Result,'&','')
SET @Result = Replace(@Result,'*','')
SET @Result = REPLACE(@result,' ','')
Return @Result

END

Open in new window


I need to create a SELECT CASE for various input_field conditions

I was trying:

   CASE WHEN @INPUT_FIELD = 'QTY' THEN
SET @Result = Replace(@Input,'!','')
SET @Result = Replace(@Result,'@','')
Return @Result
CASE WHEN @INPUT_FIELD = 'SKU' THEN
SET @Result = Replace(@Input,'!','')
SET @Result = Replace(@Result,'@','')
SET @Result = Replace(@Result,'#','')
SET @Result = Replace(@Result,'$','')
SET @Result = Replace(@Result,'%','')
SET @Result = Replace(@Result,'&','')
SET @Result = Replace(@Result,'*','')
SET @Result = REPLACE(@result,' ','')
Return @Result
ELSE -- 'DO NOTHING'
END

I keep getting the syntax wrong ?


and then call it:
UPDATE programflow.dbo.pricing_detail_test_special
SET target_gp = dbo.ReplaceSpecialCharacter(target_gp),(sku)
  ??



Thanks
fordraiders
0
SQL SSRS subscription fails with "Failure sending mail: An error occurred during rendering of the report.Mail will not be resent."

SQL Standard edition Version 11.3.6020.0

I have 40 SSRS reports that are working and just 2 that give this error, can anyone help?
0
Hi Experts,

I am in need of some help in order to convince manager to agree on designing a table in a normalized manner.

Attaching two versions of the form, the first one is a none normalized version, while the second is in normalize state.
Also attaching how table definitions would be according to each of those forms.

Basically would need a list of advantages vs. disadvantages we are to expect by choosing each path, in this case in particular.

Since users are used to enter data manually in sheets in a weekly bases (similar look to the denormalized version) they have preference on taking that route.

FYI-Table in question is PatientsMedications (posting test data).

Thanks
Untitled.png
Untitled1.png
Untitled2.png
Untitled3.png
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.