I have some tables with the same name but owned by different users. I have some UDFs that return a table variable. The SELECT statements in the UDF cannot 'see' the non dbo tables. The Same SELECT does work outside of the UDF. In the SELECT statement there is no owner prefix. If I prefix it to th...more >>

Hello Group, I am having problems generating the following result set... Can it be done? Raw Data ======== DateTime varA varB --------------------- ---- ---- 2003-04-28 09:27:00 0 1 2003-04-28 09:28:00 0 1 2003-04-28 09:29:00 0 1 2003-04-28 09:30:0...more >>

Hello All, Can this be done? Can I create a view from a stored procedure I remember seeing this once. Thanks!...more >>

Dear, I am using SQL Server 2000 SP3, when I input a query string in Analyzer: select 1 where null not in ('1') the result is "1" But if I put this query into a Stored Procedure,like aa: CREATE PROCEDURE aa as select 1 where null not in ('1') GO after the running, the result i...more >>

Sorry to bring this up again but I need a link to explain to me how to store a path of an image for SQL to retreive the image off the host computer and then how to do the select statement in vb.net to tell SQL to bring the image. I am not doing something correct. I am not sure what it is. I cr...more >>

Hi, I currently have a system using ADO.NET to update a SQLServer2000 database. I use DataSets (not strongly typed) and set up an appropriate SQL SELECT/UPDATE/INSERT/DELETE method for each. I am now in the middle of moving all of my data access to stored procedures. I have done most of the...more >>

I am new to SQL server, I have a database of conference visitors. visitor, visit_begdate, visit_enddate. I want to write an SQL script to get the daily count of visitors over the conference period period. The uration of the conference can be up to six months. Thanks ...more >>

I have a table with 5 date columns, nulls NOT allowed. Let's assume the table has 100 records. I want to return a resultset that has 100 records, and for each record the most recent date among the 5 on that record. That is, I want to get the max of the 5 dates for each record, and the Select ...more >>

No schema but its easy to reproduce. Create a table with a couple of columns (VC, VB) of type varchar and varbinary. VC = 'Test' VB = 0x123456 Now do something like This works and returns the row SELECT * FROM Table WHERE VC LIKE %st% BUT this fails (Hex - 0x25 = '%') SELECT *...more >>

Hi I have a SP that inserts records from an xml string created by ado. THe SP works fine except in a few cases, where it does not insert a complete record into the DB. It truncates a few characters will inserting However it is noticed that the records before and after the particular row in ...more >>

tried to update the column using a query but, it doesn't seem to like it. update sched_org_avail_fact a set a.shift_avail_time_sssss = (select b.end_time_sssss-b.start_time_sssss from sched_shift_dim a wher...more >>

Hi, I just installed MDAC 2.7a and for some reason my GUID that are returned from my SQL 2000 queries are not returned with {} anymore. It is a major issue since all my application is programmed looking for {} Thanks, alex. ...more >>

I don't think this q is searchgable (or what the hell is written ;-) ) SELECT TOP 5 * FROM T1 WHERE Col1 = 'mickey' AND CAST(SUBSTRING(Col2,6,1) AS INT) & 32 > 0 Is it the best way i can write this? any help appreciated regards -- == fabriZio ...more >>

Hi, Simple scenario; one .mdf and one .ldf in the same directory. I would like to move the .ldf to a different drive, for fault tolerance. I guess I need an ALTER DATABASE command, but can't figure it out. And I can't delete the .ldf, since it is the primary log file. Any help appre...more >>

Hi, Is ther a good sql editor to write stored procedures, triggers...? May be even be able to connect to remote server. Keeping in mind that we do not have admin rights. Thanks ...more >>

I have constructed my sql string to return the xml as I like is, but my problem is: Using SQLClient in .net the returned XML is cut of at 2033 chars. Is there a way to increase this? I need to be able to return XML that is much larger than this. Thanks, Ryan ...more >>

I have a row with 2 columns. I want to set the value of one of the columns to null without removing the entire row. How can this be done? Thanks, Ron ...more >>

Below SQL works declare @startdate datetime, @enddate datetime, @sql varchar(1000) select @startdate = '06/01/2003' select @enddate = '06/03/2003' select top 10 * from mytable where mydate between @startdate and @enddate But , below one error out with message Server: Msg 241, Level 16, S...more >>

Hi All! I have a problem converting an Access DB application to SQL Server. I use a query to join a table twice, like SELECT * FROM (Table1 AS A JOIN Table2 AS B ON A.Field=B.Field) JOIN Table2 AS C ON A.Field=C.Field It works OK, The only problem is that ...more >>

if i have a TSQL statement in a job step and want to report failure based upon the output, how can i fail the step.. if (select count(*) from table ) > 20 then fail the job step.. its based upon similar lines but wanting to know how to exit with failure ...more >>

I there a way to view and disconnect all or some users connected to a particular database? Thanks in advance! ...more >>

I am trying to create a Db on a network drive from my SQL server 2000 Box. When I try to create the Db it tells me the MDF is on a network drive not supported for database files. Can anyone tell me how to get the network drive to support database files? Thanks, joe ...more >>

Hi! I can't seem to be able to use parameters in OPENDATASOURCE. ----------------------------------------------- A non-working example: ----------------------------------------------- SELECT @external_datasource = 'THESERVER' INSERT INTO OPENDATASOURCE('SQLOLEDB','Data Source=' + @external_d...more >>

Hi, I need to pass a temp table to a stored proc, and get back the updated table. When I use 'Create Procedure procABC @TmpTbl Table', it says error near Keyword table. I have tried defining columns before and after the word 'table' but that did not help either. Please help!! AnsG ***...more >>

Hi all, Iam executing the following query and this works absolutely fine. -------------- Select ADETDATE from CC_CDR where ADETDATE >=convert(char(8),getdate(),112) and ADETDATE <convert(char(8),getdate()+1,112) -------------------- Iam executing the same query but with EXTRA COLUMN and t...more >>

I have a variable '@v_dept' of int data type. I have to make it a string of length 4 '@v_deptS' with left padding @v_dept with zeroes. Its like if @v_dept = 5 then i want @v_deptS = '0005' but if @v_dept = 56 then i want @v_deptS = '0056'. I have already done it by this : SELECT @v_deptS = ca...more >>

I am trying to pull some records from a sql 7 database and am need of some assistance in getting my sql statement correct. I am trying to pull a single data record from tableA. (there are duplicate records in the table) for a 30 day period. I have the dates listed in tableb Here is wh...more >>

Can any one help me. Here is the table Create table test3( starttime varchar(50), endtime varchar(50)) insert into test3 values('2003-07-14 00:02:34','2003-07-14 00:05:57') insert into test3 values('2003-07-14 00:09:43','2003-07-14 00:09:51') insert into test3 values('2003-07-14 00:...more >>

This is what I ran... Create table Test ( Col1 int ) go insert test values (1) insert test values (2) insert test values (3) go Begin transaction Truncate table test Rollback transaction select * from test --returns all values So what Im trying to understand is the logged na...more >>

Hi all, whats the best way to hold columns titles? I have coded (below) a solution but my guess is this is not the best mothod! Example - Table 1 holds values, Table 2 holds the titles for Table 1 columns... Table1 C1, C2, C3 7, 12, 15 Table2 C1, C2, C3 'Titl...more >>

The following SQL #1 works: UPDATE EmpMaster SET Trained = 1 WHERE Trained = 0 AND EmpID NOT IN (SELECT DISTINCT EmpID FROM EmpHireAudit WHERE NewStatus = 'A' AND OldStatus IS NULL AND HireD...more >>

Hi, From what I know is TimeStamp always generate a unique value. So, is it possible there might be an occurrence that both update concurrently at the same timestamp??? Thanx, Kenny ...more >>

MSS2000 I am writing a UDF called IsZero to act similar to IsNull. My concern is that I think I need to know what numeric data type is being used so if the value is not zero, I am sure to return the correct datatype dynamically. This leads to four questions: 1) How can I 'generically' ...more >>

hi all, i was wondering if you could shed some light on this error. 'rollback transaction request has no corresponding begin transaction.' any help would be greatly appreciated. thank you. the following trigger causes the error... create trigger uwdstatus1 on contact2 for update as ...more >>

I ran the dbcc traceon(1204) command in a Query Analyzer window, and left it open since it seems to turn the trace off if you break the connection. But deadlocks are happening that aren't showing in the error log. Is the trace run only on the connection that issued the command? Bob Castleman ...more >>

This is probably simple, but I cannot figure how to do this. I'm wanting to select all records in column1 that have ALL of the values in a list that exist in column2. A sample of the table is below. (simplified) So for example, I want to select all of the ControlNum values that contain ...more >>

Hi, What is the easiest way to import table from Home page at http://www.pokerpulse.com/ into my database? I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP3 Thanks in advance Nikola Milic ...more >>

Hi. I'm trying to create a stored proc which uses a table containing a breakdown of activities listed by date, time, user, Sum(Activity Type 1), sum(Activity Type 2), sum(Activity type 3) to create a summary by week in the form of: UserName, Mon_ActType1, Mon_ActType2, Mon_ActType3, Tue_ActTy...more >>

I need two different table fields. One with dates, but in short format, like dd/mm/yyyy and the other in the time format, like hh:mm. I think the solution is creating a user type. But, how can i write the rule? Could anybody give some examples? Thanks in advance! ...more >>

I would like to copy\move files around on my servers utilizing the sp_oa stored procedures. I have looked through the documentation and I really did not find anything that would really help me. Does anyone have t- sql script or stored procedure that does what I am looking for? I am runni...more >>

I am trying to update a field to the top X rows in a table when ordered by priority (descending). The Select statement would be SET ROWCOUNT = @TopX SELECT * FROM Targets WHERE ID = @TID ORDER BY Priority DESC I then want to update a field called TGT_Priority with a constant like 10...more >>

hey guys, is it possible to use case statement in WHERE CLAUSE ? i m trying to do some thing like this but it gives me error SELECT * FROM TABLE1 WHERE TABLE1.ID IS NOT NULL AND (CASE WHEN TABLE1.ID = 0 THEN TABLE1.ACTIVE = 1 END) any help will be great. Thanks, Brij ...more >>

Can anyone help me??! What is the best way to import data from JDE (One World) into SQL Server? If it's possible to use DTS then should I use the OLEDB connection object? Many thanks, Steve ...more >>

I've found a strange issue with using IS_MEMBER on integrated NT logons. I have two logons: DOM\User1: A windows NT logon with access to the database User2: A SQL Server logon I have a single role, called Sales. Both users are set-up with identical permissions and are members of the S...more >>

Hello... Due to security reasons... is there another way that I am able to copy and move files (for example copying or moving a text file from one directory location to another) without using xp_cmdshell? I was thinking... is it possible to do it in script... if so what would the synta...more >>

A stored procedure that produces a single result from a view occasionally takes a very long time to execute. Most of the time, though, it works almost instantaneously. The view from which the results are gathered is a view of another view constructed from the joining of two large tables (~ 1 M...more >>

Dear all, Please... I need a help. I need to put in priority order. For example: I have same teams in a specific table. I need choose one of them, but I have same details. In the first I want team A1, if this team not found the system must be show me team A2.... For exemple: Marta Morais - t...more >>

I am using a lot of variables to find the primary key ( int value )based on an id column and table name. The user determines the unique id and table name. The sp is below and it works fine. create procedure TableLookupProc @uniqueid varchar(40), @tablename varchar(40) as begin ...more >>

Can I do a Net Send from a stored proc? If so, how? ...more >>

How do you convert/cast a varchar to money without dropping the decimal part? For e.g. DECLARE @strNum VARCHAR(10) SET @strNum = '0000053.10' SELECT CONVERT(MONEY,@strNum) The result of the above script is 53.00. How would you get it to have the result 53.10? Thanks, Maligaya...more >>

Hi All, I have been a java/oracle/linux programmer for last 3+ years. I have to build an application using asp and Sql Server. I am fairly comfortable with ASP but not Sql Server. Can any please recommend a book which is neither for beginners and nor for advanced programmers using microsoft...more >>

I am trying to create a query that searches for key words in text fields for a web based helpdesk application. Currently I am using the like operator but if I do a search on problems with ACT (a contact management app) if will find all cases with those letters in the history. Example below: ...more >>

Hello All, I'd like to know if there is someone out there who could provide me with some help on T-SQL. Let's say I have the following data in an IBM DB2 table: ---------------------------------------- product_code effective_date price ---------------------------------------- AA ...more >>

I have 3 tables (Site, Patron, and ShipInfo). Site has a SiteID, and a ShipInfoID. Patron has a PatronID, SiteID, and a ShipInfoID. As you can see, Site and Patron are maped, and individually Site maps to ShipInfo and Patron maps to ShipInfo. Only one of the tables (Site or Patron) wil...more >>

Hi I want desactivate trigger without drop it do you know a command to do that thanks...more >>

Hi all, I run the query below on two tables having exactly the same structure. One being an older copy of the other. Purpose of the query is to show differences in one column called bookedreserve. select a.*, b.bookedreserve as oldbookedreserve from (select office,treaty,uwy,origccyid,...more >>

Hi all, I use Query Analyzer (2000) to create and edit stored procs, views, udf's and such. I actually like it pretty much, but there's one thing that's annoying me. When I want to edit for example a stored proc, I use the object browser and then right-click on the proc I want to edit and cho...more >>