[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

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
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!

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
Hello Experts!

I have create a macro called AutoExec to run the function below:
Public Function SqlLinker()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed Successfully", vbOKOnly, "CA Premier Accounting Package"
End Function

Well the function links the tables in SQl Server at runtime and if the ODBC 17 path is correct a message with Re link completed Successfully pop up. All is well as long as I continue using my laptop the problem comes up if I install the same database on a different server client stand alone network not connect with my laptop because the new path is supposed to be:
constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=PETER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Now how best I can be changing the path below to be reference the correct server name? For example if the same name changes to CHRIS is there a way to alter the path below:

constr = "ODBC;DRIVER=SQL Server; " & _
"SERVER=USER\SQLExpress;DATABASE=Accounting;Trusted_Connection=Yes"

Example where there is USER change to CHRIS,the challenge here is how to change the code manually because we always send an accde format to clients. Some are suggesting to  put this string below in …
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
Hello Experts,

I have stored Images in MS SQL database in image data type format, I want to to read the Image from database and save the Image in a specific folder via a Batch file.

I can read the Data from Sql Server via Batch file but I don't know How I can convert and save image in a specific folder .

Any one can suggest me best way to do this.
0
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
Experts,

I routinely build Access Databases as desktop processes to analyze data. I'm looking to understand the best way to handle the following situation:
Every query I run joins tables on Cost Center. My Cost Centers are 10 Char Alphanumeric. I've already learned from previous questions that I should create a Cost Center master table and use the Autonumber (or other short numeric) field as a CostCenterID for use in joins because its more efficient.
So now let’s say I have a
1.      Cost Center Table with 2000 cost centers and a CostCenterID (Autonumber) field that I’ll use as my primary key, and a
2.      Transaction Table with 1mil records that I want to join on the Cost Center table, but the CostCenterID does not yet exist in the Transaction Table.

How is performance effected if I:
1. Use an Update Query to add the CostCenterID from the Master Cost Center table as a foreign key to any Transaction Tables, so Cost Center and Transaction tables can be joined directly, or
Use Interim Query/Tables
2. Use a Select query to create an interim relationship.
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber)
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window

3.      Create an interim table with a make table query
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber) INTO tbl_Bridge
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window


One opinion I was given is that option 1 is best. I just want to make sure as I will be basing many processes going forward off of this method

Thanks!!
0
Hi,
am Getting an error when importing a file using SSIS
The "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" failed because truncation occurred, and the truncation row disposition on "Flat File Source.Outputs[Flat File Source Output].Columns[TEST_ACT_TYPE]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.  
An error occurred while processing file "\\testfolder\\Inbound\test_file_123756.TXT" on data row 1.  
any guidance appreciated
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
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.

I need regular expression for following in Sql Server ( I hope it is same in vb.net or c#)
I am looking for a pattern  someletters somenumbers  some letters- someletters somenumbers  some letters     ( All capital letters)
  examples  AA111WW-B123SS
                      AB1DFG-S3ERT

   Another pattern I am looing is   somenumbers someletters somenumbers -someletters
     examples   11AAA123-SDFG
                          1AB23-AB
1
We have a SQL Server database with assets (financial products and linked objects, like loans, mortgages, real estate). An asset can be related to one or more customers. A customer can be related to one or more assets. See this table for an example.

Source data
I need to group all customers that share an asset - even when it's an indirect relation. For example, customer a shares assets with customers b and c. But c also shares an asset with d. This links d not only to c, but also to a and b. The same goes for e, f and g. Customers f and g share no asset, but they both share an asset with e - that makes them part of the same group.

Customer groups
I'm looking for a way to find all customer groups in the database. For your information: there will be about 2 million customers and 3 million assets. A customer group is expected to have 1 to 20 members. The result in this example would be something like this:

Query result
Can anyone help me to find a solution, keeping in mind the data volume? Your assistance is of course much appreciated!

This code will create and populate a table with the data from the example above.

create table dbo.AssetCustomer
( assetId int
, customerId char(1))

Insert into dbo.AssetCustomer values (1000, 'a')
Insert into dbo.AssetCustomer values (1000, 'b')
Insert into dbo.AssetCustomer values (2000, 

Open in new window

1
/*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
I have a sqlcmd command which I would like to not execute manually in the cmd window or in a Windows batch file, but to run as a SQL Server job, but putting the sqlcmd command line in the job results in an error on every single part of the command line, although the SQL Server doc gives an example of a sqlcmd call as a job step. So I'm a bit lost.

My SQL Server agent job looks like this: 1 step with the following content:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe" -S MyServerName -i "C:\temp\My T-SQL.sql" -v DWH = MyDBName -o "C:\temp\My T-SQL Log.txt"

When parsing this, the job window already complains about the -S bit. If I eliminate that, it complains about the -i bit, so clearly, something is completely wrong with the whole thing, but what ? Must be simple, but I can't figure it out. And, needless to say, the job runs but fails with the laconic error message "incorrect syntax near" and mentions the sqlcmd.exe part of the call.

Can someone help me ?

Thanks
Bernard
0
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
Hello All;

I have a table where the column is a varchar.
(If it needs to change to an INT or Date, please let me know and I will do so.
It is not too late to make that change.)
In the column named = Years
It has the following example.

1978
1965
1985
2017
1962
1998
2012
2002
1958
1978

OK, What I need to do is the following.
For the years in the table, they need to output as

1950
1960
1970
1980
1990
2000
2010
etc...

Thanks.
Wayne
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 have the below query, which I've simplified as far as I can and still retain the warning:

SELECT LU.ID, CND.DataValue AS ClosedNotifiedDate
FROM  tblClientLoanDataDates AS CND
INNER JOIN tblLoanUnits AS LU 
ON LU.ClientLoanID = CND.ClientLoanID 
AND CND.ClientLoanID = 124880

Open in new window


The join between these two tables has a "No Join Predicate" warning:

NoJoinPredicate.png
These are both physical tables, the ClientLoanID is a basic int in both tables, there are no functions/UDF/views involved here.  I have to run this query in 2012 compatibility mode, as going past that is slowing down our database so much that it becomes unusable for the company after about 20 minutes.  I believe the new cardinality estimator is having issues with this for some reason.  I can offer up any more information that would help to solve this, but I can't think of anything else of value at the moment.
0
Microsoft Azure 2017
LVL 12
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.

Hi Guys,
I have got the requirement to get the maximum from a table which needs to perform the calculation first and then get the maximum. for example

             DECLARE @table TABLE (TableValue VARCHAR (50))
             INSERT INTO @table VALUES ('(1+200)/100'),
									   ('150/100'),
									   ('34'),
									   ('(1+5)/2 + 7');

Open in new window

How do i do the calculation first and then get the maximum from this table. I am using SQL Server 2014.

Thanks in advance
0
I'm using Sql server 2008

I have this query:

SELECT [Column1]
      ,[Column2]      
      ,Column3 = REPLACE(CONVERT(VARCHAR(MAX), Column3), '<', '< ')
      ,[Column4]
FROM [DMS].[dbo].[TestTable]


Column3 is ntext datatype.
Column 3 holds notes that are typed into a textbox.

With the existing query above, if column 3 contains a value like this:

test notes <br/> this is a test

then it changes it to this(it adds a space after the less than sign):

test notes < br/> this is a test


How do i revise my existing query so that if the value in column 3 has a less than symbol followed by a space already,
then don't add a space after it.

Only add a space after then less than symbol, when it's followed by any character other than a space.
0
I'm working in SQL Server 2012 and I'm having difficulty casting varchar as int for comparison between two tables.  

Here's some example DDL/DML:

CREATE TABLE #tbl1 (
      col1 int,
      col2 int
      );
CREATE TABLE #tbl2 (
      col1 varchar(255),
      col2 varchar(255)
      );
INSERT INTO #tbl1 (col1, col2)
VALUES (1, 2);
INSERT INTO #tbl2 (col1, col2)
VALUES (1.0, 2.0);

SELECT * FROM #tbl1 as t1
inner join #tbl2 as t2
on t1.col1=CAST(t2.col1 AS int)
where t1.col2=CAST(t2.col2 AS int)

And the error I'm getting:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2.0' to data type int.

If I do this:
SELECT 1.0 AS Original, CAST(1.0 as int) Casted
I get this:
-----------------------------
| Original |Casted |
-----------------------------
|      1.0     |    1       |
------------------------------
which is what I would expect.

I think I'm missing some basic foundational concept here.  Any help will be greatly appreciated.

Thanks!
1
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

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.