[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

Scenario:

Table : Supp
Columns:
AppUser
SQLUser
DateCreated
DateModified
SuppCode
SuppDesc
Address
SuppType

Whenever application changing 'SuppDesc' column data in 'Supp' Table, we need details include hostname (user modifying application from his desktop) through email alert.

Version : SQL Server 2008 R2.

Please help me with solution.

Thanks,
Chandra
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.

Hi All,

My company is using Microsoft Dynamic RMS (Retail Management System) as retail application. In store Operation manager, I have to create 18000 new items in it. I have got all the information for all the items includes name, description, cost, supplier, quantity and all rest of the information to create item in the system. It is almost impossible to do it manually. I have google online for import utility but got no success and also have no idea about the required format in CSV file to import all these items and how to import it?

Can anyone help me with this?

Thank you in Advance.
0
I have two tables, one with a list of customers and their price multiplier and the other table has a list of parts and their price.

I need to create a table that for each customer number creates a list of the parts and their price and customer price multiplier.

Scustpart.xlsxee example in the attached spreadsheet
0
  • Type: System.Data.SqlClient.SqlException
  • Class: 20
  • LineNumber: 0
  • Number: -1
  • Server: DBServerName
  • State: 0
  • Source: .Net SqlClient Data Provider
  • ErrorCode: -2146232060
  • Message: <ii>A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)</ii>

Usually what will causing this event?  and notice it only happen on selected time frame...  
I not sure is the SQL Application issue or the OS layer (e.g. during VSS the SQL is stop processing.. or others...)
0
Hi  Experts
I Have created stored procedure.. and getting desired result... now i want to export that result into csv file ..
but using stored procedure.. Not using SSMS

please assist me ..

Note:
Database is : sql server 2014 express edition:
0
Hello,
I am using Xamarin crossplatform app with VS 2017. Any example to create an app connecting to SQl Server DB (on prem).

Thanks
0
Visual Studio 2015, SSDT, Report Authoring, Windows 10

I am having a very difficult time getting my VS2015 installation to include SSDT and Reporting Authoring Extension (for Fetxchxml).  I have tried suggestions from the web, Stack Overflow and downloaded several versions of SSDT and Report Authoring Extensions.  I have uninstalled versions that didnt work, rebooted and tried again.

I am thinking that if I can get the versions from someone where all three components are working on Windows, I should be able to get this working.  I have had this working on other laptops, older versions of VS and other operating systems.

My environment is:
Dell Latitude i7 | 64-bit processor | x64 processor
Windows 10  | Version 1803  |Build 17134.167
Visual Studio Professional 2015  | Version 14.0.25431.01 Update 3

As for SSDT, not having a lot of problems installing this; it is where I try to install the Report Authoring Extensions that I encounter problems.  I am able to get the RAE wizard to run (to failure) using the advice from this link: http://meganvwalker.com/creating-fetch-xml-reports-using-visual-studio/

When the RAE install fails it tells me that SSDT is not installed; I have tried many versions of SSDT, SSDT-BI, BIDS and Report Authoring starting with the VS2015 versions (and earlier versions).

I am just spinning my wheels.

Thank you.
0
I have 2 database on 2 different server there both the same. one if on the cloud and 1 local i want to have it local sync with the cloud or when the cloud access is loss the local take over and when cloud could be access again let the local database update back the cloud.
0
Hello,

Any idea please how to convert Date stored in SQL Server column with type bigint to normal Date format.
bigint is totally a different format storing a date.

Thanks.
0
Installing a new 2016 server.  Loaded SQL Express 2017 onto it and created an instance.  I am able to see the instance using SQL Management Studio on the server.  Client PCs on the network can't see the SQL instance.  I created runs int eh server 2016 firewall following the Microsoft KB and clients still can't see the SQL instance on the network.
0
Acronis True Image 2019 just released!
LVL 1
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

How can I compare my ONE table named "CUSTOMERS" in my two Microsoft SQL 2014 databases (TEST and PRODUCTION) to see what fields changed ?

Example
 Column Name, DatatypeTEST, DatatypePRODUCTION
  CUSTOMERID, varchar(10), nvarchar(20)
  etc

Maybe something like the below ?
 1. https://solutioncenter.apexsql.com/compare-sql-server-database-schemas-automatically/
 2. https://www.red-gate.com/products/sql-development/sql-compare/
 3. https://www.mssqltips.com/sqlservertip/4824/easy-way-to-compare-sql-server-table-schemas/
0
This SP builds a comma delimited string that is used to populate a list. Calling it in SMS works fine:
declare @TasksCompleted varchar(500) 
declare @PropertyMaintenance_ID uniqueidentifier = 'EDC35FCC-7E5F-E811-80C6-00155D050902'

exec [HTS].[TaskCompleted_SELECT_CompletedTasks_04] @PropertyMaintenance_ID, @TasksCompleted OUTPUT
select @TasksCompleted

OUTPUT: 100,101,104,105,106,107,109,200,201,202,203,204,205,206,207,300,301,302,303,304,306,307,400,402,404,406, 

Open in new window

When called with SqlClient from the GUI, it returns "", although with no errors. It's just "".
ALTER PROCEDURE [HTS].[TaskCompleted_SELECT_CompletedTasks_04]
(
	@PropertyMaintenance_ID	as uniqueidentifier,
	@TasksCompleted			as varchar(500) OUTPUT
)
AS

declare @objcursor as cursor 
 
declare 
    @vsql       as nvarchar(max)
    ,@vquery    as nvarchar(max)
    ,@id        as int
    ,@value     as varchar(50)
    
DECLARE @list varchar(1000)
DECLARE @listTemp varchar(100)
DECLARE @item varchar(300)

set @vquery = 'SELECT DISTINCT Task_CD from HTS.TaskCompleted WHERE PropertyMaintenance_ID = ''' +  
					CONVERT(varchar(36), @PropertyMaintenance_ID) + ''''

set @vsql = 'set @cursor = cursor forward_only static for ' + @vquery + ' open @cursor; '
 
exec sys.sp_executesql
    @vsql
    ,N'@cursor cursor output'
    ,@objcursor output

fetch next from @objcursor into @item

while (@@fetch_status = 0)
begin

SET @listTemp =  @item

SET @list = concat(@list, @listTemp + ',')

 fetch next from @objcursor into @item
end

close @objcursor
deallocate @objcursor
SET @TasksCompleted = @list

Open in new window

When a GUID is passed as a string literal it works. If passed as a variable it fails.
 	Dim cmd As New SqlCommand("[HTS].[TaskCompleted_SELECT_CompletedTasks_04]") With {
		.CommandTimeout = 60,
		.CommandType = Data.CommandType.StoredProcedure
	}

	cmd.Parameters.AddWithValue("@PropertyMaintenance_ID", g_CurrentPK)	 < --------------------------------this fails
	cmd.Parameters.AddWithValue("@PropertyMaintenance_ID", "edc35fcc-7e5f-e811-80c6-00155d050902") <------ this works

	Dim TasksCompleted = New SqlParameter With {
		.Direction = ParameterDirection.Output,
		.SqlDbType = SqlDbType.VarChar,
		.Size = 500,
		.ParameterName = "@TasksCompleted"
		}

	cmd.Parameters.Add(TasksCompleted)

	Dim conn As New SqlConnection(mConnectionStrings.ConnHortDB)
	conn.Open()
	cmd.Connection = conn

	'Dim str As String
	cmd.ExecuteScalar()
	Return TasksCompleted.Value.ToString
	conn.Close()

Open in new window

Can anyone see what's wrong? Thanks much for any help.
0
Good Day Experts!

I have quite the dilemma here.  I have designed my query and it works fine in SMS.  I am trying to go some grouping on the report but perhaps it is not possible.  So can you give me your feedback please?

I have my query in an embedded dataset.  Now I want to run several different grouping patterns over the resulting Student data.

1)  Group by grade with the count of students in each grade
2) Group by teacher with a count of how many students for each teacher
3) Group by gender with a count of how many male or female

I was think it would be "neat" if I could have multiple tables on the report for each Item. One table for the Grade counts, one table for the teacher counts and one table for the gender counts.

If this is not possible, I am open to other design solutions as I am new to SSRS reporting.

Thanks,
jimbo99999
0
MS Access crashes when attempting to attach SQL Server tables using ODBC
0
I have installed SqlServer express 2017 and am looking to access it remotely. I have opened ports 1433 TCP and 1434 UDP and I and can access the server, but it comes up Login failed for user.
I then tried mixed authentication and have set up an sa user and can log in using it on ssms locally, but even specifying sa and the password I still get login failed for user. I then selected to enable a proxy account but still no joy.
0
I need to fetch the aggregrate count data from 3 different tables and have them listed as  column value in a single row.

eg.

TABLE_1

SELECT DEPARTMENT_ID,COUNT(*) AS TOTAL_COUNT FROM TABLE_1 GROUP BY DEPARTMENT_ID  

TABLE_2

SELECT DEPARTMENT_ID,COUNT(*) AS TOTAL_COUNT FROM TABLE_2 GROUP BY DEPARTMENT_ID  

TABLE_3

SELECT DEPARTMENT_ID,COUNT(*) AS TOTAL_COUNT FROM TABLE_3 GROUP BY DEPARTMENT_ID  


Now , I want the the consolidate data from these 3 tables to be displayed in a view.

DEPARTMENT_ID, TABLE1 COUNT, TABLE 2 COUNT, TABLE 3 COUNT
10,5,2,3
11,2,3,4
12,1,2,3
etc
How can I do this transformation.

Thank you.
0
Hello, all.
(ASP Classic)

Splitting strings, in my case, I have multiple values coming from the database.

a = 94/95
b = value1/value2

Open in new window


Now, on the page, it will be.

<a href="?Type=Label&LabelID=<%=x%>" title="<%=y%>"><%=y%></a>

Open in new window


I can use a split, to get the values broken up.
However, since we are dealing with 2 different values coming in, I cannot use the split and it duplicates everything.

<%
a = split(LabelID,"/")
b = split(LName,"/")
for each x in a
for each y in b
'response.Write (x & "<br />")
%>
<a href="?Type=Label&LabelID=<%=x%>" title="<%=y%>"><%=y%></a>
<%
next
next
%>

Open in new window


So, my question is this.
How can I split it properly, to where it is only a single code that split both values?

Wayne
0
I have a parent table and child table. it is a one to many relationship.

child IDs are in one field. I want to convert each child ID into it's own field by concatenating child and position (child + #, i.e. child1) and join all the new child fields to the parent field.
I would then get a row of parent and child fields. there can be any number of children.

i.e.
parent ID, child1ID, child2ID, etc. for each unique child of a parent.

parent| child1| child2| child3|
John, Jimmy, Jan,Jerry
Don, Danny
Nancy, Mary, Mike
0
Just trying to find out more information about SQL SSIS which I know nothing about.  Can SQL SSIS be used ingests a Multi-Worksheet EXCEL file and separate each worksheet into a separate output file like text or csv?  We are think about using SQL SSIS as a preprocessor for an application that does not support multi-sheet EXCEL files.  I am aware we can use SQL SSIS to do data transformations but now i want to know if this can file re-engineering tasks like this.  Also, SQL SSIS be configured to generate an XML file?
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 am getting this attached error when setting up a listener to a newly created always-on availability group. It throws this error every time I try to create one. The users I am using has all the rights on the domain and on the both the sql boxes.

Any idea?
alwayson_error1.JPG
0
I have a spread sheet that connects to an outside database and draws data from predetermined views.

This spread sheet has 20 connections.

When I first start the spread sheet up it almost completely locks up.  The database slows down horribly as well.

After about 5-10 minutes the spread sheet finally delivers an error message like the following:

The following data range failed to refresh:
datasets.mydomainname.com program 2017 product
Continue to refresh all?

If I click "yes" it finishes its data refresh and all is well.

If I look under data, connections, this connection does not seem to exist.  I would love to find this connection and delete it.  This spread sheet is supposed to be 2018 and all 2017 data is outdated.

I am using Excel 2013 and am connecting to SQL server 13.0.4451

Thoughts??
0
My understanding of the XML language is that it is used to store and transport documents across the world wide web, but I have yet to experience some specific examples of it being used.
One that I thought of is, that if I wanted to move some data from a SQL Server database to an Oracle database, then I would transfer it as an XML document.
Is this how it would be done ?  
What other examples can you experts think of ?
0
Hi,
Is there a way to extract Month Year e.g. Sep 2018 as date data type from a date column in SQL Server 2014.
It should be one column not two columns.
Thanks.
0
Hello,

I am trying to incorporate a trick to dynamically set the FROM clause within the following code that I am using to send an HTML-formatted table using DBMail (sp_send_dbmail).

First off, the trick that I have used on it's own:

DECLARE @SQLQUERY nvarchar(512) = 'SELECT * FROM [' + @DYN_DB_NAME + '].[dbo].[' + @TODAYS_DATE '];'
EXECUTE sp_executesql @SQLQUERY;

Open in new window


I found this code on Microsoft's documentation page for sp_send_dbmail to send a formatted HTML table:

DECLARE @tableHTML  NVARCHAR(MAX) ;  

SET @tableHTML =  
    N'<H1>Work Order Report</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +  
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +  
    N'<th>Expected Revenue</th></tr>' +  
    CAST ( ( SELECT td = wo.WorkOrderID,       '',  
                    td = p.ProductID, '',  
                    td = p.Name, '',  
                    td = wo.OrderQty, '',  
                    td = wo.DueDate, '',  
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty  
              FROM AdventureWorks.Production.WorkOrder as wo  
              JOIN AdventureWorks.Production.Product AS p  
              ON wo.ProductID = p.ProductID  
              WHERE DueDate > '2004-04-30'  
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2   
              ORDER BY DueDate ASC,  
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC  
              FOR XML 

Open in new window

0
I'm needing to find when an order is on time.  The issue is some orders can have items that are considered on time while other items are not.  I need to find when the whole order is on time.  

select order_no, item_id, CASE WHEN (DATEDIFF(dd, dateadd(day,1, requested_date), ship_date)) <= 0 THEN '1' END AS item_on_time
from sales_history
GROUP BY order_no, item_id, requested_date, ship_date

So below is what I'm needing which would be column D
 2.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.