Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have a date column that is unfortunately varchar(10) and need to use it in a between statement. I've tried every conversion I could find but always get an error.
select p.person_id, p.last_name, p.first_name,hm.encounterdate,
hm.encountertype, convert(VARCHAR(10), hm.date_px, 120) as date_px, hm.hospitaladmitdate from Histories_Master_ hm
inner join person p on hm.person_id = p.person_id
and convert(VARCHAR(10), hm.date_px, 120) between '2017-06-01'
    AND '2017-06-30'
and hm.encountertype in ('hospitalization', 'emergency room')

Open in new window

1
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.

When I execute a stored procedure
Is there any way to log or track what table triggers execute?
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
Hello,
I a have table with three fields

CREATE TABLE [test_DEBUG](
      [SQL] [nvarchar](2000) NULL,
      [EntryDateTime] [datetime] NULL DEFAULT (getdate()),
      [Tracking_user] [varchar](255) NOT NULL DEFAULT (suser_sname())
) ON [PRIMARY]

How can I know how many insert statements are on each day.

Thanks
0
Hi Experts,

I'm looking to create a new user for particular database that will have only limited rights, for example to view only a few tables, and some of those tables only to certain columns.

Also would like to put restriction in some tables that should be able to add/edit only but not delete, and to some only to add, and other tables only read permission.

How would I go about all that?

Is there a script I can have that would allow me to list all those object and particular rights, or perhaps someone can can post some screenshots how do I accomplish that with the SSMS user interface?

Thanks in advance.
0
Hi there,
  I am trying to configure the logon trigger which should allow the connection only from specific IP addresses. Below is the code which I got by googled few blogs, when I tried with that I am unable to get the IPaddress from the Eventdata().

USE master

GO

-- Create table to hold valid IP values

CREATE TABLE ValidIPAddress (IP NVARCHAR(15)

CONSTRAINT PK_ValidAddress PRIMARY KEY)

-- Declare local machine as valid one

INSERT INTO ValidIPAddress

SELECT '<my Local Machine IP>'
-- Create Logon Trigger to stop logins from invalid IPs

CREATE TRIGGER tr_LogOn_CheckIP ON ALL SERVER

    FOR LOGON

AS

    BEGIN

        DECLARE @IPAddress NVARCHAR(50) ;

        SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',

                                      'NVARCHAR(50)') ;
                   
        IF NOT EXISTS ( SELECT  IP

                        FROM    master..ValidIPAddress

                        WHERE   IP = @IPAddress )

            BEGIN

            -- If login is not a valid one, then undo login process

                SELECT  @IPAddress

                ROLLBACK --Undo login process

            END

------------------

SET @IPAddress = EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]',

                                      'NVARCHAR(128)') ;
                    I am getting Null Value as output for the variable @IPAddress. can somebody help me with this?
Please Note, I am …
0
Hi Experts,


Currently I have created a temp table.  But the problem is when there are large amounts of data.  After the temp table is created (yes very large) then there is a where clause on that temp table to reduce the amount of records.
Declare
@DB_@STATUSTYPE varchar(255) = 'No Status'


Select *
From #TempTable
Where ([StatusType] = @DB_@STATUSTYPE)


This is in the select statement when inserting into the temp table:

Select
CASE WHEN cce.CVA_StatusID = 1 THEN cvs.StatusType
         WHEN cce.CVA_StatusID = 4 THEN cvs.StatusType
         WHEN vra.Actionable = 0 THEN 'Not Actionable'
         WHEN cce.CVA_StatusID IS NOT NULL THEN cvs.StatusType ELSE 'No Status' END AS [StatusType],

From TableName as cce

so this can be done more faster I would like to include:

Where
([StatusType] = @DB_@STATUSTYPE) instead of on the temp table

But because this is a case statement it does not work this way.

What do I have to do in the Where statement to get this to work.

Please help and thanks
0
Hi

In what scenario should I use this command?
0
I need to change the password of a user at SQL Server level (main login of systems).

But my doubt is that other things I should check before making this change, that other factors can be affected by doing this since this user used it almost for everything within this DB.

Obvious that at the application level will make the respective changes with the new password (it is a production pass-through server, nobody has access to this), but my doubt comes from things in the database itself, for example, as the Replicas that I have to other servers, sending mails, or where else should I look where maybe that password is left and I have to update it for the new one perhaps manually.

Here are several servers, I have to check all and see which are connected to my main server, since changing the password will cause several (load processes, dblinks, etc) to fall.

What is the correct way to make this change to decrease the margin of error
0
hi,

we see application log of a SQL server show this error:


1) sqlservr (1336) An attempt to open the file "C:\Windows\system32\LogFiles\Sum\Api.log" for read only access failed with system error 5 (0x00000005): "Access is denied. ".  The open file operation will fail with error -1032 (0xfffffbf8).

and

2)
The Open Procedure for service "BITS" in DLL "C:\Windows\System32\bitsperf.dll" failed. Performance data for this service will not be available. The first four bytes (DWORD) of the Data section contains the error code.

the first one. I google it and it shows :

https://stackoverflow.com/questions/25830912/error-occurred-while-opening-logfile-c-windows-system32-logfiles-sum-api-log

https://support.microsoft.com/zh-tw/help/2811566/error-1032-messages-in-the-application-log-in-windows-server-2012?wa=wsignin1.0

but I am wondering why SQL server need to write to this folder and why we want to change read/write permission for the SQL server ! we are not the first day to run that box but now, funny error like this happened.

please share experience on this!
0
MS Dynamics Made Instantly Simpler
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

I have SQL 2008 Standard that I need to upgrade to SQL 2008 R2.
All my tests pass fine to do the upgrade.

The upgrade due date comes and thru the procedure I get this instance 'DBXBNDV1' that does not exist on the particular server that I am doing the upgrade on.  Where does this instance come from?

Infact all my SQL intances don't show any name relevant as that as an Instance name. Due to this issue, my upgrade to SQL 2008R2 standard has halted. Any advise would be greatly appreciated.
0
hi experts

i am reading about Upgrading Data Quality Services:
DQS schema must be upgraded as a separate step

what's the mean DQS schema
0
Hi Experts,

I am trying to pass a multi value parameter.


User has a downdown:
Severity dropdown
I am creating a manual and it looks like currently it is not correct.
see attachment.

I am also dealing with a drillin report which at this point is not passing correctly.  I have tried many different ways using what is mentioned in the Manual.

Ex:
On Main report I first do the Dataset like:
Main Dataset Dataset Properties
I click on fx to enter expression:
Express within the dataset
On the drillin from the main report under SEVERITY:
Click fx to go into the expression for SEVERITY
Within the expression:
Expression for the drillin from main report
On Drillin Report DataSet:
Drillin Dataset Expression
And I have followed all on the manual for the all SP's involved.

Please help and thanks.
SeverityCheckBoxParameter.docx
0
Hello,
Need to write a query on table to mask values return.


Table1

Name    EndDate
A             23-11-90
B             31-12-9999
C             12-10-2013


Diplay results:
Name    EndDate
A             23-11-90
B            
C             12-10-2013


Mask the max date

Cheers
0
I need to formulate update statements for each record in the attached spreadsheet. The SQL statement I need for each field and value is attached. Is it possible to do this?
warehouse-level---lines-to-modify.xlsx
0
Hello guys,

Please, take a look at this query:

select A.QtdPRO, B.QTDEMBALAGEM,
       convert(float, CEILING( ( A.QtdPRO / convert(float, (A.QtdPRO / B.QTDEMBALAGEM))))) as qtde1,
       convert(float, CEILING( ( 88 / convert(float, (88 / 60))))) as qtde2,
	   CEILING( ( 88 / convert(float, (88 / 60)))) as qtde3,
	   ( 88 / convert(float, (88 / 60))) as qtde4,
       ( 88 / (88 / 60)) as qtde5,
	   ( 88 / 60) as qtde6
  from arifat a 
inner join arest b on (a.codpro=b.codpro)
where a.numped=6758

Open in new window


This is the result:

QtdPRO      QTDEMBALAGEM      qtde1      qtde2      qtde3      qtde4      qtde5        qtde6
  88                         60                        61                 88             88                 88               88                  1



I use a store proc where I need to make a calculation for labels:

QtdEmbalagem = Total of product per box
QtdPro = Quantity of product sold

First weird qtde1 <> qtde2,  Why?

The result of Qtde1 should be 60 and not 61

When I have other values, I got the correct result, for example:

If I have qtdPro = 80 I will get the Qtde1 = 60
If I have qtdPro = 90 I will get the Qtde1 = 60 and so on

Why?
0
Hello guys,

Sometimes I need to update more than on row, maybe 2 or 100 rows at the same time, but my trigger only fire on row.

I did an example very simple on it. Maybe you can help me on this:

create table alex (codcad int, nome varchar(100))

create trigger teste on arcad
for update
as 
begin   
  declare @codcad int
  declare @nomcad varchar(100)
  
  select @codcad=codcad, @nomcad=nomcad from inserted

  insert into alex (codcad, nome) values (@codcad, @nomcad)

end

update arcad set numdoc1=numdoc1 where codcad > 1 and codcad < 50

Open in new window


After run the update, only one row is recorded through my trigger. how to solve this?

thanks
alex
0
A SQL job is scheduled to run at 6.00AM every day. Some days the job fails with the error: Unable to connect to SQL Server (local) failed.

When executing the job manually it always succeeds and it runs without failure, however there are times that the job fails for no apparent reason with the above error.

What could cause this behavior?
0
Consider this example:

use msdb
 SELECT * FROM backupset  /* 1st step */
 SELECT @@ROWCOUNT /* 2nd step: low execution cost */
 SELECT COUNT( DISTINCT (name)) FROM backupset /* 3rd step: cost is alike 1st step */

The goal is to output the SELECT, then output the count of the result set, and lastly the distinct value count of a specific column.

now substitute a complex query instead of restorehistory.
how can you produce the result without exectuting the complex query twice in expecution plan cost.

is there a way to simplify/modify the query, as to not run the execution plan and joins twice to get the 2 resultsets..

(would creating a temp table and then doing 3 statement with different filters efficient/advised?)
0
[eBook] Windows Nano Server
LVL 1
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

I have a table (User_Values) where I need to create 613 rows of data into columns "User_ValueKey" (primary key) and User_Values (int).  The values are sequential, from 333 to 948.  The data would look like this in the table itself

User_ValueKey      User_Value
333                          333
334                          334
335                          335

And so forth.

Is there a quick way to do this?  I have SQL Server 2008 R2 Express, if that helps.

Thank you!
0
Hi experts
I have a Windows Server 2012 R2 - 64 bit, 32 GB RAM.
On this runs a SQL Server 2008 R2 Enterprise Edition 64 bit

The issue is that the sqlservr.exe service consumes almost 95% of memory (approx 28 GB).
I was checking and the configurations of min and max memory are not configured, I see the values like this:

Min server memory = 0
Max server memory = 2147483647
These are the default values.

My question is: what is the correct way or what should I take into account to give the appropriate values to my BD in relation to memory. Or how to tell if these 32GB of RAM I have is what I really need or are already running short.
0
hi,

for any SQL server install SSL security , under what situation your customer will do it ? usually company I work with install SSL only in web server login page.

to protect DB backup from getting restore to other DB ? so using TDE ?  but TDE must use SSL cerification from a known provider like symantec ?
0
Hello,
Can't figure out what is wrong with this SP.
The first condition does not fire at all.
 @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null,
	 @ColumnName2 varchar(100) = null,
	 @ColumnName3 varchar(100) = null,
	 @Value varchar(100) = null,	
	 @Value2 varchar(100) = null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]

	
          
			 	
			if  @ColumnName3 ='' 
				SET @SQL = 'SELECT DITINCT '  
			   
				SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'  +  ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName2 + ']  LIKER ' +   '''' +'%' +  @Value + '%'+''''
	
	        if @ColumnName3 <> '' 
				SET @SQL = 'SELECT DISTINCT '  
	         

				SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'  +  ' FROM  [' + @tableName + '] '   + ' WHERE   CAST(CollectionDateTime AS Date) =  ' +   '''' + @value  +''''  +
				 ' and   [' +  @ColumnName3 + ']   like ' +   '''' +'%'+  @Value2 +'%' +''''
	
	  SET @SQL = @SQL  	
	  
	  EXEC (@SQL)	
	  PRINT(@SQL)	
   

Open in new window


Cheers
0
Hi Experts ,

Today morning i have noticed below error .any thoughts on that how to solve the problem?

Error: 7884, Severity: 20, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
0
Hi experts
In May I had installed the sql server express with the ssdt. SSDT showed error, see attached file

In June a new installation was made and now I have the sql server 2012 developer
Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
Sep 23 2016 15:49:43
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack

However the error is the same as it showed in May and I can not create packages in the SSIS
www.png
0

Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.