Is there a benefit to using one over the other? Both give me the same result. Should I go with the function or the stored proc? ALTER FUNCTION f_PhraseCount ( @Phrase VARCHAR(512) ) RETURNS INT AS BEGIN Declare @PhraseCount INT SELECT @PhraseCount = COUNT(*) from SearchHistor...more >>

Hello, I'm entering an existing project that has hunderds of views. I was wondering how can I get usage statistics for the views - which ones are in use, which aren't, when were they in use, etc. Thanks in advance, R. Green ...more >>

I'm getting an "Arithmetic Overflow" error in the "LAST STEP" section in the DDL below. It's caused when I try to "CAST" a decimal and varchar field into decimal type. The reason behind these strange conversions is that within the production environment, the data that is being imported into...more >>

hi i can use SQL2005 Client Tools (lik vs2005) with SQL2000, to control and run SQL Statement? -- Best Regards Tark M. Siala Development Manager INTERNATIONAL COMPUTER CENTER (ICC.Networking) Mobile: +218-91-3125900 E-Mail: tarksiala@icc-libya.com Messenger: tarksi...more >>

I am trying to get the output from the below query for the past few days. Could someone take a look at this. May be different set of eyes might help. Here is the structure of the tables. ---------------------------------------------------- -- dbo.LANDING_PAGE_CONTROL ----------------------...more >>

Hello All! Here is my SP CREATE PROCEDURE dbo.AddWg (@UserID NvarChar(50), @Credits Nvarchar(50)) AS IF EXISTS (SELECT UserID FROM Bankroll WHERE UserID=@UserID) RETURN -1 ELSE Insert Bankroll (Credits, UserID) Values (@Credits, @UserID) RETURN @@IDENTITY GO I would like to be able to...more >>

Hello All, I'm in the process of learning T-SQL while using MSDE 2000 SP4 with Access 2002 and the NorthwindCS.adp database. I've purchased the "SQL: Access to SQL Server" published by Apress. Unfortunately the code for the chapter is not included in the download file from http://www.apress....more >>

The short of it.. is that I have a table ... with a PrimaryKey I'm doing a simple Update statement.... using the PrimaryKey as the only item in the WHERE clause. The update is on a field...which is indexed (non clustered) When I check the locking..... it is locking the table. (it has about 8...more >>

I need to do something like this. how would i do this? select * from t1 where c1=@c1 and c2=isnull(@c2,null) but i know this wont work because if @c2 is null it'll try to compare 'c2=null' which should never be true. so how would you do this? thank you....more >>

The KB below talks about 6.5. Does anyone know if this still is an issue for 2000, or 2005? PRB: INSERT INTO EXECUTE Holds EX_PAGE Locks on System Tables View products that this article applies to. Article ID : 162753 Last Review : February 22, 2005 Revision : 3.1 This...more >>

I want to give an application user rights to execute stored procedures on SQL Server 2000, and not access the tables except through the stored procedures. I thought this was how stored procedures worked. However, I have a few stored procedures with joins to other databases (on the same insta...more >>

I have a live, production db that I'd like to keep lean and mean for performance reasons. Every so often, I'll run a cleanup script to scrub out some older data in order to keep the tables from growing indefinitely and slowing my querying speed. But some employees would like to query ALL the...more >>

Hello, I'm tasked with exporting about 300 images stored in our SQL Server 8.0 database as blobs <Long text> and saving them as acutal image files. I've done some searching around and I've found something called TextCopy but I don't seem to have that on my computer (I searched for textcopy.ex...more >>

...more >>

I need to create a variable of type UNIQUEIDENTIFIER in order to later use INSERT to populate a table column of that type: DECLARE @guid uniqueidentifier SET @guid = NEWID() INSERT INTO xxx VALUES (..., ...., @guid, ....) For some reason this returns the error: Must declare the varia...more >>

declare @s varchar(300) set @s = ' recID = 100' Select * from tbl1 Where + @s This returns a syntax error. Is it possible to concatenate text like this to the Where clause? What does the correct syntax look like? Thanks, Rich...more >>

I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is nvarchar(8) type. I have 2 problems. 1. I need a way to concatenate the 2 fields into 1 datetime field with a select statement 2.In my example data below, you can see that dtTime is in a "military" time format. Is there...more >>

Hello, I tried the following: SELECT (CONVERT(NUMERIC,CreditAmtCash) AS CREDCASH FROM tablename and got: Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric Is there a way to scan or capture the values in CreditAmtCash (some of which are zero) and write ...more >>

I have a DECIMAL field, DECIMAL (30,16). I can't change this data type. Due to different import sources some of the data has 2 decimal places while some has 4. I assume this could create inconsistencies with calculations. How can I truncate the last 2 decimal places of data that currently has 4...more >>

Hello. I would like to generate raport from Reporting Services on SQL Server 2005. So I wrote the console application that uses the ReportExecutationServices class and generates the report to a file. It works good. But I want to execute this procedure by sql server. So I created an assembly i...more >>

I have a udf with a date column and I would like to be able to filter returned records by passing NULL as a criteria. I set the column default value to NULL. When I open the function and select either default or null from the drop down I get no records returned. This in not a data problem, I ve...more >>

I tried to convert an legacy C++ program to managed C++ and build a CLR function. However, too much compiler error. Is it possible to build a CLR function and call/link.. the legacy C++ code?...more >>

i have created a temp table and the last column in the create table statement has a column at the end. How is that possible?...more >>

Hi, I am trying to complie a dynamic columnlist to feed a query (so I can trun IDENTITY_INSERT to ON) using a Cursor. For some reason I can't get anything to happen...no errors, nothing happens. What am I missing??? DECLARE @CurrentDB VARCHAR(255) DECLARE @CurrentTable VARCHAR(255) ...more >>

Create Procedure stp_test @recID int = '%' As Select * from tbl1 Where recID Like @recID Go when I try to run the sp I get the error message that it can't convert varchar value '%' to column of data type int. Is there a way to specify a wildcard for a default value of type int? of...more >>

Does anyone know how to make this Update statement run faster? Currently it takes over 30 minutes; there are over 4,310,000 records that would be updated. Thanks for any suggestions. UPDATE ParcelAuthority SET PrevDue = lastYear.Due FROM ( SELECT PrcNr, AuthCd, Due ...more >>

Hi, I have a table that I'd like to add a new column and populate that column with data from a different table Table base info: #Row = 3.9M #Col = 105 columns #Index = 15 nonclustered indices Table source info: #Row = 5.1M #Col = 84 columns #Index = 0 Here's my update statement= ...more >>

I can't seem to find any place that a similar issue has been encountered, so here goes: INSERT INTO destination(dest_id) SELECT src_id FROM source WHERE NOT EXISTS (SELECT dest_id FROM destination WHERE dest_id = src_id) The subquery executes as expected when run by itself, returning [n] ...more >>

Why is "Manage Indexes..." grayed out in EM when connected to a Standard Edition a SQL instance, when right clicking on a view and displaying "All Tasks"?...more >>

I have a stored procedure that returns GUID and BIT datatypes (see below). I am using the VS 2005 TableAdapter.GetData to execute and return the values. BUT, I can't pass a null value for the GUID output parameter. - Why is it required to pass a value for an output value? - How do I retriev...more >>

I'm fairly certain the answer to this question is no, but I thought I'd ask anyway... Is there a parameter type that can be used as a list? Something like: Select Fields from Table Where SomeField In (@AList) TIA, -Steve- ...more >>

Hi, I need to do a calculations on an amount field between rows in one field in SQL Server 2000, i.e., (current_amount - previous_amount/previous_amount). Let's say we have the following: Quarter Amount Q1 200000 Q2 150000 Q3 125000 Q4 ...more >>

Have patience, I'm just a script kiddie. I'm trying to write a vbsscript that queries a sql 2005 database to see if a record exists and if so update some values and if it doesn't then insert an entry. On the If Exists(Select * FROM HDW WHERE UserID = " & strID & ") line I'm getting the following...more >>

Hi, Q: Is there a way to make the table's name in the from clause dynamic? I have a job (T-SQL statements) that makes copies of current month's selected tables to our archive DB. All copies will have a time stamp like TableName_yyyy_mm. What I always do is that I edit the T-SQL statements ...more >>

I am creating a package that can migrate databases from one server to another. One of my steps is to grab the logical file names of the source database. For this I have chosen "Execute SQL Task". I am using an OLE DB connection with the appropriate server and database. Using Direct Input I...more >>

What is wrong with my partitioned view? I have split a table into partitioned view by month (see below). Check the following query information --this is the original table select count(*) from activitydetailbackup where [datetime] between '1/2/2006' and '1/26/2006' --takes 35 seconds --...more >>

Hi I have three Databases in Sql Server Within Each Database I have the exact Table Definition which consists of the following columns CompanyID Branch Amount .. Is it possible to Create a View that would consolidate the same table of all three databases into a single view Thanks...more >>

I need to update a field so that if the field's empty, I'll update with 0. If it's a number, then I want to add one to it. But I want to do this without having to use a cursor. I'll also be updating field [create_date] with today's date. Is it possible? Thanks....more >>

According the documentation in 2000 and 2005 indexed views are only supported in the Enterprise edition. So please explain why I can create a unique clustered index on a view that is schemabound on the standard edition of SQL Server 2000? Is an index on a view different then and indexed view...more >>

Users will modify (add, delete, update) their configuration settings with the subset of all available settings. The more I think about about the effort which will have to go into figuring out whether the row is an update or insert, and then (if update) wheter the column value is valid the...more >>

In the lastest sqlmag, there is an atricle concerning database context switching. As an operational DB, this is something I've struggled with trying to iterate thropugh databases and objects. I unserstand why use db go select * from table go use db go doesn't work in dynamic SQL. T...more >>

Hi all, I am having issues of efficiency of backing up data from one SQL data base to another. The two servers in questions are on different networks , behind different firewalls. We have MS SQL 2000. On the source data i run a job with the following steps: 1> take trans backup every 4...more >>

Hi Each month I back up the structure of our databases by right-clicking on each database and select Generate SQL Script... >From the General tab I select script all objects and from the Options tab I select script database, along with script indexes and script primary keys. As we have s...more >>

I am trying to create a custom error message to be used with a trigger. I created a VB 6.0 app that displays a message box "this is an error." The trigger is as follows: CREATE TRIGGER Test ON [dbo].[IMITMIDX_SQL] FOR UPDATE AS exec master.dbo.xp_cmdshell 'start d:\macappssql\temp\pro...more >>

Hi, I hope someone can help. We are using SQL MSDE on a SBS2003 server for monitoring purposes. There is a small problem with one of the SQL processes leaking memory. I need to be able search for and kill a connection from the Firewall service to the master table. I am doing this as a s...more >>

Hi, I am probably doing somethig stupid, but I have two identical tables located in two seperates SQL databases. What I want to be able to acheive is that if an entry is deleted in one table a trigger will fire which will delete the corresponding entry in the second table. I have an identity...more >>

Hi all, Depending on a parameter in a sp, I have to do work either locally or remotely. The work to be done is the same in both case. I wrote: if @param = 1 begin transaction else begin DISTRIBUTED transaction /* do the work */ commit transaction Does anyone knows if Mic...more >>

Dear all, I have use the IDENITITY column as the only primary key for my table. And someone suggested me that I should not use the IDENTITY as the only key for the table. What is the problem that I use IDENTITY as the only key for the table? Thanks. ...more >>

hello, i want to create some unique entries (updates) in a column of a table with 2000 entries where ~ 1500 entries must be updated Tablestructure id int (primary key) FirstName varchar(50) LastName varchar(50) username varchar(50) i want to create some uniue entries for username - it...more >>

Aloha to all, I have thrown together some code that generates a file with a lot of data (something like 200,000 rows). I would like to load all this data in a SQL Server table, but my attempt to execute BCP from C# doesn't want to behave. I do as follows (more of less stealing everything fr...more >>

Every time I run DBCC to do an integrity check I get the following errors... and the first one i dont understand it says Expected value 0_PCT_FULL, actual value 100_PCT_FULL, I checked and it was set to 0... Msg 8914, Level 16, State 1, Line 1 Incorrect PFS free space information for page...more >>

Does SQLSERVER support fuzzy text searching(like the function of agrep). For example, given the input keyword [homogenos] and similarity parameter [2 characters], the function will find out valid result from datasource by either replacing,inserting or deleting upto two different charact...more >>

I have a text file of postcodes in CSV format that I would like to import... I am using SQL Server Express and do not have access to DTS. I would prefer to import the information as opposed to linking to it, what is the simplest way to get the text info into my table. Thanks in advance... ...more >>

Hi, I am trying to write a query that inserts a series of records based on a period of time from an active database to an archive database. Obviously I have to make sure the records don't already exist in the Archive beofre writing them. Here is the script I came up with...but it errors...more >>

Hi Group Transact SQL defines that int is: Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. This implies that only SIGNED integer values are possible with SQL-Server. I'm aware that from a data conversion point of view thi...more >>

hi i have downloaded sql cookbook. anyone know where i can get the code from that book so i can run the examples? thanks ...more >>

Please suggest good books for SQL Server 2005 Thx Much ...more >>

Good morning, I'm creating a data base SQL server as BE and access 2003 as FE. I can protect my front end and nobody can see tables, queries views, forms and etc. But if I open enterprise manager, I can see all tables, relations, views, SP, UDFs. How can I protect the back end? Thanks ...more >>

Hi Guys I am trying to append to a text (text data type) field in my database. Simply put, the Customer table has a field called Notes. I want to append some extra data to this field via TSQL. So for a Customer who has an ID of 1234, how do I append some extra text to his Notes field? The h...more >>