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

Hi,

I need to store pdf documents and images in a sql server database.
It is possible to calculate the approximate space for which eg. 1 Gb of documents, which size matches the database.

Best regards
0
Exploring SQL Server 2016: Fundamentals
LVL 12
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Experts,

I have an SSRS server setup and we are currently scheduling nightly delivery (via EMAIL) of reports to clients.

The issue is that I would like to "dynamically" change the "FROM EMAIL" address for the out-going emails that contain the reports to a "Special EMAIL Address" for certain CLIENTS.
So for example, if I schedule a nightly report delivery via email for Customer "A", then I want EMAIL ADDRESS "A" to be included within the body of the email as the FROM email.

If I schedule a nightly report delivery via email for Customer "B", then I would like EMAIL ADDRESS "B" to be included within the body of the email as the FROM EMAIL.

Can someone help with HOW TO DO THIS?

Let me know if you have any questions.

Thanks
M
0
Guys,
I have a situation where  I need to migrate / copy existing  logins from windows authentication to - sql authentication. Every windows  Logins  has specific server role, database role and attach to  schema. So  in order to copy this login and again create them as sql logins, do you guys able to recommend a method of doing this ? currently this sql server has around 2000 actives windows login that I must convert them to sql authentication.
0
The following command string exports a PDF to be saved or opened.

start explorer "<http://ServerName/ReportServer$NamedSQLInstance?%2fPurchasing%2fPurchaseOrder&PoNumber=103290&rs:Format=PDF"

Need to NAME and SAVE the file Automatically, from a Delphi desktop application.
0
Hi experts,

I get a requirement to monitor deleted records in SQL server 2005. As I know, neither change tracking or change data capture is available in this version of SQL server. Any idea would be appreciated!

Best Regards,
Shelwin Wei
0
Hello.

I want to create a trigger on a table on INSERT. So when an INSERT attempts to happen on the table, a SELECT is first run and if the SELECT returns rows, the INSERT is cancelled but the transaction ls logged to another table.

The code that has the SELECT in, is as follows

BEGIN TRAN
IF (EXISTS (SELECT *
FROM DBO.TABLE1 AS A, DBO.TABLE2 AS B 
WHERE A.ACCOUNTID = B.ACCOUNTID
AND A.COUPONID = B.COUPONID
AND PREVBALANCE = 4 AND BALANCE = -2
AND ISSUEDLOCALDATE> '3-JUL-2018'))
BEGIN 
	SELECT @@TRANCOUNT,'UNABLE TO UPDATE TABLE 1 WITH THESE DETAILS - CHECK TABLE_ISSUE' 
	ROLLBACK
END

Open in new window



The above works as expected.

I'm now trying to develop a trigger on the table in question:


USE [DB]
GO
/****** Object:  Trigger [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]    Script Date: 08/16/2018 09:41:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TRIG_WIDGET_ADDITIONAL_COUPON_ISSUE]   
ON [dbo].[WIDGET_COUPON]   
AFTER INSERT 
AS 

	DECLARE @inserted TABLE ([couponID] [bigint] NOT NULL,[organizationID] [bigint] NOT NULL, [orgLevelID] [bigint] NULL, [couponTypeID] [bigint] NULL, [accountID] [bigint] NULL,	[promotionID] [bigint] NULL,[customerID] [bigint] NULL,[hashCode] [int] NULL,[couponCode] [nvarchar](32) NULL DEFAULT ((0)),[serialNumber] [nvarchar](50) NOT NULL,[createdLocalDateTime] [datetime] NOT NULL,[createdHostDateTime] [datetime] NOT NULL,[createdLocationID] [int] NULL,[issuedLocalDateTime] [datetime] 

Open in new window

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
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
Hi Everyone,

How to setup software upgrade group (SUGs)? Can you show me how to configure in SCCM console or GPO push down the windows updates & security to the clients.

How to retrieve full software report for SCCM

Tks.

Lcuky
0
Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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