[Last Call] Learn how to a build a cloud-first strategyRegister Now

x

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

Kindly check whether the arrangement of the SQL below with date format is Okay:

SELECT Qry10003CustomerLedgerunion.CustomerID, Qry10003CustomerLedgerunion.AccountID, Qry10003CustomerLedgerunion.Company, Qry10003CustomerLedgerunion.InvoiceID, Qry10003CustomerLedgerunion.CDate, Qry10003CustomerLedgerunion.Debit, Qry10003CustomerLedgerunion.Credit, ([Debit]-[Credit]) AS Balance, Qry10003CustomerLedgerunion.Reference
FROM Qry10003CustomerLedgerunion
WHERE (((Qry10003CustomerLedgerunion.CustomerID)=[Forms]![frmCityLedger]![CboCustomerID]) AND ((Qry10003CustomerLedgerunion.AccountID)=[Forms]![frmCityLedger]![CboAccountId]) AND ((Qry10003CustomerLedgerunion.CDate) Between Format(([Forms]![frmCityLedger]![txtDateA]),"yyyy\/mm\/dd") And Format(([Forms]![frmCityLedger]![txtEndB]),"yyyy\/mm\/dd")));

At the moment I have no issues the query seam to be working properly , but I just want a second opinion on this.

Regards

Chris
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

This script returns the subject error. Done this sort of thing before, been a while. Snow Blind

/*
Error Message
(16 row(s) affected)
Msg 512, Level 16, State 1, Line 59
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

(16 row(s) affected)
*/

 declare @Po as int
  set @Po = 99308

  declare @PoHistory table
      (
       [ID] [int] identity (1, 1)
      ,[Po] [int] null
      ,[Item#] [float] null
      ,[Qty] [float] null default (0.0)
      ,[RecQty] [float] null default (0.0)
      ,[Amount] [decimal] (10, 2) default (0)
      ,[AuthorizedAmount] [decimal] (10, 2) default (0)
      ,[PendingAmount] [decimal] (10, 2) default (0)
      ,[PaidAmount] [decimal] (10, 2) default (0)
      )
            
      insert into @PoHistory
            (
             [Po]
            ,[Item#]
            ,[Qty]
            ,[RecQty]
            ,[Amount]
            ,[AuthorizedAmount]
            
            )
            (
            select
                   [PO_ID]
                  ,[ITEM_]
                  ,[QTY]
                  ,[RECVDQTY]
                  ,[TOTCOST]
                  ,[AuthorizedAmount]
            from [TVENDPOD]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            )
            
      update @PoHistory set [PendingAmount] =
            (
            select
                  sum( [TOTCOST])
            from [ACCOUNTSPAYABLE_DETAIL]
            where [RECORD_] is not null
            and [PO_ID] = @Po
            and [CHECKNUMBER] = 0
            and [PAID_DATE] is null
            group by [PO_ID], [ITEM_]
            )                  
      from @PoHistory, [ACCOUNTSPAYABLE_DETAIL]
      where [ACCOUNTSPAYABLE_DETAIL].[PO_ID] = [Po]
      and …
0
Hi there,
I would like to create a query which looks at sales during a particularly large period of time, and the results are then passed into a temp table to view these results.

For example.
Over the past 60 days I want to check our sales where the actual customers phone number would have appeared in this group (introduce a sub query which will then hold all of those phone numbers for those same 60 days) - what is important here is that the phone number that appears in, for example, a sale on day 1 should be checked against the sub query for the same day 1 (not any other day). Does that make sense?



Select salesdate, sum(salesrevenue) from SalesTable where salesdate between '2017-01-01 and '2017-01-02'
And phonenumber in (select phonenumber from customertraffic table where customer traffic hitdate between '2017-01-01' and '2017-01-02')
Group by salesdate

However I want to be able to have this query run over a longer period of time not just one day, but it needs to be day to day,
so I'm thinking if I could loop the query to run
day+1 type thing, I'm just not sure how to go about it.
and then insert the results into a temp table so I can see all the dates and their results.

declare @datefrom  datetime, @dateto datetime
SEt @datefrom = '2017-01-01'
SET @dateto = @datefrom+1

--how Stop the loop when 60 days of data is returned. ?


Insert into #TempTable (salesdate, salesrevenue)
Select salesdate, sum(salesrevenue) from SalesTable where …
0
ServerA (source server) is running Windows server 2008 with SQL server 2005 SP4 which is hosting a 90GB database.  ServerB (destination server) is fresh, scratch built VM running Windows server 2012 R2 with SQL server 2008 R2.  I've researched various ways to migrate the database (backup/restore, log shipping, etc).  Which is the easiest method to migrate this 90GB database?   I have a 2 hour maintenance window to get this done.  This is my first time being tasked with migrating a SQL database, so I do not know what to expect nor do I know of any gotchas that may occur during the process.Any tips/advice would be greatly appreciated.
0
Hi Experts,

I have this:
CONVERT(varchar(8), DATEADD(MILLISECOND, SUM(TimeProcessing), 0), 8)/Count(Name) as TimeTook

Open in new window


But am getting error:
Conversion failed when converting the varchar value '03:18:20' to data type int

Please help and thanks
0
Experts -

Table: FiscalCalendarMain
Fields:
FiscalCalendarID - Autonumber in SQL
FiscalCalendarName - nvarchar(255) - User Entry comes from a web page
FiscalCalendarStartDate - smalldatetime - User Entry comes from a web page
FiscalCalendarEndDate - smalldatetime - User Entry comes from a web page
FiscalStartMonth - nvarchar(255) - User Entry comes from a web page.

User accesses a web page & posts data onto above table. Once they submit, below table should also be automatically updated via a stored procedure with the parameters from above table (FiscalStartDate, FiscalEndDate, FiscalStartMonth)
Ex:
FiscalStartDate = 11/24/2017
FiscalEndDate = 06/30/2020
FiscalStartMonth = 7


Table: FiscalCalendarDetails
Fields:
SNO - Autonumber in SQL
FiscalCalendarID - nvarchar(255) - Comes from above table
FiscalDate - Smalldatetime - Ex: 11/24/2017
FiscalDay - nvarchar(255) - Ex: 24
FiscalDayName - nvarchar(255) - Ex: Friday
FiscalMonth - nvarchar(255) - Ex: 11
FiscalMonthName - nvarchar(255) - Ex: November
IsWeekend - nvarchar(255) - Ex: 1 for weekend(from FiscalDayName for Saturday and Sunday) and 0 for Weekdays
IsHoliday - nvarchar(255) - Default value for this field is 0.
HolidayName - nvarchar(255) - Default value for this field is ""
FiscalWk - nvarchar(255) - Ex: WK01 - Week number will be decided from the FiscalStartMonth from above table
FiscalQtr - nvarchar(255) - Ex: QTR01 - Quarter number will be decided from the FiscalStartMonth from above …
0
Collation Error while joining the tables. can somebody help.
0
I need a function I can wrap a column in to scrub out invalid xml characters

It is a varchar(1000) column



Select dbo.scrubhtml([myCol]) from tblPersonData
0
hi experts
I have a server that is already making the backup and I want to save it somewhere else (Shared Path), it is understood that I should create a folder and give the permissions to (NT SERVICE \ MSSQLSERVER), but it does not take it, because that I must be on my own PC or server, in these cases as it is done, since it is understood that the backup of the database must not be on the same server.
0
What SQL account will give me access to create?

•      bulk updates
•      stats monitoring
•      insert
•      VIEW SERVER STATE
0
Vote for the Most Valuable Expert
LVL 7
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Last weekend we moved our SQL Server from one server (Windows 2003, SQL 2005) to a new server (Windows 2012, SQL 2012) and copied over all the users and roles to the new server. The Migration went rather smoothly and the users are able to use the database without problem however all the notifications that come from that server are no longer sent. The accounts are still there the Jobs are still there and running without problem but the notifications are not going out. I have checked the SQL Agent and emailing is enabled and I can send out emails to my email from Database Email yet no notifications are sent.

What could be the issue? No firewall is on and the Exchange server it not rejecting the emails eventhough they are being sent from DB Mail. I Don't know where else to look.
0
Good morning

how can I make a query Sql where I pass a range of dates and returns me a column with the day and another column with the day in string.


date.                        day
04/11/2017         Saturday
05/11/2017           Sunday


Thank you
0
Hello

Need assistance writing a query which only returns instances where the Job_Id is the same, but the Product_Id and Type are different.
*See attachment.
Customer	JoB_ID	Product_Id	Type
ABC Movers	33225	1775		RE
ABC Movers	33225	1776		Non_RE
XYZ Ltd	33001	1775		RE
XYZ Ltd	33001	1775		RE

Open in new window

Thanks for your tremendous assistance.
Jobs_EX.xlsx
0
hi experts

i have this error
The module being executed is not trusted. Either the owner of the database of the module needs to be granted authenticate permission, or the module needs to be digitally signed.

the patch was installed but the problem persists.
www.png
0
Hi Experts,

I have this query:

Declare
 @REPORTTYPE as varchar(50),
 @POLICY as varchar(2000)

DECLARE @DB_REPORTTYPE as varchar(50)
SET @DB_REPORTTYPE = @REPORTTYPE
DECLARE @DB_POLICY as varchar(2000)
SET @DB_POLICY = @POLICY--'1 Draft VA SQL Server 2012 Policy (2)'

SELECT
	'Please fix to only one' as NeedBothAdded,
	--BOWN.[Branch Region],
	--BOWN.[Branch Network],
	--BOWN.Branch, 
	(Cast(SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END ) as int)) as [Fail],
	(Cast(SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END ) as int)) as [Pass],
	Count(*) as [All], 
	(Cast( SUM( Case when ActionableResult = 1 THEN 1 ELSE 0 END)  as float) /Count(*)) as PercentFailed,
	(Cast( SUM( Case when ActionableResult = 0 THEN 1 ELSE 0 END)  as float) /Count(*)) as PercentCompliant,
	Case when ActionableResult = 1 THEN 'No' ELSE 'Yes' END  as Success,
	fac.ComplianceAssetKey,
	fac.OwnershipGroupKey,
	fac.ScanPolicyGroupKey 
	--fac.AssetHostName
	--fac.[CheckName]

FROM [dw].[FactComplianceAssetCheck] as fac 
  inner join dw.brgComplianceScanPolicy as BCP on BCP.ScanPolicyGroupKey = fac.ScanPolicyGroupKey                                   
  inner join [dw].[brgOwnership] as BOWN on BOWN.[OwnershipGroupKey] = fac.OwnershipGroupKey
  inner Join dw.DimComplianceAsset CA On CA.ComplianceAssetKey = Fac.ComplianceAssetKey 
  WHERE fac.IsRowCurrent = 1 
  and Left(BCP.PolicyName, 72) = @DB_POLICY -- Note: Policy name had to be truncated by 72 for the dropdown otherwise way too 

Open in new window

0
Hello guys,

I am trying to do this, but MSSQL is complaining about my syntax, I need your help.

create function dbo.LeFormula(@codpro varchar(24), 
                              @codespec varchar(5),
							  @formula varchar(500), 
							  @CodcampoFormula varchar(100)) 
returns varchar(100)
as
begin 
declare @valor as varchar(100);
  
    select @valor= Exec sp_CALCULO_FORMULA_ESPEC @codpro, @codespec, '', '', '', '', '', @formula, @CodcampoFormula 

   return @valor
End

Open in new window


Thanks
Alex
0
Hi,

  I need to grant the 'SELECT' privileges to a user for a View that belongs to another database, but i do not want to grant 'SELECT' to any table of that database to the user, or even any of the tables that belongs to that view.    What happens is that I only want that user to be able to 'SELECT' to that view, for specific fields that belongs to that view.

  For example.., if the views is built using TABLE A  that has 8 fields, but in the view the display is only viewing  3 fields of that table, the user must be able to SELECT  the view for only to see the fields that belong to the view, but can not see the others fields of the table, or any other table of the database.
 
  Can i do it in MS SQL Server ?

Regards,
0
I have a query, when I try to run it commenting a logic defined below, it runs fine.

But I need to include this logic and run the query, the query is taking hell lot of time. 2 hours for 1 lakh record.

PLEASE HELP ME CHAMPIONS.


SELECT
--CASE WHEN (select distinct  1
--from TBL c            
--where EXISTS (SELECT 1 FROM TBL1 leave            
--               WHERE convert(date,c1.DT) = leave.DT
--                 ))
--IS NOT NULL THEN 1 ELSE 0 END AS LEAVE

FROM (select *,
case when test= 'A' then DATEADD(hour,8,Date)  
     when test=  'B' then DATEADD(hour,2,Date)  
     when  test= 'C' then DATEADD(hour,-4,Date)
     else Date end as DT

 from STAGING c
 ) c1
0
I'm using SSRS 2016 and facing rendering issue in IE 11 (one e.g. Drilldown button is missing when we browse reports via IE 11 ). It works fine with Chrome, but our end users preferred browser for  SSRS reports is IE 11. Is there anything can be done at server level to fix the rendering issue.

Your help is highly appreciated.
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
             DROP TABLE #Test

DECLARE @TextSearch NVARCHAR(1000) = 'India,,'
Select Value into #test  FROM dbo.udf_Split(@TextSearch, ',')

BEGIN
IF @TextSearch IS NOT NULL
    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C     
   
    )A
    INNER JOIN #test t on A.Name  like '%' + t.value + '%' and a.name <> ''

ELSE 

    Select CountryRegionCode,Name  from (
    select  DISTINCT CountryRegionCode,Name  from [Person].[CountryRegion] C 
   
    )A

END

Open in new window

0
I have SQL Server 2005 express. Somehow, the program itself was deleted and I need to reinstall it. The .mdf, .ldf files still exist. For the moment, I have renamed the Data folder to Data_Save to avoid overwriting.

How can I go about reconnecting these file after re-installing Express? Should I rename the Data folder back to its correct name before re-installing?
0
Hi Experts,

I have this:
=(Sum(Fields!Resolved_Vulnerabilities_in_30.Value) / Sum(Fields!Total_Vulnerabilities.Value)) * 100

Open in new window


as expression.

But I get this:
2/368,421 = 0.00

5.428

Please help and thanks
0
Hi Experts,

I have this part of a calculation:
Cast(ISNULL(Sum(ResolvedVul60)/NULLIF(Sum(TotalVul), 0) * 100, 0) as Decimal(18,2)) as [Percent 60]

Open in new window


but the outcome Example:
TotalVul = 64
ResolvedVul60 = 5

I get:
0.00


Please help and thanks...
0
For a subject of leasing will be made renovation of servers, among them the commercial database.

I had mapped the following:
1) Installation of new operating system.
2) Installation of SQL Server software, here would configure the collation to use for my BD, the memory that I will assign to SQL (I think it will be a 64GB of RAM server).
3) Migrate my Logins to the new engine via script.
4) Restore my FULL backup from my old server database.
5) Check the users and their BDs by default, check orphaned users.
6) Migrate DB Link's from my old database.
7) Set up my replica since this server is a subscriber of another server that has the accounting data that would become my publisher.
8) Script the job creation of the old server.
9) Recreate indexes, update statistics, pagination.


With this, keeping the same IP and leaving offline the old server should work everything without problems.
The work will be done at dawn, I have a window of 24 hours.

Anything else you should consider to do this base migration to the new server?

Maybe a checklist for a successful migration.
0
Hi Team,
   I am not able to install SQL server 2008 R2 in windows 8 machines.If I install the setup file after few seconds automatically closed.Not running What should i do?I was tried 32 bit & 64 bit setup file for installation.Both are not able to install
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.