Microsoft SQL Server

158K

Solutions

48K

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 a table that stores

UID  (BIGINT)     ExternalID (VarChar(50))   Active(BIT)

I need a select
Groups by ExternalID
And BOTH records are active
0
Free Tool: IP Lookup
LVL 9
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Hi Experts,

I am trying to write a value to a field when it is either null or blank.

I have tried:
Select
IsNull(NULLIF(SiteCode, ''),'NON-VHA') as SITECODE,
From Site_CTE

but it does not work

Please help and thanks
0
I am having issues with TMG 2010 trying to install SQL server 2008. TMG 2010 server is associated with SQL Express. How do I unlink the SQL Express and link the new SQL server 2008? How do I keep a copy of the ISA logs on TMG 2010 and SQL server 2008? I have read most of the Microsoft TechNet files on the TMG 2010.
0
Hi EE,

I would like to transfer all data from a prod instance DB to a UAT instance DB. However, I would like to omit tables between the two DB where the schemas are different. Is there a way to do this via the GUI if not what would be the command to check the compare the schemas before transfer?

I am using SQL server 2012.

Any assistance is appreciated.

Thank you.
0
I have a stored procedure that I know is probably set up wrong but what is causing problems consistently is the attempt to return the row number.  I have the following code but I'm not sure since the table wasn't specifically designed with an identity row that this will work. Sorry, I'm new to trying to pull in row and not an expert at testing stored procedures either.
I was hoping to test executing the sp and return all declared output columns. I attached a file with a screenshot of the table columns.

 
ALTER PROCEDURE [dbo].[cpsp_preg_num]
(
--@Design Bit,
@pi_enterprise_id  CHAR(5),
@pi_practice_id  CHAR(4),
@pi_cur_person_id  UNIQUEIDENTIFIER,
@txt_date			VARCHAR(10) OUTPUT,
@txt_acttext		VARCHAR(75) OUTPUT,
@txt_method			VARCHAR(30) OUTPUT,
@txt_comments		VARCHAR(100) OUTPUT,
@txt_completedby    VARCHAR(100) OUTPUT,
@txt_time			INT OUTPUT,
@new_identity		INT OUTPUT
)
AS

-- SELECT @new_identity = SCOPE_IDENTITY()
 select top 1
 @txt_date = txt_date,		
@txt_acttext = txt_acttext, 	
@txt_method	= txt_method,	
@txt_comments	= txt_comments,
@txt_completedby = txt_completed_by,    
@txt_time		=	txt_time,
@new_identity	 = SCOPE_IDENTITY()
From cpsp_ext_ 
where txt_pregNum = 1
order by create_timestamp asc

Open in new window

cpsp-table.jpg
0
Is there a SQL command that ignores errors and instructs it to move on to the next statement?

example:

begin
INSERT INTO OPERATION_MASTER (OPM_AUTO_KEY,SYSUR_AUTO_KEY,OPERATION_ID,DESCRIPTION,BER_THRESHOLD,FLAT_RATE,PARTS_FLAT_PRICE,LABOR_FLAT_PRICE,MISC_FLAT_PRICE,FLAT_RATE_LABOR,DEFAULT_REPAIR,TURN_AROUND_TIME,IN_CAPABILITIES,CONSOLIDATE,TEMPLATE_TYPE,YIELD_PERC,MAX_LOT_SIZE,PART_COST,LABOR_COST,FO_COST,VO_COST,OSV_COST,COSTING_METHOD,FIXED_UNIT_COST,SEQUENCE,ACTIVE_FLAG,MISC_COST,MAIN_ASSY,HISTORY_FLAG,LABOR_FIXED_COST,DO_COST_UPDATE,ACTIVE_VERSION,GLOBAL_DESIGN,CONVERTED,BATCH_JOB,SYSCM_AUTO_KEY,BLOCK_FLAG,BGM_AUTO_KEY,PNM_AUTO_KEY,WWT_AUTO_KEY) VALUES(G_OPM_AUTO_KEY.NEXTVAL,1,'233N3223-1027 STD BIC','233N3223-1027 STD BIC',0,'F',0,0,0,'F','F',0,'F','F','W',0,0,0,0,0,0,0,'A',0,0,'F',0,'F','F',0,'F','F','F','T','F',1,'F','',38249,'');
INSERT INTO OPERATION_MASTER (OPM_AUTO_KEY,SYSUR_AUTO_KEY,OPERATION_ID,DESCRIPTION,BER_THRESHOLD,FLAT_RATE,PARTS_FLAT_PRICE,LABOR_FLAT_PRICE,MISC_FLAT_PRICE,FLAT_RATE_LABOR,DEFAULT_REPAIR,TURN_AROUND_TIME,IN_CAPABILITIES,CONSOLIDATE,TEMPLATE_TYPE,YIELD_PERC,MAX_LOT_SIZE,PART_COST,LABOR_COST,FO_COST,VO_COST,OSV_COST,COSTING_METHOD,FIXED_UNIT_COST,SEQUENCE,ACTIVE_FLAG,MISC_COST,MAIN_ASSY,HISTORY_FLAG,LABOR_FIXED_COST,DO_COST_UPDATE,ACTIVE_VERSION,GLOBAL_DESIGN,CONVERTED,BATCH_JOB,SYSCM_AUTO_KEY,BLOCK_FLAG,BGM_AUTO_KEY,PNM_AUTO_KEY,WWT_AUTO_KEY) VALUES(G_OPM_AUTO_KEY.NEXTVAL,1,'233N3223-1016 STD BIC','233N3223-1016 STD 

Open in new window

0
I have a select that puts things in the proper order

What I need to do is when there are two ExternalID records
And the second record has an ExternalID2 <> '0'
AND the first record IndividualTypeID = 1
Set active in the first record = 0

Screen print of results and query follows
SP

SELECT   TOP 3070 i.IndividualTypeID ,
         i.Active ,
         i.ExternalID ,
         i.ExternalID2 ,
         ROW_NUMBER() OVER ( PARTITION BY i.ExternalID
                             ORDER BY i.ExternalID2 ASC
                           ) AS RN
FROM     Individuals i
         JOIN dbo.ewSalesSitesToIndividuals s ON i.IndividualID = s.IndividualID
         JOIN SalesSites ss ON s.SalesSiteID = ss.SalesSiteID
WHERE    ss.BusinessID IN (   SELECT BusinessID
                              FROM   ewBusinesses
                              WHERE  BusinessName LIKE 'Breckenridge Grand Vacations' + '%'
                          )
         AND i.DateAdded > GETDATE() - 7
         AND ISNULL(i.ExternalID, '0') NOT IN ( '', '0' )
ORDER BY i.ExternalID ,
         i.ExternalID2 ,
         i.Active ,
         i.IndividualTypeID;

Open in new window

0
I am trying to use the Import 32 Bit Data Wizard as I need to connect to a Visual Fox Pro Database, is this Possible, I was able to do this using SQL 2008, we have since upgraded our SharePoint Infrastructure and need to be able to read that data in?

John
0
Hi All,

I have a query where I am trying to find duplicates, and once I find the duplicates I need to make another pass to the table to get a column that was not part of the dup query to display the values in separate columns.
 Below is an example.

Path name is not part of the dup query

Select Date, Drill_group, NameGroup, Drillname, Count(1) As DupCount
From Sourcetable
Group by Date, Drill_group, NameGroup, Drillname,
Having Count(1) > 1

Source table
Date      DRILL_GROUP      Name Group      Drill Name      PATHNAME
20170630      ATTRIB_SECTOR      Gold      OPTION      MATERIAL
20170630      ATTRIB_SECTOR      Gold      OPTION      OTHER
20170630      ATTRIB_SECTOR      Intl      CAPITAL      HEALTH
20170630      ATTRIB_SECTOR      Intl      CAPITAL      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      CAPITAL      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      CAPITAL      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      INDUSTRIAL MACHINERY      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      INDUSTRIAL MACHINERY      INDUSTRIALS
20170630      ATTRIB_SECTOR      Mid Co      MACHINERY      HEALTH
20170630      ATTRIB_SECTOR      Mid Co      MACHINERY      INDUSTRIALS
20170630      ATTRIB_SECTOR      Income      AMERICAN      CONSUMER
20170630      ATTRIB_SECTOR      Income      AMERICAN      OTHER
20170630      ATTRIB_SECTOR      MLP      ENERGY      NATURAL
20170630      ATTRIB_SECTOR      MLP      ENERGY      PETRO
20170630      ATTRIB_SECTOR      Advisors      ENERGY      NATURAL
20170630      ATTRIB_SECTOR      Advisors      ENERGY      PETRO
20170630      ATTRIB_SECTOR      GTC      CAPITAL      HEALTH

Result for Dup Query:
Date      DRILL_GROUP      Name Group      Drill Name      Dup Count
20170630      ATTRIB_SECTOR      Gold      OPTION      2
20170630      ATTRIB_SECTOR      Intl      CAPITAL      2
0
Struggling a bit with a query. Here's the details:

Table1

ID, StartDate, EndDate, Approval (Bool/bit), Hours (Decimal 5/2), FiscalYear, EMPLID


Table2

ID, EmployeeCode, FirstName, LastName, Mgr



I need to display T2.FisrtName, T2.LastName, T2.Mgr, T1.StartDate, T1.EndDate, T1.FiscalYear, And the sum of T1.Hours
WHERE T1.EMPLID = T2.EmployeeCode

So basically I'm looking to display 1 record for each record in T2 while displaying the sum of T1.Hours for each correlating T1.EMPLID/T2.EmployeeCode record(s)


Result might look something like:


Peter     Griffin     Glenn Quagmire     1/1/2000     1/6/2000     1999     84.5



How would I do this?
0
Revamp Your Training Process
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Good day Experts,

Please view the attached script which is an update trigger. Neither of the two update statements are working. I know that the processing reaches that part of the script because the message in the THROW statement displays, therefore something is wrong with the logic.
I cannot see it and therefore the only other way seems to be a CROSS APPLY or a CURSOR which I am not keen on.
Updating_agent_functions.sql
0
Hey

How do I connect to a SQL server - and return a recordset? (Trusted_Connection=True)

For example (Select * from mytable where field1=1)

Using VB.NET

Thanks in advance

Mike
0
I have a column that I need to always be 4 characters

It may at timesbeb 2 or 3

In thise cases I need to pad out to the left some zeroes.
0
I need help improving the query potentially combining the query without using the UNION. And help improving the query execution

SELECT F.* 
FROM ( 
SELECT T.*,
 case WHEN T.availSeats = 1 THEN (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 
 FROM users U 
 inner join sessionMap SM2 on SM2.userKey = U.userKey WHERE SM2.sessionKey = T.sessionKey) 
 WHEN T.repost = 1 THEN (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 
 FROM users U inner join sessionMap SM3 on SM3.userKey = U.userKey 
 WHERE SM3.sessionKey = T.sessionKey and SM3.repost = 1) ELSE 0 END as age 
 FROM ( 
 SELECT S.SessionKey, S.locationKey, S.locationKeyList, S.officeStateKey, SU.sessionStart, SU.sessionEnd, L.name AS locationName, L.address1, L.address2, L.city, L.state, L.zip, I.gender,
  ( SELECT TOP 1 UIL.licenseCd FROM userInstructorLicense UIL WHERE UIL.userKey = I.instructorKey and UIL.expirationdt is not NULL )as badgeNum,
   ( SELECT COUNT(1) FROM sessionMap SM WHERE S.sessionKey = SM.sessionKey ) AS seatCount, S.Seats, 
   IsNull(SU.btwSeatsOverride, S.Seats) - ( SELECT COUNT(1) FROM sessionMap SM WHERE S.sessionKey = SM.sessionKey and sm.userKey <> 0 ) AS availSeats,
    LS.btwScheduleThreshold, LS.availStateCode, ( SELECT top 1 isNull(SM.repost,0)
	 FROM SessionMap SM 
	 WHERE SM.sessionKey = S.sessionKey
	  ORDER BY isNull(SM.repost,0) desc ) as 

Open in new window

0
I have over 45 databases that need the log files backed up. I can go through each database and manually backup the log files or prefer not to if it's an option due to time restraints. Is there a way to run transaction log backups using one command or automate the process for all databases.
0
I can't seem to find this in my search. I simply have a table or range in my excel spreadsheet and I'm looking for a VBA code to insert the data into SQL Server.  I don't want to use any integrated services or an OPENROWSET in SSMS since this will be a spreadsheet in a shared folder.
0
I asked this same question earlier and I thought it was working with the suggestion I received but I am not getting the data I need.

We moved data from a set of fields in a table to another set of fields in the same table.  

These are the old field names:
QWife1Child1 , QWife1Child2,  QWife1Child3
 QWife2Child1 , QWife2Child2,  QWife2Child3
 QHusb1Child1 , QHusb1Child2,  QHusb1Child3
 QHusb2Child1 , QHusb2Child2,  QHusb2Child3

These are the new field names:
Child1, Child2, Child3 ... Child10

If there is a name in the old field then we copy it to a new field.  Not every couple has a child together.  They could each have children from previous marriages.  Keeping that in mind Child1 doesn't always  = QWife1Child1.  Child1 could = QHusb2Child1.

I wrote the query to complete this task and I believe that it ran like I wanted.  I just need to make sure.  

I need to write a MSSQL query to make sure that every child from the old fields are listed in the new fields.  Below is what I have so far but it is not working correctly. As a note  the WHERE clause is only

I orignially used OR but I changed the 'or' to 'and' because when my results came back there were a lot of them.  I went into a few records and things matched.  There was nothing wrong.  That's when I changed the 'or' to 'and'.  Again I did random spot checks and I found two records that had data in QHusb1Child1 and Qhusb1Child2 but nothing in Qchild1 or Qchild2.  These are the types of records I am …
0
In SQL Server, I have a View with a function that Calcs a certain number for people based off other settings, such as this (details of the function aren't important), with this output...

select FName, LName, Gender, dbo.CalcNum(field1,field2) as MyNum from tPeople

Al, Smith, M, 12
Bob, Carson, M, 13
Hank, Small, M, 3
Jake, Big, M, 45
Sandra, Lee, F, 9
Ellie, Day, F, 9
Debra, May, F, 10
etc.

What I now need is another View that queries this first View, with a total count of MyNum of all those rows with a specific number (for example, a total of all those with a value of 8) in different columns, and separated into 2 different rows by Gender. Not only that, but include a couple of columns that are number ranges.

For example, columns are Gender, TotNum7, TotNum8, TotNum9, TotNum10, TotNum8to10, TotNum11to15.

So given the above data, the output would be...

F, 0, 0, 2, 1, 3, 0
M, 0, 0, 0, 0, 0, 2

This basically says...

Of Females, 2 have the number 9, 1 has number 10, 3 are in range 8 to 10, and none in other columns.
Of Males, 2 have the number in the range 11 to 15, and none in other columns.

How can I do this? I suspect a PIVOT query? I tried to understand how they work, but in doing so, I'm not sure it's even the right option. And even if it is, I don't grasp how to make it work. Thoughts? To give a starting point with the data from above...

create view View1
as
select 'Al' as FName, 'Smith' as LName, 'M' as Gender, 12 As MyNum
union …
0
Hello,
I have been playing around with this error for a while now and not getting anywhere.  I do feel my joins are correct.  Can someone point me in right direction?  Thank you in advance!


IF OBJECT_ID ('tempdb..#Temp1') IS NOT NULL
BEGIN
    DROP TABLE #Temp1
END

DECLARE @StartDate DATETIME
         SET @StartDate = '2010-01-01 00:00:00.000'

CREATE TABLE #Temp1
         ( BookingID INT PRIMARY KEY
         ,BookingNumber VARCHAR (20)
         ,StartDate DATETIME
         ,EndDate DATETIME
         ,OptionInDate DATETIME
         ,OptionOutDate DATETIME
         ,BookedServiceID INT
         ,ServiceID INT
         )

INSERT INTO #Temp1
    SELECT DISTINCT
         B.BookingID
         ,B.BookingNumber
         ,B.StartDate
         ,B.EndDate
         ,BO.OptionInDate
         ,BO.OptionOutDate
         ,BS.BookedServiceID
         ,BS.ServiceID
    FROM dbo.Booking b
INNER JOIN DBO.SERVICE BS  ON BS.BOOKINGID = B.BOOKINGID  
INNER JOIN DBO.OPTION BO  ON BO.BOOKEDSERVICEID = BS.BOOKEDSERVICEID

         WHERE b.StartDate >= @StartDate
         AND BO.OptionInDate >= @StartDate
--ORDER BY B.BookingID

SELECT  * FROM  #Temp
0
Use Case: Protecting a Hybrid Cloud Infrastructure
LVL 4
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

I am using the FileUpload control in ASP.NET and I am trying to convert any file uploaded to a TIF into SQL Server. I am currently saving images as memory stream and into an Image data type in my table with no issues. I was asked to save all files(.doc, .txt, .whatever) as .TIFS and Im not sure its possible, but I would think you could save anything as an image but I have no idea how to do this.
Any help would be appreciated.

Thanks
JK
0
Hello

I am looking for a stored procedure to look only in a specific table

I see this procedure all over the internet

https://www.sqlservercentral.com/Forums/Topic1232230-391-1.aspx 

that works like so:

EXEC SearchAllTables 'Computer'

but I want to look only in one table

EXEC SearchTable 'TableName'  'Computer'
0
Good afternoon,

I have a database with hundreds of tables and many of them have a field named LOCKED_BY_ID.
I need a query to list all of the tables and values that are not null.

Here is a simplified example of one of the tables.  Columns vary in the tables.

CREATE TABLE [TestDatabase].[PART](
      [ID] [char](32) NOT NULL,
      [CREATED_ON] [datetime] NOT NULL,
      [CREATED_BY_ID] [char](32) NOT NULL,
      [LOCKED_BY_ID] [char](32) NULL,
      [DESCRIPTION] [nvarchar](256) NULL
GO

Thanks!
0
A client changed his SQL server 2008 R2 SA Password. He doesn't remember what the old password was. Now his Access application doesn't work.

We have a good backup. Is there a set of files/ folder we can restore from backup to get the old PW back? I'd really not go through the pain of doing a complete system restore.

Thanks!
0
Hello experts I've got the following error time & time again in Event Viewer.  Not sure how to resolve.

Citrix XenApp failed to connect to the Data Store. ODBC error while connecting to the database: 28000 -> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
0
We moved data from a set of fields in a table to another set of fields in the same table.  

These are the old field names:
QWife1Child1 , QWife1Child2,  QWife1Child3
 QWife2Child1 , QWife2Child2,  QWife2Child3
 QHusb1Child1 , QHusb1Child2,  QHusb1Child3
 QHusb2Child1 , QHusb2Child2,  QHusb2Child3

These are the new field names:
Child1, Child2, Child3 ... Child10

If there is a name in the old field then we copy it to a new field.  Not every couple has a child together.  They could each have children from previous marriages.  Keeping that in mind Child1 doesn't always  = QWife1Child1.  Child1 could = QHusb2Child1.

I wrote the query to complete this task and I believe that it ran like I wanted.  I just need to make sure.  

I need to write a MSSQL query to make sure that every child from the old fields are listed in the new fields.  The query I wrote to do this is giving me syntax errors.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How can I change this query to get the data I need?

SELECT q.MattersQTRUSTINFO, 
	QWife1Child1 , QWife1Child2,  QWife1Child3
	QWife2Child1 , QWife2Child2,  QWife2Child3
	QHusb1Child1 , QHusb1Child2,  QHusb1Child3
	QHusb2Child1 , QHusb2Child2,  QHusb2Child3
FROM [MattersQTRUSTINFO] q
JOIN Matters m ON m.matters = q.Matters
WHERE QWife1Child1  NOT IN (SELECT qchild1, QCHILD2, QCHILD3, QCHILD4, QCHILD5, QCHILD6, QCHILD7, QCHILD8, QCHILD9, QCHILD10

Open in new window

0

Microsoft SQL Server

158K

Solutions

48K

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.