[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft SQL Server

162K

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 Experts,

I'm middle of converting my Access BE database to MS SQL Server and at the first glance users realized filtering forms are much slower.

A- Is there a magic/trick to have things run faster (or at least how they used to work) upon conversion?
B- Can someone help me convert this Access query to SQL to run it in the most efficient matter?

SELECT Skilled_Nursing_Visit_Note.ID, SNV_Printed_History.VendorsID, SNV_Printed_History.SNV_ID, Skilled_Nursing_Visit_Note.Client_Last_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, Skilled_Nursing_Visit_Note.Visit_Date, Skilled_Nursing_Visit_Note.Shift_From_Hour, Skilled_Nursing_Visit_Note.Shift_To_Hour, Skilled_Nursing_Visit_Note.Date_Signed, Skilled_Nursing_Visit_Note.Nurse_Name_Stamp_SNV, Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, SNV_Printed_History.ReviewedBy, SNV_Printed_History.ReviewedDate, SNV_Printed_History.PrintedDate, SNV_Printed_History.PrintedBy, SNV_Printed_History.PrintedDate, Skilled_Nursing_Visit_Note.Client_First_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, Skilled_Nursing_Visit_Note.Date_Of_Birth, Skilled_Nursing_Visit_Note.Shift_From_Minute, Skilled_Nursing_Visit_Note.Shift_To_Minute, SNV_Printed_History.ReviewedDate, Skilled_Nursing_Visit_Note.Treatments_Administered, CInt(IIf([duration]=0,1440,[duration])/60) AS DurationHr, SNV_Printed_History.NoPrint, Skilled_Nursing_Visit_Note.Status, Skilled_Nursing_Visit_Note.Client_Last_Name_Init, 

Open in new window

0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Please guys i need to achive an update to a table

update actividades1 set codigo= SUM(CAST(a.hrs as decimal(15,1))) from hrsh a, actividades b where  b.descripcion=a.disciplina and a.actividad=b.actividad

Open in new window


the error

An aggregate may not appear in the set list of an UPDATE statement.

Please you help me experts!
0
This is SQL 2014.

I have a table with 3,000,000 rows something. It has lots of columns and we're thinking about using this flat file as our search table.

Can I have Full Text Search applied to this table only and not affect the rest of the database? My manager says something about FTS is not portable and will be a hassle to port to production database.

We do wildcard search on 6 of the columns
0
Hi,

I have something somewhat similar to the Data Connections, Data Sources, and Connection Strings (Report Builder and SSRS) with the Expression-based Connection Strings. This is with SSRS 2014.

But when I go to publish it's asking for datasources on the Report Server -> Report Manager. How would/could I do the Expression-based Connection Strings with that?

Any information on that would be greatly appreciated.

Thanks
0
I need an SSIS Example project (VS Shell 2013) That Uses a 2003 version of Excel file(s)

And 1 by 1 pulls in the file... sends the data to SQL
Then renames the file to another folder
0
I'm having a problem with my SQL server sending out email through my Exchange 2010 server. This process has been working for quite a while and then starting yesterday I started getting failures with emails going out. There are some emails that are going out but others that are not. When I see a successful email and look at the details in the Database Mail Log I see that the Last Modified By entry to be my SQLAGENT account but when I look at the failed emails it shows SA. The message I see in the logs varies but essentially they all say:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (date/time), Exception Message: Cannot send mails to mail server. (The operation has timed out.) or it says (Failure sending mail.) or (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host failed to respond 192.168.0.xx:25)

I have checked the Exchange server to make sure the SQL server can relay through it. I have checked the SQL server and ran test database mail emails which are successful but I can't seem to find the issue with this error. Also when the user tries to send the email out again through our ERP system it works.

Suggestions? Where can I check on the Exchange server to see if the email request is getting to the server and why it is failing?
0
I need to create a join that displays EmployeeID and Rate based on the AttendantCount
Currently the Rate and AttendantCount are displayed as rows but I need to display the data horizontal.
I was told this could possibly be done using PIVOT in t-sql.
I have attached a screenshot of what the result should look like

The CommissionLevelDetail table contains CommissionLevelID, AttendantCount, Rate
The CommissionLevelAssignment table contains CommissionLevelID, EmployeeID


Screenshot of expected resultstbl_CommissionLevelAssignment.xlsxtbl_CommissionLevelDetail.xlsx
0
I have an SSIS package that is giving me an error in our test environment. It runs fine in DEV, but errors in test. It is a data flow task with an OLE DB source that executes a SQL Servr stored procedure. The code is:
EXEC [uspME-GF-30250-HMS_HD] ?,?,?,?
WITH RESULT SETS(
(	
	ORDERS VARCHAR(1),
	RECORDS VARCHAR(1000)
 )
)

Open in new window

The error is:
Error: 2018-11-14 09:24:03.48
   Code: 0xC0202009
   Source: DFT_LOAD_MEMBER_EXTRACT_FOR_HMS OLE_SRC_MEMBER_EXTRACT [9]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "Invalid column name 'CXCX_RECORD_NO'.".
End Error

I thought of removing the WITH RESULT SETS but I can't even save the changes. SSIS gives me the following error:
The metadata could not be determined because the statement 'INSERT INTO #StaticData...' uses a temp table.
The stored procedure is 'relatively' complex, creating and loading several temp tables, some intermediate staging tables, them finally building the data extract result. I've never run across this issue before and am looking for a solution. Checking Google for…
0
I need to make this SP get permissions pretty close to the way I wrote the SP below.  The problem is a single PeopleID can belong to multiple custom groups.  I don't know how to write it!

I'll post the SP that would work if the PeopleID could only belong to 1 custom group.


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_GetPeoplePermissionsByType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_GetPeoplePermissionsByType];
GO
CREATE PROCEDURE [dbo].[usp_GetPeoplePermissionsByType]
(
		@PeopleID INT,                     
        @GroupID INT,                       
        @GalacticID INT,                       
        @PermissionType nvarchar(256)
)
AS
BEGIN    
    SELECT x.ID
	FROM [dbo].[Permission] x
	LEFT JOIN [dbo].[PermissionGroupCluster] g ON x.PermissionID = g.PermissionID
	LEFT JOIN [dbo].[PermissionCustomGroupCluster] c ON x.PermissionID = c.PermissionID
	LEFT JOIN [dbo].[PermissionPeopleCluster] p ON x.PermissionID = p.PermissionID
	WHERE x.PermissionType = @PermissionType AND x.GalacticID = @GalacticID AND x.Active = 1 AND x.Admin = 0 AND x.Staged = 0
	AND g.GroupID = @GroupID AND p.PeopleID = @PeopleID
END
GO

Open in new window



Model
0
sql server 2012 when i login as below it shows some values which are unclear to me under server name

how to make sure to put server names some meaningful names to understand

instead of say
CXXXXX1234.xy.LMP.com,99999
i like to give alias name like
Production Database
or
System Test
etc

so that i do not need to refer every time when i login to check documentation to see where i am making changes etc

also how to connect multiple environments same time paralleled like production and System Test, integration etc
do i need to open microsoft sql mgmt tool multiple times or can i open separate tabs in one instance one underneath other?
Where can i refer for good usage tips and learning? any good vidoes or resources around this

what is difference between
windows authentication
and
sql server authentication
when to use which one

please advise
SQLServer2012_NAmeEnv.png
0
Determine the Perfect Price for Your IT Services
Determine the Perfect Price for Your 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 with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hello,

I try to open the properties Windows of the \\.\pipe\microsoft##wid\tsql\query WID database on a Windows Server 2016 essentials to limit the maximum amount of memory available from the SQL Server Management Studio , but I receive this error:

TITRE : Microsoft SQL Server Management Studio
------------------------------

Impossible d'afficher la boîte de dialogue demandée.

------------------------------
INFORMATIONS SUPPLÉMENTAIRES :

Impossible d'afficher la boîte de dialogue demandée. (SqlMgmt)

------------------------------

Échec de la récupération de données pour cette demande. (SqlManagerUI)

Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------

Une exception s'est produite lors de l'exécution d'une instruction ou d'un lot Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Une erreur grave s'est produite sur la commande actuelle. Les résultats éventuels doivent être ignorés.
RegQueryValueEx() returned error 2, 'Le fichier spécifié est introuvable.' (Microsoft SQL Server, Erreur : 0)

Pour obtenir de l'aide, cliquez sur : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
BOUTONS :

OK
------------------------------
0
inserting number with decimals in table.column the decimal point is gone
the column type is numeric (18,10)

the database was moved from a sql 2008 srv to sql 2016

language settings on both servers are the same. english
connection string info is like
Response.CharSet = "UTF-8"
Response.CodePage = 65001

session("DatabasePath") = ""
session("DatabaseAddress") = "localhost"
session("DatabaseName") = "dbname"
session("DatabaseUsername") = "dbuser"
session("DatabasePass") = "1234asdf"(example)

Conn =      "Provider=MSDASQL;Driver={SQL Server};" & _
            "Server=" & session("DatabaseAddress") & ";" & _
            "Database=" & session("DatabaseName") & ";" & _
            "Uid=" & session("DatabaseUsername") & ";" & _
            "Pwd=" & session("DatabasePass")

session("site_Conn") = Conn

the users types values like 1,23 (european)
it then gets converted to 1.23
but in dbase value is 123

Maybe the conversion from 1,23 to 1.23 is allready going wrong?
0
/*14.      Create a table variable using data in the dbo.HospitalStaff table with the following 4 columns
a.      Name – Located in the NameJob Column : Everything before the _
b.      Job – Located in the NameJob Column : Everything after the _
c.      HireDate
d.      City – Located in the Location Column : Everything before the –
e.      State – Located in the Location Column : Everyting after the –
*/

DECLARE @New_Hospitalstaff TABLE(
                            NAME Varchar (50),
                                          JOB   varchar (50),
                                          HIREDATE    Datetime,
                                          CITY  varchar (50),
                                        [STATE] varchar (50)
)
INSERT INTO    @New_Hospitalstaff

       
        SELECT  left(location, CHARINDEX('-', Location) -1)as staff_city
               right(location , len(Location)-CHARINDEX('-',Location)) as staff_state

       
                    
 FROM DBO.HospitalStaff

SELECT *
FROM @NEW_HOSPITALSTAFF



what did I get wrong in my syntas
0
Hi Experts
As I am currently extract data in excel file via MSSQL from local computer in which there is linked server with Oracle Database, I got errors message for excel -2147417484 (80010108).
But I am successfully extract data if small dataset. What should I do? Transfer to sever side instead of local computer? Please kindly advise

Many thanks
1
Hi All,

I have below code :

Private Sub frmTHGRPOBELIWITHROLL_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing

        If intMode = ERV_Global.MB_UPD Then

            Dim trxTransaction As SqlTransaction = Nothing
            Dim cmdSQLCommand As SqlCommand = Nothing

            Try

                Using conApp As SqlConnection = New SqlConnection(strSQLInvCompanyConnection)
                    conApp.Open()

                    cmdSQLCommand = conApp.CreateCommand()
                    trxTransaction = conApp.BeginTransaction
                    cmdSQLCommand.Transaction = trxTransaction

                    Update_Total_Qty_Transaksi(cmdSQLCommand)

                    trxTransaction.Commit()
                End Using

            Catch ex As Exception
                trxTransaction.Rollback()
            End Try

        End If

    End Sub

Open in new window


Is those code enough if it has exception at any point ?
I sometimes get error message at      trxTransaction.Rollback(),  if the transaction has been commit some where.

Thank you.
0
MS SQL corruption error

Getting this error on a sbs 2011 server

The description for Event ID 8646 from source MSSQL$MICROSOFT##SSEE cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

1
692197516
SUSDB

The specified resource type cannot be found in the image file

Any ideas?
sqlerr.PNG
0
I am newbie to SSAS. I created the DSV, a few dimensions and a cube. Once I deployed the cube, it got so many error message. I assume that there might be some configuration I did not do properly. Please advice! Thank you!

Capture.PNG
0
excel document every time i open asking to save changes even though i have not made changes? how to avoid this message prompt every time(even i made no changes) which i have to click no button
please advise
0
Hi All,
 
I have trigger below :

USE [YSL_TRADING]
GO
/****** Object:  Trigger [dbo].[TRG_INSERT_UPDATE_DELETE_TMSTOKBARANGDETIL2018]    Script Date: 11/9/2018 9:01:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TRG_INSERT_UPDATE_DELETE_TMSTOKBARANGDETIL2018] ON [dbo].[TMSTOKBARANGDETIL2018]  
AFTER INSERT, UPDATE, DELETE
AS  
BEGIN
IF UPDATE(QtyTransaksi)
BEGIN

	SET NOCOUNT ON

	BEGIN TRAN
		INSERT TMSTOKBARANGTOTAL2018 ([TahunBulan], [GdgCode], [BarangCode], PPN, [SaldoAwal], [QtyStokMasuk], [QtyStokKeluar])
		SELECT DISTINCT CONVERT(VARCHAR(6), i.TglTransaksi, 112), i.GdgCode, i.BarangCode, i.PPN, 
		ISNULL((SELECT TOP(1) ISNULL(A.SaldoAwal, 0) + ISNULL(A.QtyStokMasuk, 0) - ISNULL(A.QtyStokKeluar, 0)
		FROM TMSTOKBARANGTOTAL2018 AS A
		WHERE A.TahunBulan < CONVERT(VARCHAR(6), i.TglTransaksi, 112)
		AND A.BarangCode = i.BarangCode 
		AND A.GdgCode = i.GdgCode AND A.PPN = i.PPN
		ORDER BY A.TahunBulan DESC
		), 0), 0, 0 
		FROM inserted i
		WHERE NOT EXISTS (SELECT NULL FROM TMSTOKBARANGTOTAL2018 S 
		WHERE S.BarangCode = i.BarangCode 
		AND S.GdgCode = i.GdgCode AND S.PPN = i.PPN
		AND s.TahunBulan = CONVERT(VARCHAR(6), i.TglTransaksi, 112))
	COMMIT TRAN

	BEGIN TRAN
		UPDATE S SET QtyStokMasuk = ISNULL(s.QtyStokMasuk,0) + (ISNULL(i.QtyStokMasuk,0) - ISNULL(d.QtyStokMasuk,0))
			, QtyStokKeluar = ISNULL(s.QtyStokKeluar,0) + (ISNULL(i.QtyStokKeluar,0) - ISNULL(d.QtyStokKeluar,0))
		FROM TMSTOKBARANGTOTAL2018 S
		OUTER APPLY 

Open in new window

0
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

-- 12.Find the State with the highest Avg UPB.

SELECT  [state],avg(UPB) AS Avg_UPB
FROM Loan
group by [state];


SELECT MAX(AVG(UPB))
FROM Loan
GROUP BY [STATE];

--I get this msg when I execute  it , how do I get the highest avg upb

Msg 4145, Level 15, State 1, Line 247
An expression of non-boolean type specified in a context where a condition is expected, near 'group'.
0
Has anybody used Azure Site Recovery to protect a SQL Server 2008 standard edition cluster?

I am just trying to wrap my head around how the data gets sync'ed. Typically a hot data synchronization is an enterprise feature however this article states that ASR can be used to protect a SQL Server 2008 SE fail over cluster.

I don't see any details on how the data sync works though.
- https://docs.microsoft.com/en-us/azure/site-recovery/site-recovery-sql

Can anybody share their experience with Azure Site Recovery specific to SQL Server 2008 standard edition.

Thanks experts!
0
Sql 2016

I need to find a record(s) that contains two different individuals names as specified by the user.

The person names are stored in the Person Table.
Person Table

Fields:
agency
Year
ID
Sequence (incremented for each individual in a particular record)
Name
Given1
 
The record for individuals would look like this.

Agency   RecordYear    Record ID   Sequence    Name     Given1
1               2018                   1                    1              Allen     John
1               2018                   1                    2             Erwin    Mary
1               2018                   2                    1             Smith    Joe
1               2018                   3                    1             Kline     James
1               2018                   4                    1            Allen     Karen

Currently I have the following sql:

select *
 from Person
      where (name like 'Allen' or name like 'Erwin')
      --where name in ('Allen','Erwin')
      and agency = 1
      and year = 2018

This returns the record I am looking for but also any other record with the name Allen or Erwin.

1               2018    1         1              Allen     John        <----  Should return  this
1               2018    1         2             Erwin     Mary       <----  Should return  this
1               2018     4        1             Allen      Karen      <----  Should not return

I want to only return the record(s) with both …
0
Good day experts,

Here are my issues with an SSRS I've got to get working:

1. I'm trying to build a report that should have details in columns ( left to right or horizontally ) rather than rows.
    For this, I was able to create a Matrix, delete the Row groups and then create the details in the columns groups, as you can see in the attachment. It seems to work however I'm mentioning it because it might be the issue with the page break.

Example of my grouping inside a matrix tablix.
2. Then I'm trying to apply page break to this column group.
    For this, I created an expression on the column group that is: =Ceiling(RowNumber(nothing)/10) in order to have ten details per page. I then apply page break option to this group however it doesn't apply the page break. The grouping on the expression is correct though. (see attachment)

Enter of page break not working
Could you please help me understand what I'm missing or what new approach I should have to solve this issue.

Regards,
0
I need to call a rest api from my sql server, so I found a way of doing it but to make the code to run I need to enable
Ole Automation Procedures in sql server by running this query

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO  

What I need to know is for sql server database security wise, is it good practise to call web api from a Stored procedure. As I am not a sql server dba or security expert.
I am open in there is any other way also.
Please let me know
0
Hi Support

How to convert the BACPAC file to bak file format.

what is the free tools that we can look for?
0

Microsoft SQL Server

162K

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.