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

I need get the list of instructors who do not have any classes between a given date and time .

I got the code but it does not work

SELECT DISTINCT U.userKey , U.firstName , U.lastName , U.email 
FROM users U WITH (NOLOCK) 
INNER JOIN user_securityRole USR WITH (NOLOCK) ON U.userKey = USR.userKey 
INNER JOIN securityRole_lkup_permission SRLP WITH (NOLOCK) ON USR.securityRoleKey = SRLP.securityRoleKey 
INNER JOIN lkup_permission LP WITH (NOLOCK) ON SRLP.permissionKey = LP.permissionKey 
WHERE U.enable = 'Y' 
AND LP.permissionCd = 'INS' 
AND EXISTS (SELECT 1 from instructors I WITH (NOLOCK) where deleted = 0 and I.instructorKey=u.userKey) 
AND EXISTS (SELECT 1 from userInstructorLicense UIL WITH (NOLOCK) 
where UIL.userKey = u.userKey and DATEDIFF(DAY,UIL.expirationdt,'2018-07-19 14:00:00.0')< 0 
and UIL.availStateKey = 1) 

and NOT  EXISTS ( 
	SELECT 1 FROM Session S WITH (NOLOCK) 
	INNER JOIN SessionUnit SU WITH (NOLOCK) ON S.SessionKey = SU.SessionKey 
	inner join sessionMap SM WITH (NOLOCK) on SM.sessionKey= S.sessionKey 
	WHERE su.instructorKey = U.userKey AND  (('2018-07-19 14:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) 
OR ('2018-07-19 16:00:00' between convert(datetime,SU.sessionStart) and convert(datetime,SU.sessionEnd)) ) ) 

ORDER BY U.lastname , U.firstname

Open in new window

0
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

I have a function that if I pass in a string and  the seperator it returns to me the table
Example  select * from dbo.ParseListString('ABCIDEF','|')
returns me the following table
str
ABC
DEF

What I need is to build a list from a table where there may be thousands of rows with bar separated string of many values but I want to return a distinct list

Example... in the following code I would want to return this data
OfferID
SDFLL3NCCC
SDORL3NCCC
SDFLL3NDDD


DECLARE @IndividualID BIGINT = 3612805;
DECLARE @table TABLE
    (
        CampaignID INT ,
        IndividualID BIGINT ,
        OfferIDs VARCHAR(1000)
    );
INSERT INTO @table ( CampaignID ,
                     IndividualID ,
                     OfferIDs )
VALUES ( 1000, @IndividualID, 'SDFLL3NCCC|SDORL3NCCC' ) ,
       ( 1001, @IndividualID, 'SDFLL3NCCC' ) ,
       ( 1002, @IndividualID, 'SDFLL3NDDD|SDORL3NCCC' );
SELECT *
FROM   @table
WHERE  IndividualID = @IndividualID;

Open in new window

0
This tutorial is a great guide for newbies in SQL Server who never backup the database and for people with some experience, because you will learn some tips that you may not know.
1
Hi,

I have this script to list duplicate rows in my table:

SELECT *
FROM lin
WHERE ids IN
(
      SELECT ids
      FROM lin
      GROUP BY ids
      HAVING COUNT(*) > 1
)
ORDER BY ids

I need to update the duplicate vales and add a number to have different value. I tried this but not work like a select, how can change the query to update?

update lin set  IdS=IdS + convert(varchar,Row_Number() Over ( Order By lin.ids ))
WHERE ids IN
(
      SELECT ids
      FROM lin
      GROUP BY ids
      HAVING COUNT(*) > 1
)
0
I have a table (tMain) of sales... one entry for each sale.  In this table is the model (FrameModel) and an office (Office, currently just testing for all offices in the table.)  I would like to run a query that will pull the max similar model sold and display their count and also find their percentage (based on the total number of FrameModels sold) .  I am using the following query to try to pull this and a date range based on a form for the user,  (Basically trying to find models that have sold over 1 to find out "top model" sellers.

Its returning results but  I know for sure that there are other fame models not even showing up that have many sales (over 10)

SELECT tMain.DOS, tMain.FrameModel, Count(tMain.FrameModel) AS CountOfFrameModel, tMain.FrameLine, tMain.Office
FROM tMain
GROUP BY tMain.DOS, tMain.FrameModel, tMain.FrameLine, tMain.Office
HAVING (((tMain.DOS) Between [Forms]![FReportSelect]![DateStart] And [Forms]![FReportSelect]![DateEnd]) AND ((Count(tMain.FrameModel))>1))
ORDER BY Count(tMain.FrameModel) DESC;

Thank you.
0
Source Table Name :- CLI
Field Name :- AMOUNT

Business Rule

SELECT SUM(AMOUNT) AS B
FROM Test1 CL , Test2 CH, Test3 SH WHERE CH.NO = CL.NO AND SH.NO = CH.ORDER_NO
group by no

Target Table:- SERVICE
Field Name :- AMOUNT_PC
0
ms sql management studio how to query select stored proc by passign inputs graphically without writing query.

one of my colleague passed inputs in one cool graphical option in ms sql management studio
not exactly sure how he was able to do it
please advise
0
Good Day Experts!

I have been tasked with another assignment that I need some help with.  Hopefully you can point me in the right direction.  

I have a list of students that each have 4 classes.  So far we have made it through 3 quarters.  However, the student could have moved here at the start of the second quarter so would only have 2 quarters in the books.  When the class changes, I have to avg the grade percentage for as many quarters as the student was enrolled.  All of the class grade percentages need to be on one line per student.  I have attached a file to try and make it clearer for what I am trying to achieve.  

What is the best way to approach this?

Thank for the help,
jimbo99999
EE.xlsx
0
I'm taking Backup of the LOG every 15 minutes, but which LOG has created up to 90GB.

While it is true every time you get a BK Full or LOG truncates the LOG, but this has grown a lot, as you can calculate how much is needed for transactions LOG and not grow as much, as in this case.

This is for several BD that you have.
0
Hi

what are the permissions that my user must have in order to see the jobs (successful, failed)

1. If I can see the msdb database but I do not see the jobs
2. You do not work with the integration services catalogs, they inform me that they work the jobs through the agent and see them visually with jobis
3. My server roler to the instance is public
4. My role membership in the database is role_main and public

I need a permission that allows me to see the jobs, where they show error what are the errors, if they stay hung, execution time etc etc. The limitation is that DBA can not give me administrator permission on the instance.
0
Cloud Class® Course: Python 3 Fundamentals
LVL 12
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

PLEASE

view attachment

The files . mdmp are being saved in the Log folder of Disk C where SQL Server C: \ Program Files \ Microsoft SQL Server \ MSSQL13.POSEIDON \ MSSQL \ Log is installed, the .mdmp files are equal to the dump file I think, when you I right click on a dump file and see its properties, its extension is .mdmp

On the C disk the mdf and ldf files of the databases are not being saved, on the C disk there are only the installation folders and the 223 GB weight of the log referred to the Log of the installation folder C: \ Program Files \ Microsoft SQL Server \ MSSQL13.POSEIDON \ MSSQL \ Log and it's just so heavy because every day a lot of Dump files are being generated

 On the other hand, we do not have visible problems with the bd server, everything works fine, it does not fall or anything, that's why I wanted to know why so many dump files are being generated daily or how could I identify what is happening to solve it? that eliminates the dump files and frees a little space, in a few days it is full again.
0
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
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
Free Tool: Subnet Calculator
LVL 12
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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
I have a database that has a field with observations, which is entered by an application in which the user records all the comments made by the user, in which are uppercase, lowercase, letters of less than 4 digits, numbers letters, symbols.

what you want to do is extract the words that are most repeated and show it in another column.
Muestra_Aleatoria.txt
0
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
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and consists of using a SQL Recovery Software.
2
 
LVL 53

Expert Comment

by:Mark Wills
Comment Utility
Good Article,

Even as a SQL Server "expert", always good to know about tools - especially when it can make life much easier.

Stellar Phoenix SQL Database Repair looks the goods.

Thanks for sharing :)

Cheers,
Mark Wills
2

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.