Database Newsgroups: SQL Server database programming

query

x taol - 8 Jul 2006 10:23 PM - 6 messages

fNum fNal fPum fIn fOut fStock 1 2006/05/06 p1 9 1 8 ...

GROUP BY question

Mike9900 - 8 Jul 2006 3:52 PM - 7 messages

I need to return certain rows that I do not want to be grouped by. Is there a way to do so? Here is how I want it. SELECT DISTINCT T.TID,T.Date,TD.PaymentType,(T.Debit - T.Credit) AS ...

Case sensitive vairable names

Jesse Houwing - 8 Jul 2006 1:24 PM - 8 messages

Hey all, We're working with two teams, working on two different SQL Server instances. As it turns out the first server will accept the following: declare @NUMBER int set @Number = 1 The second server is more picky however. It will only accept: ...

Job category not visible

VSS - 8 Jul 2006 7:30 AM - 2 messages

I have added one new category using , sp_add_category, but when i open to add new job, category is not available in dropdown of catgory in the new job dialog? I used following tsql for adding categroy: EXEC sp_add_category 'JOB', 'LOCAL', 'ABC' ...

PARTITIONS

joeydj - 8 Jul 2006 6:00 AM - 2 messages

i created a wrong left-ranged partiton function named wrongpf wrongpf is being used by partition scheme ps1 to partition tablex to 24 partitions. to rectify the error I create a new partition named rightpf testing the data using $partition function shows that the set of data that ...

Need a pros advice here

Chris - 7 Jul 2006 11:06 PM - 17 messages

Hi, I have a table with 8 columns and 500000 rows and no primary key. col1,col2,col3,col4,col5,col6,col7,col8 col1,col2,col3,col4 makes a row unique. Should I combine these 4 columns to make a primary key or should I add another column at the beginning and set it ...

Design question TransactionHeader and TransactionRows

Edgard L. Riba - 7 Jul 2006 10:42 PM - 2 messages

Hi, I was wondering if I could get some opinions on how to design the following. These will be two tables to store different kinds of inventory transactions (e.g. purchases, invoices, warehouse transfers, adjustments, etc...). The tables are called iTrans and iTransRow. iTrans = inventory ...

Using Query Analyzer with SQL Server 2005 and sprocs

Alan Samet - 7 Jul 2006 9:34 PM - 3 messages

Just thought I'd share the following procedures with the group. One of the issues with using SQL Server Query Analyzer (2000) with SQL Server 2005 is that you can't view stored procedure source (unless there's something that I'm missing or have misconfigured). I've created the ...

How to query for the DefaultData and DefaultLog

Vidya - 7 Jul 2006 8:51 PM - 2 messages

Hi, I need to find the DefaultData and DefaultLog property of SQL Server (both 2000/2005). How do you do that? I would interested in 1) Registry Entries 2) SQL Query to retrieve the option (since SQL-DMO or SMO is not an option) ...

Datatypes

Robert Bravery - 7 Jul 2006 8:39 PM - 3 messages

HI all, What select statement can i use to return all the availabe datatypes, so that I can populate a combobox Thanks Robert ...

How is a byte array retrieved?

ChrisB - 7 Jul 2006 8:33 PM - 4 messages

Hello: I am using SQL Server 2000 to store a byte array in an image datatype. // Example. String stringValue = "TestString"; Byte[] testValue = new Byte[stringValue.Length]; UnicodeEncoding unicodeEncoding = new UnicodeEncoding(); testValue = unicodeEncoding.GetBytes(stringValue); SqlParameter[] Parameters = new SqlParameter[2]; ...

Where can I get an explanation of AdventureWorks database

HONOREDANCESTOR - 7 Jul 2006 8:00 PM - 4 messages

I'm taking an online course from Microsoft, and it uses the AdventureWorks database. But it doesn't explain the fields of the tables. Is there any documentation anywhere that does? Thanks. ...

Combine two rows HELP!!!

tao_benz - 7 Jul 2006 7:32 PM - 6 messages

Hi: I have rows in the table like following: A | B | C | 1 | a A | B | C | 1 | b A | B | C | 1 | c A | B | C | 2 | f A | B | C | 2 | c A | B | C | 3 | p A | B | C | 3 | e ...

Northwind database extra SQL needs

soalvajavab1 - 7 Jul 2006 7:30 PM - 3 messages

I have asked for the following questions and I need your advises. Utilizing the Northwind database suppied with SQL Server, create SQL to solve each of the exercises listed. 1. I want to contact all customers who have received over $1,000 in ...

Wrong Number of Rows with Correlated Subquery + Group By

mbrierst - 7 Jul 2006 6:49 PM - 6 messages

While working on calculating medians for a table (in SQL server 2000), we ended up with a query like the one below. The query as written returns one row for each row in the temp table (7 rows total). We just ...

Query analyser syntax color

Ganesh - 7 Jul 2006 6:43 PM - 4 messages

Hi There, My query analyser not showing any colors, always black and white only. I checked editor preference that looks in different colors. And also when i paste any text to the editor i can not see the text just ...

Microsoft stored procedure aspnet_Users_DeleteUser questions

nkw - 7 Jul 2006 6:35 PM - 2 messages

The following stored procedure is used by asp.net 2.0 form athorization part. I think every line of this important stored procedure should exist there for a reason. However I don't understand some code lines.... I insert comments ...

HOWTO: Mimic a constraint from a trigger?

Ian Boyd - 7 Jul 2006 6:33 PM - 35 messages

Am i right in assuming that you cannot simulate a NOT NULL, FOREIGN KEY, CHECK or other CONSTRAINT from a non-"instead of" trigger? Using raiserror will not prevent the modification from happening, and using ROLLBACK TRANSACTION inside a trigger will cause the transaction to be rolled back. ...

sysfiles and sysfiles1

Bilal Abbasi - 7 Jul 2006 5:43 PM - 4 messages

Why would the number of rows be different between these two tables? ...

Table function question

VC - 7 Jul 2006 5:33 PM - 3 messages

Folks, I have a vendor-provided stored procedure and in the procedure, they are repeatedly using a UDF in their SELECT statements, similiar to this: SELECT x.* FROM dbo.FT_lookup(@Id,@WId,@EId) x WHERE Type = @Codes ORDER BY Desc ...

Select data between braces []

Dan Shepherd - 7 Jul 2006 5:20 PM - 10 messages

I have a column (char 255) that has data between braces. I want to select only the data that is contained within the left and right brace. An example of the data is below: select sequence, psn, hazardclass from xhazwaste returns ...

Get Max Value and Second Max Value

Reddy - 7 Jul 2006 4:54 PM - 8 messages

Hi All, Here is my example table table name: weights table wt_id wt_value grade 11 21 a 11 23 c ...

SQL2005 FullTextSearch Error.

SouthSpawn - 7 Jul 2006 4:33 PM - 2 messages

When I attempt to add a new FullText Index in SQL2005. I get the following error. "Property Default Path is not available for FullTextService". I am trying to do this on my "Local" database and I get this error. I have ...

subqueries in case statements

jbennett via SQLMonster.com - 7 Jul 2006 4:29 PM - 3 messages

is this possible to do. i have a subquery that pulls in a bunch of customer numbers for customers that ordered a certain part. if they did i need it to say yes if not then no. i think there is another way to do it but i would ...

@@rowcount without displaying select

peter walker - 7 Jul 2006 4:22 PM - 7 messages

Hi, im hoping someone could shed some insight. If i do the following in a trigger: select * from <table> where <condition> if @@rowcount > 0 begin <perform tasks> ...

How Can I access SQL Server SMO Objects in VB6

ANeelima - 7 Jul 2006 4:11 PM - 2 messages

I tried to make COM callable wrapper (CCW) for SMO using the following command line execution using RegASM.exe After executing the command line RegAsm.exe, I got the following output. ------------------------------------------------------------------------------------------------------ Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.42 ...

DDL and DML in same stored procedure

Bodhicitta - 7 Jul 2006 3:47 PM - 7 messages

When executing the following stored procedure (EXEC dbo.AAA), I occasionally get binding errors. Is this because TSQL is not a procedural language and therefore the SELECT INTO TABLE C statement will be executed before the SELECT INTO TABLE A statement has completed? If so, what are the options for ...

Efficient update of column based on earlier records.

lmcphee - 7 Jul 2006 3:35 PM - 4 messages

I have a table defined as Create MyTable ( IncidentID bigint IDENTITY(1,1) Primary Key, Tag varchar(32) NOT NULL, Tm datetime NOT NULL, ...

performance: table value UDF vs view

nkw - 7 Jul 2006 3:29 PM - 4 messages

I may use one of the following two method: 1. table value UDF: select .... from dbo.udf1('p1','p2') inner join ..... 2. view select .... from view1 inner join ..... where view1.p1='p1' and view1.p2='p2'... which one has better performance? Or the same? ...

Deadlock

Marc - 7 Jul 2006 2:57 PM - 13 messages

Dear all I use a view and a function to get data from a transaction system whitch use only select commands. Can it be that this view and function can lock tables? And what is the use of the command: select * from v with (nolock) ...

OpenRowSet doesn't import first row in worksheet

Pete - 7 Jul 2006 1:57 PM - 3 messages

After having tried to use the new SSIS program in SQL 2005 and failed, I resorted to using the OpenRowSet command in TransactSQL to import a worksheet in a Excel2005 workbook. The command works to a point. For some unexplained ...

Transfer between databases

SteveWalker - 7 Jul 2006 1:50 PM - 2 messages

Hi, I'm writing a system which uses several different SQL Server databases, one for each year the system is used (the system was laid out before I started on the project so can't be changed!). I need to write a procedure to transfer ...

UPDLOCK + TABLOCK = Exclusive lock??

ChrisOdd - 7 Jul 2006 1:27 PM - 2 messages

I've run into some surprising (at least to me) behaviour in SQL Server 2000. I have a table Customer, with PK = CustomerNumber (int) When running in isolation level READ COMMITTED, I do the following in query manager: begin tran ...

problem with combining data (join /union)

Purva - 7 Jul 2006 1:01 PM - 2 messages

Hi all I have following situation. table a contains sales data about a product. table b contains marketing data about the products. now suppose table a contains 3 rows for a product and table b contains 2 rows for the same product . ...

smalldatetime conversion error

marcmc - 7 Jul 2006 12:16 PM - 8 messages

I get this error when i run the below: Server: Msg 296, Level 16, State 3, Line 1 The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. I have isolated it to the 3rd line in the where clause but cannot find a ...

Updating Tables in SQL Express

dario.dee - 7 Jul 2006 11:24 AM - 20 messages

Hi, I am trying to find a way to update tables on SQL Express. I have replicated a database to a local database from a remote database using linked servers but i want to be able to update the local database with ...

drop a not null field

Lorenzo - 7 Jul 2006 8:36 AM - 6 messages

Hi to all, sorry for my bad english. I need to remove a field that has been created in this way: Alter Table "tabella" Add "NewFldName" char(10) NOT NULL DEFAULT '' I tryed with: Alter Table "tabella" Drop Column "fieldName" ...

Switch database in a managed code stored proccedure

Nick Prudhoe - 7 Jul 2006 8:29 AM - 2 messages

Is there a way to swicth databases when in a managed code stored procedure? Obviously I could create a new ADO.NET connection but I'm assuming that would nullify the advantages of using managed code SPs as you are going out to ...

Creating Database using DMO

Usman - 7 Jul 2006 7:28 AM - 2 messages

Hi I have to write an application that can create Database on a server that may be located locally or remotely. To take the best scenario, there can be multiple intances of MSDE's installed on a remote machine and each instance ...

Cursor Question

Chris - 7 Jul 2006 4:12 AM - 7 messages

I have a cursor that pulls the name of the tables one by one on my server and I need to use it in a sql statement to compare the counts to the same table names in another server. How can I get the Cursor that holds the name of the ...

[SQL2000] How to retrieve this value in activex?

Sphenixs - 7 Jul 2006 3:00 AM - 3 messages

This is the code where I retrieve the value from the task '********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() Dim DTS, ExecSQL, SQLStatement Set DTS = DTSGLOBALVARIABLES.Parent Set ExecSQL = DTS.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask DTS.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask.connectionID = 4 ...

COALESCE and data types

Vern Rabe - 6 Jul 2006 11:33 PM - 7 messages

What are the steps that occur during a COALESCE execution? The following script exemplifies my question: DECLARE @c char(10), @v varchar(1); SET @v = '5'; SELECT COALESCE(@c, @v) + ']', DATALENGTH(COALESCE(@c, @v) + ']'), LEN(COALESCE(@c, @v) + ']'), ...

INSERT raws from another table

Hitesh - 6 Jul 2006 9:51 PM - 3 messages

Hi, I have two almost identical tables in two diff databases... let's say tbl1 and tbl2. The only diff is that tbl2 has one more column called date. I want to insert any raws that is not in tbl1 but exists in tbl2 and ...

Update statement too slow .....

Balaji Rajaraman - 6 Jul 2006 9:35 PM - 4 messages

All, In Sql Server 2000, Update statement in a stored procedure is too slow. The table holds more than 30 milliion records and i am trying to update the records.For updating the records i am joining an another table ...

Stored Proc: Re-Assigning Parameters to declared variables

honusbam - 6 Jul 2006 9:29 PM - 3 messages

I have a proc with three parameters: @tmpStartDate1 datetime, @tmpEndDate1 datetime, @tmpSkuNbr1 nvarchar(15) When I run the query with hard-coded values, the query runs super-fast, like 3 seconds or less. However, when I convert the query to a proc with the ...

how to insert a new record to view ?

Hasan O. - 6 Jul 2006 9:28 PM - 4 messages

Hi, I have a view which contains 2 tables with left join. Trying to insert a new record, it gives this error. What can be done about this situation? Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC SQL Server Driver][SQL Server]View or function 'usersView' ...

Implementing a Business Rule

Alexander Kuznetsov - 6 Jul 2006 9:23 PM - 9 messages

I need to implement a business rule. Here is the original DDL: create table parent(parentID int not null, ValidFrom datetime not null, ValidTo datetime not null) go create table child(childID int not null, parentID int not null, ...

Locking tables for UI

lord.zoltar - 6 Jul 2006 9:18 PM - 17 messages

Part of my application displays a Datagrid of rows from a table. Double-clicking a row will bring up a detailed edit window which allows editing. To confirm the changes, the user presses a "save" button which commits the transaction. It is possible (and quite likely) that ...

Sub Queries and Performance

arock - 6 Jul 2006 8:53 PM - 11 messages

I need some suggestions. I am currently trying to enhance the performance of a stored procedure that uses sub queries to sum some cumulative values. Here is an example with 1 sub query: --------------------------------------------------------------------- SELECT T.period_number, T.Weekending, T.ClientCode, T.Client, T.JobCode, ...

xp_sendmail @subject problem

Matt Sonic - 6 Jul 2006 7:43 PM - 7 messages

When I do this the record is added but I don't get an email. The @subject is causing the problem because if I remove it, it works. CREATE TRIGGER tExpressItemAdded ON [dbo].[ExpressItems] FOR INSERT AS Declare @ID as nvarchar(100) ...

Use Management Studio to Update MS Access db?

Jeremy - 6 Jul 2006 6:08 PM - 3 messages

Can I use Management Studio to connect to a MS Access '97 database? I want to update some data in the .mdb file before bringing it over to SQL Server. I don't want to issues these [many] queries from within MS Access (I just ...

missing rows in procedure output

Keith G Hicks - 6 Jul 2006 5:57 PM - 7 messages

I'm getting a result set that seems to be missing some rows in one situation. I have some code like this in a stored procedure: Select * From vwCustomers vwCustomers is a view on several different tables. When I run Select * From vwCustomers in QA all by itself it returns 88 rows. ...

view vs table & when

sali - 6 Jul 2006 5:38 PM - 4 messages

is there supposed to be some significant overhead or other difficulty for example: select * from table1 where deleted=0 and year(datefield)=2006 compared to: select * from view1 where year(datefield)=2006 assuming view1 is view created on table1 where deleted=0 ...

insert query runs in Query Analyzer, but not in Job

Randall Arnold - 6 Jul 2006 5:07 PM - 4 messages

I created the following query to perform an hourly update grabbing data from a linked server (mpws_live) and placing the new rows in my personal server database (asoqa): insert into ASOQA.dbo.MPWS_L_DNN SELECT * FROM OPENQUERY(mpws_live, 'select * from dbo.L_DNN') a where a.create_ts not in (SELECT create_ts FROM ...

Is null and indexes

RLoski - 6 Jul 2006 4:54 PM - 4 messages

I have a set of queries that I run against a SQL Server 2000 database: -- Statement 1 select count(*) from myTable -- Statement 2 select count(*) from myTable where col is not null -- Statement 3 select count(*) from myTable where col is null ...

SQL Table Design Question

jonfroehlich - 6 Jul 2006 4:18 PM - 5 messages

I am relatively new to SQL server. I am currently using SQL Server Mobile 2005 to store user response data, which can either be text or binary. If I understand the documents correctly, I can use the SQL datatype "image" to store binary data of any kind. I'm wondering which ...

Auditing MSSQL 2000 Data Changes

Jordan - 6 Jul 2006 3:02 PM - 3 messages

I'm looking for advice and insight into best practices in auditing. We have a new application coming online in the next year and are defining auditing requirements now. We want to be able to: identifiy who/when/what changed in a particular record for select tables. This is going to be a ...

Date Calculation for n days ago

Patrix317 - 6 Jul 2006 2:54 PM - 13 messages

Just a quick question that I'm stumped on getting an answer: I have a date field in my table and would like to select those records that are n days old. Example: Show records that are 17 days old: ...

Your thoughts???

AJ - 6 Jul 2006 2:52 PM - 2 messages

Hi all, Below is a copy of a discussion i have been having at my work with some other developers and IT crew. I must confess, having used by MySQL & MS SQL, i much prefer MS SQL; for me it is the case of MySQL = Good, MS SQL = Best. ...

Restoring the master database

Sammy - 6 Jul 2006 2:49 PM - 3 messages

Is there any point in restoring the Master Database the company I am in restore it each quater for a clean up. I see no point unless it was danmaged does anyone else do this. thanks for any advice Sammy ...

Pirmary Key versus Unique Index

GCeaser - 6 Jul 2006 2:15 PM - 12 messages

OK - So I have read about the difference between the two and summarized them below: 1. Unique index defaults to non-clustered - Primary Key index defaults to clustered 2. Unique indexes allow nulls in columns - Primary Keys do not. ...

stored proc with cursor for inserts becomes slower

P Platan - 6 Jul 2006 1:22 PM - 8 messages

Hi I have a procedure that contains a trigger(fast forward read only) which takes ~300000 records from a table in database A and inserts them in 4 tables in a database B. (We do this because we are in a transition to jump ...

Distributed transaction from trigger

muted.p - 6 Jul 2006 11:41 AM - 5 messages

There are two servers with Win2k (SP4) in LAN. MS SQL 2000 SP4 has installed on the first server and MS SQL 2005 - on the second. There is a DB on every servers. Both of DBs have a table t1 with ...

log shipping error

VSS - 6 Jul 2006 11:24 AM - 6 messages

I m getting following error in copy/restore operation "The process cannot access the file because it is being used by another process." This come against the file first_file_000000000000.trn every time copy restore is done. Only first time it went successful. Error number is 32. ...

Paging in Sql 2k

SqlBeginner - 6 Jul 2006 11:22 AM - 6 messages

I have a table without any PK (pls don't ask me why :) ). I need to do paging using the records in the table. Can anyone help me in the right direction. Regards Prasanth ...

How to Convert binary into Image

Andry Cahyadi - 6 Jul 2006 11:02 AM - 4 messages

Dear All, I am new in my office, i have a job to make a report...I must convert Binary data to Image in Excell. I don't know how to make that, Can u all help me, plz, it's very urgent.. ...

Update Table

Selvarathinam - 6 Jul 2006 9:32 AM - 3 messages

Dear All, I created a new table as TABLE15 to import few columns from TABLE10. The table was created with the below query. SELECT INVNO, SUM(ADJUST) INTO TABLE15 FROM TABLE10 GROUP BY INVNO HAVING SUM(ADJUST) <> 0 ...

Error read data from Sybase with SSIS 2005

Sal - 6 Jul 2006 8:51 AM - 3 messages

Hi to All, I am in process of transfering data from Sybase to Sql Server using Microsoft Visual Studio 2005 have taken a Data Flow Task in Control Flow tab In Data flow tab, I have taken one Ole DB Source ...

Text datatype

Microsoft - 6 Jul 2006 8:23 AM - 2 messages

hi, i have a string variable with a length of more than 30000 chars which is populated from external system. i'm unable to handle the string using any stored procedurs. and im unable to use text datatype in stored proc. ...

'Containing text:' option of Windows Search not working in *.tql f

Bob - 6 Jul 2006 8:23 AM - 7 messages

When using Windows Explorer to search my template (*.tql) files for certain text, it's not returning any results. Has anyone come across this before? I'm using Windows XP and have both SQL2K and 2005 installed. The .tql files ...

Displaying table/view schema

Robert Bravery - 6 Jul 2006 6:50 AM - 4 messages

HI all, I have used this statement to display rows from an excel worksett range: Select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\work\coke\Detailed P&L June RE.xls', 'SELECT top 1 * ...

sql server properties

ozcan - 6 Jul 2006 4:06 AM - 4 messages

Hi, Is there a system stored procedures, which shows all of the properties of the sql server 2000? Thanks ...

How to detect obsolete/invalid column reference

Ethan V - 6 Jul 2006 3:26 AM - 5 messages

Occasionally my DBA would rename a column. He did his best to update all affected stored procs, but sometimes he would miss 1 or 2. Please share with me your experiences and suggestions on how to detect an obsolete and ...

"SET" question

Keith G Hicks - 6 Jul 2006 1:08 AM - 8 messages

I have a silly question. I can't remember how to find out how something is currently set. Specifically, ARITHABORT. I know how to set it but I need to know how to determine the current setting. I tried sp_configure 'ARITHABORT' ...

_WA_Sys

RBC - 5 Jul 2006 10:31 PM - 9 messages

Is it possible not to build _WA_Sys statestics files and only build you own statestics files? I build 1 table and I got 21 statestics files with my PK and UNQ, total 23 files... Thank You, Rune ...

Help building a SQL Statement

jamesburkett - 5 Jul 2006 9:40 PM - 6 messages

Two fields: member number and email address while email address is not null update member number I need to create a sql statement that will update a VARCHAR member number from 123456789 to x23456789... is that possible? thanks. ...

Saving XML in SQL...

craig - 5 Jul 2006 9:28 PM - 6 messages

I have several .net MemoryStream objects in my application, each of which contains a sizeable chunk of XML. I would like to save the contents of these MemoryStream objects to my SQL database. I was just wondering if someone might be able to point me in the right ...

HowTo:Using Managment Studio Express migrate Access Northwind to

iano - 5 Jul 2006 9:24 PM - 3 messages

SQL Server. Or tell me where I can download a copy of Northwind in SQL Server. then the next question is: How do I connect the downloaded database to Managment Studio Express. Please... I amdoing this right now so I can ...

Version Control for stored procs

Donkey Cart Racer - 5 Jul 2006 7:06 PM - 9 messages

Hi, Currently having the problem that two people end up working on the same stored proceedure at the same time, over writing each others changes Is there anyway to use version control, cvs etc to manage the checkouts of stored proceedures to prevent multiple users from modifying the same ...

Updating an SQL Table from a Linked Server

norrisinc - 5 Jul 2006 7:01 PM - 7 messages

I am trying to copy data from a linked server to a table in a new SQL database, Here is what i have I am trying to take the results of this query: SELECT NUMBER FROM PROJECTS...PROJMAST WHERE STATUS='G' ...

Merging 4 queries into one

Deki - 5 Jul 2006 5:51 PM - 7 messages

Hello, I have a query qFlorida that calls 3 sub queries qDRI2, qDRISF and qRRI. All sub queries have WHERE clause that should prompt users to input from/to dates. How can I make one query out of these four? ...

help on case/if in sp

mamun - 5 Jul 2006 5:40 PM - 3 messages

Hi All, I am giving a section of code of an SP I am working on and I believe I can get help with this as it seems simple yet hard for me to accomplish. Here is the portion of the code starting in the "where" clause of the ...

create remote database from a sql file

Jon Paal - 5 Jul 2006 5:16 PM - 6 messages

with sql enterprise manager, how can I create a remote database from an sql file. The sql file is an export of a local database. I tried running the sql file in the query analyzer on the remote machine, thru remote desktop, and the query completed ...

SELECT Closest Date

Jim Smith - 5 Jul 2006 4:21 PM - 4 messages

Given this table... CREATE TABLE SomeTable ( [SomeID] [int] IDENTITY (1, 1) NOT NULL , [SomeText] [varchar] (75) NOT NULL, [SomeDateTime] [datetime] NOT NULL ) What SELECT statement would give me the row that is *closest* to some given ...

problems calculating a running (cumulative) total

Carl Imthurn - 5 Jul 2006 4:01 PM - 15 messages

I'm trying to update a cumulative (running) total in a table and meeting some resistance . . . Here's the DDL for recreating the scenario, and my desired results. IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.#TEMP') AND OBJECTPROPERTY(id,N'IsUserTable') = 1) ...

Passing cursor from SP when selecting from temp table

Sn0tters - 5 Jul 2006 3:56 PM - 3 messages

Hi, I am getting an error telling me my cursor @oc is not defined when I do this create procedure poc @curses cursor varying out as declare @footie table ( int1 int, string1 varchar(20) ) insert into @footie values ( 1 ,'23' ) ...

Maintenance Plan

Sandy - 5 Jul 2006 3:28 PM - 3 messages

Hello - I need to create a maintenance plan (in Enterprise Manager) for a database. The wizard seems to be pretty straightforward and my first inclination is to check all of the options such as Reorganize data & index pages, Update ...

Incorrect Syntax Issues going from Access to SQL

hydrazee - 5 Jul 2006 3:12 PM - 3 messages

Hello, I am working with a client who has had a lot of custom work done in Access and now wants to switch to SQL. I upsized the database and was 85% successful. The real problem is they have many views based on other views and many ...

Using field values as Column Names in another query?

Christopher - 5 Jul 2006 2:37 PM - 4 messages

Hi there, I'm currently querying the team foundation server database in SQL Reporting Services. In one table there are two columns for example, a field name and a field ID. An example of one row is below... ...

SQL 2000 linked server

Andy - 5 Jul 2006 2:32 PM - 2 messages

I have a query that I am running across a linked server that returns a date column. The data type on the column is smalldatetime. When I run it across the linked server it is returned as a datetime, but when I run it directly on ...

newbie: I got problems with this stored procedure

Jeff - 5 Jul 2006 2:13 PM - 10 messages

hey sql server 2005 (the express version embedded in visual web developer express) When I try to verify the sql-syntax in the stored procedure below I get 3 errors: a) 'CREATE/ALTER PROCEDURE' must be first statement in a query batch - (but ...

update a view

mcnewsxp - 5 Jul 2006 2:12 PM - 5 messages

is there an elegant and reusable way to update a view after column names have been changed. i will need to make the same changes to 3 separate DBs. tia, mcnewsxp ...

Error in retrieving value to declared variable

Rob - 5 Jul 2006 2:06 PM - 3 messages

Hi, In the attached abridged stmts., I am attempting to store some values into declared variables... -------------------------------------------------------------------------------------------- DECLARE @sys_date datetime, @bus_date datetime, @eodrun bit, @loc_code varchar(3), @scmd varchar (500), ...

Help with this select statement

rhaazy - 5 Jul 2006 2:01 PM - 9 messages

CREATE TABLE [dbo].[tblAsset] ( PK[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , [AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [AssetTypeID] [int] NULL , ...

Generating sql script

cyberkrul - 5 Jul 2006 1:39 PM - 5 messages

Hi, I want to script all stored procedures in my database (MS SQL 7 or 2k) where I found my name in comment. As a result I would end up with (idealy) drop and create statement where Author is "Joe"; in another word all my stored procedures. ...

UPDATE with WHERE CURRENT OF

Nikola Milic - 5 Jul 2006 1:32 PM - 18 messages

Hi, Does anybody know about link which show more complicated examples of use UPDATE with WHERE CURRENT OF? I use SS2000, SP4, Win 2000 Advance, SP4. Thanks in advance Nikola Milic ...

CLR integration - cannot find type[??] in assembly [??]

Paul_Monaghan - 5 Jul 2006 1:23 PM - 2 messages

Hi, I'm new to Integration services and .Net programming but am trying to create a dll that I can access from Sql server 2005. The dll read's an xml file and carries out some processing. I've run ...

create stored proc with parameter startrow endrow

Xavier - 5 Jul 2006 12:49 PM - 8 messages

hello, in my table i have some columns custnr(int) -primary key ,companyname(varChar) example 123000 Microsoft 125000 Sony ......... The table has ~ 200000 records is it possible to have a stored proc that returns only for example the rows from 50000 to 500100 ...

Search for Value

dan - 5 Jul 2006 12:36 PM - 2 messages

I want to search a db for a value and get back the table name. How do I do that? Thanks ...

get random row

Smokey Grindle - 5 Jul 2006 12:15 PM - 6 messages

Is there a way to get a random row in a table? say i had for example a daily joke table and i wanted to pick one at random each time in my Stored Proc... each item is numbered with a integer identity PK... any easy way to do this? ...

SQL agent job vb

SP modification Date

how openquery update and delete tables on linked server

How OPENQUERY update and delete tables on linked server.

Arithmetic overflow error converting numeric to data type

SP Insert Into with vars

stored procedure in Access database

Two Boolean Columns Per Attribute

Trying to be efficient and use SQL

Express Edition embeddable?

Column Checks

sql 2005 system tables and views...

Beginner query question

Future of dba's?

bulk insert question

how to store stored procedure return values to memory variables

update and delete tables (T-SQL) on linked server with OPENQUERY

Calculating Next Sequential Number

table permission

Retrieving excle schema

Updating a stored procedure

converting datetime for comparison

Help needed to merge a few database - same tables

Replication

Random records based by weight

Trigger Problem

update tables with stored procedures

stored procedure precompiled in sql server 2005?

How write a sp to return consecutive dates except Sat and Sun?

ignore table during log shipping

Update SQL statement with SELECT

Writing data to database and redirecting to index page

identity and rollback

sql...... difficult

using the replace function on underscore charaters

Updating a column

PREFIXING COLUMN VALUES WITH 0s

Exec not returning a result

Performance Difference between SELECT * and SELECT col1, col2, ...coln

new commer

Blank character in SQL table

Most recent dependent row only

table variable sql 2005

Loop through selected value and altering table.

Join DB declared in a variable/parameter

Displaying numbers like 05 instead of 5

subtracting fields from two records

Question on correct syntax for stored procedure

Should you tune against subtree cost, scan count, or logical reads?

Help Optimising SP

SQL 2005 versus SQL 2000 Difference - Order By Column qualification

Multipls cursors help

When using the .NET Framework data provider - does connection string style make a difference?

removing decimal points

Calling a Stored Procedure from inside of a loop..

Trace textdata truncated

Lock Request time out period exceeded

Sql query prob

Arithmetic overflow error converting float to data type numeric.

AVG by columns not by rows

Temporarily disabling constraints

SSMS and Dev Edition

Convert 1-column result set to 3 columns

Comparing two tables

sync-ing databases over an internet connection

Stored procedures for updating table

Urgent - Aggregated Query

SQL Server 2005 SP1 error: A transport-level error has occurred when receiving results from the serv

Migration Meta Data Storage

alternative function for to_char in sql server

How to accomplish creating a simple calculated column in a table

Update multiple tables

Replication and ReadOnly Database

I downloaded and installed SQLEXPR_ADV.EXE

CREATE TABLE [ database . [ schema1 ] . | schema1. ] tableName

SQL/.NET naming convention

How to convert xs:datetime into sql datetime type?

sql statement

I have problem with comparing records in the same table

SS2005 Version Control

About the presence of alter procedure?