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

When I execute a stored procedure
Is there any way to log or track what table triggers execute?
0
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

I have a column that I need to always be 4 characters

It may at timesbeb 2 or 3

In thise cases I need to pad out to the left some zeroes.
0
Hello,
Can you please help,
I'm using below code, but it  returns multiple rows.
I need to combine the NoteText, seperated by __

Select Distinct FO.Orderno AS [Order #],FO.Orderdate AS [Order Date], NoteText AS [Reason]
From FinalizedOrders FO
INNER JOIN FinalizedOrderNotes ON FO.OrderNo=FinalizedOrderNotes.OrderNo
Where NoteText Like '%QA1%'
AND FO.orderdate >= ................. and ............... and ..................

Example Results:
Order #                       Order Date                                  Reason
7517502                       2017-07-10 08:31:55.000      Q1-Test1
7517502                       2017-07-10 08:31:55.000      Q1-Test2
7517502                       2017-07-10 08:31:55.000      Q1-Test3

Would like to have it
7517502                       2017-07-10 08:31:55.000      Q1-Test1__Q1-Test2__Q1-Test3

Your help is appreciated.
0
Hi there,
  I am in process of configuring logon trigger.
My need for this trigger is to restrict few SQL logins to only login from particular IP address:

I  got the below code from google, but when I setup the trigger, first time the user can able to login but next time when we disconnect and try again then the trigger is not letting to login the sql server even though we tried from the same machine(same IP address)

CREATE TRIGGER [TR_check_ip_address]
ON ALL SERVER
FOR LOGON
AS
BEGIN

    DECLARE @ip_addr varchar(48)

    SELECT @ip_addr = client_net_address
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    IF ORIGINAL_LOGIN() = 'Test' AND @ip_addr <> 'xxx.xx.xxx.xx'
        ROLLBACK;

END

thanks
Deepak
0
I have a table that stors table counts with a running total at a particular time
SELECT tbl, tme, cnt  FROM #RunningCount ORDER BY tbl, cnt
I need to add logic that partitions by tbl value
And gets the difference between the most recent row and the provious row cnt for THAT tbl
0
Can anyone give me step-by-step instructions on how to just get this task to successfully execute my stored procedure.  The error messages are getting worse
  • "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
  • "The query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
  • "The EXEC SQL construct or statement is not supported."

At this point, I'd just like to get the stored procedure working.  All it's doing is having a parameter string value passed into it from a user defined variable in the package and then assigning a value to the SP output parameter which then gets assigned to a different package user variable.

My environment:  Windows 7 Professional, SQL Server/Visual Studio 2005

Any suggestions?
0
Hi Experts,


Currently I have created a temp table.  But the problem is when there are large amounts of data.  After the temp table is created (yes very large) then there is a where clause on that temp table to reduce the amount of records.
Declare
@DB_@STATUSTYPE varchar(255) = 'No Status'


Select *
From #TempTable
Where ([StatusType] = @DB_@STATUSTYPE)


This is in the select statement when inserting into the temp table:

Select
CASE WHEN cce.CVA_StatusID = 1 THEN cvs.StatusType
         WHEN cce.CVA_StatusID = 4 THEN cvs.StatusType
         WHEN vra.Actionable = 0 THEN 'Not Actionable'
         WHEN cce.CVA_StatusID IS NOT NULL THEN cvs.StatusType ELSE 'No Status' END AS [StatusType],

From TableName as cce

so this can be done more faster I would like to include:

Where
([StatusType] = @DB_@STATUSTYPE) instead of on the temp table

But because this is a case statement it does not work this way.

What do I have to do in the Where statement to get this to work.

Please help and thanks
0
Hi

In what scenario should I use this command?
0
I need to change the password of a user at SQL Server level (main login of systems).

But my doubt is that other things I should check before making this change, that other factors can be affected by doing this since this user used it almost for everything within this DB.

Obvious that at the application level will make the respective changes with the new password (it is a production pass-through server, nobody has access to this), but my doubt comes from things in the database itself, for example, as the Replicas that I have to other servers, sending mails, or where else should I look where maybe that password is left and I have to update it for the new one perhaps manually.

Here are several servers, I have to check all and see which are connected to my main server, since changing the password will cause several (load processes, dblinks, etc) to fall.

What is the correct way to make this change to decrease the margin of error
0
I am having a problem with my production database and need to know how to fix this issue. This database is currently in use. The error I am getting when I run DBCC CHECKDB with NO_INFOMSGS is below:

Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

I have ran DBCC CHECKDB with NO_INFOMSGS on the master, Tempdb, msdb, model and all came back fine but when I run it on my production database for our ERP system I get the above error. How can I fix this?
0
Creating Instructional Tutorials  
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

How can I estimate the local area network utilisation involved in remotely executing a simple SQL Server Stored Procedure ?

I have a windows application that uses ADO to execute a stored procedure on a Microsoft SQL Server 2005 database server.
The stored procedure is simple and returns a single result in an  integer output parameter.

I am executing this procedure every 10 seconds and have been asked to estimate the "network utilisation".  There are no input parameters to the stored procedure. I will be answering the question as "virtually no network utilisation" but I anticipate being asked how to justify this statement.
0
Hello guys

I am having problem to do this, maybe the way is not correct and I don't have another idea how to achieve the task

take a look at the code:
with tab1 
as
( select numped, seqped, seq from arifat 
       where arifat.numped = 19315 
)
, tab2 as
(
  
  select * from dbo.Calculo_Tributos_Aux('FAT', 'P', '', tab1.numped, tab1.seqped, tab1.seq)  
)

select * from tab2

Open in new window



is it possible something like this?

I got this error:
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'tab1'.

thanks
0
this code not show error

select PERIODO,
            SUM(CONVERT(float, DEBE)) AS DEBE_MENSUAL,
            SUM(CONVERT(float, HABER)) AS HABER_MENSUAL
FROM [dbo].[Mayo]
GROUP BY PERIODO

PERIODO      DEBE_MENSUAL      HABER_MENSUAL
20150500      1740846979.85011      1740849103.85011

this code show error
select PERIODO,
            SUM(CONVERT(decimal(14,3), DEBE)) AS DEBE_MENSUAL,
            SUM(CONVERT(decimal(14,3), HABER)) AS HABER_MENSUAL
FROM [dbo].[Mayo]
GROUP BY PERIODO

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Float is better than decimal?
0
Hi experts
In May I had installed the sql server express with the ssdt. SSDT showed error, see attached file

In June a new installation was made and now I have the sql server 2012 developer
Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
Sep 23 2016 15:49:43
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack

However the error is the same as it showed in May and I can not create packages in the SSIS
www.png
0
Hi; How can i solve repeated value columns issue solve?  I have a query below, it is producing below table. But Batch and  ExpiryDate always repeated . they are the same in all rows. How can i rearrange below query?

SELECT k.*,k1.Batch FROM 
                            (
	                            SELECT 
	                             CONVERT(DATETIME, s.[ExpiryDate]) ExpiryDate
	                            ,s.[CustomerFullName]    
	                            ,s.[CustomerID]
	                            ,s.[MaterialName]
	                            ,s.[MaterialNumber]
	                            ,s.DeliveryDate
	                            ,s.SapContractNumber
	                            ,s.SapDeliveryNumber
	                            ,DATEDIFF(DAY, GETDATE(),s.ExpiryDate) DateDiff,
	                            s.PartType
	                            FROM [Sade].[dbo].[StokView] s 
                            WHERE  ( s.[ExpiryDate] BETWEEN @date1 AND @date2 )   @customer
                            )k
                            OUTER APPLY 
                            ( 
	                            SELECT di.Batch FROM dbo.DeliveryItems di WHERE di.SapContractNumber= k.SapContractNumber 
	                            AND PartType <> 'ReAgent'
                            )k1

Open in new window

image.png
0
This is SQL 2014

I have this sample data

create table #temp
(
   rowId int identity,
   cId int,
   year int,
   rank int,
   cName varchar(500)
)
 
 
 
insert into #temp
select 1, 1712, 2015,3,'test1'
 
insert into #temp
select 2, 1929, 2015,2302,'test2'
 
insert into #temp
select 3, 1712, 2014,1,'test1'
 
insert into #temp
select 3, 1929, 2014,2024,'test2'

Open in new window


I get 4 row. This is how I want it to look like. (I wanted to loop thru the #temp table, compare the "cid" and then maybe concat and dump the result into another table but that can't be the correct way of doing this)

I need the data in "Rank" column to be from earliest year (2014) to latest year (2015)

CId                           Year                                        Rank                              Cname

1712                    2014,2015               1,3                                  test1

1929                     2014, 2015                           2024,2302                              test2
0
hi experts

I have a table with 100,000 records, as I should modify it to support temporary system tables

this create code
CREATE TABLE dbo.Manager
( ManagerId int NOT NULL PRIMARY KEY,
  ManagerName nvarchar(50) NOT NULL,
  ManagerPassword varbinary(200) NOT NULL,
  ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
  ChangedBy sysname NOT NULL CONSTRAINT DF_Employee_ChangedBy DEFAULT  (SUSER_SNAME()),
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ManagerHistory));
GO

But my table already exists and has data, so my question is: how do I modify the table
0
We have an old SQL 2005 server that is a VM machine and has been working fine but on Monday the backups started failing due to medial errors.  I looked at the permissions on the folder that the backups are stored and the local administrative account has full permissions but the backups are failing. Below is the error we are receiving and I would like to know how to troubleshoot and fix this issue. The database seems to be working fine so I don't think it is a corruption issue but can't be sure.

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.5069 Report was generated on "SQLSERVER3".
Maintenance Plan: Daily Full DB Backup Plan
Duration: 01:11:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Full) (SQLSERVER3)
Backup Database on Target server connection
Databases: CommerceCenter,master,msdb
Type: Full
Append existing
Task start: 2017-06-06T18:30:04.
Task end: 2017-06-06T19:39:16.
Failed:(-1073548784) Executing the query "declare @backupSetId as int select @backupSetId = position from msdb..backupset where database_name=N'CommerceCenter' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'CommerceCenter' ) if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''CommerceCenter'' not found.', 16, 1) end RESTORE VERIFYONLY FROM  DISK = N'M:\\MDF Files\\CommerceCenter\\CommerceCenter_backup_201706061830.bak' WITH  FILE = @backupSetId,  …
0
CREATE DATABASE RLSDemo
go
USE RLSDemo
GO
CREATE USER Employee1 WITHOUT LOGIN;
GO
CREATE USER Employee2 WITHOUT LOGIN;
GO

As you may be able to create database users without login in a DB that is not contained
0
Get 15 Days FREE Full-Featured Trial
LVL 1
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

hi experts
i do not understand
Temporal tables limitations:
User tracking requires adding a column to the table to hold SUSER_SNAME

 https://msdn.microsoft.com/es-es/library/dn935015.aspx

can you give me example in T-SQL
0
Hi;

Below query is extremely too slow and getting alotof rows is not related my customer. I guess something is wrong. How can I make it faster?
 SELECT
                                        DISTINCT
                                         s.Id
                                        ,s.[Batch]
                                        ,s.[BillTo]
                                        ,CONVERT(datetime, s.[ExpiryDate]) ExpiryDate
                                        ,s.[MaterialNumber]
                                        ,CONVERT(INT, s.[Quantity]) Quantity
                                        ,s.[PartType]
                                        ,s.[TestAmn]
                                        ,s.[SapDeliveryNumber]
                                        ,d.[SapContractNumber]
                                         ,sc.MaterialName
                                        ,s.Stock Total
                                        ,(SELECT CustomerFullName FROM Customers c WHERE c.CustomerID = @customerId) CustomerFullName
                                        FROM DeliveryItems d
                                        left JOIN Stok s on  s.SapContractNo= d.SapContractNumber
                                        left JOIN dbo.StockCards sc ON s.MaterialNumber = sc.MaterialNumber
                                         where d.SapContractNumber=@contractNumber and  s.BillTo=@customerId  and s.Stock>0 ORDER BY s.SapDeliveryNumber

Open in new window

0
i have this query

SELECT col_1
            ,SUM(CAST([COL_13] AS decimal(28,2))) as DEBE
            ,SUM(CAST([COL_14] AS decimal(28,2))) as HABER
FROM [dbo].[CuentaCorriente_2015]
GROUP BY col_1

i test from
SUM(CAST([COL_13] AS decimal(10,2))
.
.
.
.
SUM(CAST([COL_13] AS decimal(28,2))

but always error is
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
cuenta_corrient.txt
0
As I can determine the first parameter of the decimal data type, sometimes I had to put 10 and sometimes I put the value of 11, I did a lot of tests. There is some function that allows me to determine the value of the first parameter x of the decimal (x, y)

select PERIODO, SUM(CAST(DEBE AS decimal(10,2))) AS DEBE_MENSUAL,
            SUM(CAST(HABER AS decimal(10,2))) AS HABER_MENSUAL
FROM [dbo].[Octubre]
WHERE ISNUMERIC(DEBE)=1 AND ISNUMERIC(HABER) = 1
GROUP BY PERIODO


UNION ALL
select PERIODO, SUM(CAST(DEBE AS decimal(11,2))) AS DEBE_MENSUAL
            , SUM(CAST(HABER AS decimal(11,2))) AS HABER_MENSUAL
FROM [dbo].[Diciembre]
WHERE ISNUMERIC(DEBE)=1 AND ISNUMERIC(HABER) = 1
GROUP BY PERIODO
0
This sql statement works

SQL = "SELECT top 1000000 convert(varchar, TimeStamp, 101) + right(convert(varchar, TimeStamp, 120),9), LogicName, PointValue FROM Trends"

Open in new window


But if I add "Order By" I get a query timeout error within 15 seconds

SQL = "SELECT top 1000000 convert(varchar, TimeStamp, 101) + right(convert(varchar, TimeStamp, 120),9), LogicName, PointValue FROM Trends order by LogicName ASC"

Open in new window

0
this query
select PERIODO, SUM(CAST(DEBE AS decimal(10,2))) AS DEBE_MENSUAL,
            SUM(CAST(HABER AS decimal(10,2))) AS HABER_MENSUAL
FROM [dbo].[Mayo]
GROUP BY PERIODO

has errror
Error converting data type varchar to numeric.
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.