Hi there, I am trying to execute sp_executesql while producing the database name dynamically. The code look something like that: CREATE PROCEDURE [sp_dest_comp] @dest_comp varchar(20), @product_code varchar(4) Output AS Declare @stmt nvarchar(120) Decla...more >>

would like to know because of " Go " in the command below: ALTER TABLE clie ADD clie_ind_rest CHAR(1) NULL go UPDATE clie SET clie_ind_rest='N' go ALTER TABLE oper ADD oper_vl_cessao DECIMAL(17,2) NULL go Thank you! ...more >>

Is there some code I can use in an sp that would start sql server agent? If so, I'd like to put this in a startup sp in the master database. Tx for any help. Bernie Yaeger ...more >>

Hi! Could anyone check this stored procedure... It is not working. If I'll remove WHERE clause everything is working... Thanks! James CREATE PROCEDURE SP_Search @MinPrice Money = 0, @MacPrice Money = 1000000 AS SELECT Categories.*, COALESCE(Products.MinPrice, 0) AS MinPr...more >>

Hello all, I have the following col in the a table (DDL,DML included at bottom) My questions, before I state the example are: 1. Can this be done? 2. Even though there can be any number of "levels" of numbers. ie. <num>.<num>.<num>.<num>.<num>.<num> etc etc Example: --------- task...more >>

Why does the following fail the parser ? CREATE PROC [dbo].[GetQuestionAnswers] @QuestionID int AS DECLARE @TotalCount int SELECT @TotalCount = Sum(AnswerCount) FROM Answer WHERE QuestionID = @QuestionID IF (@TotalCount = 0) BEGIN SET @TotalCount = 1 END SELECT AnswerID, AnswerTe...more >>

Hi! I would like to make a select that retrieves only e.g. the 50-100th rows of the result. So, something like this: select [from 50 to 100] * from mytable select [from 100 to 200] * from mytable In Oracle I can use the ROWNUM pseudocolumn for this, such as: select * from mytable where ...more >>

Hi! How I can return 0 instead of NULL when using LEFT JOIN? Thank you! James ...more >>

About 50% of the search time is spent just counting the number of rows in the resultset. We page the resultset and show only one page at a time. Is there any way we can count the number of rows in the resultset faster? Casper ...more >>

Hi All, I need a help from experts. We have an vb.net app using sqlserver. I review the app and I saw a function declaring a connection, open, execute sp and close. This app runs once a day for couple of minutes. Now I suggest that this app should have one defined connection and in the program ...more >>

Hi group, This is a very general question about indexes and best practices, no DDL will be provided. There's a fairly indexed table with over 30mill records. Over 20 millions have been added in the last 2 weeks. I am noticing that queries that used to run really fast take hours now. I can't e...more >>

We have a process that takes XML files and split them into pdf's. These files then based upon a field in a table need to be faxed. The fax info is stored in a SQL table. The process needs to insert into the pdf control characters for faxing. Then this newly created file needs to be picked u...more >>

Hi, If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '), no problem. But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse" ') returns an error, because the word 'the' is in the black list. On my website, the users can check a checkbox if they want a s...more >>

I tried to write a batch script which copies a database by making full backup, then copying the backup file to another location and restoring it. The backup schedule of the my database is this: full backup on sunday at 23 h stored in file1 differential backup every day except sun...more >>

Hi! I have the following sp: ALTER PROCEDURE dbo.[Buscar Clientes] ( @Begin As DateTime = Null, @End As DateTime = Null ) In VS 2003, If i dont enter any value in the parameters, shouldnt both be Null? If i dont enter any value for the parameters i get the following error w...more >>

I am querying a view (as v) composed of a bunch of columns, e.g. EventDetailID, Descr(from Event), etc. Two of the underlying tables are: Event as e EventDetail as d ------------ ----------- EventID INT(PK) EventDetailID INT (PK) Descr ...more >>

Hi all, How can I achieve this? select col1,distinct(col2),col3,col4 from table1 TIA ...more >>

Hi All, Is there a way to found out size of the table? Thanks ...more >>

In the past, a user would run a Windows application that connected directly to the database. When that user made changes, we used a trigger to log those changes to a separate table. We used Current_User to record who made the changes, Current_TimeStamp to record when and values out of the ...more >>

Any help is appreciated. My problem is that I need to calculate an offset date using manufacturing calendar and number of offset days. It can be negative, 0 or positive. Given a base date I need to add, lets say, 7 days respecting planned working (manufacturing) days. I want to replace an ...more >>

I was wondering if i should create a lil program that could run in the tray showing status of jobs running on a SQL server. By that i mean if they are enabled, runnable and so on. Are there anyone in here that can lead me to some info related to that? I want to devlope it using C#. I have been ...more >>

Hi! Just a question. During an Insert, what does the lock optimizer lock by default? Is it a page? Or just the record being inserted? Thanks Antonio ...more >>

I have been trying to get a simple DTS to fire from a SQL Query. The dts just contains a Active X control that just writes out a log record, nothing fancy EXEC DEVBOSS.dbo.spExecutePKG @Server = 'ServerName', @User_Name = 'UserName', @PkgName = 'Test DTS' , @ServerPWD = 'PassWord'...more >>

Hi, I have a ASP.NET application which is hosted on English - based server. This application works with MS SQL Server, also English-based. I have this in my Web.config: <globalization fileEncoding="windows-1251" requestEncoding="windows-1251" responseEncoding="windows-1251" culture="bg-BG" ui...more >>

Hi, I have a stored procedure in database X that selects on a table in database Y. A user has execute permission on the stored procedure in database X. However, an "access denied" error is raise when the user executes the procedure unless the user also has "select" permission on the table ...more >>

SQL 2K I need to copy a 3 GB database across WAN . This db is with just one user table ~ 9 million records. What is the best method to acheive this. Thanks In Advance Smith ...more >>

This seems simple, but I'm stuck. SELECT count(*) AS duplicate_count, doc, num FROM fvd GROUP BY doc, num HAVING count(*) > 1 AND doc IS NOT NULL This first query finds all the records in fvd table which are duplicates (assuming doc + num is your unique record here). What I've been doi...more >>

Select tp.col1,tp.col2,tp.col3,tp.col4,tr.col5,tr.col6 from table1 tp (nolock) join table2 tr (nolock) on tp.col1= tr.col1 join table3 t (nolock) on tr.col_name = t.col_name where t.colname1= '00000022' and t.colname2= 1 table1 has abt 12 million rows,table2 has 2 million rows and table3 a...more >>

Hi all, In SQL Analyzer you can use this SET NOCOUNT ON What about the setting "Default results target"? under options in the GUI. That way in code I can set how I want the results from grid or text. thanks gv ...more >>

I would like to have really good search functionality on my website. I thought about using full-text search. If I had a database row in my table, with the "item name" value as "pineapples", and if I use full-text searching for the value of "apple", it does not find "pineapples", but if I use...more >>

I am trying to develope a tabulation Query in which the user will have a data entry screen and they can select all criteria or just partial criteria and query the database so that they can get a count of the number of records matching that criteria. I have set up a small sample query to ...more >>

I am trying, unsuccessfully, I think, to use an index. I have a table that is about 800,000 rows. I have an index on a field BatchDateTime. When I perform "select * where BatchDateTime>'9-1-2004'" it takes about 5 min. When I perform "select * where ServiceDateTime>'9-1-2004'" it also take...more >>

Hi, I have a question, should i validate my forms on a trigger (like: ON dbo.account BEFORE INSERT AS) Or should i use stored procedures (like: ( @Nome varchar(50) ) AS SET NOCOUNT ON IF EXISTS(SELECT Nome FROM BANCO WHERE Nome = @Nome) RAISERROR('This bank already exists. ...more >>

Hi, I need to copy just the new rows from a Oracle DB to a MS-SQL table. I created a DTS and I can get the information with no problems the issues is that I only want to insert the new rows or records created. Any tips ...more >>

We are using OPENDATASOURCE to select data from an Access db using the MS Jet OLEDB provider. My question is regarding the authorities needed to execute this command. When I run this command using windows authentication or integrated security everything seems to work fine—even with users t...more >>

Hello, I was wondering what the best way is to use lookup tables. i don't want to use a new table for every lookup. Basically what i want is a generic lookup table for all kinds of data. In queries, to translate the integer value of the lookup, it should know which description it should ...more >>

I have the same question that was posted by someone else back in 1999. The suggestion to use SYSTEM_USER - doesn't work - it returns the computer name of the user connected. And my current trigger's use of USER_NAME always returns dbo. The SQL 2000 database table has a trigger that should b...more >>

We are currently having a problem with a load process in SQL 2000. A bit of background: Our process works offline to sort through client data and normalize to our structure. Throughout most of the process, there is no interaction with the database, as everything is manipulated as files. Whe...more >>

Dear all, I am not be able to delete a row in the following table: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSysRights]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tblSysRights] GO CREATE TABLE [dbo].[tblSysRights] ( [sinRightID] [sma...more >>

I have a UDF that when I run by two different users, gives two different orders of data columns. One follows the syntax of the function and the other is mis-ordered but returns this way consistently. Has anyone else encountered this?...more >>

Hi I am running a T-SQL script on a SQL2000 using OSQL. I want the script to return run status that easily can be picked up from a C# application (no text only a number should be returned). The guy coding the C# app don't want me to use the RAISERROR statement, because it comes with a...more >>

Hi I have the following table to allow centres to be related to each other, the concept is a parent centre or head office for a bunch of centres: tblCentre ( CentreID PK, CentreName, ParentID NULL) With a FK constraint defined between ParentID and CentreID First, as the parent relations...more >>

I am trying to develope a tabulation Query in which the user will have a data entry screen and they can select all or just partial criteria and query the database so that they can get a count of the number of records matching that criteria. I have set up a small sample query to test but it...more >>

Hi, I need to run execute an heterogeneous dynamic query (I build the query using nvarchar variable). Code sample : CREATE PROCEDURE COPY_DATA (@ServerName nvarchar(20), @DbName nvarchar(20),@TableName nvarchar(20), @DateDeb DateTime, @DateFin DateTime) AS -- Build SQL Query -- Se...more >>