I recently accidently wrote a really ugly stored proc where I wished I had enums, Eg. CREATE PROCEDURE Proc_search_with_enum @user int, @account_category {enum} I understand that SQL 2000 doesn't have enums as a first class language construct, which coding conventions do you use to simulate enums or otherwise address the same issue?...
I am looking for a function that would be able to do the same thing as the MySQL REGEX function for TSQL. Basically i need my Query to look something like the following: SELECT * FROM Routing WHERE (@Message REGEX RouteRegex); I am not to keen to use CLR at this point in time. Any Ideas? ...
hello, I have a table that contains information for 4 electrical generators I would like to have the results of the four querys in one row. does any one have a suggestion. Thanks SELECT avg(KW) as GEN_101_AVG FROM genset WHERE (GenSetName like 'GEA3519') and GenDate >= '1 jan 2003 00:00:00' and GenDate < '1 feb 2003 ...
A UI (before the report shows) shows a look up (Combo) that has (ID = 0).All Organization Units (ID =4).HR (ID = 5).DEV I need to: Be able to show data of (4) + (5) if (0) is selected. Only (4) OR (5) if either HR or DEV is selected. Lookup combo code (Selected Feeds the parameter in the below query.) Select 0 AS ID,'All O...
I recently found the following article: http://www.tsqltutorials.com/with-common-table-expressions.php The article doesn't list which version of SQL server this became available in. Does this work in SQL Server 2000 and if not what is the earliest version that it is usable in? Update: I did try a quick test and the test doesn't work...
Hi, I am scheduling the a query to a table for each database in a particular instance. The query and table for each database are similar. I direct the query results to text file. Before the query results, I include the database where the particular query is being made. In my test in AdventureWorks, however, the result I got is a databas...
Using SQL Server, I have... ID SKU PRODUCT ======================= 1 FOO-23 Orange 2 BAR-23 Orange 3 FOO-24 Apple 4 FOO-25 Orange I want 1 FOO-23 Orange 3 FOO-24 Apple This query isn't getting me there. How can I SELECT DISTINCT on just one column? SELECT [ID],[SKU],[PRODUCT] FROM [TestData] WHERE ([PRODUCT] = (SE...
I normally use fully-explicit transactions in my stored procs (BEGIN TRANSACTION .... COMMIT). I just ran across an old one that instead uses "CHECKPOINT" at certain places in the code. This won't do the same thing, right?? Even if the database is in simple mode, the whole thing will still run as one big transaction, even with a bunch o...
When data is copied over from source to destination in a SSIS package, source being a sql query with 'group by' keywords used and destination being a table, is it necessary that the data at a row position has to match the data at the same row position at the destination table?? sagar ...
Hi there What is best approach of joining 2 tables from different Database? In my situation, I have a development database that has postfix such as _DEV while on the production has _PROD. The issue is that if I join these 2 tables I have to reference a full database name such as DB1_DEV.dbo.table1 INNER JOIN DB2_DEV.dbo.table100 Work ...
Is there a way to get a value of a local variable specified by its name dynamically in SQL Server SP? declare @foo int declare @bar int declare @variable_name varchar(10) set @variable_name = '@foo' print -- magic happens here - how to print the value of the variable -- which name is stored in @variable_name, in this case @foo ...
How can i resolve a hostname in t-sql? a 2000 compatible method is preferred. Although something that works on 2005/2008 would also be helpful. eg. If i have the hostname stackoverflow.com i want to return 69.59.196.211 ...
Crystal Reports 9 seems to save the database connection information inside the report file itself. I am having an issue changing that connection. I work with a team of developers who all have their own copy of a database on the same server. We are using Trusted Connections to the db. When we need to make changes to a crystal report, ...
i am trying to create a table with a row where id=0 but the identity insert column starts at 1,1 so all following use the identity insert i have to execute DBCC CHECKIDENT ('Foo', RESEED, 0); after i manually insert (using a script) to get the next row to have id=1 is there another way to get id=0 for my first row which is from the i...
select HASHBYTES('sha','what is it') Result --0x2327A09C2FDAD132E436B5CC12E9D5D283B5BA69 is it possible to convert back hashbytes to string '0x2327A09C2FDAD132E436B5CC12E9D5D283B5BA69' as a input want to get out put as 'what is it'? ...
Say i have Four tables 1) Studnets: Student_ID First Name Last Name 2) Contact (Will take the latest item) Contact_ID Address ZipCode DateAdded 3) Phone (Will take the last three items) Contact_ID PhoneNumber DateAdded 4) StudentContactRef Student_ID Contact_ID How can I query this table? I want to have the fields as sh...
Hi!Can you please help me to understand where is the problem? SELECT mz_t_NaznExec.p_Services FROM mz_t_NaznExec Above script returns recordset where record with p_Services = 200000000000115 not exists SELECT mz_t_Services.p_ID from mz_t_Services Above script returns recordset where record with id = 200000000000115 exists But this ...
Hi guys This is a sample of the data that I have. -ID- -Rank- -Type- -Status- -Amount- 1142474 2 Under Offer Approved 23 1148492 1 Present Current 56 1148492 2 Under Offer Approved 3 2273605 1 Present Current 24 Where the ID is the same I only want the record with the highest rank. So the end result of the query....
I am using Access 2007 (VBA - adp) front end with a SQL Server 2005 Backend. I have a report that i want to save a copy as a PDF as a binary file in the SQL Server. 1, Report Opened . 2, Report Closed - Closed Event Triggered. 3, Report Saved as PDF and uploaded into SQL Server table as Binary File. Is this possible and how would i ac...
This query returns me first below table. forexample; 2009-06-01 00:00:00 people enter my system '41' times. i need second table declare @date1 smalldatetime, @date2 smalldatetime , @countgap int, @page nvarchar(100) select @date1='2009-01-01', @date2='2009-12-30 23:59:59' ,@countgap=1, @page='Tüm Sayfalar' declare @@date1 smalldate...