Improve company productivity with a Business Account.Sign Up

x

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 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. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

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

Sign up to Post

Good afternoon, I have a question, is it correct to configure the SQL Server Databasemail to send mail, this violates the security of our server ?, if it were the case that another solution is had.

This commented since I want to do it so that I get emails when there is an error in several JOB, either maintenance or sending reports or internal processes of the company.

Thanks for the help.
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I had a database I could not access until I set it using the following...

ALTER DATABASE emergencydemo SET EMERGENCY;
    GO
ALTER DATABASE emergencydemo SET SINGLE_USER;

How do I get it back to normal mode?
0
I have been reviewing a database server that we have here and I see that Disk C, where the SQL Server is installed, is with 0 bytes available.

I have navigated through all the folders and I have noticed that the "Log" folder weighs 223 GB and inside that folder there are thousands of dump .mdmp files, I know that the dump files are generated when there is a problem in the server, so I started to find out on the internet and in a forum they mentioned that it could be that a database is corrupt, so I consulted the table sys.messages and found a suspicious error:

"An inconsistency was detected during an internal operation in database '%. * Ls' (ID:% d) on page% S_PGID. Please contact technical support." with the code 5242,

On the internet they mention that this error code indicates database corruption but in the message they do not detail what database it is.

So I started to run a DBCC CHECKDB to all databases and all are fine, the CHECKDB does not find any error

what should I do?
0
Scenario1:-

SELECT DISTINCT MATERIAL_CAT11_CD AS A
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT11_CD <> '-' and MATERIAL_CAT11_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD (Coming From Table XX)

Scenario2:-

SELECT DISTINCT MATERIAL_CAT13_CD AS B
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT13_CD <> '-' and MATERIAL_CAT13_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)

Scenario3:-

SELECT DISTINCT MATERIAL_CAT12_CD AS C
FROM TABLE1 M
where curr_rec_ind = 'Y' and (MATERIAL_CAT12_CD <> '-' and MATERIAL_CAT12_CD <> '') and M.SERVICE_CD = STAGING.SERVICE_CD(Coming From Table XX)


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

Please help experts I need to combine all three of the above scenarios in one combination.

<<Help URGENT>>
0
How to write a SQL statement to have this scenario concluded :-

IF A_NO = B_SERIAL_NO THEN A_NO + '-' + RIGHT(B_SERIAL_NO,4) ELSE A_NO + '-' + B_SERIAL_NO    --> populate to some Y in different table

Please help experts.

A_NO and B_SERIAL_NO are from the same table X
0
I have a SQL agent job (test.sql) which is on  server10.

I have a source file(Dummy_Test) in AB folder\srcfiles, I need to move that to AB\TgtFiles. This folder is located on server 11.

How can I move them? Please help
I am using and running sql agent job test.sql on server10 which has below mentioned code in one of the step.

move '\\server11.a.b.com\shared\AB\SrcFiles\Dummy_Test' '\\server11.a.b.com\shared\AB\TgtFiles\Dummy_Test'

--- It says syntax error.

Please help, is it possible to do this? or am I missing something?
0
Can I run the informatica workflow which happened to be in BI14 server, from my SQL agent job which happen to be in BI13 server?

Please help experts!

Running below code:- But got access is denied.

declare @result int;
declare @cmd as varchar(1000);

set @cmd = '\\BI14::PATH:\AB\CD\EF.bat\ Folder_Name Workflow Name';

create table #output (id int identity(1,1), output nvarchar(255) null)
insert #output (output) EXEC @result = xp_cmdshell @cmd
select @result = count(1) from #output where output like '%successfully%';
--drop table #output

IF (@result = 0) RAISERROR (50001, -1, -1);
0
My  system is having issue of SQL server failed to start with error message 3417. Please kindly assist with relevant information to enable me fix this error. Thanks
0
I am using MS Access as the front end (Microsoft Office 365 ProPlus) and currently have a SQL2008 server. I am migrating to a newer SQL 2014 server and are experiencing issues with code written using SQLDMO while testing in the new environment. I have an SSIS job that is called using the now deprecated SQLDMO that is obviously failing in the new environment. I am having limited success finding how to use SQLSMO in a similar fashion. Can someone please give some general direction (or specific!) so that the application can call these jobs in the new SQL server 2014?

Here is the code snippet we use:

Private Sub Command45_Click()

   Set objSQL = CreateObject("SQLDMO.SQLServer")
   ' Leave as trusted connection
   objSQL.LoginSecure = True
   ' Change to match the name of your SQL server
   objSQL.Connect "Server2K14"
   Set objJob = objSQL.JobServer
   For Each job In objJob.Jobs
      If InStr(1, job.Name, "SSIS Bills") > 0 Then
         MsgBox job.Name
         job.Start
         MsgBox "Job Started"
      End If
   Next

End Sub

Thank you, and please let me know if I have failed to provide necessary information.
KLB
0
Good Morning:

i am using SQL Server 2016 Enterprise / Standart
the LOG file is big, we are talking in Gigas, I think it's a lot, I make LOG backup every hour but the size of the log file is the same (it does not decrease)

DECLARE @FECHALOG VARCHAR(20)
DECLARE @ARCHIVO_BK_LOG VARCHAR(500)
DECLARE @NAME VARCHAR(500)
DECLARE @DESCRIPCION VARCHAR(500)
DECLARE @MEDIANAME VARCHAR(500)
DECLARE @NOMBREBD VARCHAR(100)
DECLARE @CARPETABK     VARCHAR(100)
DECLARE @RUTARAIZBK NVARCHAR(100) = 'V:\BACKUP\'

/*BD Loyalty*/
SELECT @NOMBREBD = UPPER(name) FROM sys.databases WHERE UPPER(name) LIKE N'%MIBD'
SET @CARPETABK = CONCAT(@RUTARAIZBK, @NOMBREBD, '\')
EXECUTE master.dbo.xp_create_subdir @CARPETABK
SELECT @FECHALOG = CONCAT(RIGHT('00' + CAST(DAY(GETDATE()) AS VARCHAR(2)) , 2), RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) , 2), YEAR(GETDATE()), RIGHT('00' + CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR(2)) , 2), RIGHT('00' + CAST(DATEPART (MINUTE,GETDATE()) AS VARCHAR(2)) , 2))
SET @ARCHIVO_BK_LOG  = CONCAT(@CARPETABK, @NOMBREBD, '_LOG_', @FECHALOG, '.trn')
SET @NAME = CONCAT('Backup LOG ', @NOMBREBD)
SET @DESCRIPCION = CONCAT('Backup LOG de la BD ', @NOMBREBD, ' dia y hora ', @FECHALOG)
SET @MEDIANAME = CONCAT('Backup ', @NOMBREBD, ' LOG')
BACKUP LOG @NOMBREBD TO DISK = @ARCHIVO_BK_LOG
     WITH FORMAT,
     NAME = @NAME,
     DESCRIPTION = @DESCRIPCION,
     MEDIANAME = @MEDIANAME
GO

Open in new window

0
Upgrade your Question Security!
LVL 12
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Hello, I have a table which has a xml column and two varchar columns, and I would like to send this table as an email report.
I used SQL Server Database mail and scripts similar as below, but the xml column becomes NULL in the email report.   May I ask if someone knows how I can send the email, with both varchar data and xml data? I would like the xml data to show in xml format so that user can click on it for more details..  Thanks in advance :-)

DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
N'<head>
<Style>
body {font-family: Verdana;}
</style>
</head>'+
 N'<table border="1" width="90%">' +
 N'<tr><th>Duration</th><th>SessionID</th><th>SQL_Text</th></tr>'+
      CAST ( ( SELECT td = Duration,'',
                    td = SessionID, '',
                    td = SQL_Text, ''
  from Table FOR XML PATH('tr'), TYPE  
    ) AS xml ) +     N'</table><br><br>'    ;
      
declare @subject varchar(300)
select @subject='email report'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBMailService',
@recipients='email@hotmail.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML'
GO
0
All,

I'm getting the following error when I try and process my SQL data cube:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DIM_GEO', Column: 'CITY', Value: 'COLUMBUS'. The attribute is 'CITY'.

How do I correct the issue?
0
hi experts

the column store [Fe creac  ]
Column 0	Usuarios    	Grupo	Tipo          	Creado por  	Fe creac  	Válido de 	Fin valid 	Entr sist 1  	Entr sist 2	Clave acc 1	Clave acc 2	Bloqueo1	Bloqueo2                                       	EntrSisInc	Column 15
	A_MATERIALES	     	A Diálogo     	ATI         	14.11.2016	          	          	20.02.2018  	07:18:10  	          	15.11.2017	       	                                              	          	
	AABARCA     	     	A Diálogo     	SMIADMIN    	31.01.2012	          	          	17.02.2018  	10:12:30  	          	18.12.2017	       	                                              	          	
	AAGUILAR    	     	A Diálogo     	SMIADMIN    	28.11.2011	          	          	19.02.2018  	11:01:50  	          	15.12.2017	       	                                              	          	
	AAROCA      	     	A Diálogo     	SMIADMIN    	13.12.2013	          	          	20.02.2018  	08:12:10  	          	06.02.2018	       	                                              	          	
	ABALLADARES 	     	A Diálogo     	ATI         	17.07.2017	          	          	04.08.2017  	18:32:07  	          	18.07.2017	       	Responsable                                   	          	

Open in new window


this query has errors
SELECT*
  FROM [SMI].[dbo].[rsusr200_20022018_1335]
  where CAST([Fe creac  ] AS datetime)   >=  CAST('01.10.2017' AS datetime)

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
0
Hello guys,

I have a query that is resulting me an error:

Arithmetic overflow error converting float to data type numeric

SELECT 
convert(numeric(14,2),round(convert(numeric(17,8),qtdpro)*round(convert(numeric(17,8),prunit)*convert(numeric(17,8),(1-(a.pdes/100)) ), dbo.fn_DigitosValor())  +convert(numeric(17,2),(a.FRETE+a.SEGURO+a.OUTDESP),2),2)) as BASCSLL
from aripco a,arpco b 
where (a.numped = b.numped and a.seqped = b.seqped) 
  and qtdpro > 0 
  and prunit > 0 
  and b.flagoriginal = 0 
  and (B.FLAGFINALIZADO = 0)

Open in new window


How could I get the row that is resulting me this error?

Thanks
Alex
0
hi experts

i  am reading about tuning
what's the mean
Design the tables considering the implicit conversions, which should be avoided.

can you give me T-SQL code
0
Hi experts

i am reading about tuning

i do not understand this
Minimize the use of temporary tables or table-type variables. Use sub-queries, table expressions or window functions

can yo give me an exmple in TSQL code
0
I am trying to do a join between different tables using acct_id. Table 1 is the main linking table and has this field formatted as varchar(10). All other tables has it as has it as Decimal(10).
Table 1 also has blanks (not nulls) for acct_id. When I tried to use CAST I just get errors. I think it's coming from those blank fields but don't know how to fix it.
I'm using SQL going through a linked server.
Ex:
Select * from openquery(CMFR1,'
Select
mcr.ln_no
,mcr.tran_num
,mcr.acct_id
,prod_dt
from MSP_CR mcr
left join on dly_prod prod on mcr.acct_id = prod.acct_id
')
Then left joins on 4 other tables using the same mcr.acct_id =
0
Count (*) gives an error;

 is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT IET.[ICol1]
      ,IET.[Col2]
    
         , ''
         , NULL
         , NULL
         ,  COUNT (*)

FROM [table1] IET  

Open in new window

0
We have SQL Server 2005 running on Windows Server 2003. I am looking to upgrade both the OS to Server 2012 r2 and Sql to SQL 2014 Ent, is there any specific order this should be done in and if so what is that order? SQL 2014 Upgrade Advisor
0
Free Tool: Port Scanner
LVL 12
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Experts,

Which is the most optimized way to update a table using a STORED PROCEDURE?


Like this?:
----------
update a
set col1 = b.col1
from table1 a
inner join --SUBQUERY TABLE
      (select id, desc
            from table3) b on a.id = b.id




OR like this:
-------------
create table #Temp1 (id int, desc varchar(25))
insert into #Temp1
(select id, desc
      from table3)

update a
set col1 = b.col1
from table1 a
inner join #Temp1 b on a.id = b.id
0
Hello,
Can you please help,
I have a stored procedure that fires some emails to my clients.
Currently it runs twice a month.
if DATEPART(day,getdate()) in (6,21)  
Begin xxxxxxxxxxx

I need to change the date to make it run every other Saturday. (Starting  January 6th, 2018)
Jan 6th, Jan 20th, Feb 3rd, and so on.

Any help is greatly appreciated.
SQL2008

W
0
The following code gets me the top 100 tours and reservations

How can I make that into one select so that I get 200 records... 100 or each "group"

SELECT   TOP 100 *
FROM     ewAppointments
WHERE    AppointmentTitle LIKE  'Reservation%'
ORDER BY AppointmentID DESC

SELECT   TOP 100 *
FROM     ewAppointments
WHERE    AppointmentTitle LIKE  'Tour%'
ORDER BY AppointmentID DESC;

Open in new window

0
hi,

how mirroring and alwayson are diferent. What are various types of mirroring. what are advantages of each.

what i rpo and rpt and ms hcl means
please advise
0
hi,

how sql server maintenance plan different from setting sql server agent setting up cleanup at a particular time. Please advise
0
I have a very old database  on an very old server

The database has a table that has gotten bloated and is eating up drive space.

When I try and delete rows from the table I get a tempdb out of space

I cannot add drive space.  

What are my options?
0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 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. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.