Microsoft SQL Server 2005

71K

Solutions

26

Articles & Videos

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

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
Free learning courses: Active Directory Deep Dive
LVL 1
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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
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
Best Practices: Disaster Recovery Testing
LVL 1
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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
I work for a law firm where a client case file is called a Matter.  Each Matter can have multiple 'Matter Originators', 'Responsible Attorneys' and 'Paralegals'.  Originally I only took the TOP 1 of each category and UPDATED Server2.DB2.  The below query is what I used.  

MERGE Server2.DB2.[dbo].HBM_Matter AS Target
	USING 
	(		
		SELECT DISTINCT mp.Matters, 
		(
			SELECT TOP 1 hbm.Empl_Uno--, mp.Matters
			FROM Server1.DB1.[dbo].MattersProfessionals mp1
			JOIN Server1.DB1.[dbo].Professionals p ON p.Professionals = mp1.Professionals
			JOIN Server2.DB2.[dbo].[HBM_PERSNL] hbm ON hbm.Initials = p.Initials
			WHERE mp1.AssignedType = 'Matter Originator' AND Matters = mp.Matters
			ORDER BY mp1.AssignedPercent DESC					
		)  AS MORG,		
		(		
			SELECT TOP 1 hbm.Empl_Uno--, mp.Matters
			FROM Server1.DB1.[dbo].MattersProfessionals mp1
			JOIN Server1.DB1.[dbo].Professionals p ON p.Professionals = mp1.Professionals
			JOIN Server2.DB2.[dbo].[HBM_PERSNL] hbm ON hbm.Initials = p.Initials
			WHERE mp1.AssignedType = 'Originating' AND Matters = mp.Matters
			ORDER BY mp1.AssignedPercent DESC							
		) AS ORIG,
		(
			SELECT TOP 1 hbm.Empl_Uno--, mp.Matters
			FROM Server1.DB1.[dbo].MattersProfessionals mp1
			JOIN Server1.DB1.[dbo].Professionals p ON p.Professionals = mp1.Professionals
			JOIN Server2.DB2.[dbo].[HBM_PERSNL] hbm ON hbm.Initials = p.Initials
			WHERE Matters = mp.Matters AND mp1.AssignedType = 'Responsible' 
			ORDER BY mp1.AssignedPercent DESC
		) AS RESP
		

Open in new window

0
hi,

right now we have a task to relocate SQL 2000 (long time ago, I can't remember how to do it...) from a Windows 2000 server to Windows 2003 server so that we can apply security patch on Windows 2003 for any new virus attack.

anyone still remember how to move system DBs and user DBs from one SQL 2000 to another SQL 2000 ?
0
Hi.  I wrote a stored procedure (SQL Server 2008 R2) that moves files between folders on our network.  I am using an Access frontend.  The form has a button that users click to call the stored procedure.  

The file move works for me but does not work for my users.  My login has system admin privileges.  I am a domain admin.  I have a Windows Test account that is not a domain admin and doesn't have any special privileges on the server anywhere, just like the users.    I am logged in as this test user on another computer so I can test. The error message is "User does not have permission to perform this action."

I looked at links on the web and tested many different ways.  However, each way I try works for me but not the users.  When I push the button in my test program, the logic should rename the test file on the server under c:\Temp even though the users do not have permissions to this folder through Active Directory.

Here's the link I used as a basis for my code:
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx

I am testing with a basic stored procedure to rename a file on the server.  I applied the logic from the link above.  It works for me but not for my users.  I also tried "EXECUTE AS" my login but that doesn't work either.

USE [ExperimentTracker]
GO
/****** Object:  StoredProcedure [dbo].[spTestWindows]   

Open in new window

0
I am looking into a migration of an environment which was recently aqcquired from a previous IT, havent had the best of handovers. The business has 2003 R2 server running SQl 2005 Express , the business has now got a new Windows Server 2012, I have installed SQL Server 2016 SP1 express edition.

There is one database on the 2005 which was built inhouse and has a IIS6 front end. I am trying to find the best way forward to get the database from 2005 onto the new 2016. I believe i need to backup and import the DB and also do the same for the IIS front end.

-Please assist in providing a migration plan, how to preform this, high level/low level
-Any issues in migrating from 2005 to 2016, should i opt for SQL 2014
0
Raise the IQ of Your IT Alerts
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Hi Experts,

I'm in middle of a process of securing data in our database.

Would like to move all employees sensitive data into a separate table and have the columns there set as encrypted columns.

Now I am basically looking for an introduction on how to successfully use that within my Access FE application.

Was trying to follow what it says on following link
https://blogs.msdn.microsoft.com/lcris/2005/06/09/simple-demo-for-how-to-encrypt-and-decrypt-a-table-column-in-sql-server-2005/
However when I ran the following view, the encrypted column was showing as null..
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

Open in new window


Thanks in advance.
0
Hi People,

I've got about 8x SQL Servers VM that is used 24x7 and very big, with some of them is around 12.5 TB in single VM (multiple large VMDK):

16x vCPU
128 GB vRAM
C:\ - OS - 300 GB
D:\ - Database .MDF - 6.5 TB
E:\ - Backup .BAK & TRN -23 TB
L:\ Logs 3.1 TB

So my question is, can I just backup the C: drive and the E: drive only so that I can back it up more frequently like every 6 hours (using Veeam Backup - VM snapshot, no SQL transaction logs commit)?

Does backing up the OS and the E: drive containing the SQL maintenance backup file dump is enough to restore it functionally?

I cannot backup the whole VM due to the below concerns and stumble block:
1. Disk space for backup repository
2. Since the SQL server is very busy, sometimes when the  Veeam ABckup failed after 55 hrs of the backup job, snapshot removal process kills it for 3 hours bringing down the entire business with it.
3. Backing up the whole VM is not fast enough with VM snapshot mode.

So any help would be greatly appreciated.

Thanks.
0
Experts

I'm dealing with the following issue:
I need to know how to use a VARIABLE as a linking-field in a SQL QUERY.

declare @variable1 varchar(30) = select top 1 fieldname1 from table_3

Note: Result of @variable1 is a string "Col1" and represents the name of a column in a table. This will be somewhat dynamic in that it can be different columns. (ie. Col1 or Col2 or Col5 etc..etc..)

I need to create a SQL script that will LINK on this @variable1

Like this:

select t1.col1, t1.col2, t2.col3, t2.col4
from table_1 t1
inner join table_2 t2 on t2.col1 = @variable1

The problem I'm having is that @variable1 is using the literal TEXT  "Col1" but I need it to actually be linking on the "DATA" within "Col1"...

Please advise on how to handle this issue?
0
I'm working on a project and we have a geometry column. I'm querying the database to get the data , send it to Mapbox and display it on the US map.

Now, the query is slow and someone at work says there's some GIS method of geometry data type that I can add to the query that simplifies the returned shape and it has some accuracy compression parameter that controls the amount of detail that is lost.

I've been Googling and we don't know what it is.

We're using SQL 2014. C# MVC VS2015.
0
Experts,

I have a table that looks like this:


Attribute_1_Type,Attribute_1_DBFieldName,Attribute_2_Type,Attribute_2_DBFieldName,Attribute_3_Type,Attribute_3_DBFieldName,Attribute_4_Type,Attribute_4_DBFieldName,Attribute_5_Type,Attribute_5_DBFieldName,Attribute_6_Type,Attribute_6_DBFieldName,Attribute_7_Type,Attribute_7_DBFieldName,Attribute_8_Type,Attribute_8_DBFieldName,Attribute_9_Type,Attribute_9_DBFieldName,Attribute_10_Type,Attribute_10_DBFieldName
HPBP,Column_2,CLIENT_NAME,Column_5,OUTBOUND_SUPPRESSION_FLAG,Column_10,COHORT,Column_23,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

I need to create a dynamic SQL script that will result in this:

SELLECT 'HPBP',Column_2,'CLIENT_NAME',Column_5,'OUTBOUND_SUPPRESSION_FLAG',Column_10,'COHORT',Column_23,'NULL',NULL,'NULL',NULL,'NULL',NULL,'NULL',NULL,'NULL',NULL,'NULL',NULL
FROM #MyTempTable
WHERE data_source_id = @datasourceid

So I need to turn a table of data into a SQL Script that can be ran in my stored procedure to produce data.

Can someone help me with the syntax, especially in regard to my example above. I need to single quotes around the specified fields as my example sql script shows.

Thanks
0
Hello,
I have to columns in a table
Date : Data type is Datetime
Time: Data type is Datetime.

need to sort it by Date and time in asc manner

Cheers
0

Microsoft SQL Server 2005

71K

Solutions

26

Articles & Videos

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.