Hi, I just received an older database project where the tables, views have different ownerships. I found a solution to change the ownership to dbo but now I'm wondering that I have to change the content of approx. 85 stored procs manually or is there a solution to do it via T-SQL? Greetz, ...more >>

Hello, I've been using the trial version of SQL2000 and I'm going to be purchasing a copy in the next few weeks. However I was wondering if anyone can explain one question for me? What's the difference between purchasing a CAL version or a Processor Version? I'll only be using SQL as a...more >>

While Database programming in C++ with Rougwave , to connect to a Database , i give the parameters like (user-name , password , SID and the Access Library) . Then i get the conection to that Database . But no idea whether it is an Oracle Database or SQL Server Database . Currently i am usin...more >>

I am not able to apply the check for temp table. Why ? I execute this on SQL Server 2000 Query Analyzer: begin use lrt create table #C (emailcontent int) select object_id('lrt..#C') select * from lrt.#c end The result: ----------- NULL (1 row(s) affected) emailcontent ------...more >>

Hello Friends, Situation: I have been charged with cleaning up a large legacy database. During this process I have identified tables no longer used and removed them from the schema. Question: I now have may stored procs that reference these tables and are no longer needed. How can ...more >>

I have a large table (probably =~ 1GB) which I just transferred from one machine to another. Since I used the wizard I lost all defaults, and keys on the table. I am trying to re-add the identity column to the table, but it takes forever, and the log file ends up taking over the whole machine...more >>

Understanding I'm going to get either "it's impossible" or "it's not a good idea"/"don't try this at home" -- I'm still kind of curious. I need to do a small-paged query of a large dataset and get the results in random order. TOP/SET_ROWCOUNT + ORDER BY + RAND will work fine, but this necessitat...more >>

I am trying to pass the date of 4/05/1955 to a smalldatetime field in SQL server. The value comes form a webform and is passed to a stored procedure that is supposed to update the field in my table. The field has a format of smalldatetime. The input parameter is varchar (10). So the input is...more >>

Can anyone explain B-tree in Simple and how is it useful in SQL? Thanks a LOT ...more >>

A relative dts/sql newbie and just seeing if anyone has any ideas... Basically I need a task in my DTS package to check if there are any non-alphabetic (anything that's not a-z) characters in the first name and last name fields of a table, then bust off an email if there is. Someone gave m...more >>

please look at my codes is it effective.. i look at http://www.sommarskog.se/dyn-search.html this is effective way it says. in asp i call stored like this set rs=baglantim.execute("sp_test1 @surname='"&a&"',@onlinestatus='"&b&"'") my stored procedure is CREATE PROCEDURE sp_test1 @su...more >>

Hi I have a table Create table tmp_name ( name char(15), sales float, yr char (4), mth char (2) ) and it has the following data Joe Smith 15452.00 2001 03 Joe Smith 12157.00 2001 06 Joe Smith 14342...more >>

Whenever I issue an openquery to an Oracle database, SQL Server converts Oracle's 'number' data type to SQL Server's 'nvarchar'. Is there a workaround on this other than reiterating the Oracle select fields clause in the select .....from openquery fields clause and doing a CAST or CONVERT ? ...more >>

Can Local Variable Tables (Create @TempTable Table...) be indexed? Or can only tables created with the # sign be indexed? Thanks! -- Lucas Tam (REMOVEnntp@rogers.com) Please delete "REMOVE" from the e-mail address when replying. http://members.ebay.com/aboutme/coolspot18/...more >>

Hello: I was wondering if there is a way, within a stored procedure, to determine if the value associated with an input parameter of type uniqueidentifier is empty (i.e. Guid.Empty as assigned in C#). Thanks, Chris ...more >>

I must be missing something obvious. The following SQL Server 2000 stored proc is called by the included ASP.NET code. After the ExecuteReader command, the values of both output parameters always returns zero (even if I hardcode the proc to pass back some non-zero value). If I remove all o...more >>

Hi all, I am trying to understand the essential differences and benefits of one over the other of char vs varchar vs nvarchar. I know nvarchar is Unicode but is said to occupy double the space of non-unicode. Similarly variable-length columns like varchar take up more space than fixed-length? Ar...more >>

can anyone explain to me the benefit of creating/using temp tables with the '@' sign rather than the '#' for example: declare @temp table(_id int IDENTITY(1,1), user_id int) declare @count int declare @max int insert into @temp(user_id) select user_id from tUser set @count = 1 selec...more >>

How can I modify the query listed below to obtain the average value for the FullScan_Sec for the month of July with using only the time from 8 AM to 5 PM. The date_time field is sampled every minute. The date_time field is the only sample time field in the table. select avg([FullS...more >>

I'm new to the windows side of things and sql server. I'm trying to move a database in msde to sql server. The sql server is a shared hosting account and I tried to do a backup and restore but the restore gives me this error. User does not have permission to RESTORE database 'mydb' Is there...more >>

Is there a newsgroup specifically dedicated to SQL Reporting Services ? ...more >>

What's the easiest way to get the table list and the fields list for each table for the database having the owner's access level? The same for Viewers? Can we get this list from the sysobjects and just reorder it to make readable/usable? Just D. ...more >>

I have a reasonable knowledge of Visual Basic, but am completely new to SQL server. I am trying to write a few ActiveX scripts for use in DTS packages, but keep finding my code fails because I am using an undefined function.. ( I am obviously using functions which are available in Access and V...more >>

I'm listing grocery items in a table. How can I return the Min priced ITEM Grouping by ITEM (if there are ties, return all ties). There are duplicate values for each group of ITEMS (Paper Plates with Price $1, $2, $3). I need to return ALL the fields listed below. Can this all be done in ON...more >>

I have a field name Cust_Name which includes the lastname & the firstname of the customer seperated by a comma. How do I remove this comma using SQL? I have been unable to come up with a query for this. E.g. values are: Timothy, Dilton Anthony, Gonzales, Cathy, Francisco Marc, Johnst...more >>

OK, I need to get the count for records based on year. The years start @ 1982 n end @ 2003. There are over 132 mil records in the tbl. So? I know I can : SELECT DISTINCT DATEPART(YEAR, cc_date) As Yrs FROM SECC GROUP BY Yrs. And get a single record for each year, how do I get the counts for ...more >>

Hi Is there a way that I can save the results of a Select statements and any prints that happen in my stored procedure into a text file for analysis after it has been run by my App. Kind of like a copy and past of the results window in Query Analyser after EXEC a stored procedure. Thanks...more >>

For performance reasons I want to use sp_executesql for certain recurring SELECT statements, providing conditions via parameters. Each SELECT contains many conditions in the WHERE clause, with all values provided by parameter. It seems that I cannot use the same syntax if a parm value is nul...more >>

This is what I need to do: When a record is added in one table ( a person). I need to add a record based on this record(a person using primary key) to several other tables and populate these rows with data assigned to another person acting as a 'template'.Any advice. thanks, Joe...more >>

Hi, I lost all SQL Server database connections (about 10) right after installing XP Service Pack 2. When I uninstalled it, I had them back, and when I installed it again, I lost them again. I was using datasource setups for these ColdFusion pages and for each datasource, I connected to SQL S...more >>

Hi, SQL 2000 What is your recomendation for Index Fill factor? %0 or %90 What if a table is getting inserted every second and if a table is just for global information like ZIPCODE table ? Thanks, Patrick ...more >>

Hi, SQL gurus: I try to us trigger to monitor the changes made to a system table (sysobjects, syscolumns...). But I don't have a privilege to put a trigger on a system table. Oracle8.0 has a round way to approach this by using information_schema system view. Is there any aound way to ap...more >>

sql2k sp3 Howdy all. Im trying to write a UDF(my first one) that will retrun the quarter and year of the date thats fed in. alter function fnGetQuarterYear (@date datetime) returns int as begin declare @q int declare @yr int set @q =datepart(q,@date) set @yr = datepart(YYYY,@date) ...more >>

variable=zip my data type is int and lengh=4 and default value=NULL in stored procedure my type @zip int =NULL, i use an update query. when i call the procedure from my asp page the query also is written like this ,@zip='' but when i look at my database it was saved 0 not null.. w...more >>

Hi, Is it possible to pass a field name into a stored procedure as a parameter? I have tried this below but it doesn't seem to work. CREATE PROCEDURE dbo.[proc_rep_info] @begindate as datetime, @enddate as datetime, @field as varchar(50), @COUNT as integer OUTPUT AS SELECT @COUNT ...more >>

Hello, is it the sql server name has to be the same as mahine name? Thanks. ...more >>

Hi, Suppose if i have a clustered index on two columns fname and lname. Consider this as sample data fname lname Bob Bryan Charles Babbage Dan Schefield Robert Bryan Now if i do a search on fname i know the index will be used, but if i do a s...more >>

Hi I had created an SP that looked like the following: CREATE PROCEDURE sp_lookupWebInquiriesEmail @EmailAddress VARCHAR(75) AS DECLARE @exist INT SET @exist = (SELECT InqID FROM dbo.tblWebInquiries WHERE EmailAddress = @EmailAddress) IF @exist IS NULL SET @exist = 0 ELSE UPDATE ...more >>

Hi, Is it possible to make a composite primary key from 2 tables. Eg Table1 id1 Table2 id2 Primary key(id1,id2). Is it possible? Thanks ...more >>

Hi all, I'm some combination of stupid and stuck in the DMZ between systems. I'm trying to set up a linked server in SQL Server into a Visual FoxPro database and, of course, all documentation I can find gives the generic syntax ("@srvproduct = 'server product' ==> Enter the server product fo...more >>

Hi, Can some people tell me how they deal with apostrophe's in varchar column's At present I have created a couple of routines in my .Net code and replace the ' with alternative text and replace the alternative with ' when reading the text. Is there a better way to handle this issue? ...more >>

Hi all Can any one tell me if this sort of error hadling would work using the EXEC in a stored procedure. BEGIN TRANSACTION SET @sSQL = 'SELECT * FROM TABLE' --PRINT @sSQL EXEC(@sSQL) --This will run the insert IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTI...more >>

I have the following script that if I execute one step at a time, works fine. However, if I try to execute it all at once, it does not at the additional column regionID. Any thoughts out their? select * INTO #xl FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=C:\Proj...more >>

Hi Sql Gurus, Is it necessary to index on type tables or not ??? most of my type tables is less than 8 KB I found the following. Table with no Index is better than Non Clustered Index , Non Clustered Index is better than Clustered Index. Better in terms of Memory Usage , logical reads....more >>

Hi, I am trying to create a generic function which will accept a table-name and a column-name and will return a comma-separated string of the values in the named column. for example: SELECT dbo.GETCSVALUES('pubs.dbo.authors','au_fname') will return 'Abraham,Reginald,Cheryl,Michel,Inn...more >>

Please excuse if you feel this is posted in wrong group (I have posted in Access groups as well)... I do know there is at least some some cross-over base here that may help... I am trying to establish links from Ms Access to tables in a SQL server database. There is an Access table (tblLinkMa...more >>

I'm trying to figure out a way to (in a SELECT statement, if possible) join from one table to another, but to join based on a table value in the first. Let me give you a better example. We have three tables, tableSource, table1, and table2. tableSource has several columns, one of which is ...more >>

Hello, I've recently switched from Sybase to SQL Server, and am trying to do a join (that Sybase had no problem with) that SQL Server will not allow: 3 tables: tables: store store_att attribute columns: key *----- key att_code ----- at...more >>

Hello, i need to know if there is any wayto suspend a trigger execution until a commit trans. thanks...more >>

I have a table like this Create Table Incident (iIncidentId INT NOT NULL, iIncidentCategory smallint NOT NULL, iIncidentTypeId INT NOT NULL iSourceId INT NOT NULL, ) iSourceID points back to iIncidentId thru (PK-FK relationship) if iIncidentCategory is 13.Otherwise, it points...more >>

Here is my newbie question o' the day. In Access I can have a query like this: SELECT FieldName FROM TableName WHERE DateField > #8/1/2004#; and it works fine. But when I try this in the Query Analyzer I get this: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax n...more >>

Hi I would imagine there are standard ways to solve this scenario. I’m building a Vacation application using an SQL Server 2000 database with ASP.Net/C# client interface. I have 2 problems. 1) How to calculate the vacation duration (in days) given the start and end dates (less any ...more >>

Is there any penalties of shrinking Transaction LOG on daily bases schedule, in the night while no one is using the system? With no backup taken to it (BACKUP LOG ,,, NO_LOG ) clause. -- MCSD not .Net ...more >>

Can anyone help me with what should be simple, a trigger to insert 'username' into a column called 'LastChangedBy' whenever any column of a record is updated? I can get this to work when nominated columns are changed, but not sure how to do this when ANY column is changed....more >>

I have written a vb.net component for automatic distributed transaction. the code is as: Imports System.Data.SqlClient Imports System.Configuration Imports System.EnterpriseServices <Transaction(transactionoption.required)> _ Public Class ShabTransmitDataComponent Inherits Servicedcompone...more >>