Recent update: 2008-3-21
How do I get the identity for a row I just inserted? When using @@identity and there are triggers inserting rows into other tables I get the wrong id. I know sql2000 has scope_identity but I'm stuck with sql7 for legacy reasons.
Hi, I would just like to know if there's a simple way of querying a time disregarding the date. I mean, I want to query all employees that spends time on the office after 5pm. I don't care about the date, I just want the time... Im currently achieving this by using DATEPART function and ...
323 byte By
DarinV at 2008-3-5
I am doing the following to change an int into money (I want the commas) but it is adding a decimal and 2 zeroes at the end:convert(varchar,convert(money,m.[FirstTier]),1) as 'First Tier',m.firsttier = '1583456'after conversion = '1,583,456.00'Is there any easy way to not have the ...
Hi,I thought TSQL of yukon would have a way of passing an array of values to a SP which can be used by it in an IN statment.ie:List<int> idList = new List<int>();idList.Add(5);idList.Add(7);SqlCommand cmd = new SqlCommand("sp_Core_GetDetails");cmd.Parameters.Add("@idList", ...
42538 byte By
Debra at 2008-3-1
I am getting errors such as ..."invalid column name 'Name'" and "Msg 4104, Level 16, State 1, Procedure vw_MWOrderValues, Line 3 The multi-part identifier "dbo.Orders.OrderID" could not be bound." when trying to use an Order by clause in View Definitions. (The second error happened when ...
Hello,I am blocked with a Join RequestI have 3 tables :TaUtilisateurs store the usersTaClients store the customersTaJoinClients_Utilisateurs store right of user on customers :for example : TaUtilisateurs Id_Utilisateur Login 1 Nico 2 Philo TaClients Id_Client Entreprise 0 Company 1 ...
44 byte By
guest at 2008-2-28
what is extended storeproc.and how to use it
what is covering index?
663 byte By
JoseTA at 2008-2-27
HiDECLARE @MyValue varchar(10)SELECT @MyValue = (SELECT RIGHT(YEAR(GETDATE()),1)+ REPLACE(STR(MONTH(GETDATE()),2),' ','0')+ REPLACE(STR(PlaceID,2),' ','0')+ '00001' FROM Provinces WHERE PlaceName='Kinshasa' )SELECT @MyValueI written a code like this to generate (example: 5080100001 ...
1158 byte By
wkb at 2008-2-26
I'm not sure how I can best phrase this, but I need help writing a query that is inflectional, thesaurus and non word ordered specific. In other words, say a user is looking for a "car wash" but types in "wash auto" or "auto wash" or "vehicle wash" or "cars washed" or "washing cars" or "car ...
guys i have a programming question i have table called prerequsiteswhich is like thisprerequisite component componenta bso inside my before insert trigger i have to check if the prerequisite is a valid entry like for instance an entry like b is a prerequisite component for component a which is ...
There are two sql statement which do the same thing,I wonder which is more efficient and why?The two sql statement are as below:1. select Sessionid from tableTest1 where sessionid in ( select sessionid from tableTest2 )2. select t1.Sessionid from tableTest1 t1,tableTest2 t2 where t1.Sessionid = ...
861 byte By
prk at 2008-2-23
Hi friendsI need ur help with syntax of following dynamic sql.actually it is simple version of what am doing but i know for sure this line actually causing the error.DECLARE @p_taskentrydtfilter nvarchar(50),@taskentrydttag nvarchar(100)declare @p_taskentrydt DATETIMEset @p_taskentrydtfilter= ...
247 byte By
bobbye at 2008-2-22
I am getting errors when trying to write a stored procedure with alter view statements in it. Can this not be done or is my syntax just off. If it can't be done is there a common work around or different method I should consider? Thanks
What i want is to add by parameter a Where clause and i can not find how to do it! CREATE PROCEDURE [ProcNavigate] ( @id as int, @whereClause as char(100) ) AS Select field1, field2 Where fieldId = @id /*and @WhereClause */ GO thx
I am trying to update a table and the DateInactive smalldatetime field is causing errors. Here are the statements and errors. UPDATE Categories SET DateInactive = '7/31/2005 15:32', AdminInactivating = 36 WHERE CategoryId = 15 causesODBC Error Code = 37000 (Syntax error or access violation) ...
509 byte By
Coggsa at 2008-2-20
Hey AllI have 2 DBases - Production and Archive - that need rows copied from Porduction to Archive, then deleted from Production. The Issue is that rows from multiples tables need to be copied, and the number of Child rows that need to be copied for foreign key integrity. Any suggestions of how to d...
671 byte By
rajani at 2008-2-20
Hi FriendsI need ur advise on this one.when writing sql statements and whenever there is need to join 2 or more tables am used to the following syntax (using WHERE to join tables)select a.name,b.code from table a,table b where a.code=b.codeas you all know we can also use INNER JOINS,OUTER JOINS ...
253 byte By
guest at 2008-2-20
I have an accounting table that holds transactions by client. I want to get a total for each client but the credit trans amounts need to be added while the debit trans amounts need to be subtracted. Is there a way to do this with a SQL select statement?
I have a temp table which I am trying to do a select INTO. The temptable has an identity field which increments by one. In the select query, I am doing an order by. It does not appear that the temp table retains the physical ordering of the records from the order by. When I do a select * FROM ...
3293 byte By
Fregas at 2008-2-20
Hello all. I'm hoping someone can help me with this problem.I have a lot of experience with sql server and as the years have progressed, I have gotten much picker about my relational design. I enforce referential integrity whenever possible. However, I'm running into a pattern in my current ...
I am having a problem creating an index. When I check the table for dupes I don't find any. When I try to create the key I get an error that there are duplicated values. I think that this may be because the key is too long? I don't see any other reason. The data looks OK. Each client should ...
Hi.I have a problem. I am trying to copy a record in a stored procedure. There is a primary key ( id, indentity ) and the copy should be an exact copy of the row except for the PK, a field called SerialNo and a field called createtime having a default time of (getdate())I would like to avoid ...
920 byte By
CraigG at 2008-2-17
the current statment reads as follows - SELECT @NonConsent as NonConsent, @NonConsentPAS as NonConsentPAS, SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) <=25 THEN 1 ELSE 0 END) As Under25, SUM(CASE WHEN Cast(DateDiff(Day, EP.DateofBirth, GetDate())/365 as int) ...
I have an 'instead of insert ' trigger an a view containing multiple tables, one of the table-columns i use within the view is an identity-column.Trying to do an insert into query on the view without the identity-column (funcid) results in: insert into passw_functie (userid, functie, dienst, ...
Hello, I don't know how to make this : 1 table containing list of cities 1 table containing date and temperature both linked by IdCity how can i create a table to display cities in rows, date in colums and temperature at intersections ? Thanks !
237 byte By
offo at 2008-2-15
When i use alter table alter column,i can lengthen the length of a column of nvarchar type. But when i use the same way to shorten column length,it issue an error. I know how to do this in Enterprise Manager,but not in Transact-SQL.
287 byte By
Srik at 2008-2-15
I have a requirement for converting Oracle's CTX_DOC packages to SQLServer: There is a function CTX_DOC.FILTER (which generates a plain text or HTML version of a document and stores it in a table). Is there any option to generate a HTML/text version of document in SQLServer.
I have a stored procedure that contains an INSERT statement (I am inserting into temporary a table that forms part of the result of the stored proc). When calling this stored proc from C++/ADO, I get back a closed record set when running in VS2005/Windows XP x64 (this has previously worked on ...
74 byte By
Srik at 2008-2-15
Hi All, Is there any equivalent to Oracle's rowid in sqlserver
430 byte By
Srik at 2008-2-14
Pls lemme know which is better UPDLOCK OR SCROLL_LOCKS. I need to migrte below oracle syntax to T-SQL syntax: Oracle: cursor emp_cur1 is select * from EMPINCOME for update; SQLServer: Option 1: emp_cur1 CURSOR SCROLL_LOCKS FOR * form EMPINCOME Option 2: emp_cur1 CURSOR SCROLL_LOCKS FOR * form ...
Hello everybody,how can I verify a partition scheme in SQL Server 2005 (June edition)?SELECT p.* FROM sys.partitions p, sys.objects o WHERE o.name = 'mytable' AND o.object_id = p.object_id GOthis always returns only one partitionthank you Eckard ...
Hi all, I have a view called Item CREATE VIEW ITEM ASSELECT * FROM General.DBO.ITEMUNIONSELECT * FROM Specific.DBO.ITEM Schema of Item & ItemLog in General & SpecificItemcode int, ItemName varchar(50), Rate int, Flag char(1)I have a trigger (Instead of Update) on this view : CREATE ...
Our challenge is as follows: A third party application writes phone call logs to an SQL Server 2000 database. The start and end times are unfortunately inserted as Unix time (seconds from 1-1-1970). We want to make a monthly csv export of these records by selecting the detail records between ...
Hi. I have in my databases the follow tables:Cliente: PK = cdCli. FoneCli: PK = isnFone. One client can have many fones (home, work, moblile, neighbor, father, etc)I need in a query, get the clients and yours fones home, work and moblile.I do this now with the follow command:select ...
Hi all,I've got a table with the following data: (sample data) Tbl_MachineHrs ASSETID DATE HR23 OPR23 HR06 OPR06 HR07 OPR07 HR14 OPR14 HR15 OPR15 HR18 OPR18 HR19 OPR19 HR22 OPR22 806CD03 11/07/2005 6817.9 116 6824.3 116 6824.9 103 6827.2 103 6827.6 46 6831.1 46 8066584 11/07/2005 ...
Hi all,I've got a view with the following data: (Sample)Class Date Month Year Day Target Actual Variance MName====================================================================DropBalling 01/07/2005 7 2005 1 165000 117850 -47150 JulyDropBalling 02/07/2005 7 2005 2 165000 92550 -72450 ...
342 byte By
Srik at 2008-2-10
Hi, Is there any difference between NUMERIC and INTEGER datatype? I am currently migrating my application from oracle to sqlserver hence i need to know the best equivalent: Oracle var_int int(38); SQLServer: @var_int numeric(38) or @var_integer integer Please let me know the best equivalent ...
Hi Guys, I would like to take a backup of tables and further use them for manipulation purpose. (e.g.) Select * into arc_employee_07_07_2005 from employee Where 07_07_2005 is formatted from getdate(). How can i achieve this formate.Thanks in advance. ...
Hi Guys, What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error. In Detail. This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to ...
471 byte By
Sigura at 2008-2-7
Someboy help me to receive the status of cubes's metadata info via SQL Query. I've been analyzing this problem for two weeks and found several solution for example ActiveX (ADOMD.Catalog) or other programing solution, bat I want faund SQL solution this problem. Information Microsoft SQL ...
709 byte By
Srik at 2008-2-7
I have req: where i need store a string value and retrive it later on. And it should be session specific. Hence I thought of using "set context_info". But when I set the value and then retrive it the values are not similar. Here is a sample where the length are different: declare ...
1925 byte By
KL at 2008-2-7
I hope some T-SQL XML expert can help me with a solution. Here is the what I try to do:Thanks a lot!-----------------THIS WORKS FINE FOR ME--========================================== DECLARE @SQL nvarchar(4000); DECLARE @entityID nvarchar(4000); DECLARE @xmlEntityData XML; SET @SQL = N'WITH XM...
I'm having this query: SELECT ss.subscription_id AS SubscriptionId, s.id AS ScopeId, s.[name] AS ScopeName, s.base AS ScopeBase, dbo.iqGetShapesByScopeAsString(s.id) AS ShapesAsString FROM subscription_scope ss, scope s WHERE ss.subscription_id = @subscription_id AND ss.scope_id = ...
250 byte By
siaj at 2008-2-7
Hello, I have just Installed SQL Server 2005 on windows 2003 OS. Is there any client tools like Query Analyzer of SQL server 2000 through which i can connect to the server and fire my queries? Where do I find that tool... Thanks, siaj
520 byte By
Vinoth at 2008-2-6
Hi I'm using Sharepoint Services in my application.My database is SQl server 2005 My Sharepoint site using one database there is one table called docs. In this table one column is called MetaInfo and its datatype is Image. My Question is How to write a Query to Get and Set Image data type ...
201 byte By
SSM at 2008-2-6
Hi, I have a table in a particular order which I ned to maintain.when I say select a,b, from tab group by a,b,c with rollup the order of the output is changed.How can I avoid this. thanks, ssm
Are there any documents published yet listing what the breaking changes are in T-SQL (if any), when moving existing 2000 apps to 2005 ? Thanks.
I have a table that has a "Problem" field with the following dataCustomer Service...Jam...Jam 5...Planned Technical Support...WinOnCD...WinOnCD6PowerEdition...Request...Reporting New Drive Sale MadeI need to separate this "Problem" field into separate fields the issue1, issue2, issue3, issue4 ...
269 byte By
Abyers at 2008-2-4
I have been ask to create a trigger that tracks the addition of objects to all of our databases. I have a method to track the changes to the sysobjects table, however I have not been able to create the trigger on sysobjects table. Is there a way to do this in SQL 2000?