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 have a failed SQL backup and have no clue why this is happening? Can anyone help with this?

Failed:(-1073548784) Executing the query "BACKUP DATABASE [JDE910] TO  DISK = N'\\\\x.x.x.x..." failed with the following error: "Cannot open backup device '\\\\x.x.x.x\\JDE Images\\SQL Backup\\Daily\\JDE910_backup_2018_07_14_220001_8461617.bak'. Operating system error 1311(There are currently no logon servers available to service the logon request.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


X.X.X.X are the IP address of the webserver jde
x.x.x.x is the ip address of the NAS device where the backup has to be moved
0
Cloud Class® Course: Microsoft Office 2010
LVL 12
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Dear expert,

Simple query, I want MSSQL to exclude all the NULL result in columns from the table.

Any easy solution?
Thx
0
Hello Experts,
I am trying to display a table rows horizontally by TKT_Id.  I have two tables, TKT_DETAILS and FIELD_DETAILS.  Please see the details below.

TKT_DETAILS
TKT_Id
Field_Id
Field_Value

There are about 750 different fields numbered as 1 thru 750.  Each row contains only 1 field.  There are 750 rows for every single record vertically.

FIELD_DETAILS
Field_Id
Field_Name

This table contains Field Names of the every Field_Ids.

Because it is very difficult to read vertically, I am trying to create a view to show them all 750 fields in a single row as below.

Tkt_Id, Field_Name_1,  Field_Name_2,  Field_Name_3,  Field_Name_4,  Field_Name_5...  Field_Name_750
    1             001                     002                     003                     004                      005            ...             750

Plesae let me know how to do it without using PIVOT.  Thank you in advance for your time and help!
0
Hello Experts,
Currently I have a view as below (look Current).  I am trying to make it more dynamic, and trying to convert it to as below (look Future).  Please let me know how can we make it possible.  Thank you in advance.

Thank you!



Current
------------

USE ITN
SELECT DISTINCT            
   TKT_ID       
  ,CAST(MAX( CASE FIELD_ID WHEN 1  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_1
  ,CAST(MAX( CASE FIELD_ID WHEN 2  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_2
  ,CAST(MAX( CASE FIELD_ID WHEN 3  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_3
  ,CAST(MAX( CASE FIELD_ID WHEN 4  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_4
  ,CAST(MAX( CASE FIELD_ID WHEN 5  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_5
  ,CAST(MAX( CASE FIELD_ID WHEN 6  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_6
  ,CAST(MAX( CASE FIELD_ID WHEN 7  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_7
  ,CAST(MAX( CASE FIELD_ID WHEN 8  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) FIELD_8
FROM TKT_DETAILS (nolock)



Future
-------------


USE ITN
SELECT DISTINCT            
   TKT_ID       
  ,CAST(MAX( CASE FIELD_ID WHEN 1  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) SELECT FIELD_1 FROM FIELD_DETAILS
  ,CAST(MAX( CASE FIELD_ID WHEN 2  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) SELECT FIELD_2 FROM FIELD_DETAILS
  ,CAST(MAX( CASE FIELD_ID WHEN 3  THEN FIELD_VALUE END  ) AS VARCHAR(100) ) SELECT FIELD_3 FROM FIELD_DETAILS
  ,CAST(MAX( CASE FIELD_ID WHEN 4  THEN …
0
Hi All,

I want to get current language id.
I have below code :

SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
        VALUES
            ('en-US')
        ,   ('en-GB')
        ,   ('ja-JP')
        ,   ('Ro-RO')
        ,   ('el-GR')
    ) C (culture);

Open in new window


I want to do something like :

SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
       SELECT ...... (get current system culture)
        
    ) C (culture);

Open in new window


How could I do it ?

Thank you.
0
I just installed Visual Studio Community 2017 with the SSDT tool kit, connected to my SQL server opened a table  but Diagram, Criteria and Results panes are grayed out. In "Tools" | "Options" | "Database Tools" | "Query and View Designers" all the checkboxes for the Diagram Pane, Criteria Pane, SQL Pane and Results Pane are checked. I am extremely new to VS I normally do all my work in SSMS.
0
Hello Experts,
I am trying to run a SSIS package using Visual Studio 2015.  When execute it with Start Debugging mode, the package runs just fine.  But when run in Start Without Debugging it fails with the below error message.  Any idea what is going on?  Please try to help.  Thank you in advance.

Error Message:  The task has failed to load. The contact information for this task is ""

Thank you!
0
Thank you all in advance for your help!

I need some help enabling the following script with loop capabilities.

In other words, the script should cycle through the list of recipients sending an  email to each.

Thanks again!



DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @email NVARchar(4000)
Declare @count NVARCHAR(MAX)
Declare @lastname VARCHAR(MAX)


Set @count =
(Select count(email)
FROM  vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > ' 2018-4-1'
and email = 'ana@aol.com')



Set @lastname =
(
Select distinct (lastname) from vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > ' 2018-4-1'
and email = 'ana@aol.com'
)



SET @xml = CAST(( SELECT email AS 'td','', convert (varchar, Created, 101) as 'td','', LOC AS 'td'
FROM  
vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > '2018-4-1'
and email = 'ana@aol.com'
order by created desc







FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))



SET @body ='<html><body><H3>'+ 'Dr.' + @lastname + ','
+
' Currently ' + @count + ' genba walks have been registered for you this quarter.'
+
' '+ 'Please let us know if you have any questions regarding this count.'

+ ' '
+ 'Thank you!'
+

'</H3>
<table border = 1>
<tr>
<th> email </th> <th> Created </th> <th> Location </th></tr>'    

 
SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ACE DB Admin', -- replace …
0
Thank you all in advance for taking a look!

The sql below sends an email update in the form of a table. Each row in the table provides detail for a particular transaction. I would like to include a count of the rows in the body.

Any suggestions would be much appreciated!



DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @email NVARchar(4000)





SET @xml = CAST(( SELECT email AS 'td','', Created as 'td','', LOC AS 'td'
FROM  
stbl_Provider_test_6_25
Where Created > ' 2018-4-1'
and email = 'joe@aol.com'
order by created desc






FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))



SET @body ='<html><body><H3>
Email Update
</H3>
<table border = 1> 
<tr>
<th> email </th> <th> Created </th> <th> Location </th></tr>'    

 
SET @body = @body + @xml +'</table></body></html>'


EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ACE DB Admin', -- replace with your SQL Database Mail Profile 
@body = @body,
@body_format ='HTML',
@recipients = 'joe@aol.com', -- replace with your email address
@subject = 'E-mail in Tabular Format';

Open in new window

0
SELECT   COUNT(*) as total_items, "Category" =  
      CASE  
         WHEN NR_R < 1645 THEN 'less costly'
         WHEN NR_R > 1645 AND PKG_NR_R < 1700 THEN 'ok costly'  
         WHEN NR_R > 1800 THEN 'high costly'          
         ELSE 'not listed to sell'  
      END  
   
FROM SCHEMA1.TABLE_1
GROUP BY  NR_R
GO

above query working fine.

But i am trying to transpose data to look in column view where each category shows in the below column format.



less costly  ok costly  high costly
23                 50             77

SELECT   SUM (CASE WHEN NR_R < 1645 THEN 1 ELSE 0) as "less costly"
         SUM (CASE WHEN NR_R > 1645 and WHEN PKG_NR_R < 1700 THEN 1 ELSE 0) as "more costly"
         SUM (CASE WHEN NR_R > 1800 THEN 1 ELSE 0) as "most costly"
         ELSE 'not listed to sell'  
           
FROM SCHEMA1.TABLE_1

GROUP BY  NR_R
GO
i tried as above says incorrect syntax with )

Please advise
0
Cloud Class® Course: Ruby Fundamentals
LVL 12
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Hi,

I have this query but it seems to take a really long time to complete. is there any modifications I could do that could perhaps make it a little more efficient?  I was envisaging for a year and a halfs worth of data, perhaps taking a few minutes, but I stopped it before at 23 minutes.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
declare @fromdate date
declare @todate   date
set @fromdate = '2017-01-01'
set @todate   = '2018-05-05'
SELECT 
c.de_number
,client.cl_number
,c.de_status
,Max(CASE  WHEN a1.co_type = '2'  THEN a1.co_description  ELSE c.de_status END) Prev_Stat
,c.de_type
,c.de_collector
,c.de_salesman
,c.de_listed_date
,c.de_owned_by_debtor
,c.de_principal
,c.de_principal + c.de_adjustments Netprin
,c.de_paid
,c.de_last_payment_date
,c.de_last_worked_date
,c.de_active
,c.de_poe
,c.de_zip
,CASE  WHEN c.de_cell_phone IS NOT NULL  THEN 'X'END Good_cell
,CASE  WHEN c.de_home_phone IS NOT NULL  THEN 'X'END Good_landline
,CASE  WHEN c.de_email IS NOT NULL  THEN 'X'END Good_Email
,Max(case when (co.co_description = 'DMOT Request for payment letter' OR co.co_description = 'DMOT Small Balance Letter') AND co.co_type = '0' then co.co_date end) first_demand_letter
,Max(case when co.co_description = '1st Demand Email' AND co.co_type = '0' then co.co_date end) first_demand_email
,Max(case when co.co_description = 'DMOT 48 Hour Notice' AND co.co_type = '0' then co.co_date end) 'DMOT-48hour'
,Max(case when co.co_description = '48 Hour  -Email' AND co.co_type = 

Open in new window

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
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
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 am getting the attached error when I try to launch the SQL Sentry Client.

I login to the server as a domain admin that is supposed to have permission to the SQL server and its database.  I am using integrated windows authentication.

I login to the SQL server directly using the same domain admin account and I am able to see the database and its view tables in SQL Management Studio.

Please advise where I should look.  

Thanks.
0
visio how to see shapes stencils then task, artifact, start msg event, gateway, intermediate event.

how to select above kind of shapes and draw nice diagrams to demonstrate systems

for me all disabled some reason.

please advise
visio.png
0
Dear Experts,

I have configured an SQL 2016 with some dummy data and program an hourly backup but it will override the sql backup file.

I tried to do a database restore from the backup and renamed the database to another name.

Some who after the backup, the original SQL DB SQLDB became SQLDB (Restoring...)

Where could I have gone wrong?
0
I have a 2016 reporting services running from an independent SQL Server.  I have a .net web application that calls some reports via a hosting page.  Note it was built in an older version  but I have had this exact code run fine in 6 of my 2016 customers....i have 2 new customers that the reports fail.

There is no IIS involved..this is a native mode configuration running on port 80.

Here is stack trace
Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response. 
  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

 Exception Details: System.InvalidOperationException: Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.

Source Error: 


 An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 



[InvalidOperationException: Client found response content type of '', but expected 'text/xml'.
The request failed with an empty response.]
   Microsoft.Reporting.WebForms.Internal.Soap.ReportingServices2005.Execution.RSExecutionConnection.GetSecureMethods() +192
   

Open in new window

0
I have a table having varbinary column which is primary key.
Whle trying to retrieve the data as below gives 0 result:

Select count(*) from Trn_Account where cast(TransID as varchar(20)) = '3038D'

below query gives me 1 result:
Select * from Trn_Account where  TransID=  cast('3038D' as varbinary(20))

Below is my table structure:
Trn_Account:

TransID      varbinary      no      20                            no      no      no      NULL
TDate      datetime      no      8                            no      (n/a)      (n/a)      NULL
TrnsNo      numeric      no      9      18      0      no      (n/a)      (n/a)      NULL


Please help.
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.