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
Industry Leaders: We Want Your Opinion!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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
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 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
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
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 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
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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
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
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
Greetings,

We have a virtual server with 48GB of RAM and 2 Processors. The performance according to SPOTLIGHT is terrible and the getting a lot of Latching and I/O alerts. What SQL Server Database configuration setting should I change to get rid of this problem or should I add more CPU Processors and more RAM? Please advise.
0
Hi
I am new to Microsoft sql server management studio tips 11.0.210 version connecting to the database using this client IDE.

How to search on the stored proc, function etc names.
what are tips in using it and best practices. please provide good links, resources on it
0
Hi All

( This isn't a question as much as it's a member poll, so points will be split across any meaningful answer )

Does anyone use a naming convention for SQL Agent jobs?  Most of my jobs fall under these categories:
  • DBA tasks such as create statistics, reorganizing indexes
  • ETL jobs that archive data to another db or a straight delete of old data no longer needed
  • ETL jobs that load a Data Warehouse / Data Mart
  • One off reports / emails
  • Data validations that I love / emails for any failures

I've inherited a bunch of servers with SQL Agent jobs with poor names, and would like to come up with a standard.

Thanks in advance.
Jim
0
Hi there,

thank you for reading this ,  i have a light SSIS package . It runs without any errors in the visual studio 2012 in  dev environment.  but once it gets deployed to test environment   using proxy account with full permission access ran the package in SSMS 2012 . it shows error below .  Does anyone have any ideas ?  Thank you .

DataFlowTaskError: there were errors during  validations
DataFlowTaskError: Failed validation and return status "VS_ISBROKEN"
DataFlowTaskError:  one or more component failed validation
DataFlowTaskError: Microsoft SQL server Client 11.0  hresult:0x80004005
Description :Syntax error, permission violation , or other non specific error
0
Three Considerations for Containers
LVL 2
Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.

Hi there,
   We have log shipping for 4 databases which are going well but however only Restore Jobs for two databases gets failed in DR environment occasionally. The error message says like this
Executed as user: xxxxxxxxx. The process could not be created for step 1 of job 0x867837754D077D4A9BFF608D000072BD (reason: 5).  The step failed.

I have configured the Job->Step->Advanced->output file to capture the error message but this error was not written in the output file as well. In fact this error doesn't seem to be happened at all as I can see the output file has only log which is written before the last run which was failed.

thanks
Deepak
0
If you check a linked server within SQL Server, and go to the security tab, and check the security tab, if the “be made using this security context” is checked, and a username/password entered – does this mean any account with access to this instance can query the remote instance under the security context of the pre-populated remote login, or will it prompt them to answer that password each time?

My concern is some linked servers are set to “be made using this security context” with remote login credentials pre-populated. And in some cases the account used for access to the remote instance are quite powerful.

If you wanted to limit who can make use of this linked server to only a trusted few, how could you achieve this within the linked server security options?
0
I have one table that I cannot edit through Sql Server Management Studio.  It is being used with Sql Server 2008 R2.  I get Unspecified error MS Visual Database Tools.  Searching the web has not given me any solutions.  Can someone out there help with this?

Sql Version is 10.50.6220.0.

Microsoft SQL Server Management Studio                                    10.50.6000.34
Microsoft Analysis Services Client Tools                                    10.50.6000.34
Microsoft Data Access Components (MDAC)                                    6.1.7601.17514
Microsoft MSXML                                    3.0 4.0 5.0 6.0
Microsoft Internet Explorer                                    9.11.9600.18697
Microsoft .NET Framework                                    2.0.50727.8669
Operating System                                    6.1.7601
0
Hi Guys,

I place the manually created SQL tables on Entity Framework and create relationship , I could do the operations on that table

what about the dynamically created SQL server tables

how to bring the dynamically created tables in EF and how to created the relationship with previous tables and newly created tables
0
I would like to use LIKE in the IN Clause Ex

Select * from Companies
where PLANT IN (LIKE '%US%,LIKE'%CA%',LIKE'%MX%')

Thanks
Srini
0
I need an SQL query for a 2012 MSSQL instance, which will show for a database, all database roles and members.

I also need a way to query an entire database for any tables which contain fields starting with pass*, e.g. password, passwd. I need to verify which users then have read/write access to those tables, so any query which can do an ACL per table as well would be a great help.
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.