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

Im using sql server 5 and I want a query to loop through a table adding a value in each row to a counter and break when i reach a max value - eg TotalQty

eg table
'records'
name qty
lou 1
paul 2
alan 1
jess 3
.. loop through this table and stop when adding qty in each row = TotalQty

eg TotalQty = 4

should return counter = 3
 as it would have looped through rows 1 to 3
if eg TotalQty = 2 it should return counter = 2

pseudocode eg
select * from records
counter  = 0
recordcounter = 0

while counter <  TotalQty
 counter = counter + table.qty
recordcounter = recordcounter  + 1
wend

any ideas welcome
0
10 Tips to Protect Your Business from Ransomware
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hi experts

can you share scripts for administration about block SQL SERVER 2016
0
Hi experts
1. When should I use IFI - instant file initialization?
2. Some URL that I can recommend for the implementation
3. Should I have it configured because it helps in the autogrowth?
0
Restore Stored Procedure in SQL Server
In this article, we will discuss how to deal with a situation wherein you face an issue with a mandatory stored procedure in your critical database.
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
Hi,

I need a query to return the average per day for each customer and total average of all customers per day. How can i do that?

I can calculate the average for each date/client but how can calculate (and add a row) with the average of the day .

select Avg(Value) as Med , Customername as customer, Datea as dateinvoice from AGH group by datea,Customer

Attached, image with example what i need.

best regards
Img14.png
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...

We have a number of Tables, that need to be "migrated" from one database to another, in MSSQL-2012.  There are FK Dependencies on a good number of these tables.  For example:

PS_TKT_HIST_LIN                   (Table-# 1)
PS_TKT_HIST_DISC_COD       (Table-# 2)
PS_TKT_HIST                           (Table-# 3)
PS_TKT_HIST_LIN_CELL         (Table-# 4)
PS_TKT_HIST_LIN_CELL_EXT (Table-# 5)

...and so on.  In the example above, Table-# MUST exist FIRST...then...Table-# 2 MUST exist SECOND, then Table-#1, Table-#4, and Table-# 5.  I know we can use the built-in MSSQL EXPORT/IMPORT function...and that's great...but, because these need to be Exported/Imported IN SEQUENCE (due to the FK Dependencies), we figured we'd have to do that "Write a query to specify the data to transfer"...as opposed to..."Copy data from one or more tables or views".

Based on the above example, therefore, can someone please help us to develop the correct script with syntax, to accomplish this task?  It would be unbearable to do this 1-table-at-a-time, as there are over 200 tables, with FK Dependencies.  If we can get an example to provide a roadmap, that would be great.  And before you ask...the COMMAND LINE utilities are not an option...this MUST be done, within Management Studio :-)

Thank you in advance...Mark
0
I have a huge backup file the decomposed into 3 files test.bak1,Bak2 and bak3. How can I restore this DB in Standby mode and apply additional Tlog files?
0
Newly released Acronis True Image 2019
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

I'm under a tight deadline (and have a moody manager who's not patient ) to remove some hardcoded .Net  code and create a table so we could read the data dynamically.

This is how the code looks like (there are 48 FieldIds...so 48 rows)
  Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });

Open in new window



And this one (used for some boolean fields)
            Fields.Add(new ReportField
            {
                FieldId = "48",
                TableName = _tbUnit,
                ColumnName = "Returned",
                DisplayName = Normal.Returned,
                DbType = DbType.Boolean,
                Bool_ValueIfTrue = Normal.Yes,
                Bool_ValueIfFalse = string.Empty,
                Bool_TrueText = Normal.Yes,
                Bool_FalseText = Normal.No
            });

Open in new window


This is how I want to do it:


A table with columns that match each property in the code. So,
Identity field (this is the auto increment identity field)
FieldId (ex-developer hasn't used sequential Ids. I want to keep the same Id because we have data saved already with these Ids)

TableName
ColumnName
DisplayName
DBType
Bool_ValueIfTrue
Bool_ValueIfFalse
Bool_TrueText
Bool_FalseText

Should I add the Identity field? Any other ideas given that I have to get this done fast?
0
This is SQL 2014

We have a table with 3 Text fields. It holds data that has been compressed in .Net code. Not encrypted, it's compressed and .Net code decompresses the data to display on the screen.

It looks like this
r1.png
I don't know if we'll lose the data but can I change Text field to nvarchar(max) without losing data?
0
Hi there. We currently have SQL Server 2008 R2 running on a single server/DC (small office setting), and we just purchased a new server box, and want to move everything over to it (including SQL Server). Unfortunately, the installation media with the licence on it is missing - is there a way to find the licence that the old server with SQL 2008 R2 is using? To clarify, SQL was installed separately, so should have its own licence. I looked through the registry, but the "Productcode" registry key didn't have the correct format (wasen't aaaa-bbbbb-ccccc-ddddd-eeee).
Thanks.
0
I have a full backup every weekend, and every 30 min transaction log backup. Today I found out one transaction log backup fail last night but after 30 min, the transaction log backup runs again, it succeeded, then continue to run succeeded.

I want to know if I should go ahead to run a full backup to ignore the failed transaction log backup, no need to wait to weekend. My reason is I cannot restore all the logs if something happened now. I have to use last week's full backup + all the transaction logs before next weekend full backup. Since one of the transaction log failed, I am not able to restore to point in time, Am I right?
0
I have a text field in a table and I want to read them. I did this but I still see a value like this
4sIAAAAAAAEAE2PT0sDMRDFv0qZ8x52W//U3LZKRcQt2OJFPAz

 r1.png
0
Hi Guys,

select hashbytes('SHA2_256','123'+'456'+'1') -- Hashbytes will always give UNIQUE value for n number of rows.

Just wanted to know, if we convert the HASHBYTES function to BIGINT like:-

select convert(bigint, hashbytes('SHA2_256','123'+'456'+'1')) will this conversion will always give UNIQUE values if their are million or billions of rows?

Please help.
0
Good morning,

I am having a problem running SSIS package in SQL 2016. I can create and run it in separate Import/Export Data utilities, but when I execute it in SQL itself I am getting an error message "Parser NULL". I also can not run the Import/Export wizard withing the SQL database engine. I am getting an error: "The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered."

Please advise. THanks
0
Hi All,

I have below code.

 UPDATE A
		SET A.NilaiAlokasi = ISNULL(B.TotalNilaiAlokasi, 0)
		FROM TDMONEYPAYDIST A
		INNER JOIN
		(
		SELECT
		KodePembayaran
		, SUM(NilaiAlokasi+NilaiLebih) AS TotalNilaiAlokasi
		FROM TDALCAP A
		WHERE EXISTS
		(
		SELECT
		NULL
		FROM TDALCAP B
		WHERE A.NoAlokasi = B.NoAlokasi
		AND B.NoAlokasi = @NoTransaksi
		)
		GROUP BY
		KodePembayaran
		) B

		ON A.ID = B.KodePembayaran

Open in new window


It is using INNER JOIN, but I think it is not correct, but if I change to LEFT JOIN, then it should not have correct data update.

What should I do ?

Thank you.
0
I have a SQL statement like this

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0
AND SiteCountryCode = 'USA')
or (SiteAddress != NULL
and SiteAddress != 'NULL')

Open in new window


SiteAddress is nvarchar(256)

Howcome this SQL brings back NULL in siteaddress?

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

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
Hi

This is how query looks like:
First query:

select FullName, SystemUserId from SystemUser
ORDER BY FullName

This query will show 100x systemuserID now to the next one:

update UserEntityUISettingsBase
set RecentlyViewedXml = null
where OwnerId = 'SystemuserID'

ok here comes the question, how do I do this easiest? Declare a var for 100 systemuserID? how do i do this so the 100x systemuserID updates easiest.

Thanks
0
Hello,
Can you please help,
I need to get the [Driver_In] Minimum Date Time/ [Driver_Out] Maximum Date Time between 2 tables (Per Driver) , then calculate the difference (Hours and Minutes),
This is What I have so far (But , it is wrong, I'm getting 2 lines per Driver

Select PickUpDriver AS [Driver], (Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) AS [Name],
CONVERT(date, OrderDate) AS [Order_Date],MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)) AS [Driver_In],Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME)) AS [Driver_Out],
DATEDIFF(second, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) / 3600.00 AS [Hours_Worked],
DATEDIFF(minute, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) AS [Minutes_Worked]
From ActiveOrders
where (((OrderDate > = DATEADD(d,0,DATEDIFF(d,0,GetDate())))  And (OrderDate <= DATEADD(s, +86399, DATEADD(d,0,DATEDIFF(d,0,GetDate())))))) AND Cast(datepart(yyyy,PickedUpTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(PickedUpTime)), 2) + '-' + Right('0'+cast(datepart(DD,PickedUpTime)as varchar(2)),2) = Cast(datepart(yyyy,DeliveredTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(DeliveredTime)), 2) + '-' + Right('0'+cast(datepart(DD,DeliveredTime)as varchar(2)),2)
And ((Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver)
0
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

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.