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 experts

the column store [Fe creac  ]
Column 0	Usuarios    	Grupo	Tipo          	Creado por  	Fe creac  	Válido de 	Fin valid 	Entr sist 1  	Entr sist 2	Clave acc 1	Clave acc 2	Bloqueo1	Bloqueo2                                       	EntrSisInc	Column 15
	A_MATERIALES	     	A Diálogo     	ATI         	14.11.2016	          	          	20.02.2018  	07:18:10  	          	15.11.2017	       	                                              	          	
	AABARCA     	     	A Diálogo     	SMIADMIN    	31.01.2012	          	          	17.02.2018  	10:12:30  	          	18.12.2017	       	                                              	          	
	AAGUILAR    	     	A Diálogo     	SMIADMIN    	28.11.2011	          	          	19.02.2018  	11:01:50  	          	15.12.2017	       	                                              	          	
	AAROCA      	     	A Diálogo     	SMIADMIN    	13.12.2013	          	          	20.02.2018  	08:12:10  	          	06.02.2018	       	                                              	          	
	ABALLADARES 	     	A Diálogo     	ATI         	17.07.2017	          	          	04.08.2017  	18:32:07  	          	18.07.2017	       	Responsable                                   	          	

Open in new window


this query has errors
SELECT*
  FROM [SMI].[dbo].[rsusr200_20022018_1335]
  where CAST([Fe creac  ] AS datetime)   >=  CAST('01.10.2017' AS datetime)

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
0
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

hi experts

i  am reading about tuning
what's the mean
Design the tables considering the implicit conversions, which should be avoided.

can you give me T-SQL code
0
I am trying to do a join between different tables using acct_id. Table 1 is the main linking table and has this field formatted as varchar(10). All other tables has it as has it as Decimal(10).
Table 1 also has blanks (not nulls) for acct_id. When I tried to use CAST I just get errors. I think it's coming from those blank fields but don't know how to fix it.
I'm using SQL going through a linked server.
Ex:
Select * from openquery(CMFR1,'
Select
mcr.ln_no
,mcr.tran_num
,mcr.acct_id
,prod_dt
from MSP_CR mcr
left join on dly_prod prod on mcr.acct_id = prod.acct_id
')
Then left joins on 4 other tables using the same mcr.acct_id =
0
Count (*) gives an error;

 is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT IET.[ICol1]
      ,IET.[Col2]
    
         , ''
         , NULL
         , NULL
         ,  COUNT (*)

FROM [table1] IET  

Open in new window

0
We have SQL Server 2005 running on Windows Server 2003. I am looking to upgrade both the OS to Server 2012 r2 and Sql to SQL 2014 Ent, is there any specific order this should be done in and if so what is that order? SQL 2014 Upgrade Advisor
0
Experts,

Which is the most optimized way to update a table using a STORED PROCEDURE?


Like this?:
----------
update a
set col1 = b.col1
from table1 a
inner join --SUBQUERY TABLE
      (select id, desc
            from table3) b on a.id = b.id




OR like this:
-------------
create table #Temp1 (id int, desc varchar(25))
insert into #Temp1
(select id, desc
      from table3)

update a
set col1 = b.col1
from table1 a
inner join #Temp1 b on a.id = b.id
0
Hello,
Can you please help,
I have a stored procedure that fires some emails to my clients.
Currently it runs twice a month.
if DATEPART(day,getdate()) in (6,21)  
Begin xxxxxxxxxxx

I need to change the date to make it run every other Saturday. (Starting  January 6th, 2018)
Jan 6th, Jan 20th, Feb 3rd, and so on.

Any help is greatly appreciated.
SQL2008

W
0
The following code gets me the top 100 tours and reservations

How can I make that into one select so that I get 200 records... 100 or each "group"

SELECT   TOP 100 *
FROM     ewAppointments
WHERE    AppointmentTitle LIKE  'Reservation%'
ORDER BY AppointmentID DESC

SELECT   TOP 100 *
FROM     ewAppointments
WHERE    AppointmentTitle LIKE  'Tour%'
ORDER BY AppointmentID DESC;

Open in new window

0
hi,

how mirroring and alwayson are diferent. What are various types of mirroring. what are advantages of each.

what i rpo and rpt and ms hcl means
please advise
0
hi,

how sql server maintenance plan different from setting sql server agent setting up cleanup at a particular time. Please advise
0
[Webinar] Kill tickets & tabs using PowerShell
LVL 12
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

I have a very old database  on an very old server

The database has a table that has gotten bloated and is eating up drive space.

When I try and delete rows from the table I get a tempdb out of space

I cannot add drive space.  

What are my options?
0
Hello,
I have this existing query
UPDATE table1
SET  DrImage = (SELECT *
                FROM   OPENROWSET(BULK 'C:\WXG.png',
                       SINGLE_BLOB) AS x)
WHERE id = @ID

Open in new window


What is needed is :

UPDATE table1
SET  DrImage = (SELECT *
                FROM   OPENROWSET(BULK @Drimg,
                       SINGLE_BLOB) AS x)
WHERE id = @ID

Open in new window


I need the correct syntax to do so.

Thanks
0
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
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
Learn to develop an Android App
LVL 12
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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

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.