with dynamic SQL i'm of all sort of quoting issues and the ugliness of such code to maintain etc.. i've had a couple of simple ideas to deal with quoting and inserting variables into dynamic sql, i just want to pass them pass people just in case there is a huge glaring hole in my logic declar...more >>

Hi All, In the database i have a table ATTRIBUTE where I define some attributes. CREATE TABLE ATTRIBUTE (attribute_id int, attribute_name varchar(20)) INSERT INTO ATTRIBUTE VALUES (1, 'Location') INSERT INTO ATTRIBUTE VALUES (1, 'Department') Now the domain of values for an attribute can ...more >>

select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled') returns "1" but full-text isn't really enabled because the service isn't running. Is there a way to detect programmatically if the service is running? thanks charels ...more >>

hi, I work with sql server 2000 servers. I want to put the info of sp_help_job into a temp table using codes below, but it failed with error: 'Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67 An INSERT EXEC statement cannot be nested. (0 row(s) affec...more >>

Please let me know what you think of this scheme I have come up with (not to imply that noone else has before me) for managing relationships. I have created an entities table with Individual and Organizational subtypes. I need to be able to relate them to eachother (e.g., some are customers ...more >>

Whats is the Difference between Procedure and functions.(w.r.t SQL) Thanks ...more >>

Hi all, I am looking for some help for the sql gurus here. I have one table with the following schema: ID INTEGER NAME VARCHAR(20) DATEFROM DATETIME DATETO DATETIME I need a query that will take 2 parameters startdate, enddate and will r...more >>

I have the following query: SELECT TOP 5 W.blogDate, W.blogHeader, W.blogComment, W.blogPostTime, COUNT(C.blogID) AS Comments FROM dbo.tblWeblog AS W LEFT OUTER JOIN dbo.tblUserComments AS C ON W.blogID = C.blogID GROUP BY W.blogDate, W.blogHeader, W.blogComment, W.blogPostTime ORDER BY W....more >>

Intentionally Cross-posted to maximize exposure. Okay, I have a very interesting question. My clients require a schedule in this format. Format Required 11 12 13 14 (these are dates) 1-2 O P P P (codes) 2-3 a b c d 3-4 4-5 5-6 (t i m e s) Records...more >>

Hi, I use xp_cmdshell to list text files on certain folders. Is there another way of doing this? Thanks a bunch! ...more >>

Hi Experts~ How can I store a picture in a table (Manually), retrieve it and display in the web (by ASP) Thanks~ ...more >>

for some reason just doing simple insert statements is causing SQL server to report " Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." yet, while select statements execute perfectly... what could be going on here? and is ...more >>

Hello, I have a table of Users and Roles, and I need to write a report with one role per line and all users in that role to the right. For example, here are the two tables... USERS: UserID RoleID Username --------------------------------- 1 1 Sally 2 ...more >>

Hi, How can I search for a table inside all stored procedures's text? Basically I need to know which stored procedure is working with a table Thanks, Alan ...more >>

How would I search through: 1. all the tables in my db to find where getdate is used as a default constraint? I need to change it to getutcdate. 2. all views and stored procs to find where i've used getdate, and change to getutcdate? ben...more >>

Hi All, I would like to prompt the user to enter a reason when a trigger gets invoked. The triggers I have written (update/delete) are attached to the tables itself. Is there a way to throw an inputbox from the trigger for the user to enter a reason?? Thanks, Tamanna. ...more >>

Hello, I have a question regarding table ids I'm hoping someone can help me with. I have a table where I can use either the tinyint or a char(1) datatype for my table id(pk) due to the table having a small number of records. Both are stored as 1byte so is there a performance advantage t...more >>

Hi, I'm trying to sort a result (from Table A) based on the sort order specified in another table (Table B). No big deal because all you need to do is take Table A, create a join to Table B, then sort by the 'sort column' in Table B. Table A DrawingName EL-201 EL-204 EL-205A EL-206 EL...more >>

Hi, I got an error message says: "Could not allocate space for object '(SYSTEM table id: -3025745)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full." I checked the TEMPDB. There was still plenty of space, and it was set as 'automactically grow file' and 'Unrestricted file growth...more >>

I have a table EmployeeOffice with a field [Employee Name]. Values in that field are like Smith, John, always separated by a comma. How can I read the string up to (and excluding) the comma, store that value and SET my new field Lname to be the value, and then move one space past the comma t...more >>

Hi, I need to obtain the date part only from the current date. I mean a version of Getdate() that instead of returning '2004-07-23 09:01:345' returned '2004-07-23 00:00:000' Since Transact SQL has not a SetDate() function, Which is the recommended way to do that? I've tested tricks like g...more >>

I would like to write some query using strings and result should be Sp_test 'name', @date = "'address'" I tried something like this DECLARE @sA varchar(1000), @sB varchar(100) SET A = "'address'" SET B = "Sp_test 'name', @date = ...more >>

I have a table that is setup as follows: TestID Attribute Value 1 Si 20.89 1 Al 4.89 2 Si 20.22 2 Al 4.88 2 Mg ...more >>

Hi, Over the past several months I have been working on our schema (extending and studying performance). Two interesting thoughts on performance have come up: 1. AFAIK, caching of tables is done on a row by row basis. It would be interesting to cache by column. Where this comes in handy i...more >>

Hi. I have a small existing database containing about 10 tables (all of type 'user'). Recently, I created a few more tables, and noticed that these new tables were being created as system tables. What could possibly be the reason for this? I would like the tables I create to be user tables...more >>

Here is my problem. Please help if you can. Thank you so much for your help and time. -- drop table #t if object_id('tempdb..#t') is null create table #t (OrderNo int, OrderLineNo int) set nocount on truncate table #t insert #t values (1,205) insert #t values (1,3) insert #t values (...more >>

I always hear somebody said that build index in database I would like to know what does it mean? does build index same as create a primary key in a table?? thanks~ ...more >>

I'll be eternally gratefull if anybody could help with this. I'm attempting to warehouse various chunks of data from around the business from a variety of data sources. The most problematic data sources tend to be Progress databases (especially dates), but in general i can handle these using L...more >>

Hi, I have a float column (amount) in the table. Say, the column as the data 123456.78901 How can I display this data as a string with commas as thousands seperator, i.e., as 123,456.78901 Thanks!...more >>

Hi, Suppose i have a view which comprises of data from two tables. So what will be my update statement if i want to update the base tables from using view. Please give an example. Thanks ...more >>

Can we use the update statment to update more then 1 table?...more >>

Hi guys Senario: I have a T-SQL script that looks like this: While @I<=@MaxI Begin exec(@SQL) Set @I=@I+1 End Question: How will this execute. Will it wait until the @SQL command is done executing before moving on or will it send the @SQL command to the SQL Server then move...more >>

Is there a c#-callable method which will execute a .sql batch file? thanks charles ...more >>

Hi, I'm trying to write to a file (.txt) from a Stored Procedure but have not found a way to do so. Appreciate any help and thanks in advance. --- Posted using Wimdows.net NntpNews Component - Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Aler...more >>

I wrote this question back on Tuesday 9/14/04, but apparently it did not get posted, so I am trying again. I have an application that represents objects in a tree like structure such as: + Level 0 +--Level 1 +----Level 2 +------Level 3 .. +-------- Level n E...more >>

I'm looking for a way to determine whether or not a coulumn is s a primary key in a table. Thanks ...more >>

So far my VB6 application used to use the particular SQL Server database, let's call it "coord" located on srv10 server. I open connection (hard coded in VB) this way: strConnectionString = "DRIVER=SQL Server;SERVER=srv10;UID=genuser;DATABASE=coord;pwd=genpwd" objConn.Pr...more >>

Hi, I'm interested in understanding SQL Server index structures a little more and have used DBCC Page to dump the contents of a clustered index page. Does anyone know where I can get hold of further information on the fields contained within the 'BUFFER' and 'PAGE HEADER' sections? BUFFER: ...more >>

What is the latest tech to use EMAIL in conjunction with SQL Server. I don't want to use SQL Mail as it requires Outlook to be installed on SQL Server. What things outside SQLMAIL can be used to accomplish this feature? Thanks in Advance. -Live in Peace...more >>

I am trying to write a query that would return the number of words in a column of type text. I want to count the number of words in the body column select title,body,[word count here] from main Is there a way to do this? Thanks...more >>

Hi there, Use Nothwind GO select RequiredDate from Orders Result: ------------------- 1996-08-01 00:00:00.000 1996-08-16 00:00:00.000 All I want is : 1996-08-16 or 08/16/1996 without the 00:00:00.000 How do I do that ? SELECT RequiredDate FROM Norhwind Where RequiredDate...more >>

Hello everybody, my question is more than simple: Are they the same? SELECT * FROM Employees SELECT * FROM dbo.Employees SELECT * FROM Northwind.dbo.Employees THIA PETER ...more >>

Good Afternoon Workgroup I am having a bit of difficulty in creating a Trigger. What I am wanting to do is to create a Trigger to a Table that logs Jobs. I would like to add a new Record to another Table to record the new Job Number, which is in another Database but on the same Server...more >>

I have a hierarchy structure held in a table, Tree. 1 / \ 2 3 / \ 4 5 These are the table defs and the sql to populate them: create table Tree ( ParentId varchar(4), ChildId varchar(4) ) create table Role ( [Id] varcha...more >>

I have a fnction and while execute sql using the function, found error in sqlserver2000 see down: select dbo.long_to_number('PARA_TX_VALOR','SGI_PARAMETRO',PARA_ID_PARAMETRO) FROM SGI_PARAMETRO Error: Server: Msg 557, Level 16, State 2, Procedure long_to_number, Line 27 Only functions a...more >>

I have an MS Access 2002 project that exports spreadsheets to Excel using the OWC10.Dll Functions. The code is listed below: ****************************************************** With Rs_Spreadsheet .Source = "dbo.Temp_CasesToSC" .Open .Source, Cur...more >>

Hai all, Is there any way to get the identiy col value that would be generated on a table if i inserted a row? Or put it another way can i get the last identity col value generated on a table? TIA Satya...more >>

I need to create a procedure which checks to see if a record exists and return either true or false but I also must check to see if the procedure executed correctly and return an error if it fails. Can anyone point me in the right direction ? I am brand new to TSQL and haven't a clue....more >>

Hi all, Is it possible to select across database servers? So something like: Select * FROM Server1.database1.table1 UNION SELECT * FROM Server2.database1.table1 If not... any solutions besides copying the table data over to a central server? Thanks! -- Lucas Tam (REMOVEnntp@roge...more >>

I am looking for information, books, websites, etc that will help me figure out the following things in MSSQL and Oracle: 1. Optimizing the database configuration itself. 2. Optimal table and index design. 3. Optimizing SQL statement lookups. 4. Anything else that might help to speed up our d...more >>

Hi, Is it possible to instruct SQL Server in a SQL Query to run a query on a lower thread priority than normal? We have some software that uses a SQL database heavily for small request but from time to time a large detailed stored proc is run. This kills the performance of the rest of...more >>

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BacsHdrYearly]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BacsHdrYearly] GO CREATE TABLE [dbo].[BacsHdrYearly] ( [BHYear_Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [B...more >>

Here is trick : I use system with decimal point presented with "," not "." ( Croatian language as default on Win2k). I have table named TABLE1 on SQL server with 2 fields: Name varchar (50) Price decimal(20,2) Using VB6 and ADO 2.8 I create ADO command text like this one txt = "IN...more >>

hi, i need to write a VB6 source code to edit the password of a SQL Server registration. can someone tell me what API or methods must i use ? thanks for your help clement...more >>

I've got a table with 2 fields: CarModel and CarPicture. Several CarModels have the same CarPicture. I want 10 RANDOM UNIQUE CarPictures with the corresponding CarModel. I gave up on straight sql, but let me know if I've overlooked something. I've tried several approaches in a stored proc a...more >>

Hi, I need to update some fields based on the value they have in a catalog. There is a common id in both tables, my "Activities" table and the "Processteps" table which is the catalog that has the values. This is the query as I have it and is not working, maybe someone can help me with t...more >>

Any thoughts on the pros and cons of splitting an address into street and number fields (city, state, and zip are not an issue) vs. a single field in an address table? I've been avoiding this last little bit of normalization, but its time to make a decision. I see the upside being the ability ...more >>