Microsoft SQL Server

163K

Solutions

50K

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

are ReportServer & ReportServerTempDB MSSQL default databases, and does their presence in an instance of MSSQL indicate any specific feature has been installed? What data do they hold/and could the databases hold personal or sensitive information, or is it more generic configuration type info?
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Hi,
I am connect to a SQL 2016 Express Server through VBA in Excel . When I connect for the first time it times out but after that it connects fine.
0
Hi All,

I use laravel. I want to update database using

$data = DB::connection('sqlsrv2')->statement('exec [spname] ?, ?',  [$empcode, $pwd])';

The response is ok, but it does not update database.

What is the problem?

Thank you.
0
I'm getting the following Event Viewer errors when trying to start the SQL Server (SQLEXPRESS) Service:

SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.


Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

TDSSNIClient initialization failed with error 0x2740, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Only one usage of each socket address (protocol/network address/port) is normally permitted.

TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.

Server TCP provider failed to listen on [ 'any' <ipv4> 49172]. Tcp port is already in use.

I've tried restarting the Server but get the same result.
0
I am trying to write a SQL statement whereby I have a field named FormElementReference, which is the name of the question, and an associated field called value which holds the answer to that question.

I've written a case statement to try and achieve this, and store the separate values in separate fields for export to SSRS.

What I currently get is an row for each answer value.

Screenshot showing SQL with a row for each value.
SELECT DISTINCT
  FormResults.FormResultKey AS [FormResults FormResultKey]
  ,FormResults.FormID
  ,FormResults.FormReference
  ,UserForename + ' ' + UserSurname as [Full Name]
  ,FormResults.FormResultDate
 
 

,CASE WHEN FormResultDetails.FormElementReference = 'Date' then value else null end AS JobDATE
,CASE WHEN FormResultDetails.FormElementReference = 'Time' then value else null end AS TIMEFrom
,CASE WHEN FormResultDetails.FormElementReference = 'TEB' then value else null end AS TIMETo
,CASE WHEN FormResultDetails.FormElementReference = 'TEA' then value else null end AS TotalWorked
,CASE WHEN FormResultDetails.FormElementReference = 'JNO' then value else null end AS JobNumbers
,CASE WHEN FormResultDetails.FormElementReference = 'AA' then value else null end AS JobAddress
,CASE WHEN FormResultDetails.FormElementReference = 'Reason' then value else null end AS JobReason
,CASE WHEN FormResultDetails.FormElementReference = 'Managers' then value else null end AS ManagerAuthorised


FROM
  FormResultAnswers
  LEFT OUTER JOIN Documents
    ON …
0
Hello:

In SQL Reporting Services (SSRS), I have an "If, Then" formula shown in the first of two formulas pasted below.

I have this formula as an "expression" in a cell.  I want to multiply this cell times a value in another cell and have the product (multiplication result) appear in another cell.  I keep getting an error message that does not explain itself.  But, the error is clearly letting me know that I;'m not doing this correctly.

Specifically, I'm trying to tell SSRS to multiply this formula times another field.

This other field contains the second formula pasted below.

What formula do I use to accomplish this multiplication?

Thank you!

John


=If(sum(Fields!TRXQTY.Value)=0,0,sum(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))
/ IIF(sum(Fields!TRXQTY.Value)=0,1,sum(Fields!TRXQTY.Value))


=Sum(Fields!TRXQTY.Value*(Fields!DEBITAMT.Value-Fields!CRDTAMNT.Value))
0
Hi Experts,   I have one simple question on SQL Server Stored procedure.   I have  staging table with 300k+ rows, my requirement is read each row from the staging table and compare with key value in  the target table and do update/insert/delete . But if there is any error i have to log each error to a log table.

I have tried this in two ways , but both took more than an hour which is not at all acceptable.
1) open cursor , fetch row,  perform insert/update/delete  , close the cursor
2) open loop , read row  to temp table, perform insert/update/delete, exit the loop when all the rows are complete.

Please let me know what is the best way to read row by row and log the error if any issues and continue the load.
0
We need to get data from MS SQL Server to Excel a few times a day. Is it possible to get this done automatically?

Thanks
Jack
0
calculate price markup percentage
divided by zero error.

,[NetCostPerUnit] - [SellingPricePerUnit] / [NetCostPerUnit]  * 100 as MarkupPercentage2

I'm running this calculation
but keep getting a error "divided by zero error."

Thanks
fordraiders
0
Something has gone wrong with a table in a 2008 SQL Server database.  I am using Windows 7.  I have been importing data into this table for several years.  Today I received a failure, most likely due to the data being imported.  But now, I cannot import anything at all, as it just hangs there forever and never finishes.  I can't even run a simple SELECT query against the table, as it has the same result.  It doesn't fail ,but it never finishes either.  It's as if something is hanging out there that everything is waiting to be completed before moving on to the next query/import.

All other functionality within the database is working fine.

Thanks
0
Should you be charging more for IT Services?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Hi

I have 2 separate snippets of SQL, which will return an ID, but only one or the other not both,

How would I structure the SQL to return the ID of the first sql statement that has a value.

These are 2 separate sql statements, so thinking along the line of nested if Exists...

If neither returns a values then I need to return a 0 as a default value.

Andy
0
I have an issue with a SQL database. We have an application that uses SQL and was on a Windows 2008 server running SQL 2014 Standard. I had some issues with backups in the beginning and for some reason could not get my backup solution to do diff/full backups. So I set it to do a full backup every night.
Move forward a couple of years and we needed to migrate the application and SQL database to a new Windows 2016 server running SQL 2017 Standard.
When I backed up the database using SQL management studio the database size was 8gb..... when I went to restore the database on the new 2017 SQL Server, it almost filled up the hard drive. I did some checking and realized that the LDF (transaction log files) file was a whopping 120gb.
I am trying to figure out the following:
1. How do I reduce the size of the transaction log file or at least keep it from growing any larger. I have read that I should NOT try to shrink the database or accompanying log files as this can cause performance issues.
2. Do I use a simple recovery mode or Full recovery mode (on SQL 2014 it was set to full recovery mode)
3. Do I need to setup a maintenance job to backup the transaction log files sepearately and will that shrink or reduce the LDF file size?
0
When I encounter a duplicate PN in this case example 001030-503, I want to choose the one with MFG_CODE = 'SG361' and MFG_NAME = 'AEROLITE MAX BUCHER AG', not MFG_CODE '99999' and MFG_NAME = 'DEFAULT'. I need the select statement to do this for all duplicate PN's that contain a MFG_CODE = 99999 with it's corresponding MFG_NAME.

Would coalesce be a good option? If yes, what is best practice?
data_example.csv
Capture.PNG
0
Dear All,

I am new to SQL Server and would like your assistance to a query that has been raised to me recently. A client has approached me to build SQL Reports for them and is willing to provide me with the database backup in order to achieve this. First and foremost, how can I go about building SQL reports from the database, and also how can I achieve this as quickly as possible?

I would appreciate any links that I can use as a starting reference point.

Your help will be much appreciated.
0
Hi all,

This has prob been asked many times before. The various versions of SQL that are available from our volume licensing portal from Microsoft all state they don’t need a key. If I download and install is this a full working version of SQL or will it expire in 180 days (evaluation version)? When we purchase the required licence is this just a certificate to state we are legally covered or does it come with its own key that needs to be applied?

Thankyoy
0
In a software function, I receive a connection to a database server.  How can I find out using the connection whether I'm connected to Sql Server, Oracle, MySql etc.?
0
Which version of MS SQL server comes with SBS 2011, do you have a plain vanilla SBS2011 that you can check.

All of my clients that run SBS2011 have additional SQL server instances with different versions, so I am unable to tell for sure.  Please no guesses.
0
I need to create a user form for a SQL Server database.  I have experience using MS-Access as a front end, but the environment that I am currently working with does not have Access.  I am looking for a tool that will let me create a couple data entry forms, without a steep learning curve.
0
We have a number of reports that run out of our batch system behind data loads. There have been some recent updates. Previously, we have only one account type, so the parameter was just a single-value text value. We now use a multi-select parameter, and am getting an error when we attempt to run in batch although it runs properly through the web interface. I've captured the command line from our batch system:
\\xxx.com\share\CM\DEV\OPS\Apps\SSRSReportBuild\SSRSReportBuild.exe "Report=Solutions/ZZ-XX-12345-AccountAuditReport" "FileName=FCT-ZZ-XX-12345-AccountAuditReport.rdl" "MaxRunTime=1800" "Path=\\rpt2webtest\MFTest" "pAcctType=ABC,XYZ" "pAcctTypeList=ABC,XYZ" "pRunDate=20190906" "pRunType=D" "Render_Format=EXCEL"

Open in new window

When executed, the job fails with this error:
System.Web.Services.Protocols.SoapException: This report requires a default or user-defined value for the report parameter 'pAcctType'. To run or subscribe to this report, you must provide a parameter value.

Open in new window

Any suggestions of how to correct this and properly pass the value for pAcctType, a multi-value parameter?

Some of the questions that might come up
  • The source of available values for the multi-select parameter in the report is a SQL query.
  • The values 'ABC' and 'XYZ' do exist in the data.
  • We cannot pass the same parameter name twice on the command line, as the API does not allow this and will generate and error (I mention this becasue some web searching I've done indicate that is how you pass multi-select values in the web interface (e.g. &pAcctType=ABC&pAcctType=XYZ...)
0
Microsoft Azure 2017
LVL 13
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

I'm having to convert  nvarchar to decimal(18,2)


Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '-13.99' to data type int.

I'm trying to get around his error:
This is the second part to the INSERT statement



SELECT [RBP_MASTER_ID]
      ,[SKU]
      ,[QTY]
      ,[TARGET_PRICE]
      ,CAST(CAST([TARGET_GP] as INT)as decimal(18,2))    
      ,[CURRENT_PRICE]
      ,CAST(CAST([CURRENT_GP] as INT)as decimal(18,2))
      ,CAST(CAST([VENDOR_GUIDELINE_GP] as INT)as decimal(18,2))
      ,[APPROVED_PRICE]
      ,CAST(CAST([APPROVED_GP] as INT)as decimal(18,2))
      ,[PRODUCT_DESCRIPTION]
      ,[COMPETITOR_PRICE]
      ,[Escalation_Type_del]
      ,[Line_Color]
      ,[Line_Color_Price]
      ,[SEND_TO_LEADER]
      ,[TARIFF_ITEM]
      ,[RENEWAL_SKU]
      ,[RENEWAL_OLVP_QUOTENUMBER]
  FROM [program].[dbo].[escalation_detail]

I cant get the data into the new table because of the datatype difference

fordraiders
1
what kind of formulas determine how long you should retain your SQL Server database backups for? Our admins seem to be using a variety of different retention schedules for backups of different critical databases, some databases there are 1 weeks worth of SQL backups, others there are 6 weeks worth. I am trying to determine how exactly you would determine how long to keep database backups for, and for example have you ever restored data from say a 6 week old backup, which would surely incur huge data loss? Or under what circumstances would you need to keep backups for longer than say 7 days? I guess the same principle applies regardless of database platform hence adding it to other DB topic areas.
0
I have to update an XML column across several hundred thousand records in SQL Server 2014
I am using a set-based approach as follows:

;WITH XMLNAMESPACES(DEFAULT 'http://www.my-company.co.uk/mcshema1/1.0')
UPDATE C
      SET
      [Xml].modify ('replace value of (/MC/Family/Parents/Parent[@id={ sql:column("_pc.ParentId") }]/Children/Child[@id={ sql:column("_pc.ChildId") }]/Type)[1]/text()[1] with "Special Child"')
FROM [MC].[Family] C
CROSS APPLY
(
      SELECT
            __p.[FamilyId]
            ,__p.[ChildId]
            ,__p.[ChildType]
            ,__p.[ParentId]
            ,__p.[FamilyXml]
      FROM #FamilyParent __p
      WHERE __p.[FamilyId] = C.[Id]
)_pc

but keep getting the error:

Msg 2209, Level 16, State 1, Line 334
XQuery [MC.Family.Xml.modify()]: Syntax error near '{'
0
When I tried to import the attached csv file(items.csv) to SQL Server by using bcp utility from MS command prompt, bcp fails with the attached message.

bcp databasename.dbo.items in "C:\items.csv" -c -t -S servername\instance_name -d databasename

The following is the table I'm trying to insert csv data.

CREATE TABLE items (
  item_id int,
  year nchar(4),
  item_name nvarchar(20),
  price_tax_ex int,
  price_tax_in int
)

It's appreciate if I can know which syntax is not correct in bcp.

BCP version is 12.0.5000.0
items.csv
bcpfailed.JPG
1
Hi,

Would like to ask how to reduce the actual physical size of a MS SQL server DB log file?

I found one of my DB have abnormal log size of 83GB and would like to reduce the size and free up spaces.  I have perform backup of the DB and the log, but just don't know how to reduce the log size.

One of my friend told me that I can use DBCC plus Update Statistics in Transact SQL, but he do not mention exactly how to do it.  Kindly please help.

Cheers
Stanley
0
Hi Experts,

How can I create a formula field as follows.

CASE 
WHEN [@Field:Status] <> 'Draft' Then (select NotificationEmail from Caregivers where TimeAttendancePIN = [authfield_Nurse_UserName] and [No_Auto_Emails]= 0) 
Else  ' ' 
End

Open in new window


Getting error attached.

Purpose, I need to send an auto email for users filling a form with Status field = "Completed". (Caspio forms)

Thanks
0

Microsoft SQL Server

163K

Solutions

50K

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.