When I execute the following sql statement in SQL 2000 DTS or query analyzer, I received the following error message. I used [ ] because my field names have spaces in between.
The same sql statement ran perfect in SQL 7. What could be wrong? Please advise. Thanks a million.
Update SAPvsSQL set [Sales Organisation] = sales_org,
[Value Out By] = val_diff, [Qty Out By] = qty_diff
Error message:
Server: Msg 16882, Level 11, State 1, Procedure sp_runwebtask, Line ....
SQL Web Assistant: Web task not found. Verify the name of the name for possible errors.
I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions
I need to write a 'select' statement to fetch data from different tables, which are located on different servers. Can any one help in writing this 'select' statement with out moving the tables on to same server.
Hy all. My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign) So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL. Obviously I'd like to do this: SELECT * FROM EXEC sp_fkeys @table_name = 'xy' of course it is not working this way, but I'd like something like this.
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I need to return the number of min from a table I am using the following query. But it gives me an error "Msg 241, Level 16, State 1, Line 1Syntax error converting date time from character string". can someone please help.SELECT DateDiff(Mi, CAST((SCHDATE + ' ' + SUBSTRING(SCHTIME, 1,2) + ':' + SUBSTRING(SCHTIME, 3,4)) AS DateTime), CAST((ACTDATE + ' ' + SUBSTRING(ACTIME, 1,2) + ':' + SUBSTRING(ACTIME, 3,4)) AS DateTime)) AS StopMinutes, BACPY, BARTRM, BAORD, BSAPOR, BABLN, BSASSQ, BSACNO, CSTRDATA, BSASCY, BSASST, TTLREV, SHAALP, SCHDATE, SCHTIME, ACTDATE, ACTIME, OQTCOD, BAADES, PCS, WGT, Tractor, Driver FROM dbo.JCI_Delivery_Report
Hi, i have 3 fields: start_inspect_datetime, end_inspect_datetime, Diag_Hrs. so i want to get the difference of start and end datetime=Diag_Hrs. here i am using the below stored proc. but i am getting only the hours or minutes or seconds. so how to get the hours(if diff>59 mins),minutes(if diff>59 sec),seconds. for Ex: here diff=185 sec. then Diag_hrs should be 3 hours,0 mins, 5 secs. so how we'll get this. pl help me out asap. Thanx reddy
select Asset_Diag_Hrs= DATEDIFF(hh,start_inspect_datetime,end_inspect_dat etime) from asset_diag_trans_table --where Gpc_no=@GPC_no
--select Asset_Diag_Hrs=(datediff(mm,start_inspect_datetime ,end_inspect_datetime)) from asset_diag_trans_table -- where Gpc_no=@GPC_no
select Asset_Diag_Seconds=(datediff(ss,start_inspect_date time,end_inspect_datetime) ) from asset_diag_trans_table where Gpc_no=@GPC_no
Can i restore a diff. backup alone without a complete backup?
(what i am trying is this.....there are two different servers at two diff. places....i need to have both servers in sync. at all times.modifications will take place in one server and the modifications have to be reflected in the other server.i could not go for replication as the servers cannot be connected.sending complete backups daily will be a overhead .hence planning to take complete backup once and send diff. backups alone on subsequent days to the other server. how can i achieve this?)
Dear all I am new to the MS SQL, my problem is as follows. I am having a online database on sql 2k. every 15 days we have to give payout from our system, so we have a offline server in our office, we take the complete backup of that day & restore the same on the offline server, & start the payout process. The problem is that the full backup is a big file & take a lot of time for downloading from online server. is it possible that we take on diff. backup of that day & will restore the same on the offline server so the file will take less time to download. but my offline backup is 15 days old, will that update all the records or not?
I have 2 databases: 1 from production and 1 from development. None of the developers kept a changelog so i need to know what has changed (or what is different between the 2).
In playing with differential backups, i have taken a full last night, 17GB, took my first diff today 16.5GB, took another diff right after, it was still 16.5GB, took another diff right after 16.5 GB. This database has 0 activity during the day, the import takes place @ night, and select's are done to it during the day. Any help would be greatly appreciated
Hey guys, m new to SQL i mean really new so i appreciate all the help i can get on this as soon as possible. Is it possible to extract 3 foreign keys from a single table into one record/row on a different problem ? Thnx guys hope to hear soon
I wanted to know the difference between SQL Server 2005 ( RTM) Version and the regular SQL Server 2005 SP2. And does the RTM version have any service packs.
Are there any tools/utilties that can diff 2 sql server databases. I know the DB Pro Edition of VSTS can do it, but its hard to justify a jump from my $800 copy of VS2005 Pro to a $5k-6k version of VS just to get database diff. The tablediff looks promising but I need it to do SPROCS and SFUNCS too. Any helps or recommendations would be appreciated.
Hi All, What is the difference betwen real and float datatype of SQL server. Please one exapmle for each. The real datatype is like a float except that real can only store numbers.What does it mean: float can store even characters. Thanks Abdul
Hi, I need to know the difference between Numeric and Decimal datatypes. In which we case we use numeric and in which case we use decimal? I searched in some sql related websites and came to know that both are same. Then what is the need of these two datatypes? Either Numeric or Decimal is alone enough? Please explain me in detail .... Awaiting your replies...
I have a handful of jobs that I need to troubleshoot, but this one will hopefully give me enough insight to do the rest myself. I need to figure out why a Maintenance Plan that performs a Diff Backup on 4 databases, and then a transaction log back up on the 4 databases is failing. I just get the following error message, and I cannot tell what failed.
Executed as user: <SERVER_NAME>SYSTEM. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:20:05 AM Progress: 2008-01-03 11:20:05.95 Source: {SOME_STUFF_WAS_HERE} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2008-01-03 11:20:06.35 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.35 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.37 Source: Back Up Database (Differential) Executing query "EXECUTE master.dbo.xp_create_subdir N'D:Program F".: 100% complete End Progress Progress: 2008-01-03 11:20:06.37 Source: Back Up Database (Differential) ... The package execution fa... The step failed.
How can I find out how to fix this problem? And please let me know if I am doing something wrong. (For instance, perhaps those two tasks shouldn't be together.)
I have a table t1 with two columns : c11 varchar(32) , c22 varchar(32)The data in the table is :'11', 'aa01'and on upto'11', 'aa50' : total 50 entries'22', 'b01''22', b'02''22', b'03''33', 'c01' to '33', 'c40' : total 40 entries'44', 'b02''44', 'd01''44', 'd01''44', 'd01'How can write a query which will bunch together values of c11with rows 5, and then bunch together values of c11 withrows < 6, and add them up.My output should be :'11' 50'33' 40'others' 7 (3 rows for '22' and 4 for '44' are bunchedtogetheras the # of rows < 6, and added. 3+4 = 7)
I need a to build a result as shown above in matrix, many countries will be in resultset and each country performance is evaluated based on previous year. (90-80)/80=12.50%. the problem is this year and segment values are calculated based on aggregate functions and is derived by means of specific formaule. for country1 in year 2006 for segment1 the value 80,90 is arrived by using sum(leadtime*Ordervalue)/sum(Ordervalue).
So i cannot store these current and previous year values for calculating the % bymeans of custom code, runningvalue cannot be used as the formulae already has an aggregate, is there any direct way to locate the cell references just like excel R1C1 with matrix? if so then i can refer the cells for calculating my %. please help.
My goal is to add a diff into a query that grabs data from 2 different tables.
The code: SELECT MIN(TableName) as TableName, ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8 ,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16, COL17, COL18, COL19, COL20, COL21 FROM ( SELECT 'Table A' as TableName, SessionID as ID1, StartDateCode as COL1, StartTimeCode as COL2, EndDateCode as COL3, EndTimeCode as COL4, HandledByCode as COL5, DispositionCode as COL6, DNISCode as COL7, CallServiceQueueCode as COL8, ApplicationCode as COL9, IVREndPointCode as COL10, BankCode as COL11, TotalQueueTimeSS as COL12, TotalAgentTalkTimeSS as COL13, TotalAgentHoldTimeSS as COL14, TotalAgentHandleTimeSS as COL15, --TotalIVRTimeSS as COL16, AfterHoursFlag as COL17, SourceSystemID as COL18, anubisTransferExtNumber as COL19, anubisEndPoint as COL20, AccountNumber as COL21
from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact where startdatecode between 2738 and 2769
UNION all
SELECT 'Table B' as TableName, SessionID as ID1, StartDateCode as COL1, StartTimeCode as COL2, EndDateCode as COL3, EndTimeCode as COL4, HandledByCode as COL5, DispositionCode as COL6, DNISCode as COL7, CallServiceQueueCode as COL8, ApplicationCode as COL9, IVREndPointCode as COL10, BankCode as COL11, TotalQueueTimeSS as COL12, TotalAgentTalkTimeSS as COL13, TotalAgentHoldTimeSS as COL14, TotalAgentHandleTimeSS as COL15, --TotalIVRTimeSS as COL16, AfterHoursFlag as COL17, SourceSystemID as COL18, anubisTransferExtNumber as COL19, anubisEndPoint as COL20, AccountNumber as COL21
from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact where startdatecode between 2738 and 2769
) tmp
GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8 ,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16, COL17, COL18, COL19, COL20, COL21 HAVING COUNT(*) = 1 ORDER BY 2,1
---------
Is it possible to add a command into the query to output diff/compare scenario?
I have taken backup of a database, of which Data (MDF) & Log (LDF) files are located on "E:...." directory.
Now, I am using the same backup file to restore on another PC which is having no partitions at all. I mean now I have to restore the database using same backup file, of which Data & Log files should be located on "C:...." directory only, as it has no partitions.
This restore process gives me error :
Restore failed for Server 'HSVMMICROFIT'. Details : System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicroFitHealthStar v6.0 Server EditionDataMICROFIT_HealthStar_Data.mdf'.
What is to be done, in order to restore the database from the same back up file successfully, regardless whether the original physical location of the MDF & LDF file, is existing on current PC ??????????????
the following query always finishes within 1 minute
SELECT * FROM t1 INNER JOIN t2 ON t1.key= t2.key where t1.StartTime >= '3/19/2008' AND t1.EndTime <= '3/20/2008' AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime
however, the following always takes 10+ minutes declare @pStartDate datetime declare @pEndDate datetime set @pStartDate='3/19/2008' set @pEndDate='3/20/2008' SELECT * FROM t1 INNER JOIN t2 ON t1.key= t2.key where t1.StartTime >= @pStartDate AND t1.EndTime <= @pEndDate AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime
Both return same result. The only diff. is the blue part. Why the second one performances so badly? Thanks.
