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

When I execute a stored procedure
Is there any way to log or track what table triggers execute?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

I have a table that has 4 triggers on it.
An api does an insert or update on the table and these triggers fire.

On that same server I have an assembly installed... which a function calls

How can I add a call to that assembly function after the LAST trigger fires?
Is there a way to determine the ORDER of the triggers...
And then should I add that function to the Trigger...??
0
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
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
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
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
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
SharePoint Admin?
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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
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 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
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
Is it possible to view an MS-SQL Server last 10 queries on a specific database or table?  and how?  (I'm using MS-SQL Server Express 2016).

I've seen this code on SO but it doesn't show every calls, and not for a long time.  If i run the same query again, some rows of the previous result doesn't show anymore:
SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('MyDB')
ORDER BY deqs.last_execution_time DESC

Open in new window


Do i have to set a flag to keep a trace somewhere?

Thanks you
0
Hi guys,

In SQL SERVER 2008R2, I want to know if today is the 4th BUSINESS DAY DATE before end of the month.

Thanks.
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
Dear Experts,

I am fairly new to T-SQL and I'm trying to do something that is maybe easy or maybe complex. I understand that T-SQL is supposed to have builtin capabilities for geo type things..

So I have a table with maybe 500 records at a time (I'm going to do the most I can get away with at a time and still have the query be fast)..

Each record has a latitude and a longitude column and also a geography and geometry column (which represent the latitude/longitudes).

I have another table with a whole bunch of records with the same sets of lat/lng columns.

What I want to do is join the first table to the second and I only want to bring in records from the second where the latitudes and longitudes are within a mile of the joining record's lat/lng from the first table. So for each record from the main table there will be matching records selected that are within that record's mile radius.

I do this in microsoft access using an implementation of the haversine formula that I had to program into vba. It was a real pain, and I'm hoping not to have to make a .net version of that to accomplish this. But I was able to select records in this way, 100 at a time, rather well.

Does anyone know the best way to do this in a query in T-SQL? I'm ok with making temp tables or whatever to hold radiuses or whatever is needed. I've been trying to find answers online to do this but don't really understand most of them and they all seem to apply to pulling in multiple records for…
0
I installed SQL Server 2014 Express in Windows Server 2012 Standart. I've restored a backup of SQL Server 2008 and everything works fine on the server.
I've enabled TCP via Configuration Manager as well as Management Studio.

But I have a problem: I can not configure ODBC access from a terminal, where I downloaded and installed the ODBC driver 11.

Are there any settings to be made in Server 2012 or any other detail in Server 2014 Express?

Tks.

Translate from portuguese:

"Instalei o SQL Server 2014 Express no Windows Server 2012 Standart. Restaurei um backup do SQL Server 2008 e tudo funciona bem no servidor.
Eu habilitei o TCP via Configuration Manager, bem como Management Studio.

Mas tenho um problema: não estou conseguindo configurar o acesso via ODBC a partir de um terminal, onde baixei e instalei o ODBC driver 11.

Tem alguma configuração a ser feita no Server 2012 ou algum outro detalhe no Server 2014 Express? "
0
Technology Partners: We Want Your Opinion!
Technology Partners: 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!

I am using a SQL Server DB as the back end of an Access 2013 application.

A view, which is to be used for the row source of my combo box,  has been defined in SQL and linked to the front end,.  The view contains and 'Order By' in SQL Server and I understand that the Order By is not used to order the view in Access.

It all works but when I used the View as the row source the items are not in any particular order.  I want them ordered.

What I did to accomplish that was click on the '...' on the rowsource line, which took me to query builder.  I pulled the fileds into the query and set the Order I wanted.  Now the entries in the dropdown come up just as I want them.

The rowsource is now:
"SELECT dbo_vADrop_MuniMaster.MuniMastID, dbo_vADrop_MuniMaster.MuniCode, dbo_vADrop_MuniMaster.MuniName FROM dbo_vADrop_MuniMaster ORDER BY dbo_vADrop_MuniMaster.MuniCode;"

My question:  Is the way I accomplished the sorted dropwdown, the best way?  

Since this view/drop down may be used on multiple forms I thought it might be best to define a permanent Access query with the view as the basis.  The Access query would sort the data in the order I want.  The Access query would the be used as the row source on all drop downs using information from this view.

Just thought I'd check with the experts on the recommended way of handling this situation.
0
I have a new client that severely dorked up several fields in a SQL Server database by writing an update query from Access.

The backups they were doing were full, and nightly and they were deleting backups more than 4 days old.  Unfortunately, the individual that overwrote data didn't fess up until after the 4 day weekend over 4 july.  so we had no .bak file to restore from.

I have managed to find a .mdf and .log file that their off-site backup folks are doing and that file is available.  What I want to do is create a new database (different name) on the same server as the production database so that I can run some queries, identify the differences between the data in the .mdf backup and on the current production database.

What is the simplest way to create that new database from the old .mdf and .log files?
0
I have a simple query.
SELECT twt.Word
FROM Dictionary.[dbo].[TblWordTags] TWT
WHERE twt.word like'%WB' AND ClientName<>'Smmt'
GROUP BY Word

Open in new window


It returns

Word
XLWB
(180)SWB
LWB
TDI(180)SWB
(102)LWB
MWB
TDCI(90)LWB
SWB
TDCI(90)SWB

I would like to extract the Lwb, Swb, Mwb, xlwb so any other parts are excluded. the required substring could appear anywhere in the string.

I am not sure the best way, I guess I could say find the string and if true force the value like an iif in access. not sure what is best.
0
I have a schema (database) in MSSQL that is called PRODUCTION.  I also have security group setup in Active Directory called DB_USERS.  

I am having a problem with the syntax to grant the security group, (and all of the members of it), the rights of SELECT, INSERT, UPDATE, ALTER and DELETE to "all" of the tables inside of this DB.

Can someone provide me with a syntax SQL query command to do that, or alternatively, how I can do that through SSMS?   For some reason I am just not getting the syntax correct.

I tried the following script, and it creates everything, but it errors out on the last line when it comes to the GRANT command.

USE master;
GO
CREATE LOGIN [MYCOMPANY\DB_USERS] FROM WINDOWS;
GO
USE PRODUCTION;
GO
CREATE USER [MYCOMPANY\DB_USERS] FROM LOGIN [MYCOMPANY\DB_USERS];
GO
CREATE ROLE DBACCESS;
GO
EXEC sp_addrolemember 'DBACCESS', 'MYCOMPANY\DB_USERS';
GO
GRANT SELECT, INSERT,UPDATE,ALTER,DELETE ON SCHEMA::PRODUCTION TO DBACCESS;
GO

The error I am getting is "unable to access schema PRODUCTION. It does not exist or you do not have permissions."

I am logged in as SA.

Thank you in advance,
Jeff
0
I had the need to restore a SQL Server database (actually create a new db from a backup of another db) last week. The restore operation went well, but now I'm wondering; is there a way to determine what the restore history of a database is?  When it was restored, what is the name of the file it was restored from?

I know there is lots of data in the system tables, just wondering whether this info is stored somewhere where it can be retrieved, either with SQL or SSMS.

Thanks
0
Do you know how I can resolve the error "Incorrect syntax near the keyword 'primary'?

I am altering a column in a SQL Server 2008 table.

ALTER TABLE dbo.tbl_BU_StopVoidPay
ALTER COLUMN stopID int primary key IDENTITY(1,1) NOT NULL
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.