[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 had these specific related questions after viewing Upgrading from WS2003 to WS2016 (AD issues).

It seems there is a consensus that migrating (no in place upgrades) to new a DC/DNS server running WS2016 keeping DFL/FFL at 2003 for the time being, then the legacy fileservers folders can be migrated to new WS2016 servers subsequently, and finally raising DFL/FFL 2008 then 2016 when all legacy systems deprecated.

A. There is a WS2008R2 with ASP.net framework 3.x running a legacy asp.net 2.0 app and MySQL 5.x. Push to ASP4.0 framework and it falls over.

The original strategy was to leave it in place whilst a new version is developped.   There are some references to potential problems with the application and RPC in the proposed new environment.  Has anyone come across this or offer any insights in this scenario ?

B.  There is a SQL Server 2000 running on WS2003 with a DC role, under VMWare.  The same server runs Sun Accounts v4.x.  I know there are potential issues running SQL Server 2000 in the new environment, and am concerned the Sun Accounts system whilst am told it does not directly use AD, may have RPC problems similar to 1. above.

Any feedback or insights greatly appreciated.
0
Protecting & Securing Your Critical Data
LVL 1
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

The primary server is SQL 2012 standard. Is it possible to create a mirror in SQL 2017 standard version for the  SQL 2012 standard primary?
0
Hi,,
Is there a way to update a record using the "row number"? What happened is that I have three identical records in one table.  Now I need to put a different value in each record. The easiest way is to open the table and type in the value directly. But I am getting the error (see screenshot.)

Three rows are identical.  Is there a way to fix this? thanks
e.JPG
0
I have an SSRS project that was built with VS2010, and I need to upgrade this solution to VS2017.

I have installed VS2017 along with SSDT and msSQL 2017 on my workstation.  I have made sure that I can access the BI Templets for SSIS, SSRS, and SSAS. But when I try to open up the ssrs sln file I keep getting this message that says the load failed, but when I try to reload the project, I get the error message that the project has been moved.

How do I go about upgrading my old SSRS project to VS2017?

Thanks,

Rich
0
I've got a SSRS report which currently filters a dataset on "[ProgramID] = @ProgramID" and the report parameter is set to only allow a single selection.  This is currently done in the dataset which looks something like:

SELECT * FROM myTable WHERE ProgramID = @ProgramID

My client wants to change this to allow the user to select multiple Programs from the Program dropdown.

How do I modify the SQL query in my data set to accommodate this multi-select parameter?  I've tried a number of things and cannot seem to get it to work by modifying the SQL or by removing the criteria from the query and adding a filter to the tablix which uses the dataset.  I've searched and tried a number of "solutions" from here in EE and other web-sites and just cannot get this right.

Thanks
0
Hi, I'm trying to get an update query to work!  I have 2 tables with the destination table that has a date field (datetime)   (on an MSSQL server) and  the sourse is an Excel file I linked to in Access.

In my Query:

Field: Start_date
Table:  Monthly_rep
Update To: [Excel_rep].[start_date2]

the update To:  I tried to put in CAST , CONVERT  etc keep getting error!  

CAST([Excel_rep].[start_date2] as DATE)
CAST([Excel_rep].[start_date2] as DATETIME)
Cdate([Excel_rep].[start_date2])

ETC.

Thanks

Pete
0
Hello

I have this as  a  stored procedure

CREATE PROCEDURE [dbo].[spCreateInvoiceReceipt]
AS
BEGIN
      SET NOCOUNT ON;
      declare @id bigint
      INSERT INTO  InvoiceReceipt (CreateDate)
      VALUES (getdate())
      SET @id=SCOPE_IDENTITY()
     update InvoiceReceipt set ReceiptNumber=@id where id=@id
END


How can I do this with Entity Frameworks LINQ?
0
SSRS Subscription and Daylight Saving Time

What is the best way to handle  Daylight Saving Time in SSRS subscription


Can that be updated in DB ?

thanks
0
good day.

i have the following table:

ID, PK, integer
activityDescription, varchar
activityDate, datetime
activityState, tinyint
activityType, tinyint

sample data:

8, 'door', '11/16/2018 09:00', 1, 0
8, 'door', '11/16/2018 09:02', 0, 1

6, 'door', '11/16/2018 07:57', 0, 0
6, 'door', '11/16/2018 09:01', 1, 1

5, 'door, '11/16/2018 07:58', 1, 0
5, 'door, '11/16/2018 08:03', 1, 1
5, 'door', '11/16/2018 08:05', 1, 0

i want to display results that MAX(activityDate) where type 1 is > MAX(activityDate) where type 0.  therefore, using the data above the result-set would have IDs 8 & 6 but not ID 5.

will you please help me with the query - thanks!
0
This problem used to happen once or twice a year, but it's happening with higher frequency now, and I'm at a loss how to troubleshoot it - because the normal troubleshooting method I'd use is the temporary and unintended "fix" itself.

I have an SSRS 2012 report deployed in SharePoint (2013) mode on a Windows Server 2012. The report is configured to use a dynamic data source, such that it can be run against 1 of 5 similar sites (databases). When the problem arises, whether I run the report from IE or within SSDT itself, I get this red-herring message:

The error message
Here are some important outcomes from my troubleshooting so far:
  • My normal troubleshooting strategy is to gather the list of inputs the SP behind the report would use, and run the SP manually from SSMS. As soon as I run this query, the problem disappears
  • The date range on the report makes no difference. Even if the report ran last week on last week's data, setting the date range to last week when the problem arises this week still results in this message
  • The problem typically happens on 1 or 2 sites at any given time, but not all sites. The "fix" is to connect to each site's DB and run the query once in SSMS
  • I use the term "red-herring" because if it were a data issue, the problem wouldn't disappear simply by running the query in SSMS. One would expect to find a bad piece of data that would require correction
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Hi All,

I have below script.

DECLARE @Year CHAR(4) = 2018
DECLARE @Month INT = 10

DECLARE @FileLedger VARCHAR(100) = 'THLEDGER' + @Year
DECLARE @MonthFrom INT = 1
DECLARE @MonthTo INT = 1

DECLARE @SQL VARCHAR(MAX) = ''

SET @SQL = 'SELECT'
SET @SQL = @SQL + ' A.AccNo' 
SET @SQL = @SQL + ', CAST(0 AS INTEGER) AS NoSeq'

WHILE @MonthTo <= @Month

BEGIN
  
  IF @MonthTo = 1 
  BEGIN
     SET @SQL = @SQL + ', SUM(SaldoAwalDbt) + SUM('
  END
  
   IF @MonthTo < @Month
   BEGIN
    SET @SQL = @SQL + 'Debet' + FORMAT(@MonthTo, '00') + ' + '
   END
   ELSE
   BEGIN
       SET @SQL = @SQL + 'Debet' + FORMAT(@MonthTo, '00')  
   END

  SET @MonthTo = @MonthTo + 1
END

SET @SQL = @SQL + ') AS SaldoAwalDbt'

SET @SQL = @SQL + ' FROM ' + @FileLedger + ' AS A'

SET @SQL = @SQL + ' INNER JOIN TMGLACCNO AS B'
SET @SQL = @SQL + ' ON A.AccNo = B.AccNo'

SET @SQL = @SQL + ' INNER JOIN TMGLTYPE AS C'
SET @SQL = @SQL + ' ON B.GLTypeCode = C.GLTypeCode'

SET @SQL = @SQL + ' WHERE B.Global <> ''Y'''

SET @SQL = @SQL + ' GROUP BY A.AccNo'

PRINT (@SQL)

Open in new window


Is that any other better way ?

Thank you.
0
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
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
Here is a  sample row from a sql server 2012 table named custorders

CustID      OrderDate      OrderAmount
12345      2018-04-28      500.00


If I run the following query on custorders

SELECT TOP 25000 [CustID]
      ,[OrderDate]
      ,[OrderAmount]
  FROM [Test].[dbo].[CustOrders]

  where custid in (12345, 78901)

I would only get info for custid 12345 since custid 78901 does not exist

CustID      OrderDate      OrderAmount
12345      2018-04-28      500.00


Would like to "force" a row in the result set for custid 78901  with a date value of '01/01/1900' and a order amount of zero

so the query results look like


CustID      OrderDate      OrderAmount
12345      2018-04-28      500.00
78901  1900-01-01    0

Not sure best way to change the query above to do this
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 a query in MS SQL that shows me the count of records that meet the below criteria.

4 fields - CustomerName, Direction, Disposition, TimeStamp

I need all records where Direction = Outbound, Disposition = Voicemail, and there is a record after the timestamp from the first critera with the same customer name, direction = inbound.

Is this possible and if so how ?
0
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Hi,

I need help from EEs to get the the query. I have two tables.
Table A has ID that can link to Table B's ID.

I need to bring everything from Table A without duplication. As you can see in Table B, it contains everything from TableA
and some with duplications.

The logic is to bring everything from Table A and PID from Table B

The end result should look like this

ID              PID
1545043        1-001  
10018849       8-001  
10038557       32
10032746      
10555277       10-001
10010026       6-001
10039886       58-5
10037757        
10734379       85


If there is no duplication in Table B, get the PID; if there is duplication in Table B, get the one reads "001"

thanks
test.xlsx
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
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

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.