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 have to get the avg of two columns in a SQL table.  I have created a temp table and need to do the following:
1. Count the number of results
2. If the number of resulting rows is greater than 4, I need to delete the row with the lowest weight value
3. if the number of resulting rows is 2,3, or 4 then average all the weights
4. if the number of rows is 1 or less, then return 'N/A

I am trying to use a select case for this but that may be the wrong approach.

This code is very rudimentary...and a result of queries I have run on a test database to validate results.  Having problems putting it all together and need assistance.  THis is part of a stored procedure I am creating to get the avg lbs/hr AND the Estimated runtime.  I have not included the runtime calc as it is a bit simpler.

--this returns the most recent runs of the product
Select top 8
            pj.PRODUCT_CODE as PJCode,
            Pj.BAR_WEIGHT * PJ.BARS_NEEDED as Tot_Wt,
            pj.JOB# as WONum,
            cj.JOB#,
            cj.Product_Code,
            hcls.TOT_WEIGHT,
            hcls.RUN_HRS,
            hcls.start_date
            into #tmpProductDetail
            from prodjob as pj            
            join dbo.closejob as cj on pj.Product_Code = cj.PRODUCT_CODE
            JOIN dbo.heatcls as hcls ON cj.JOB# = hcls.PRODUCTION_JOB#
            
WHERE cj.PRODUCT_CODE= 'xxx' and hcls.TOT_WEIGHT > '50' order by hcls.START_DATE desc
--Count the number of records in the temp table
select count(*)as recNum from #tmpProductDetail

--if there are more than 4 records delete the lowest total weight row and …
0
Newly released Acronis True Image 2019
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Hello. I have modified a  SQL Server query that I got from a friend to output the total execution for each process. It outputs the results as 00:00:00 and the datatype is varchar. I want to combine all the results and get an average. However, I cannot figure out how to convert it from varchar to decimal so I can get the average. Any assistance is appreciated.

Current Results
Total
00:52:34
01:31:33
0:15:27

Desired Results
Total
00:53:11

Query
;WITH CTETotal AS (SELECT CASE  
	WHEN DATEDIFF(dd, tsstart.Time, tslast.Time) > 0 THEN 
	CAST(DATEDIFF(dd, tsstart.Time, tslast.Time) AS varchar(2)) + 'd ' 
	ELSE '' 
	END + CONVERT(varchar(8), tslast.Time - tsstart.Time, 108) Total
FROM 
( 
	SELECT 
	MAX(ExecutionTime) AS Time, 
	AdvertisementID, 
	ResourceID 
	FROM v_TaskExecutionStatus 
	WHERE LastStatusMessageID = 11140 AND Step = 0 and
	ExecutionTime  > DATEADD(month, -1,GETDATE())
	GROUP BY AdvertisementID, ResourceID 
) tsstart  
INNER JOIN 
( 
	SELECT 
	AdvertisementID, 
	ResourceID, 
	LastStatusTime as Time 
	FROM v_ClientAdvertisementStatus
	WHERE LastState = 13 AND
	AdvertisementID = 'MXXXXX'
) tslast ON tsstart.AdvertisementID = tslast.AdvertisementID AND 
tsstart.ResourceID = tslast.ResourceID AND 
tslast.Time > tsstart.Time)
SELECT Total
FROM CTETotal

Open in new window

1
Hello,

I'm adding a few BIT columns to an existing table using the following code:
ALTER TABLE MyAwesomeTable ADD MyAwesomeField BIT NOT NULL DEFAULT 0;

Open in new window


The reason for doing this was an assumption that using "DEFAULT 0" when creating the column would be more efficient than something like adding INT columns and setting them all to 0 in an update statement.  

The problem I'm having is that I've learned a new SQL curse word - "CONSTRAINT".

Anyway - I've looked around the web for a bit and see all kinds of crazy stored procedures and what I'm hoping are unnecessarily complicated methods to drop a default constraint.

What I'd like to do is at the top of my script (which will be run manually) is drop and then re-add some BIT columns to an existing table:
-- Drop the columns
ALTER TABLE MyTable DROP COLUMN MyBITColumn1
ALTER TABLE MyTable DROP COLUMN MyBITColumn2
ALTER TABLE MyTable DROP COLUMN MyBITColumn3

-- Readd the columns
ALTER TABLE MyTable ADD MyBITColumn1 BIT NOT NULL DEFAULT 0;
ALTER TABLE MyTable ADD MyBITColumn2 BIT NOT NULL DEFAULT 0;
ALTER TABLE MyTable ADD MyBITColumn3 BIT NOT NULL DEFAULT 0;

Open in new window


Then lather, rinse, repeat as needed.  

Is there a simple way to do this?  

Seems like I should be able to do something like:
ALTER TABLE MyTable DROP CONSTRAINT DEFAULT MyBITColumn1

Or some heavy handed hack like:
ALTER TABLE MyTable DROP MyBITColumn1 /force

Any help will be greatly appreciated.  


Thanks!
0
I am trying to derive/predict a time/timestamp in SQL when our daily load might complete. Can someone help with this?

I have a datetime field from a table [Start].
I then have a dynamically generated [Cycle_Dur_AVG column which show the average time based on the last rolling three months,
CONVERT(VARCHAR(12), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 / 60 / 24) + 'd :' + 
      CONVERT(VARCHAR(12), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 / 60 % 24) + 'h :' + 
      CONVERT(VARCHAR(2), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) / 60 % 60) + 'm :' + 
      CONVERT(VARCHAR(2), AVG(DATEDIFF_BIG(SECOND, [DDS_Dim_Start], [End])) % 60) + 's' AS [CYCLE_DUR_AVG]

Open in new window

which outputs something like '0 d 12 h 23 m 5 s'.

Is there a way I can add those average seconds to the Start column and output a timestamp/datetime field?

Thanks,
0
I have a table PaxFichas and a second table CODES. In Paxfichas I have columns such as Gender, CustStatus etc which all refer to Codes for getting the correct description.  I thought it would be possible to design 1 single query to get all the relevant descriptions such as

SELECT PaxFichas.*,  Codes.Txt1 AS GuestStatus, Codes_1.Txt1 AS Gender
FROM (PaxFichas LEFT OUTER JOIN Codes ON PaxFichas.CustStatus = Codes.CODE) LEFT OUTER JOIN Codes AS Codes_1 ON PaxFichas.Sex = Codes_1.CODE
WHERE (((Codes.ID)=5) AND ((Codes_1.ID)=24));

I have 6 records in PaxFichas and the references to CODES must be valid entries. However, this query will return more than the expected 6 records from PaxFichas (actually it returns 14 records - many of them duplicates). Do I have to build several interim queries or is it possible to handle in one singe query? Thank you so much for any help!
0
Hi,

I have a table with data looking like this:
Sql result
The purpose of the table is to register a log for Employees so you can see the times they've worked.
As you can see each day has a rowID and also a TimeClockCatID. The rowID is the id for the specific day they are registering for and the TimeClockCatID is showing if they checked in (1) or checked out (2) from the system.

Is it possible for me to count and sum how much time an employee has worked a day by comparing the TimeClockCatID = 1 with TimeClockCatID  = 2 for each rowID?

Thanks for help!

Peter
0
TableProductXML(ProductID, XMLDATA)
 ------------------------
 21,
 <DataModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <ProductId>21</ProductId>
   <PType1>
     <WH>
       <WH_ID>1</WH_ID>
       <Name>Name1</Name>
       <WHL_ID>3</WHL_ID>
     </WH>
     <WH>
       <WH_ID>2</WH_ID>
       <Name>Name2</Name>
       <WHL_ID>2</WHL_ID>
     </WH>
     <WH>
       <WH_ID>3</WH_ID>
       <Name>Name3</Name>
       <WHL_ID>1</WHL_ID>
     </WH>
   </PType1>
 </DataModel>

 
How to write tsql select statement for XMLDATA value with case statement If (WH\Name = 'Name2') and (WH\ID = 2) then return True else return false
ProductID   , Result
21,     True
0
I have an MS SQL query that will have dynamic columns that I would like to pivot the results.  I am not sure how code the SQL to do this.  The column names will vary in name and number of columns.  One order may have 4 columns.  The next order may 5 columns and the names (gate_id) can be different in each case.

This is the query:
SELECT TOP (100) PERCENT 
       CASE 
          WHEN confirmed > 0 THEN CAST(confirmed AS decimal(8)) / CAST(operations AS decimal(8))  
	      ELSE 0 
	   END AS Attn, 
	   gate_id, so_id, plant_id
FROM (SELECT TOP (100) PERCENT dbo.mfg_order_oper.plant_id, dbo.mfg_order_oper.so_id, COUNT(dbo.mfg_order_oper.oper_id) AS operations, 
             COUNT(CASE WHEN oper_end IS NOT NULL THEN 1 END) AS confirmed, dbo.sys_mfg_workctr.gate_id, dbo.sys_mfg_gate.gate_seq
      FROM dbo.mfg_order_oper 
	  INNER JOIN dbo.mfg_order 
	    ON dbo.mfg_order_oper.plant_id = dbo.mfg_order.plant_id AND dbo.mfg_order_oper.order_nbr = dbo.mfg_order.order_nbr 
	  INNER JOIN dbo.sys_mfg_workctr 
	    ON dbo.mfg_order_oper.plant_id = dbo.sys_mfg_workctr.plant_id AND dbo.mfg_order_oper.workctr = dbo.sys_mfg_workctr.workctr 
	  LEFT OUTER JOIN dbo.sys_mfg 
	    ON dbo.sys_mfg_workctr.mfg_id = dbo.sys_mfg.mfg_id AND dbo.sys_mfg_workctr.plant_id = dbo.sys_mfg.plant_id 
	  INNER JOIN dbo.sys_mfg_gate 
	    ON dbo.sys_mfg_workctr.gate_id = dbo.sys_mfg_gate.gate_id AND dbo.sys_mfg_gate.mfg_id = dbo.sys_mfg_workctr.mfg_id
      WHERE (dbo.mfg_order.cancelled = 0 AND 

Open in new window

0
Hi All,

I have a number, say 17000.

1. I want to divide it by 1.1.
2. I want do multiply it by 1.1, should back to 17000

What data type should I use and how many decimals should be set?

Thank you.
0
Trying to update an Iseries (as400) from SQL Server.

If I type at SQL Management Studio:
   update OPENQUERY(KRICOF8K,'select * FROM AMMOBJ78KF.PFUUIDR WHERE UUAKNB=71162') SET UUGGNB=104
I receive this error:
OLE DB provider "MSDASQL" for linked server "KRICOF8K" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - PFUUIDR de AMMOBJ78KF no válido para la operación.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "KRICOF8K" could not UPDATE table "[MSDASQL]".

Typing:
 UPDATE KRICOF8K.S10AF99T.AMMOBJ78KF.PFUUIDR SET UUGGNB=104 WHERE UUAKNB=71162
I receive:
OLE DB provider "MSDASQL" for linked server "KRICOF8K" returned message "[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - PFUUIDR de AMMOBJ78KF no válido para la operación.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "UPDATE "S10AF99T"."AMMOBJ78KF"."PFUUIDR" set "UUGGNB" = (104.)  WHERE "UUAKNB"=(71162.)" against OLE DB provider "MSDASQL" for linked server "KRICOF8K".

Of course, my query is more complex than that two examples, but I can't update my linked server with this two simple queries.

KRICOF8K es the linked server name and Works fine when I select rows, for example:
SELECT * FROM OPENQUERY(KRICOF8K,'select * FROM AMMOBJ78KF.PFUUIDR  WHERE UUAKNB=71162')
display a row with several columns with values.

What is the correct syntax and why am I receiveing that error?
Thanks in advance.
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,

I have a trigger that updates my detail table. I would like to use the same trigger to also update the parent table based on a value in the detail table. I keep getting an error "The multi-part identifier "Citations.LogDetails.ParentLogNo" could not be bound."

The trigger works when I use a literal numeric value but not when I pass a value.  Is there a way to pass the value from the detail table in the first update statement to the update statement in the second table?

Thanks



/****** Object:  Trigger [Citations].[tgr_LogDetailsLastUpDated]    Script Date: 9/14/2018 7:04:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[tgr_LogDetailsLastUpDated]
ON [Citations].[LogDetails]

AFTER UPDATE AS
 
  UPDATE Citations.LogDetails
  SET LastUpdatedOn = GETDATE()
  WHERE LogDetailID IN (SELECT DISTINCT LogDetailID FROM Inserted)

 UPDATE Citations.LogHeader
 SET LogHeader.TotPlatesPerLog = (SELECT COUNT(DISTINCT(VehiclePlate)) FROM Citations.LogDetails WHERE ParentLogNo = Citations.LogDetails.ParentLogNo)
 WHERE LogHeader.INT_ID = Citations.LogDetails.ParentLogNo
0
I need a reliable way to host a set of MS SQL server 2012 databases and can access them through an IP address. I already have a host, however, it costs me to add any SQL database to the site, and my site is basically used to host the databases of what will be my applications clients... Is there a cheaper way to save these database and use an IP address(s) to access them in the web?
0
I have created some code that will replace values using one long string. The problem is that the syntax is different if there is a NULL value in the variable.

I have the following SQL:

SET     Parameters = '<ParameterValues><ParameterValue><Name>SPLC</Name><Value>' + @SPLC + '</Value></ParameterValue></ParameterValues>'

that I want to replace with a CASE STATEMENT for the @SPLC value:

CASE WHEN @SPLC is null THEN '</ParameterValue>' ELSE '<Value>' + @SPLC + '</Value></ParameterValue>'

so the new code would look like this:

SET     Parameters = '<ParameterValues><ParameterValue><Name>SPLC</Name><Value>' + CASE WHEN @SPLC is null THEN '</ParameterValue>' ELSE '<Value>' + @SPLC + '</Value></ParameterValue>' + '</ParameterValues>'

But I can't figure it out.

Thank you!
0
I have dynamic sql that builds a select correctly

DECLARE @DIspo VARCHAR(MAX);
SET @DIspo = '
SELECT ''MAX(cte.'' + COLUMN_NAME + '') AS '' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE ''#table%'' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)';
PRINT @DIspo;

Open in new window


When I copy the Printed @Dispo into a query window I get this

SELECT 'MAX(cte.' + COLUMN_NAME + ') AS ' + COLUMN_NAME FROM [tempdb].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#table%' AND COLUMN_NAME IN (SELECT CODE FROM dbo.DispositionTypes
WHERE DispoReport = 1)

When I do run the select
I get three rows in a table

MAX(cte.DNC) AS DNC
MAX(cte.NI) AS NI
MAX(cte.DEALPUR) AS DEALPUR

What I need to do is in ONE process...
Get back a varchar string I can use elsewhere in a dynamic query process

MAX(cte.DNC) AS DNC, MAX(cte.NI) AS NI, MAX(cte.DEALPUR) AS DEALPUR
0
Dear expert

Please help me with this MSSQL Query:

SELECT FirstName LastName DatetimeCancel
FROM [Prod].[System].[Approvement] aa 
join [Prod].[Contact].[Individual] ci on aa.PersonalIdentityNumberSender = ci.PersonalIdentityNumber join [Prod].[Contact].[Address] ca 
on ci.Id = ca.Id join [Prod].[Subscription].[Member] rsm on rsm.IdMember = ca.Id join [Prod].[Invoice].[Receiver] ser on ser.Id = ca.Id
where ApprovementStatusId = 7 and aa.DatetimeCancel is not null and aa.Id not in (select id from [Prod].[System].[Approvement] where ApprovementStatusId = 5 or ApprovementStatusId = 3 or ApprovementStatusId = 4 or ApprovementStatusId = 6 or ApprovementStatusId = 8 or ApprovementStatusId = 9 or ApprovementStatusId = 10 or ApprovementStatusId = 11 or ApprovementStatusId = 12 or ApprovementStatusId = 13 )

Open in new window


Result return with doublets because DatetimeCancel is diffirent, now I want only show the lastest time as result, in this case 2018-05-25 others ignore. Distinct can only random remove one (or more) doublets.
Is there a way to do this?
SC.PNG
0
Hi,

How can convert date to yyyy-mm-dd, i tried some options in converter and nothing:

The following query works but i think the format is mm-dd-yyyy
select cod,ISNULL(datep,CONVERT(datetime, '03/09/2018 18:06:53',120)) as datep from Refmb
this code not work get error when converting, changing 03/09 to 13/09
select cod,ISNULL(datep,CONVERT(datetime, '13/09/2018 18:06:53',120)) as datep from Refmb

best regards
0
-- I need the stored procedure below to return the ObjectEntityID's when there is either a
-- match on the RoleID or the PeopleID.  The problem is I'm only getting a result when there
-- is matches from both tables.



USE [EntityTest]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectEntityCluster]') AND type in (N'U'))
DROP TABLE [dbo].[ObjectEntityCluster]
GO
IF NOT EXISTS (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'ObjectEntityCluster')
BEGIN
CREATE TABLE [dbo].[ObjectEntityCluster](
	[ObjectEntityClusterID] [int] IDENTITY(1,1) NOT NULL,
	[GalacticID] [int] NOT NULL,
	[EntityID] [int] NOT NULL,
	[ObjectEntityID] [int] NOT NULL,	
	[Active] [bit] NOT NULL,	
 CONSTRAINT [PK_ObjectEntityCluster] PRIMARY KEY CLUSTERED 
(
	[ObjectEntityClusterID] ASC
))
END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ObjectEntityPeopleCluster]') AND type in (N'U'))
DROP TABLE [dbo].[ObjectEntityPeopleCluster]
GO
IF NOT EXISTS (select * from INFORMATION_SCHEMA.tables where TABLE_NAME = 'ObjectEntityPeopleCluster')
BEGIN
CREATE TABLE [dbo].[ObjectEntityPeopleCluster](
	[ObjectEntityPeopleClusterID] [int] IDENTITY(1,1) NOT NULL,
	[ObjectEntityID] [int] NOT NULL,	
	[PeopleID] [int] NOT NULL,
	[Active] [bit] NOT NULL,	
 CONSTRAINT [PK_ObjectEntityPeopleCluster] PRIMARY KEY CLUSTERED 
(
	[ObjectEntityPeopleClusterID] ASC
))
END
GO



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 

Open in new window

0
Hello Experts,

How to search for values in a column that has leading zeros? Count them?

I know this is just simple to experts but I searched but cannot find any answers.

See attach file.

Thank you.
FindandCountLeadingZeros_inValue.xlsx
0
Hi,

I am trying to write a relatively simple query.

given this table of data:

row_id      tr_rowid_debtor      tr_account      tr_to_client      tr_to_agency      tr_posted_date      tr_payment_date
1      2      1      90            1/06/2018      1/06/2018
2      2      1            40      2/06/2018      2/06/2018
3      3      16            30      2/06/2018      2/06/2018
4      4      16            20      2/06/2018      2/06/2018
5      5      16            10      1/06/2018      1/06/2018
6      7      1            12      1/07/2018      1/07/2018
7      7      1      23            1/06/2018      1/06/2018
8      7      1      25            1/08/2018      1/08/2018
9      2      16            12      1/06/2018      1/06/2018
10      3      16            30      1/06/2018      1/06/2018
11      4      16            450      1/06/2018      1/06/2018
12      5      16            459      1/05/2018      1/05/2018
13      7      16            654      1/05/2018      1/05/2018

and this is my current query:

select tr.tr_rowid_debtor,max(tr.tr_posted_date)
from de_transaction as tr
where tr.tr_account = 1
group by tr.tr_rowid_debtor

i am trying to add this in as well:
 (tr.tr_to_agency+tr.tr_to_client) as amount,

what I am trying to achieve is for each tr_rowid_debtor I am trying to get the latest transaction - which is determined by the highest tr_posted_date  - and I want the associated amount as well.  Thus the  (tr.tr_to_agency+tr.tr_to_client) as amount.

Could someone please assist?

many thanks!!
0
Starting with Angular 5
LVL 12
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

So I have been using MSSQL 2012 for a while now and I like it.  I am developing an aspx webform which requires that I host the page and database.  I found a good hoster but the database they utilize is MYSQL.

First of all are there any significant performance differences between the full SQL and MYSQL.  Would it be simple to convert my database to MYSQL?  Are there any size limitations to MYSQL?  Any other notable differences?
0
Hi EE,

We are doing an in-place upgrade of a SharePoint 2010 SQL instance and we want to do some database integrity analysis post upgrade.

Since we have limited number of people to look check the database integrity post upgrade what would be the most important databases to review?

Any assistance is appreciated.

Thank you
0
Hello,

I'm creating a CTE using two select statements and a UNION of two tables.  I would like to add a "faux" column to the results to identify  which table each row came from.

Something like:

;WITH
MyCTE AS (
    SELECT field1, field2, field3, 't1' as Source
     FROM table1
     UNION
     SELECT  field1, field2, field3, 't2' as Source
     FROM table2
)

The results would look like:
---------------------------------------------
field1 | field2 | field3 | Source
---------------------------------------------
   A     |      B    |     C     |     T2
   C     |      R    |     G     |     T1
---------------------------------------------

Any idea how I can do that?

THanks!
0
Can anyone tell if its possible to use bcp queryout to do the below as I cannot get it, we have sql 2012 r2 (If I simply bcp the table out it works but I don't want at the columns and we need the order by?):  


DECLARE @data nvarchar(2000);
SELECT @data = '      bcp "select job,item,description,extruder,PiecesPerMachHr,qtyordered,operationstart,operationend,rawmaterial,unit_weight,u_m,tooling,us_length,suffix       from einc_app.dbo.EnsIntouch1
                        order by extruder,operationstart" queryout "\\eincintouch\Intouch2\Download\SytelineSch.csv" -c -t, -T -SEINCDATA01';
PRINT @data
exec xp_cmdshell @data ;

When run SQL gives me
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]
NULL



Thanks
0
I am running sql server 2016 on a new server.  I have views that convert binary blob fields to text that I can use in Crystal Reports.  For some reason, when I bring them into my crystal report, it only show the very first character of the converted field.  If I look at it in SQL Server, it show the entire text in the converted field.  Anyone have any ideas why it's doing this and how to fix it???
0
Hi all.

I have the following sql query in my Visual Studio application the data is displayed the following way:

YEAR--TOTAL SALES---ABC---DEF---GHI

but I want to change the order of the columns to be:

YEAR---ABC---DEF---GHI---TOTAL SALES

Any idea how I can do this? Thank you in advance!

SELECT *
FROM (
    SELECT 
        Year,ProductLine, Sales,TotalYearSales AS [Total Sales]
           FROM CRMProductLineSales
           WHERE PMID = '55655'
          
) as s
PIVOT
(	SUM(Sales)
    FOR ProductLine IN (ABC, DEF, GHI)
)AS pvt
ORDER BY Year

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.