hey thax for ur suggestions. One more doubts are: 1) when i partition the cube it will refresh only 1 partition. 2)drop an old partition from the cube 3) add new partion to the cube. The abv tasks can we done throw storedprocedure or other ways?? how?? pls gv ur valuable suggns. than...more >>

I'm trying to understand materialized view and how it can improve performance. I have the impression that the result of the expression: RTRIM(LTRIM(UPPER(REPLACE(REPLACE(REPLACE([Name],SPACE(1),''),'.',''),CHAR(3 9),'')))) is stored in the view, and when searching on the view, the expre...more >>

I have a question that some of you nice folks might be able to help with. First here is some quick test data. In the real data there will be a lot more columns in each of the 'employee' and 'department' tables though: ------------- CREATE TABLE [department] ( [id] [int] NOT NULL , [departm...more >>

hello, I processed the cube(dts package) in stored procedure.It is working fine (using dtsrun) as following. "exec MASTER..xp_cmdshell'dtsrun /s@server /u@user /p@pass /n@pa ckagename" I am reading the server " @@servername",user "system_use", from the system..but for database(sa) pas...more >>

Hi I am writing one function, but I am facing one error, can any one suggest me how to write this type of function? Tia Kishor Pis kpise@rediffmail.co Mumbai CREATE FUNCTION getFormatTime ( RETURNS dateTim A BEGI RETURN ( getdate() EN Server: Msg 443, Level 16, State 1, Procedure get...more >>

Hello For one aplication, I need to trace every modification made on a database: 1. changes made to the schema (new tables, new fields, field removed or renamed ...) 2. changes made to record content (for ex. Field xxx changed from 'Hello' to 'CouCou') What kind of stored procedures cou...more >>

Hi, I am trying to batch processing stored procedures with SQL 2000, MDAC 2.8. All settings are default. SQLSetStmtAttr is called with SQL_ATTR_PARAMSET_SIZE set to the number array elements. I am using ODBC CALL escape sequence. I noticed from SQL Profiler trace that the stored procedure...more >>

Hi, I need to have something like a "login event" so that whenever a user logs into SQL server, I can execute certain commands or SPs. So far, I've been using this event in my client app that when a user clicks on the login button, I do those jobs. But I want SQL server to handle and perform the...more >>

I have two tables (emp, dept I don't know how to select the top n salary from the employe The top n should be passed by a paramete Thank you very muc CREATE TABLE emp empid INT PRIMARY KEY deptid INT NOT NULL salary decimal(9,2) NOT NUL CREATE TABLE dept deptid INT PRIMARY KEY d...more >>

Why: DECLARE @myContent text is not allowed in stored procedure? It gave me an error message: "Error 2739: The text, ntext, and image data types are invalid for local variables". I know I can CAST it to varchar, but what if the length of my text exceeds 8,000 characters? Any solu...more >>

Hi, I have an app that uses SQLServer as its back end. Data retrieval and insertion is done via stored procedures. A prospective customer wants an Oracle back end. The app is vb.net 3 tier. It seems to me (naively?) that it should be a matter of translating the table structure and the stored ...more >>

Hello All, My data looks like the following: ResID PlannedWork AvailWork workdate we_date 6 8.0 8.0 2004-02-18 00:00:00.000 2004-02-20 00:00:00.000 6 ...more >>

This trigger works perfect. But my issue is, I want one trigger that can detect whether it was fired by an UPDATE, INSERT or DELETE so I can perform the proper DML statement. Im trying to avoid creating a separate trigger for each action. I want one trigger because I have to apply this to 30 differe...more >>

Hi, I have a table in a SQL2000 database with a grouping column, let's call it 'dgroup' and an allocation column, let's call it 'allocatedto'. dgroup is a smallint, allocatedto is an int. I have 13,000,000 record in the table. Every one of them has dgroup = 1 at the moment (will change lat...more >>

Hi All, We program intensively in TSQL / stored procedure, many business logic is in there. Is there any documentation tool that can properly / precisely dedscribe the flow of TSQL programs / stored procs ? Thanks for any help, Krist ...more >>

I created a procedure : CREATE PROCEDURE TEST @identifier varchar(10) AS SELECT AVG(a.gro12) AS "5yr" , MAX(b.gro12) AS "12M", CASE WHEN MAX(b.gro12) > AVG(a.gro12) THEN 1 WHEN MAX(b.gro12) < AVG(a.gro12) THEN -1 ELSE 0 END AS "Color" FROM (SELECT * FROM table1 WHERE end_dt IS NULL...more >>

I am trying to create a stored procedure if one does not exist, the following code does not work. Error message: Incorrect syntax near the keyword 'PROCEDURE'. if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAttachments]') and OBJECTPROPERTY(id, N'IsProcedure') ...more >>

I would like to use the xp_cmdshell sp to run a dir command on a directory and then save the results in a single column table. I am guessing that I could do a dir > files.txt and then do a BCP import of the text file but is there anyway to do this directly?...more >>

I am importing data from an excel file to sql server2k using the OpenRowset Method call from an Access mdb. One of the columns in the excel file is the ID column and consists of numeric text and gets imported as float. The same ID column in a table in sql server that I have to join the e...more >>

Hi folks, I am coming over here to SQL Server from the Oracle world and am now learning SS2K. I have built several stored procedures and several user defined functions and am sorely dismayed at the capabilities, or lack there of, of the editor that is used in SS2K's enterprise manager to construct ...more >>

Hi Freinds, I have a SP which returns me a 10 column table. How can I select them again just by runing that store proc. somthing like select * from exec mysp 'par1,'par2' I have to do this within a SP, means main SP returns a table by runing second SP How can I do it? Thanks in advan...more >>

Hi Guys I need some help with query. I have a table Tagdatahourly with hourly data for different tagkeys: Tagkey HoulyDatetime Value 1 2003-01-01 00:00:00 12 1 2003-01-01 01:00:00 13 1 ...more >>

i got processes locked sometimes. when i looked at the properties, i found out the process running the following sql statement 1. an embedded Insert sql statement which is running at C# program. transaction statement has been used 2. a stored procedure which will select records from the same...more >>

Hi I am trying to create a temporary table for reporting purposes. My vb.net code collects various selections based on the choices made it then generates a Temporary table containing the results ##Temp12345. The 12345 is a random number.This name is passed to another aspx page that loads the t...more >>

I have a table with an int column that may contain nulls. I want to sort the results of the query in ascending order on this column - but with the NULLS coming after any rows that are not null. Is there an easy way to do this - other than breaking up the query into two queries UNIONed together (w...more >>

I need to properly create datatype on my table so when my VB App inserts time into it it gets formatted properly: i need it to store : HH:MM PM When i create datatype smalldatetime and trying to insert 1:24 PM it gives me an error that datatype is wrong. How can i do that? ...more >>

Hi can I use Elseif in T-SQL? If so what is the correct syntax. I can not use Case When statement because I am running a complex query depending on what is passed into a stored procedure variable....more >>

Hi, This is probably simple, but my mind's gone blank with getting all the = other code sorted. I'm putting together some forum software for my = ASP.NET site (I know there are free ones, but I want to understand it = from the ground up code-wise). The Forum groups are nested tree-style, = us...more >>

I want to add a "Calculated" column to a view that basically auto increments for every row returned from the view starting from 1 and incremetning by 1, does someone know if this can be done in a T-SQL statement... I was thinking of maybe cast something as an "int identity" but I'm not sure what ...more >>

I'm not even sure you can do this within SQL but I figured I would try... select id, year from table where id In(1800,20,1750) and feesfiscalyear IN ( '2002', '2001', '2000' ) The above statement will give me all combinations of ids and years. But, I would like to match up id 1800 with ...more >>

Hi all. First of all, thanks to Tibor for helping me with my Alter Table problem yesterday. I've restructured the script I was using it in and it has since worked. Which leads me to my question for today. Does anyone know of a good online resource for SQL scripting? My knowledge of base ...more >>

Hi, I am having some trouble with the following code: SELECT @sql = @sql + EXEC sp_MyStoredProc @a, @b OUTPUT Whenever I try to run this, I get an error saying Incorrect syntax near the keyword 'EXEC'. I need to concatenate the results from my other SP with the @sql. Any suggestions wo...more >>

Hi, I have connected 2 SQL 2k Servers (both SP3a). When i make a query to a table with an unique key, i receive an error like this Server: Nachr.-Nr. 7319, Schweregrad 16, Status 1, Zeile 1 Der OLE DB-Provider 'SQLOLEDB' gab einen NON-CLUSTERED and NOT INTEGRATED-Index 'UK_Konfiguration' mi...more >>

I am doing something that I thought would be pretty simple. I have a field that contains a month and day (ie 1231, 0131). I would like to display the first three characters of the month. I have tried dateadd and datepart but it seems that I need a valid date (dd/mm/yyyy). Could anyone hel...more >>

Hi I am trying to select some database fields and a variable into a temp table as below. declare @test varchar(12) select @test = 'Yes' select @test, Field1 into ##test from Table1 but get the error 'No column was specified for column 1 of '##test'' Can anyone shed any likght on thi...more >>

I have two tables. One table is a master table, another is a child. Child table has a field that stores a record id from the master. I am thinking to have a stored procedure that will be called to process multiple records in the master table. How will it work in case of new master records? ...more >>

If a table exists in Sql Server I want to drop it --- with a call from an Access mdb. Here is my pseudo code cmd.CommandText = "If Exists tblX Drop Table tblX" cmd.Execute Obviously this does not work. If I just say "Drop Table tblX" then this works. But if the table is not there ...more >>

Hi, Can a database be recovered somehow if it was accidentally deleted from the Enterprise Manager ? And obviously there is no backup. A ...more >>

what, how, when does a logical file name is determine for a database? If I am restoring a db from Enterprise Manager, under the Options tab, after I configured the device to restore from (a file), I can see the logical filenames of both, the mdf and the ldf files. Now, I am automating all this...more >>

hi, i am trying to get xp_sendmail to work, when executing the following simple commands from the BOL; use master go EXEC xp_sendmail 'robertk', 'The master database is full.' i get the following errors Server: Msg 17985, Level 16, State 1, Line 0 xp_sendmail: Procedure expects par...more >>

Please enlighten me - I have an app which updates a row column to zero, under known certain circumstances. The app code is too convoluted to change. What's the best way to take an incoming update statement that has a value zero, and change it to NULL instead? A trigger, a constraint? tha...more >>

It's been a while , but I remember that I could get the info from a highlighted row in a grid. Can anyone tell me the property to use, I don't see any that indicate I can do this. I need to work with highlighted grid. Bruce Gilbert sofiyacute@msn.com...more >>

Hello, I have the following trigger working on a table in MS SQL 7; --------Begin Trigger-------- CREATE TRIGGER [tr_Test] ON [dbo].[Test] FOR UPDATE, INSERT AS DECLARE @Abbrev nvarchar(3), @Name nvarchar(50), @Pwd nvarchar(15), @Type nvarchar(10), @Cmd nvarchar(1000) IF (UPDATE(Pwd...more >>

Hi All, I have installed MSDE 2000 server and i installed service pack 3a. The server machine has SQL Server 2000 client connectivity. When i connect from client machine to Server machine it is showing error. [Microsoft ] [ODBC SQL Server Driver] [TCP/IP sockets] SQL Server does n...more >>

Greetings; In the following example, I want the DELETE to NOT get rolledback if the xp_sendMail file for some reason. How can I assure that the DELETE does not get rolled back if the sendmail failed? -TIA, John CREATE TRIGGER [tr_iGating] ON [dbo].[Message] AFTER INSERT AS BEGIN D...more >>

Hello All I have a sql table which has information like Area Plan EmployeeCnt Coverage 1 A 2 S 1 A 1 F 1 B 1 F 2...more >>

Hi, I hope someone can help. I have a table that stores product quantity week by week like the following: Weekid ProductNumber Quantity 1 1234 5 2 1234 10 3 1234 12 4 1234 9 Now the only way for me to work out how much was received or sold on a particular week is to subtract the q...more >>

Hi, We have an application that acts as a database gateway to SQL Server. The application uses Microsoft ODS service and client connect to the SQL Server 2000 database through it. Every query that client wish to execute first go to this gateway application.On receiving the query the gateway e...more >>

Hi everyone, I've got what (I hope) is a fairly easy question. I'm returning a couple of columns in a select statement, and would like a third column showing the current record number. The first record is #1, the second is #2, etc. Can someone reccomend a way to do this? Thanks! ...more >>

I have to convince management as to why we should invest in SQL database rather than VFP. Is there a credible comparison of the two (such as Microsoft, or another credible entity)? I'd appreciate any pointers. Thanks, John ...more >>

Hi all, With the number of records in the "case data table" and the number of fields in the "tblDenialReports table" it is taking over 2.5 hours to run at night. Is there a better way or optimize this process faster? Thanks CREATE PROCEDURE swpro.sp_DenialReporting AS INSERT INTO t...more >>

Hi I've got a 2 tables, and the second has a foreign key referencing the first table I want to use "on delete cascade" in the definition of the second table, but it rises an error (syntax error toward the key word 'ON'). I don't understand because I use Microsoft SQL-server 2000 (SQL 8.00.194) T...more >>

hi, I have a table with 5000 rows. I add a new column sequenceNumber.. Now I want to fill it up with an unique number, so that each existing record receive a number. declare @number as integer update tableA set SequenceNumber = (select number = number + 1) That don't work , How...more >>

ms sqlserver(2000-SP3) stopped.and we dont now why?. sqlservice log :"Server shut down by request." event viewer(application log):6006 :Server shut down by request. before this events there is no logs about it, before above message there is differential backup executing message(but thi...more >>

Hello Group I have a nvarchar column that accepts Unicode characters, I entered Unicode data and I can see data from Enterprise Manager. but when I want to select it doesn't work. select * from my table where filed = 'here is Unicode string' I know it's not possible to compare text and ntext b...more >>

Does anyone know how to select a tables permission and apply them to another table Ex if object_id('test') is not nul drop table test g if object_id('test_2') is not nul drop table_2 test g create table test (a int g deny SELEC ON tes TO publi GO select into test_ from test I...more >>

Is it possible to create a view inside a stored procedure? I have to create a 'virtual table' to be used with MS Access (OfficeXP). I don't have experience with Access much but I know you can import VIEWs, however not stored procedures to work with. I have an SQL query that returns multiple ...more >>

Hi, I'm reciving the following message, queryng my SQL Server Database. Is this a SQL Server Limitation ? Is there any parameter that solves this problem ?? Joined tables cannot be specified in a query containing outer join operators. View or function 'dbo.SGP_V_PROJECTS' contains joined tabl...more >>

Have two tables, one called ExchangeRates with Country, Month and (Exchange) Rate fields, the other called ExchangeRatesLastMonth with just Country and Rate fields. The latter table needs to be updated so that it just contains one month's values from the former table (the month being the last month...more >>

Hi, Here are the ddl for the two tables: Create table String( StringId int identity(1,1) primary key, MaxLength int, EnglishString varchar(30)) Create table String2(StringId int references String (StringId), TranslatedString varchar(768)) What I am looking for is: 1. Defining a c...more >>

hi all i would like to know where exactly the view definition will be stored which is used by the sql Engine. not that is that is there in syscomments table thanks ...more >>

hello as u suggested I run my proc as below: CREATE proc base_proc1 @pass varchar(30) as begin declare @server varchar(30) declare @user varchar(30) declare @dbname varchar(30) declare @package varchar(30) declare @sql nvarchar(1000) select @server= @@servername,@user=system_user,@db...more >>

Hi, I want to update one particular row in my table (send it into history, to see changes and create new one (actual), almost identical, with some changes Is there any better way how to create new, identical record, than INSERT INTO MYTABLE (all columns) VALUES (almost all old values, just some...more >>

Our company currently uses a third party software developer to conduct our business. All of our business is done over the internet. The problem (may or may not be) is our tables are not linked (relational style DB). They are only linked by the actual code on our website when a transaction occurs ...more >>

Hello, I am getting Arithmetic error on the following statement is ASP to Sql "SELECT dealership,id,zip_code " & _ "FROM mallusers " & _ "WHERE zip_code in ( SELECT ZIPCODE FROM premium "& _ "WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(Latitude/ ...more >>

Hello Experts, I come up with another issue: If I rename a field name in a table that has dependencies (Views, stored procedures...), I would like to check the syntax for these objects and list which are "invalid". Thanks in advance. ...more >>

Hello Experts, Doesn't DBCC CHECKTABLE checks-repairs views? It must be an indexed view? Thanks in advance....more >>