Hi, What I'm trying to do is have a stored procedure output single letters and numbers based on the results of a table. For example, let's say you have the following: Book Title Intended Result Die Hard 2 ...more >>

Hi! I have searched the archives to find the answers but i still dont understand! :) Im trying to link a secure access db in SQL 2000 using the following sp's: EXEC sp_addlinkedserver @server = 'Global_UAT', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider ...more >>

Let's see.. how to explain.... Ok. I am trying to get a table to display info, but only info after a count is done. I want to display data that appears AFTER the first 64 rows of data. What i have is a tourney script. Only the first 64 that register AND check in can play. I want to display a w...more >>

Hi, Are there performance impact inserting records into Table vs View, where the view is a simple select statement selecting all fields from a table eg: Select * from TableA ? Also, any significant performance in doing updating, selection and deletion to a View vs table ? Thanks, Jet...more >>

I'm frustrated. when I run the following query, it returns list of jobs which are running currently on the server. for this example, EXEC msdb..sp_get_composite_job_info @enabled=1 , @execution_status = 1 Result: job_id --------- EA0A97A3-46E2-40F1-99DC-D5E73FA271DD I want t...more >>

I have a relation like this Entity1 -|--------O<|- Entity2 where entity1 may have an entry in table2 and multiple and each entry in table2 must have an entry in table1. How do you code that in SQL to insert the entries into the database? PS: it's not homework Thanks Jack...more >>

Hello: I am trying to avoid the use of a cursor in a sql-job, but I have no clue how could I use SQL-sentences for this I have a table with this fields - location - quantity to reduce I have a second table - item - location - current existence The thing is the qty in the 1s...more >>

I have the following table: CREATE TABLE #TempForfaits ( TF_SNID [smallint] NOT NULL, TF_Day smalldatetime NOT NULL) This table is filled with data like this: insert into #TempForfaits (TF_SNID, TF_Day) VALUES (1, '2004-01-01'); insert into #TempForfaits (TF_SNID, TF_Day) VALUES (1, '20...more >>

I am not seeing the numbers to the right of the decimal point in my query: Example: This is part of my select statement: Values are (respectively): 1919 and 891 COUNT(il.invoice_line_uid) / COUNT(DISTINCT ih.invoice_no) as AverageLinesPerInvoice Which returns 2 I have also tried t...more >>

Hi, A colleague has created a DB in SQL Server - he did this by logging in as a user he had created, so all his table have that account as owner. I want to access the db from my code using a different account, however, when this account tries to access any of the tables etc I get 'Invalid ob...more >>

issue: SQL query using distinct causes lag time.... --returns multiple rows of the same data select fieldA from v_view1 (takes 7 seconds) --returns unique rows but it takes over a minute select distinct fieldA from v_view1 (takes over 1 minute) --returns unique rows but requires a temp ta...more >>

In our development environment, I am writing a stored procedure using another's table like 'John.Customer', so John need to grant me the select, insert...ect access, right ? Can someone explain when there is client application like VB, VC++ or Delphi (I think it does not matter what languages)...more >>

I want to select columns from tables of 2 databases in the same instance, can I do that? ...more >>

I'm trying to get the output I would get by selecting from a varbinary column, which in Query Analyzer would show up as a hexadecimal constant (0x00112233) and turn it into a character string so I can concatenate it with other character data with the + operator. Unfortunately, the CAST/CO...more >>

Hi all: I have like 9 global temp tables that I want to combine into one table. The total record i am expecting out of all 9 temp tables are around 3 M. All 9 table structure have different structures and some common fields too. Whats the best and fesiable way to combine all the 9 tables into 1. Pe...more >>

I cannot get the following code to run. I always get an error during the parse about an invalid character or missing >. I can't find it. I have used XMLSpy and it says that the xml is well formed. Does anyone see anything i did wrong? DECLARE @idoc int DECLARE @doc varchar(1000) set @...more >>

I need to create a trigger on insert that updates a column with a sequetial number starting at 100. Any help would be greatly apreciated. Thank you...more >>

Where / Can I changes the templates that generates the insert/ select etc statements in the Query Alalyzer? I found the the templates for Data Definition Language ie created statements....more >>

hi, is there a yukon beta forum out there? ...more >>

I am more a developer verses DBA so this may be an obvious answer. Are there any logs we can look at to view the history of a SP on a given server - maybe in transaction logs? What I'm looking for is what user compiled an object in our DEV environment. There is a process problem we need to ...more >>

i have an insert sp that inserts the current date into a table but i only want it to insert the date part, and leave the time part set to 00:00:00 is there a way to format it? or can i format it when selecting the data from the table? Cheers, Craig ...more >>

I create some pdf's from crystal reports through .aspx pages. I need the margins to print out exactly as created through Crystal. lets say my left and right margins in Crystal are .20. When IE brings up the page it displays correctly but when printing IE adds on its print margins. And I can'...more >>

Hi I have defined my own datatype " exec sp_addtype T_MY_OWN_TYPE, 'char(1)', 'not null', dbo and bound default: create default dbo.D_MY_OWN_DEFAULT as 'K' exec sp_bindefault D_MY_OWN_DEFAULT, T_MY_OWN_TYPE later I've tried to modify the table: alter table DBO.MY_OWN_TABLE add NEW_...more >>

How do I rename a column in a SQL table in SQL 6.5? Thanks....more >>

As I've not written anything but normal sql before, I've no idea if this is even possible. I have a select where one of the arguments is a filter type, so what I'm doing is something like this.. select a,b,c from table where (filtertype = 1 and val1 in (select val from table...more >>

Hello, I have a table companyz with columns key1 and company. Key1 needs to be set to the first three alpha non-blank characters of company, then appended with a two digit number making the key unique for the company (i.e. J. M. Hardware would have a key of JMH00, then JM Housing would get JM...more >>

Hi guys, How can I enable debug mode for procedure or query? On query analyzer, I go to Tool --> object browser--> debug (but debug is grayed out. ) ...more >>

where/how can i get a list of stored procedures for a certain database and details on which users have excute privlege on them i have been given a task of designing something similar along the lines of what SQL2000 already does, something similar to when you select manage persmissions in Enter...more >>

Executed this: ALTER TABLE Foo Add [col1] float (8) null, [col2] float(8) null, [col3] float(8) null, [col4] float(8) null When I look at the table definition in EM or via Sp_Help Foo, those new = columns are defined as real, not float... Anyone seen this before? --=20 Kevin ...more >>

I am very new to SQL and have been building my triggers based on examples people have given me etc. My trigger is to copy the entire record into an audit table on update/delete. Some of my columns are TEXT. I am getting an error saying: Server: Msg 311, Level 16, State 1, Procedure SYS_I...more >>

We have a table that has 4 NVARCHAR(80) columns, and over 1.6 million rows. I have to write a utility that runs down the whole table and does some crunching on every record. String crunching is easy, but loading a result set that big into memory sounds dangerous. So I want to process the st...more >>

Hi I have a query that I would like to be able to put the output in a word doc which is a Invoice number template can anyone direct me on how I would do this thanks for any help Dave ...more >>

What is "Distributed Database"and "Distributed Query"? I have a linked server set up to three Excel worksheets. I can see the three Excel worksheets name under the object I created in the linked server. When I right click on it, there are only two options, "Copy" and "Help". Can I look at the da...more >>

SQL 2000. I've got a table with 4 fields: SSN ProcedureID TotalDone DateCompetent A person may have several records for the same ProcedureID but only one of those records may have a DateCompetent. I want to sum up TotalDone by ProcedureID and create just one record f...more >>

Hello, I have a bunch of records in a table that have a grouping level and are sorted by grouping level and date(asc) for each grouping level i need the 2nd last record by date. ideas anyone? here is an example of a table and what i need. grouping level col1 col2 ...more >>

Hello, I have a bunch of records in a table that have a grouping level and are sorted by grouping level and date(asc) for each grouping level i need the 2nd last record by date. ideas anyone? here is an example of a table and what i need. grouping level col1 col2 ...more >>

I've been looking for a way to do this in MS SQL Server, with no luck. In Sybase, it's possible to refer to a column by its ordinal position in a table, rather than by name. Does MS SQL Server also have this capability? Can you please give me an example of how that would be done? ...more >>

Why is this ViewScript not working. If i create the same view without TOP command, it will works. CREATE VIEW dbo.EventoKurs AS SELECT IDEO = CASE dbo.fachbereich.abbreviation WHEN 'DWB' THEN 2 ELSE 0 END, (SELECT TOP 1 dbo.EventoLehrer.ad_id FROM dbo.E...more >>

I have PartialNumber field in a table which contains values like 9905-0055 0012-0121 0406-0202 where the first 2 digits is the part of the year (1999 for the first value, 2000 for the second one, 2004 for the third one). The next 2 digits represent the month. So the first value represent May 1...more >>

I have two tables in SQL 2000, first table is a link table to external source and the second table with similiar table structure as the link table. What I need a trigger to do is whenever there is a change at the link table, it will trigger to update the other table. Thanks ...more >>

Hi, How would a go about doing a join between a table in my SQL2000 database and a table in an Oracle database?...more >>

Can someone help me convert the following oracle query to a sql server query? select cost_center, decode(cost_center,01,'IT',02,'HR','OTHER') from (select distinct cost_center from gl) gl Thanks...more >>

Hello, I have a problem with an table column type. Its type is nvarchar(4000) and the data I'm supposed to enter besides letters contains some data like "\", "\n"... and the text might be bigger then 4000 chars. The same alert about data lenght and unsuported chars appears when I use varchar (8000...more >>

Hi, I am chasing a freelancer who's got good experience at migrating and transforming data from SQL/dBase into SQL. Must be based in or near to London. Any names / contact details welcome. tim....more >>

I have read that primary keys are typically indexed as non-clustered, but when defined by enterprise manager they default to clustered. Which is the best for primaries? Thanks, ...more >>

Hi can anybody tell me the meaning of following patterns in the check constraint ^..[^a-z|^A-Z]' ^[0|^0][^5][cC]|^[^0][5|^5][cC] ^[0|^0][^5][cC]|^[^0][5|^5][cC] (?!78|05) (?!78|05) ^..[^a-z|^A-Z] ^..[^a-z|^A-Z] (?=78c|78C) (?=78c|78C) thanks mahesh ...more >>

Hello, I try to run on the Query analyser for SQL server 7. declare ... Set @sql = 'select Max([Date]) from ' + select [sysobjects].Name from sysobjects where id = Object_Id('data_12345ka') exec(@sql) Why it no working? Thank Joseph...more >>

We have a list of stores numbers(from 1 to 50) that we create a temp table for and that joins with a table of abount 7 million records. Will it be better(or the same) to use in and a stringlist instead. Will SQL server 2000 be able to use the same indexes in a IN as with a JOIN ? Thanks ...more >>

Hi, I am having problem with this query. I just want to change/ demonstrate the display format of the result set, but everywhere output is same. declare @Da as datetime set @Da = '05/23/2004 12:00:00 AM' select @Da , Convert (datetime , @Da , 105), Convert (datetime , @Da , 101), Convert (da...more >>

Hi I have a three tabled, in all three tables I have a column called Customer. I need to see what exists in Table A(Customer) but does not exist in Table B(Customer) and Table C(Customer). Many Thanks AQ *** Sent via Devdex http://www.devdex.com *** Don't just participate in USE...more >>

Hi, I need ur help to get the query to get the data into diff format. I have one table 'UserData' like below. table UserData UserName Id Type AAA 11 X AAA 12 Y BBB 13 X BBB 14 Y CCC 15 X CCC 16 Y DDD 17 X DDD 18 Y EEE ...more >>

SQL Server 2000 Background: I have been working on a rather large Oracle to SQL Server port of an existing application. The client wants to support both databases with the same front-end application. The portion of the application that is relevant for this post is the numerous reports writt...more >>

A user may have many territories associated to them, the lowest sort order signifies the most important territory for that user. I would like to see the territory with the lowest sort order for each user: User id, User Name, Lowest Territory Name Lowest being 1 for most important, upwards for l...more >>