Microsoft SQL Server

159K

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

Greetings,

I have a remote SQL Server engine that I have to query and send the results to local csv files.  The tables are subject to change without notice and there are about 265 of them.  I have a sqlite db table of 256 tablenames with their queries I need.

because DTSX files require schema management in the input/output sections of the package, is there a way via script to engage the SQL Server Import/Export Wizard via script or other automated methods to generate dtsx packages based on the tablename and the source query set for it.

The reason why I'm suggesting DTSX is because I want to use the SQL Native driver to download the results.

Thanks
0
Get free NFR key for Veeam Availability Suite 9.5
LVL 1
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

When i check DB options :
Auto Create Statistics: True
Auto Update Statistics: True

It means that if i created any indexes in this DB will automatically update statistics? am i correct?

When i used below query:
The results are:
auto_created  user_created
           0                    0
It should be 1 becasue detabase level is True.

USE TestDB
SELECT o.name, i.name AS [Index Name],  
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC; 

Open in new window


I got confusion when we make true at database level, it should automatically set true to index level?  am i correct
0
SELECT cme.cmeID, cme.provID, providers.fullname, provtype.provtype, cme.FY, cme.totalfycme AS TotalCME, Sum(cmeexps.[$amt]) AS TotalExp, providers.lastname
FROM provtype INNER JOIN (providers INNER JOIN ((FYs INNER JOIN cme ON FYs.[FY] = cme.FY) INNER JOIN cmeexps ON cme.cmeID = cmeexps.cmeID) ON (providers.provID = cme.provID)) ON provtype.provtypeID = providers.provtypeID
GROUP BY cme.cmeID, cme.provID, providers.fullname, provtype.provtype, cme.FY, cme.totalfycme, providers.lastname, cme.fycme, cme.[$increquest]
HAVING (((cme.FY)="2018") AND ((Sum(cmeexps.[$amt])) Is Null Or (Sum(cmeexps.[$amt])) Is Not Null))
ORDER BY providers.lastname;


In the HAVING LINE it tells it to still show those people that have $0 in expenses, however it will not show the "$0" it just shows a blank. How do I tell it to show $0.00 when the person has no expenses?
0
I'm trying to connect to my database, but get the following error

Run-time '-2147467259(80004005)':
Invalid connection string

Here's the string

.ConnectionString = "Provider=SQLOLEDB.1;" & _
                        "User ID=trisnadi; Password=trisnadi;" & _
                        "InitialCatalog=Insurance"
    .Open

What am I doing wrong ?
0
Expert Advice (Kent Olsen)
me to use for adding Frieght to FactInvoiceDetails table

CREATE TABLE DimItemType (
  id   integer IDENTITY,
  TypeName varchar(30)
);

And populate it with the possible entry types:

INSERT INTO DimItemType (TypeName) SELECT 'DATA'
INSERT INTO DimItemType (TypeName) SELECT 'FREIGHT'
INSERT INTO DimItemType (TypeName) SELECT 'SALES TAX'
etc...

Add the column ItemTypeID (integer) to the FactInvoiceLine table.
When an item is stored into FactInvoiceLine, set the ItemTypeID value according to the data type.

My questions is :

how to combine FactInvoiceDetails Table and DimItemType and i have created view to transfer to FactInvoiceheader Table through
SSIS. (OLEDB source)

01. Following View is correct? or any issue
02.Then how to add the ProductID for the FactInvoiceDetail Table, becuase when i'm adding extraline for
Frieght and Tax
CREATE VIEW DW_FactInvoiceDetails
AS

SELECT invhdr.invNumber,0 AS InvLine,0 AS Qty,invhdr.FREIGHT_AMOUNT AS price,2 AS ItemTypeID --FRIEGhT is 2
FROM Invoiceheader invhdr
WHERE FREIGHT_AMOUNT <>0 --May be - also possible

UNION ALL

SELECT invLine.invNumber,invLine.InvLine,invLine.Qty,invLine.price,1 AS ItemTypeID --DATA is 1
FROM InvoiceLine invLine


UNION ALL

SELECT invLine.invNumber,NULL AS InvLine,0,invLine.Sales_Tax AS price,3 AS ItemTypeID --SALES TAX is 3
FROM Invoiceheader invLine
WHERE invLine.Sales_Tax NOT IN(0)

GO

Open in new window

Any comments greatly appriciated.

Many thanks
0
I have the following SQL Server query.

I was hoping to get help with this on performance.  The most important thing I need it to be able to sort by the 1st column in the order by so that its ascending and does not put nulls first.

I am running this in SQL mode 100 so I cannot use the nulls first or nulls last approach...I also do not seem to be able to get a case to work..

Please help.


SELECT Distinct top 2000 tContact.nid as nID,
iSNULL(tContact.cFirstName, ' ') as cFirstName,
ISNULL(tContact.cMiddleNme, '') as cMiddleNme, 
ISNULL(tContact.cLastName,' ') as cLastName,tContact.cSuffix, tContact.cDepartment, 
tContact.cTitle, tContact.cEmail as cEmail,tContact.cEmailAlt, tContact.lActive, 
 tContact.cClassify, tContact.cDesignate, tContact.cContactCompany,
 tContact.cPhone, tContact.cPhoneExt,tContact.cCellPhone,tContact.cHomePhone, ISNULL(tContact.cEIN,' ') AS cEIN,
 tSubscr.nid as nidSubscr, tSubscr.cUserID, tSubscr.lActive as lActiveSubscr, tSubscr.lAdmin as lAdminSubscr, tSubscr.dLastLogin, 
 (select cPhone1 from CADoc_CRM..tClient where nID = tContact.nIDClient) as cClientPhone, 
 Case When dupes.nIdContact is null Then cast(0 as bit) Else cast(1 as bit) End as lContactIsDuplicated, 
 Case When coalesce(changeRequest.status,1) = 0 Then Cast(1 as bit) Else Cast(0 as bit) End as lChangeRequestPending 

 FROM CADoc_CRM..tContact tContact 
 left outer join CADoc_System..tSubscrXCRMCont tSXCC on tSXCC.cIdCRMCont = tContact.nid 
 left outer join 

Open in new window

0
I have an MSSQL table, tblTimeZone, which has fields ZipStart, ZipEnd, TimeZone and State.  The values for ZipStart and ZipEnd are the first 3 digits of a Zip Code.  The TimeZone value is a digit from 1 to 6 representing the 5 US time zones plus an "Other" (#6) as a catch-all for un-found zipcodes.  There are 69 entries in this table

I have a second table, tblNames, which has names and addresses (including a zipcode field) of people. This table can have over 5,000 names.

What I need to do is using the zipcode from the tblNames look up the timezone in the tblTimeZone and create a sum of names for each timezone.

The values in the tblTimeZone are not contiguous for a timezone - hence, one can't say that all the zipcodes between 010 and 349 are in the Eastern timezone.  As an example, 374 to 377 is Eastern timezone - state TN, but 370 to 373 is Central timezone - state also TN,
as are zipcodes 378 to 385 also Central and TN.
0
Dear Experts,

I'm trying to make it so I can connect to my sql server on one machine to another, so I can copy the databases over. I am trying to connect to TCP.

The actual error I am getting is:
Can't connect to tcp:<our external ip>,1433\SQLSERVER2014
A network-related or instance-specific error occurred while establishing a connection to SQL Server... -The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225)
The remote computer refused the network connection.


I've followed guides for how to set it up, and as a matter of fact, following such a guide, had no issue setting it up so I can log into our new comper's sql server 2014 remotely. But some setting on the old computer must be preventing it from working and I just can't figure out what it is.

I have enabled TCP/IP and set it to 1433 (the default port). My sql server is named SQLSERVER2014.
I have restarted the sql server browser service and the sql server service.
I have also opened up the 1433 port through TCP on the firewall for private,public,domain (so all).
I also added program exceptions to allow the sql server and browser through the firewall.
I checked to see if there was a group policy that might be preventing it.

For the TCP/IP Properties, I set all IP1 through IPAll, TCP Dynamic Ports to empty (not 0) and I set the tcp port for all of them to 1433.

The computer is the main computer that faces the internet and the ip address of our computer goes …
0
Is there a way to write a script that will add a SQl database to another machine automatically without a hard wired connection string.  I have a vb.net program that has a SQL Server database that will be installed via download to 10 different independent computers in 10 different homes with 10 different users.  They will have different machines with different windows versions  etc.... They will also have different info on  each database with no connection to each other.

I would like to keep the database independent so that if i update the UI they can download it and run the install(setup) and find the database.  

I know this can be done with a local DB but then i would not be able to do UI updates.
Can someone steer me with this.
0
I have a couple queries wrapped in an SQL transaction like below. If one or more of the sql statements fails or doesn't complete properly, I'd like to rollback everything automatically. Is this possible? Thank you.

BEGIN TRANSACTION;

INSERT INTO.....

UPDATE tblBlah...

INSERT INTO....

COMMIT TRANSACTION;

Open in new window

0
Free Tool: Site Down Detector
LVL 10
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hello:

Below is my query, followed by the sample data and the results.  

I'm trying to SUM the ReceivedQTY column, so that only one row appears.

How can I accomplish this?

Thank you!

John

;WITH CTE AS
(
		select MO.MANUFACTUREORDER_I as MONumber, MO.ITEMNMBR as MOItem, 
	CASE WHEN MO.MANUFACTUREORDERST_I = 0 THEN 'New'
		   WHEN MO.MANUFACTUREORDERST_I = 1 THEN 'Quote'
		   WHEN MO.MANUFACTUREORDERST_I = 2 THEN 'Open'
		   WHEN MO.MANUFACTUREORDERST_I = 3 and ISS.DATERECD > 0 THEN 'Issued'
		   WHEN MO.MANUFACTUREORDERST_I = 3 THEN 'Released'
		   WHEN MO.MANUFACTUREORDERST_I = 4 THEN 'Hold'
		   WHEN MO.MANUFACTUREORDERST_I = 5 THEN 'Canceled'
		   WHEN MO.MANUFACTUREORDERST_I = 6 THEN 'Complete'
		   WHEN MO.MANUFACTUREORDERST_I = 7 THEN 'Partial Complete'
		   WHEN MO.MANUFACTUREORDERST_I = 8 THEN 'Closed'
		   END as MOStatus,
	MO.STARTQTY_I as StartQty, 
	ISS.QTYRECVD/BOM.QUANTITY_I as IssuedQuantity,
	REC.QTYRECVD as ReceivedQTY,
	(REC.QTYRECVD) - (ISS.QTYRECVD/BOM.QUANTITY_I) as UnitVariance,
	100*(1 + (REC.QTYRECVD - (ISS.QTYRECVD/BOM.QUANTITY_I))/(ISS.QTYRECVD/BOM.QUANTITY_I)) as Yield,
	MO.STRTDATE as PlannedStart, MO.ENDDATE as PlannedEnd, ISS.DATERECD as IssueDate, REC.DATERECD as ReceivedDate, 
	MO.USERID as CreatedBy, REC.USERID as ReceivedBy,
	MO.MATPROJCOSTI_1 as PlannedMaterial, MO.LABPROJCOSTI_1 as PlannedLabor, MO.LABFIXOHDPROJCOSTI_1 as PlannedOH,
	IT.ITEM_COST as IssuedMaterial,
	LISS.Cost_Array_1 as IssuedLabor, LISS.Cost_Array_2 

Open in new window

0
I have function which returns a  SMALLINT, and I have a stored procedure which calls the function.  When I execute the stored procedure, everything appears to work just fine.  However, the stored procedure create statement has an intellisense error when I reference the function.  Here is the error:  “Cannot find either column dbo or the user defined function or aggregate, or name is ambiguous”

Should I be concerned?  How do I fix this intellisense error?

Here is the function, after this I list the stored procedure statement which has the intellisense error:

SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[udfGetLineNumber]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[udfGetLineNumber]
GO
CREATE FUNCTION [dbo].[udfGetLineNumber]
(
      -- Add the parameters for the function here
      @PERSON_ID INT,
      @JUMP_MANIFEST_ID INT,
      @JUMP_DOOR_CD CHAR (2)
)


RETURNS SMALLINT
      
WITH ENCRYPTION
AS
BEGIN

DECLARE @lineNrCount SMALLINT

IF (SELECT COUNT (*) FROM PERSON_JUMP_MANIFEST_T WHERE PERSON_ID = @PERSON_ID AND JUMP_MANIFEST_ID = @JUMP_MANIFEST_ID) = 0
      BEGIN
            SET @lineNrCount = 1
            WHILE 1=1
                  BEGIN
                        IF (SELECT COUNT (*) FROM PERSON_JUMP_MANIFEST_T
                        WHERE  JUMP_MANIFEST_ID = @JUMP_MANIFEST_ID
                        AND JUMP_DOOR_CD = @JUMP_DOOR_CD
                        AND LINE_NR = @lineNrCount ) = 1
                        BEGIN
                              SET @lineNrCount = @lineNrCount + 1;      
                        END
                        ELSE
                        BEGIN
                          BREAK
                        END
                  END      …
0
Hi guys,

In SQL Server how do i find a string containing no spaces. for example i have got a column in mytable tcode. how can i find s11hp in the tcode column while this column can contain s1 2hp.

regards
0
We have a legacy application written in ms access, backend is sql server running in 2000 compatibility mode. The customer wants to upgrade to sql server 2016. We've restored the database to a 2016 instance which essentially breaks the app. The data layer is DAO via linked tables thru a System DSN. Questions really are what are our options here in regards to the database (steps to upgrade) and or application (rewrite to use ADO?)
0
I am a Senior .NET Full Stack Developer but want to have a sharper eye on the new technologies that are coming and the current technologies which are passing into the dustbin of history. What on-line industry publications are good for this?

Thanks
0
I am refreshing my memory about the various things which can cause a long response time on .NET MVC web pages and the tools to fix them.

A badly written stored procedure is the most obvious and this kind of bug can be uncovered first by looking at the Network tab of the browser and when the response time's too high, you may need to debug into the code to the point where the stored procedure is executed. You can also use SSMS to execute and fix it until the overall execution time becomes acceptable.

What other things can cause slow response and what methods uncover the problem?

Thanks
0
My HR department has asked me to develop something (ASP.NET vb -  SQL Server) for them but they say that no developer must have access to the data in the database.  What is the best way to achieve this, I'm struggling.
0
I have a table that hold the contents of a PDF document in a SQL Server Table COLUMN "DOCUMENT_CONTENT_TEXT"  .

When I attempt to search on the contents in this column it is taking a lot of time. And hence I was suggested to use FULLText Index feature. I am new to the same and I use the following script and I get the following error "Column 'DOCUMENT_CONTENT_TEXT' in table 'dbo.DMS_REPOSITORY' is of a type that is invalid for use as a key column in an index."

CREATE UNIQUE INDEX UI_DMS_REPOSITORY_DOCUMENT_CONTENT_TEXT ON dbo.DMS_REPOSITORY(DOCUMENT_CONTENT_TEXT);  

CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.DMS_REPOSITORY(DOCUMENT_CONTENT_TEXT)
   KEY INDEX UI_DMS_REPOSITORY_DOCUMENT_CONTENT_TEXT  
   WITH STOPLIST = SYSTEM;
GO

On googling I found that for Indexing a column the limit is 900 bytes but I am not sure if I could predict the size of the content being stored in this varchar(max) column.
0
I am fairly new to SQL Server.  I'm working in Developer 14.

I've created a stored procedure that will select records from a view based on the passed parameters.  One of the parameters passed is TaxTypeID.  There are 4 tax type (1, 2, 3, and 4) I want to group the returned record set depending on which tax type was passed.

If the taxtype is 1 I want to
group by CountyID, PayYear, PayMonth

if tax type is 2
group by SchoolDistrictNum, payYear, PayMonth

if taxtype is 3 or 4
group by MuniCode, PayYear, PayMOnth

Is there any way to code the conditional grouping an sorting?

This is the current version of the SP
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[spRptJerrysMonthlySummary]    Script Date: 10/17/2017 11:34:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:	Jerrys Report
-- =============================================
ALTER PROCEDURE [dbo].[spRptJerrysMonthlySummary] 
	-- Add the parameters for the stored procedure here
	@FromDate    datetime = null, 
	@ThruDate    datetime = null,
	@MuniCode    int      = Null,
	@SchoolDist  int      = Null, 
	@TaxTypeID   int      = Null 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT SUm(FaceAmt)              as SumFace, 
	       Sum(PenaltyAmt)           as SumPenaltyAmt, 
		   

Open in new window

0
Tech or Treat!
LVL 10
Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

microsoft sql

I have a table with sources, destinations and ports

image01
I want to concatenate (for xml):
all sources that have identical destination and port
all destinations that have identical source and port
all ports that have identical source and destination

image02
my query so far:
With finalresults([ROWID], [Serveur Source], [Serveur Destination], [Port]) 
AS ( 
select 
ROW_NUMBER() OVER (ORDER BY (SELECT null)) AS rowid, 
stuff((
select '__;',replace(c.[Serveur Source],' ','') as [text()] 
from [firewallrules].dbo.[outter-to-rionet] c WHERE c.[Port] = a.[Port] 
and c.[Serveur Destination] = a.[Serveur Destination] order by c.[Serveur Source]
FOR XML PATH('')
), 1,1,'') as source01, 
stuff((
select '__;',replace(c.[Serveur Destination],' ','') as [text()] 
from [firewallrules].dbo.[outter-to-rionet] c WHERE c.[Port] = a.[Port] 
and c.[Serveur Source] = a.[Serveur Source] order by c.[Serveur Destination]
FOR XML PATH('')
), 1,1,'') as destination01, 
stuff((
select '__;',replace(c.[Port],' ','') as [text()] 
from [firewallrules].dbo.[outter-to-rionet] c WHERE c.[Serveur Source] = a.[Serveur Source] 
and c.[Serveur Destination] = a.[Serveur Destination] order by c.[Port]
FOR XML PATH('')
), 1,1,'') as port01 
from [firewallrules].dbo.[outter-to-rionet] a 
)
select [Serveur Source], [serveur destination], [port] from finalresults f1 

Open in new window


The problem:
The concatenation work fine, but all sources, destinations and ports appear in every line
So, if i have 500 row before, i still have 500 rows after

Example:
this concatenation is ok:
source01 | destination01 | port01, port02, port03

This line is considered as a duplicate, because the source, destination and port are included in another line
source01 | destination01 | port02

I do not want this firewall rule to be defined twice for the same source, destination and port
0
Hi everyone,

I'm trying to attach the AdventureWorks Database to my Microsoft SQL 2012. I keep getting the message "An error occurred when attaching the database(s). Click the hyperlink in the Message column for details."

Problem is, there is no message in the message column.

A few things,
I'm running it as Administrator
I changed the permissions of the mdf file to "Everyone"
I moved the file into the DATA folder found in the Microsoft SQL tree.

Can someone help me out?
0
Have you enrolled in this month’s complimentary course? Enroll now for a free Microsoft SQL Server and Database Concepts course. Get 7+ hours of step-by-step SQL training, including possible topic interview questions.
0
Is there a while statement to execute a stored procedure for each row in the returned data below?

SELECT   ReservationID
FROM     dbo.everyware_t_reservation
WHERE    DateModifiedEW > DATEADD(HH, -24, GETDATE())
         AND LEN(Phone1) + LEN(Email) > 0
         AND ISNULL(IndividualID, 0) + ISNULL(AltIndividualID, 0) = 0
ORDER BY DateModifiedEW DESC;


EXEC dbo.brkCreateLeadFromReservations  4631496

Open in new window

0
Hi, I have Frieght chargers in the FactInvoiceHeader Table.
But I think better insert Freight Chargers in the FactInvoceLine Table.
Just wondering how do that.
0
Hello:

Below is my query, followed by the results and my query's tables.

The UnitVariance column is to be the result of subtracting IssuedQuantity from ReceivedQTY.  

In that case, the UnitVariance column should be 11, which is 144 - 133.  Instead, it's showing a result of -122.

How do I modify the syntax of my query, in order to fix this?

Thank you!

John


;WITH CTE AS
(
		select MO.MANUFACTUREORDER_I as MONumber, MO.ITEMNMBR as MOItem, 
	CASE WHEN MO.MANUFACTUREORDERST_I = 0 THEN 'New'
		   WHEN MO.MANUFACTUREORDERST_I = 1 THEN 'Quote'
		   WHEN MO.MANUFACTUREORDERST_I = 2 THEN 'Open'
		   WHEN MO.MANUFACTUREORDERST_I = 3 and ISS.DATERECD > 0 THEN 'Issued'
		   WHEN MO.MANUFACTUREORDERST_I = 3 THEN 'Released'
		   WHEN MO.MANUFACTUREORDERST_I = 4 THEN 'Hold'
		   WHEN MO.MANUFACTUREORDERST_I = 5 THEN 'Canceled'
		   WHEN MO.MANUFACTUREORDERST_I = 6 THEN 'Complete'
		   WHEN MO.MANUFACTUREORDERST_I = 7 THEN 'Partial Complete'
		   WHEN MO.MANUFACTUREORDERST_I = 8 THEN 'Closed'
		   END as MOStatus,
	MO.STARTQTY_I as StartQty, 
	ISS.QTYRECVD/BOM.QUANTITY_I as IssuedQuantity,
	REC.QTYRECVD as ReceivedQTY,
	(REC.QTYRECVD) - (ISS.QTYRECVD/BOM.QUANTITY_I) as UnitVariance,
	100*(1 + (REC.QTYRECVD - (ISS.QTYRECVD/BOM.QUANTITY_I))/(ISS.QTYRECVD/BOM.QUANTITY_I)) as Yield,
	MO.STRTDATE as PlannedStart, MO.ENDDATE as PlannedEnd, ISS.DATERECD as IssueDate, REC.DATERECD as ReceivedDate, 
	MO.USERID as CreatedBy, REC.USERID as ReceivedBy,
	MO.MATPROJCOSTI_1 as 

Open in new window

0

Microsoft SQL Server

159K

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.