SQL Server database programming

Need records forthe last month

SK - 22 Apr 2006 10:31 PM - 3 messages

I need to retrieve records that are posted within the last month and I've tried variations of below, but do not get all the desired result. I either get records outside the range or not all the records I should. Posted is ...

unable to access dbs other than default dbs

Sandy - 22 Apr 2006 7:55 PM - 5 messages

Hello - I had to uninstall and reinstall Sql Server 2000 - time for demo ran out and I reinstalled with Sql Server 2000 that I purchased. My databases are not showing up in the UI of either Query Analyzer or Enterprise Manager. The only databases showing are Northwind, Master, pubs, ...

Cross Tab SELECT

LUIS - 22 Apr 2006 4:52 PM - 6 messages

I am using MS-SQL2000, on a table called tblItems with these columns: ID Descripction -------- ------------------------------- 0012 Washer 0145 Oven 2345 Heater 7834 Fan ...

General network error. Check your network documentation ADO.Net error

RJN - 22 Apr 2006 1:42 PM - 5 messages

Hi I'm getting the General Network error, though not consistently. Stack trace is below. Any fix for this? at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, TdsParserState state) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state) ...

How can you mass-validate stored procedures, views, etc?

Byron - 22 Apr 2006 11:51 AM - 2 messages

I am looking for a way to validate all objects in the database that is is possible for in some automated fashion. For instance, if a column is dropped from a table stored procedures that rely on it will fail on the next ...

Making strings safe for SQL?

Noozer - 22 Apr 2006 10:31 AM - 6 messages

I'm writing some general SQL functions to save time later and one of the functions I'm looking for is something to make strings "SQL Friendly". What I need to know is, what should I be watching out for? ...

How to check if server address is valid?

Helmut Woess - 22 Apr 2006 10:19 AM - 5 messages

Hi, how can i check if the used server address is valid? The reason is, that if a user cannot connect, i want to show "server not found" if server does not exist or no running sql server is found at this address and "login invalid" ...

Schema Sample For Financials

DarkHorse - 22 Apr 2006 7:44 AM - 4 messages

Hi Guys I used to have a site bookmarked that had tons of schema samples for various applications. Some of it was rather basic but provided a good starting point. Does anyone know of a similar sites? I am looking to reference an schema for ...

How do you reduce the size of the Log File when using SQL Server 8?

RonL - 22 Apr 2006 2:14 AM - 3 messages

I'm using SQL Server 8. My test database is 2.5 gig but my log file is now 30 gig. I'm using all the default settings and I guess it's been just increasing until it nearly filled up my HD. Is there an equivalent ...

how to select distinct rows problem?

Rich - 21 Apr 2006 11:30 PM - 8 messages

create table tbl1( fname varchar(10), lname varchar(10), item int ) insert into tbl1 select 'joe', 'smith', 1 union select 'joe', 'smith', 2 union select 'joe', 'smith', 3 union select 'bill', 'jones', 4 union select 'bill', 'jones', 5 union select 'bill', 'jones', 6 union ...

Null data source to default value

microsoft.public.dotnet.languages.vb - 21 Apr 2006 10:50 PM - 5 messages

Hi All, I have the following situation. I am trying to populate data in a table from other tables. Say table1 has default values in all fields. But the data is coming from joining table2, table3, table4 and so on joined in a single query. ...

How to use LIKE in a store procedure

Michael Fällgreen - 21 Apr 2006 10:47 PM - 2 messages

How can you use a like-search in a SP ALTER PROCEDURE dbo.spSearch ( @s varchar(50) ) AS SET NOCOUNT ON SELECT * FROM tbl where name LIKE @s What is I need to do a '%@s%' ?? Thanks ...

Login failed State 16 SQLServer 2005

astaylor - 21 Apr 2006 10:21 PM - 5 messages

Using SQLServer 2005 I create a new database with code from my Windows application. Then I launch osql to create tables from a script. This works fine. Next I try to insert a row into one of the tables and I get: ...

SQL2005 and 4GB of RAM : How do I use it?

Russell Mangel - 21 Apr 2006 10:12 PM - 6 messages

What exactly do I need to configure on my SQL Server 2005 to use more than 2GB of RAM? Currently the Server Properties is only showing ~2GB and I have 4GB installed. There seems to be a lot of conflicting information (from Google). ...

GROUPING problem

David - 21 Apr 2006 10:03 PM - 9 messages

I am trying to get counts of jobs accum into 4 columns by date. When I try to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is invalid in the select list because it is not contained in either an ...

Right way to do a insert

Michael Fällgreen - 21 Apr 2006 8:28 PM - 27 messages

Just to make sure - is this the right way to do a simple insert? Create PROCEDURE dbo.spInsert( @name varchar(50)) AS begin tran set nocount on insert into tbl (name) values(@name) set nocount off declare @id int set @id = @@IDENTITY ...

Trigger Security

Todd C - 21 Apr 2006 8:28 PM - 3 messages

I have a Payments table with a trigger that fires on Insert and Update. It has been working fine for the past few months. Now, it disconnects the user from the db whenever it fires. However, if I, as a sysadmin, modify data in ...

problem with select * from [remote server] in QA

Rich - 21 Apr 2006 8:28 PM - 3 messages

In Query Analyzer connected to my local server using Windows authenticatio I tried to run this select statement against my remote server: select * from [11.22.333.444].remoteDB.dbo.tbl1 I got this error: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL ...

Use UDF which returns a table in a join

BF - 21 Apr 2006 8:12 PM - 4 messages

I have a UDF, dbo.ContractSummary which takes one parameter ContractID and returns a table. I want to use this UDF in a SQL select statement like this: SELECT cs.* FROM Contract c JOIN dbo.ContractSummary(Contract.ContractID) cs ON c.ContractID = ...

Inserting data into a table which already has identity

Alex - 21 Apr 2006 8:08 PM - 3 messages

Hello all, I have two tables, I'll say Table A and Table B for the example, where Table A has all my data and Table B is blank but has the same layout as Table A except it has an additional Identity field setup to create an ...

Doing the lookup on the server while inserting

sklett - 21 Apr 2006 7:51 PM - 3 messages

I have these 2 tables: CREATE TABLE tbl_ft_testsegments ( SegmentID int(10) AUTO_INCREMENT NOT NULL, TestID smallint(6) NOT NULL DEFAULT '0', ...

Get Today's Records

Pancho - 21 Apr 2006 7:48 PM - 7 messages

Hello, I have a query that needs to run daily and only collect records for that day's activity. The table has a date/time field called TranDateSold. Could someone please advise the proper syntax for the following logic: ...

I am getting an error in creating a simple store procedure!

Learner - 21 Apr 2006 7:46 PM - 7 messages

Hi , Here is my stored proce and I am not sure why its complaining about the below error SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[USP_InsertUserInfo] @aspnet_userid uniqueidentifier(50) ...

How do I link tables to another database?

0to60 - 21 Apr 2006 7:44 PM - 2 messages

With Access, I could link to tables in a foreign db, even a csv file, and query them as if they were local tables. How do I do that in SQL Server? ...

Quick Stupid Question, One to One Relationship Storage

Matthew - 21 Apr 2006 7:32 PM - 6 messages

On one to one relationships data will only be inserted into the second table if that information is provided. That is it doesn't place a bunch of null values just because you added and item to the first table ...

BULK INSERT permissions for non-admin?

Jesse - 21 Apr 2006 7:13 PM - 5 messages

I've got a user, "joe", logging into a SqlServer 2005 database using sqlserver authentication. Joe can create tables, insert, select, drop -- whatever -- but when he tries to do a bulk insert, he gets: "The current user is not the database or object owner of table 'myTable'. ...

Top 5 Quesiton

dTHMTLGOD - 21 Apr 2006 6:39 PM - 6 messages

I have a SQL table with a 2,000 + records. I need to pull the top 5 problems from each office. The fields in the tables are Office, Problem (TypeItem below), User. My queries right now only pulls the top 5 from the table (see below). I ...

Stumped on filtering data by date

jmawebco - 21 Apr 2006 6:31 PM - 2 messages

I have a stored procedure that selected records from one table and inserts them into a second table based on some criteria. What I want to add is filtering by "Today -1". The problem I am having is that the ...

Stumped on an Update Query

Mike Voissem - 21 Apr 2006 5:04 PM - 7 messages

I have an update query that I'm trying to resolve, but I'm at a lost as to how to accomplish this. I have two tables(Orders and Mailings). I need to update an Orders field with a field from Mailings. There are some that will ...

How to best handle multiple parameters?

Phill - 21 Apr 2006 4:32 PM - 9 messages

I am preparing the write a sp that will accept about 20 optional parameters that will filter my resultset. Since these are optional I was thinking that I needed to dynamically build my query string based on if a parameter had a ...

Join using LIKE ?

Martin Harran - 21 Apr 2006 4:29 PM - 4 messages

I have been given two tables in Excel which I've imported into SQL Server and I need to join them. Each of them has a unique Product but there is a bit of inconsistency in that in Table A, some (but not all) of the Products have been prefixed with ...

BLOBs and Stored Procedures

Tony HADM - 21 Apr 2006 3:57 PM - 4 messages

I have been told that using BLOBS and Stored Procedures is a bad thing. Running the SQL in the page is the only correct way. We are using SQL Server 2000 - soon to go to 2005. Could someone direct me to documentation that ...

Stored Procedure generating error

Jesusluvr - 21 Apr 2006 3:51 PM - 8 messages

I am trying to compile this procedure and run it. It compiles fine, however when I run it I get the following error: (1 row(s) affected) (1 row(s) affected) Msg 203, Level 16, State 2, Procedure ETL_CC_TBL_CUSTOMER, Line 96 ...

Complex View creation question

William Sullivan - 21 Apr 2006 3:17 PM - 2 messages

I need to create a cross-database view (same server) in a master database. The databases I'm joining in the view are listed in a table in that master. I'd like for the view to automatically include new databases whose names are ...

Calling Stored Procedure from asp without waiting

Anne - 21 Apr 2006 3:16 PM - 10 messages

Hi, I'm developping a asp application that somestimes needs some heavy synchronisation done. I wrote a stored procedure for that purpose witch takes approximatly 15 minutes. Right now I run this directly on the server (using MS SQL Server management studio). But I would love to be ...

How-To create a "Dirty" Stored Procedure (Relates to Transaction)

Mathieu Dumais-Savard - 21 Apr 2006 3:14 PM - 3 messages

I explain: I have created a stored procedure that indicate the status of an ETL... I call it like this: 0- BEGIN TRANSACTION 1- --Updating LOG Status 2- exec update_etl_status 'Main Table','Transforming' 3- update Stg.[Main table] ... blablabla ...

Use float or decimal to get time?

sdblonde63 - 21 Apr 2006 3:06 PM - 3 messages

I'm a newbie, and I need to convert a time interval of seven position numeric field from a flat text file in format MMMMSST and store in a database with the tenths of second in first position after the decimal. ...

Copy users between databases

Terry - 21 Apr 2006 2:58 PM - 2 messages

Hello All, I used DTS to copy one database to a blank database for a backup of the database. A duplicate database. How do I copy over the user logins to the new database. This did not happen with DTS. ...

Msg 226, Level 16, State 6, Line 4 ALTER DATABASE statement not allowed within multi-statement trans

RSH - 21 Apr 2006 2:40 PM - 6 messages

Hi, I am trying to execute a simple statemnt that I got right out of books online and I keep getting this error and I cant figure out what Im doing wrong: ALTER DATABASE [00057088] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ...

Sub Query ?

HeartSA - 21 Apr 2006 2:39 PM - 5 messages

I have a table in SQL Select Count(*) AS CountAll from Data Select Count(*) AS CountSome from Data where Individual = Name I would like to combine this into one statement and give me both the CountAll and CountSom ...

TSQL for date of next weekend

Matt Jensen - 21 Apr 2006 2:22 PM - 15 messages

Hi I need to calculate the date of the next weekend (so either Saturday and/or Sunday's date) from getdate() inclusive of if getdate() is a weekend-day. I need to filter some realtime travel related data for "this weekend" via ...

A stored-procedure becomes slow and needs re-creation

Boaz Ben-Porat - 21 Apr 2006 1:46 PM - 2 messages

Hi all I have a SP that beahves strange. Originally it takes about 20 milliseconds to complete, but sometimes it starts going slow and take about 5-7 seconds. When this happens, it keeps going slow until I drop the SP and re-create it. ...

Any way to query EXECUTE perms on stored procs?

PSPDBA - 21 Apr 2006 12:40 PM - 4 messages

I've recently been tasked with duplicating the permissions from one account to another. We have a development, system test, and production SQL Server, and approximately 35 databases in each. We use a fine level of control on this particular account because it's what the ...

Multiple queries SQL

Ames111 - 21 Apr 2006 11:02 AM - 4 messages

Hi my first post!!! i have an organisation table and i want to do something like this select count(orgname) as Count1 where orgname like 'A%' select count(orgname) as Count2 where orgname like 'B%' but i want it to list every organisation, so one on each row, then the ...

Interesting Query

S Chapman - 21 Apr 2006 10:43 AM - 4 messages

I have four lookup tables that have identical structure. I have to write a query to check if a particlaur string (code) exists in any of the four lookups. What is the best way of dealing with this please? 1. Write one query with four corelated subqueries (one for each ...

security for row level but not based on Database user's login

Friends - 21 Apr 2006 10:26 AM - 2 messages

Hi I need to set security for row level but not based on Database user's login. It should be based on the user table login. For the particular user I need to allow only the particular records to access insert, ...

select all the rows where min_date_time <= curr_time <= max_time.(How?)

A.Neves - 21 Apr 2006 10:09 AM - 4 messages

Hi, I have a table with the DATETIME columns min_date_time and max_date_time, these columns store a min and max time for some date. Now I want to extract all the rows that limit curr_time (independently of the date). Something ...

service broker management views

Mana - 21 Apr 2006 10:02 AM - 2 messages

According to msdn the views 1.sys.dm_broker _forwarded_messages returns a row for each Servive Broker message that an instance of SQL server is in the process of forwarding, and 2. sys.dm_broker_connections returns one row for each Service Broker ...

Why does (SqlInt32)sqlCommand.ExecuteScalar(); : Throw Specified cast is not valid.

Russell Mangel - 21 Apr 2006 9:59 AM - 12 messages

When I run the following code on SQL Server2005 I get an exception every time. SqlInt32 maxct = (SqlInt32)sqlCommand.ExecuteScalar(); // Throws Specified cast is not valid. Why doesn't this work, or what do I not understand here? Since I could not make previous code work, I am forced to do the following ...

OPENXML Insert into table failure not producing @@Error

Matt Jensen - 21 Apr 2006 8:45 AM - 5 messages

Howdy I've got the following example of a SP of mine (except in real life the @XML is ntext datatype) ----------------------------------------------------------- DECLARE @XML VARCHAR(4000) SET @XML = ' <?xml version="1.0"?> <roadrunner updatetime="2006-04-20T1752"> ...

pulling unique records from this query

musosdev - 21 Apr 2006 8:20 AM - 7 messages

Hi guys, need your help! (sorry this is quite long) I've got a table of Projects which I'm using with an asp:Repeater to display a list of the projects. Here's the sql... SELECT ProjectID, ProjectName, ProjectClient, DartsContact, LeadArtist, ...

Data Parse Challenge

xxxdbaxxx - 21 Apr 2006 8:10 AM - 11 messages

Thanks in Advance, I would like to parse a column of ntext that looks something like this: 'I was born on Sept. 14, 1960. I graduated college on August 2, 1982, and went to France on Jul 17, 1985. I died on December 30, 2001.' ...

OPENDATASOURCE

FJC - 21 Apr 2006 5:34 AM - 4 messages

Hi all, Why I can't do the following: DECLARE @stringConnection as varchar(150) set @stringConnection= 'Data Source=vrpims\cpims'+';'+'User ID=sa'+';'+'Password=sa' select @maxAttendancesVC= max(AEATT_REFNO) from OPENDATASOURCE( 'SQLOLEDB', @stringConnection).materprod.dbo.ae_attendances When I try to execute the above coding the following error message comes out: ...

SqlPipe output from CLR sproc

charles davis - 21 Apr 2006 5:12 AM - 3 messages

How would I go about capturing the output of a stored procedure that is being returned through a SqlPipe? I have to write a CLR sproc in c#, and I need get a string value out of it in the middle of a t-sql script. The only ...

How to insert empty string

hon123456 - 21 Apr 2006 4:35 AM - 5 messages

Dear All, I got a SQL statement like that Insert into TableA (columnA,columnB,ColumnC) select VariableA, VariableB,VariableC Where columnA,columnB,ColumnC are Varchar. VariableA, ...

function versus stored proc

mrmagoo - 21 Apr 2006 4:00 AM - 8 messages

I have to repost this, so I apologize if anyone responded. For some reason the copy I sent is not being refreshed in my newsreader Inbox. 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 ...

Problem building query string

Phill - 21 Apr 2006 2:33 AM - 2 messages

I am building a string variable that is my SQL. I am getting a "conversion failed when converting the varchar value". My parameter is defined as @MetroCode int = 0 This works: SELECT @output ='SELECT * FROM table1 WHERE col1=13' ...

How to use EXEC

Mike_T - 20 Apr 2006 10:04 PM - 6 messages

Hi, How can I run EXEC conditionally? The EXEC syntax woks on its own but not when it's in a query. TIA Mike select 'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0 and 2 then EXEC master..xp_sendmail 'mike', 'The master database is full.' else '' ...

Import DBF File into SQL Server using vb.net

SQL Newbie - 20 Apr 2006 10:02 PM - 6 messages

Hi, I need to import dbf file into sql server. This dbf file is about 300 MB with a 250,000 records. The import process needs to happen once a month. I can create a DTS package and schedule it but the problem is that the ...

1434 problem

JFB - 20 Apr 2006 9:35 PM - 8 messages

Hi all, My sql 2000 server is using 1434, I try to use the tool to fix the worm but it doesn't find anything. How can I fix this issue? Tks in advance JFB ================================================= Process Name : sqlservr.exe ...

Oracle to SQL Server Migration

Maxwell2006 - 20 Apr 2006 9:18 PM - 12 messages

Hi, We have an Oracle database with 3200 tables. We want to migrate this database tables (both structure and data) to SQL Server 2005. What would be the best way to do that? Is there any automated migration tool for doing that? ...

EXISTS Vs. NOT EXISTS

Justin - 20 Apr 2006 9:03 PM - 8 messages

Is there a performace difference in the following queries, assuming tblAgent contains 100,000 records? IF EXISTS(SELECT * FROM tblAgent WHERE AgentID = @SomeAgentID) BEGIN .....do operation A END ELSE BEGIN .....do operation B ...

Finding special characters in field?

VMI - 20 Apr 2006 8:55 PM - 4 messages

How can I check if there are any special chars in a certain field? How can I integrate this into my Select query? I need to check the field [Name] and see if the users have mistakenly typed special characters into the names. ...

DTS ActiveX Script error

John Keith - 20 Apr 2006 8:52 PM - 5 messages

The 1st 4 lines of code were copied verbatim from a VBS file that works perfectly. Why does it fail when running from a DTS ActiveX script? Function Main() set objShell = WScript.CreateObject("Wscript.Shell") wait = true ...

wscript.exe vs cscript

Jim - 20 Apr 2006 8:35 PM - 3 messages

Hello, I know the difference between the two but here my problem. If I run a script in sql server using xp_cmdshell with cscript.exe it does not work.....i use wscript.exe it work fine. my script takes in parameters. ...

Table Design Question - Infinite Menu Level Items

Brian Patrick - 20 Apr 2006 7:47 PM - 10 messages

We are building a web application which can have and infinite number of menu items and levels for it's main menu. For example: TopLevelMenuItem1 SubLevel1MenuItem1 SubLevel1MenuItem2 SubLevel1_2_MenuItem1 ...

Convert Access Query to Transact SQL 2000 IIF to CASE

ILCSP - 20 Apr 2006 7:16 PM - 7 messages

I need to convert a MS Access 2000 query into SQL Server 2000. Since the IIF statements are not allowed in Transact-SQL, I 'm trying to use the CASE Statement I have two tables, Results and Sections. I need to import the section ...

Next question regarding to backup location.

TJ - 20 Apr 2006 7:15 PM - 6 messages

Hi, First of all, thanks for the answer about "Have you found any solution for this?" qustion... Here is another question, but related previous question. Environment : Win Svr 2003/SQL Svr 2000 As far as I know, to specify a UNC path for backup location, MSSQLServer ...

TSQL statement Varchar/char to datetime

Joe K. - 20 Apr 2006 7:00 PM - 4 messages

I SQL Server 2000 table that has varchar(50) format that has date time samples in this table. sample: 2006-01-21 12:00:22 I would like to write a SQL statement that will convert the varchar(50) to ...

How to generate unique key? (ala Oracle sequence)

Marco Sella - 20 Apr 2006 6:59 PM - 5 messages

Can SQL Server 2K or 2K5 generate unique keys for a record? In Oracle we use a sequence. Thanks. ...

using OPENQUERY in stored procedures with parameters

Robert - 20 Apr 2006 6:52 PM - 2 messages

I am using SQL Server 2000 that has a linked server to another SQL Server 2000. I only have view access to the linked server. I wrote a stored procedure that takes 4 parameters it runs fine but very ...

UTC -> Local Time conversion - How to? (SQL Server 2000)

Tomasz Jastrzebski - 20 Apr 2006 6:20 PM - 5 messages

Hello, How do I convert UTC datetime to a local time on SQL Server 2000? I mean not just current time, but entries from months and years ago. In another words, I have a table with UTC datetime and I want my query to ...

Variable suddenly getting undeclared

Rick Charnes - 20 Apr 2006 6:13 PM - 4 messages

I hope I can trouble someone to help me with this: In my SQL Server 2000 proc, I have a constant and a variable: DECLARE @c_cr char(2) SET @c_cr = char(13) /* carriage return */ ...

UTC -> Local Time conversion - How to?

Tomasz Jastrzebski - 20 Apr 2006 6:07 PM - 4 messages

Hello, How do I convert UTC datetime to a local time? I mean not just current time, but entries from months and years ago. In another words, I have a table with UTC datetime and I want my query to ...

Can I populate an existing table from inline function table data?

Rich - 20 Apr 2006 5:39 PM - 3 messages

I need to populate an existing table with the same data contained in/returned from an inline function table. An external app uses the data from the inline function table in a form. I need to be able to update that data also in a ...

Reg-Gate's SQL Compare

M.Siler - 20 Apr 2006 5:13 PM - 5 messages

I'm kind of at a disadvantage here as I'm not a developer, but am putting out the questions here for a little advices. I have a contract developer that has requested a product called SQL Bundle Standard from [link] and I'm wondering if it is necessary. Our ...

Is there a better way of doing an INSERT or UPDATE

Bill - 20 Apr 2006 4:59 PM - 8 messages

I have several different situations where I want to Update the record if it exists or add a new record if it does not exist. An example might be -------------------------------------------------------------------- -- Has this table already got a record for this entry.... select @rowcnt=count(*) from mytable where myField = '123' ...

Total by company

Shailesh Patel - 20 Apr 2006 4:53 PM - 2 messages

Hi, Here is my sql result rows. Order Amount Company Size Total 1 109.8927 aaa 16-20 ...

ADD IDENTITY PROPERTY WHEN THERE IS ALREADY DATA

Enric - 20 Apr 2006 4:53 PM - 4 messages

Dear all, Keep in mind the structure of the following table I would need alter ID field and add an IDENTITY property but when data are already loaded. The source table begin from 4200 as value in the first row and if before of ...

SQL Server Execution Time, elapsed time, Overstated in Query Analyzer

ionFreeman@gmail.com - 20 Apr 2006 4:50 PM - 2 messages

I have a little batch query, and it putters along. It last completed in sixteen minutes, twenty-nine seconds. Now, I have check Tools/Options/Connection Properties/Set statistics time, so I get a little information on how much time each step is ...

Can you help me to remember

iano - 20 Apr 2006 4:39 PM - 2 messages

I thought I had read of a tool that would look at an existing database and them make recommendations for a better design. Perhaps I was dreaming ;-) If you know where it is, please post a link. Thanks, IanO ...

Creating a Crosstab in SQL

deodev - 20 Apr 2006 4:05 PM - 11 messages

Hello, I have data in a table as follows: number type percent 147823 MTOR 100 147823 CLOR 100 147964 CLOR 100 ...

schema design question

Jiho Han - 20 Apr 2006 3:53 PM - 10 messages

One of my pet peeves is stuffing all custom fields into one table. For example, Company table would get everything related to a company For 1-N data, it's simple because it necessitates a separate table. However, ...

Recursion problem...

BillyT - 20 Apr 2006 3:52 PM - 5 messages

I want to execute the following SQL trigger: /* Trigger: t_CASE_VISITS_U2 Purpose: Sets the value to the previous value (DELETED.c_vis_date) if the incoming value (INSERTED.c_vis_date) is '1/1/1900'. Be sure to test the condition where the value already is ...

Question on MTS and SQL Server

Rodger - 20 Apr 2006 3:43 PM - 9 messages

Hi I have a stored procedure which gets executed from ASP code, the ASP in turn uses MTS . the problem is, when the stored procedure encounters a rollback statement, instead of just sending the error message and then rolling back ...

Have you found any solution for this?

TJ - 20 Apr 2006 3:39 PM - 2 messages

Hi, I've seen a post that he/she has exact same issue like me... However, I couldn't find any solution for this... This is the past post that somebody wrote the issue... ---------------------------------------------------------------- From: Wayne Antinore - view profile ...

convert float to string

Bill H - 20 Apr 2006 3:11 PM - 3 messages

How can I convert a float value to a string, without rounding issues or ending up with scientific notation ? Seems like a simple request. I have various types of numbers stored in a Float field that I need to convert to a string as is. I don't want ...

Two EM at the same time..

Enric - 20 Apr 2006 3:11 PM - 4 messages

Dear all, I would like have at the same time two available Enterprise Manager in my workstation. I mean, keep in mind that one should show a concrete settings and another one the same behaviour. For instance, every EM with different ...

Regarding databases

Enric - 20 Apr 2006 3:11 PM - 2 messages

hi everyone, We currently have sql2k. We have got a database which contains almost 800 tables. Such tables are attending a lot of ASP solutions, some of them using 30 and others only 4.Spliting up these tables into specific databases will increase us nearly ...

SELECT with Exclude?

Retf - 20 Apr 2006 2:47 PM - 5 messages

Hi all, I need do one thing, and I need help. I have 2 tables: CREATE TABLE terminais ( --... terminal_id uterminalid NOT NULL, --... )ON [ucs] CREATE TABLE estabelecimentos_terminais ( --... CONSTRAINT terminal_id__estabelecimentos_terminais__fk FOREIGN KEY(terminal_id) REFERENCES terminais(terminal_id), -------------------------------------------------------------------------- terminal_id uterminalid NOT NULL, estabelecimento BIGINT ...

Profiler Trace Analysis

neeju - 20 Apr 2006 2:27 PM - 3 messages

Hi All, (I posted this in another group but didn't get any answer so posting here. ) I have recieved some 50 trace files (.trc) from the client and they want me to analyze them all in 1 hour and let them know certain things. ...

Performance between SP3 and SP4

Warren - 20 Apr 2006 2:14 PM - 3 messages

We have the following select statement SELECT 1 FROM DAT_SAMPLES D , WRK_SELECT W WHERE W.TRANSACTIONID = 53364 AND D.ID = W.RECORDID AND D.APPROVAL IS NOT NULL ...

Service Broker queues

Mana - 20 Apr 2006 2:01 PM - 2 messages

I am using SQL server 2005 Service Broker. Can I write a trigger on a Service Broker queue. Basically I want to mirror the queue. So whenever a message arrives into the queue, i want this trigger to get fired and copy the entire ...

Having condition

simonZ - 20 Apr 2006 1:37 PM - 6 messages

I have query: SELECT name,company,sum(quantity) as quantity from table GROUP BY name,company WITH ROLLUP HAVING name is not null But in result set I get also resulting rows with name is null: name company quantity ...

Service Broker queues

Mana - 20 Apr 2006 1:26 PM - 5 messages

I am using SQL server 2005 Service Broker. Can I write a trigger on a Service Broker queue. Basically I want to mirror the queue. So whenever a message arrives into the queue, i want this trigger to get fired and copy the entire ...

Bitwise or aggregate function

Manso - 20 Apr 2006 1:15 PM - 12 messages

Hi, I understand there are no aggregate function for ORing multiple row columns together. We have a access control system that looks like CREATE TABLE AccessTable ( UserID int NOT NULL PRIMARY KEY, EntityID int NOT NULL PRIMARY KEY, ...

Table format change

Dave S. - 20 Apr 2006 1:05 PM - 16 messages

How can I programatically change a table from ID Value 123 12 123 14 123 16 123 142 ...

Setting up foreign key

Justin Yang - 20 Apr 2006 12:59 PM - 4 messages

Just curious, can you set up foreign key constraint from a table from different database? eg say CodeID of Table1 in Database1 referencing CodeID of Table1 in Database2 (of course Database1 and Database2 are in the same sql server) Thanks ...

Question about CASE

vcinquini - 20 Apr 2006 12:58 PM - 4 messages

My proc: CREATE PROCEDURE ListOrderDetails DECLARE @idoc INT DECLARE @inn AS VARCHAR(1000) SET @inn = ' <ROOT> <OrderDetail num="261"/> <OrderDetail num="263"/> <OrderDetail num="264"/> </ROOT>' --Create an internal representation of the XML document. ...

Executing a command inside a read loop

Philip Sheard - 20 Apr 2006 12:21 PM - 4 messages

How can I execute an SQL command inside a read loop? My code looks something like this: With cmd1.ExecuteReader Do While .Read cmd2.CommandText = "..." Name = CStr(cmd2.ExecuteScalar) Loop .Close End With ...

size of a table in bytes

Search all Varchar fields in Database for text

Format numbers in a view

Converting text output of execution plans to graphical

at a time more than 8 users executing sp_xml_preparedocument what

Query in SP3a retreives result in different order in SP4

N Preceding Strings when Scripting Objects

diff between Cast and Convert

Locking Problem

**longest text in SQL**

Logging IP of connecting users

Max() on many columns performs slowly

Algorithmic question

sysperfinfo table empty

Processing tables with hierachical contents

While Inserting into a Table, it takes more than 10 secs...

Handling error level 16

shell object does not execute command.

Insert Primary Keys to all tables in a database

The usage of substring

How to extract the string

function vs stored proc

checking object usage

need help For xml explicit

sql cookbook.

Update numeric value?

Arithmetic Overflow

need an alternative for this simple query

oreilly

Table Lock on a Simple Update .. Using PrimaryKey as the only value in the WHERE clause // The whole

Controling xp_sendmail

Export Blob (images) to file (jpg)

INSERT INTO EXECUTE Holds EX_PAGE .. Still an Issue for 2000/2005 ???

possible to concatenate text to Where clause?

Scan and Write a varchar into numeric column

Create Procedure Error?

stored procedure rights

What's the best way to keep a live db and an archive db?

i can use SQL2005 Client Tools with SQL2000?

Reporting + SQL Server 2005 + assembly and "small" problem

User Defined Function Criteria/Default problem

Truncating DECIMAL data type

Is it possible to create CLR function call legacy C++ models?

Temp Table

Concatenate Date & Time

how to specify default value of '%' for int param in SP?

How can I make this update statement faster

Improve Update Statement

Multiple row insert only inserting one row?

sql cookbook.

UNIQUEIDENTIFIER datatype

Whyis "Manage Indexes..." grayed out?

Retrieve GUID from SQL 2005 Stored Procedure

Calculation between rows in one field

problem with If Exist select

Dynamic From Clause

SSIS Result Set

Partitioned View performance

SQL View

Updating table without using cursor

Indexed Views are they supported?

Recursively creating a String using Cursors

Is UPDATE worth the effort (in this case)?

semi-colon as a batch separator

Stored Proc Parameters

Efficient SQL Backup?

What am I missing in this INSERT???

::Info Request::

Generate SQL Script using a DTS

DBCC fails, why?

xp_cmdshell

Importing a text file using Express

Kill a SQL process

Does SQLSERVER support fuzzy text searching(like the function of agrep).

Triggers not Updating

integer datatype confusion, signed vs unsigned

local and distributed transaction

What is the problem of using the IDENTITY column as the only key for table.

update column dependent on previous values

Executing BCP from C# code

Protect my database (BE)

Daylite saving time problem

Insert 200 rows in one insert statement

How to insert ten rows in one insert statement

is there any limit to how long of a string SqlDataReader.GetString() can return?

CHARINDEX returns zero in TEXT column

How to get a previous rows result to currect rows for calculation

How to capture out param?

SQLDMO: Database SpaceAvailableInMB reports differently in SQL2005

Referencing another Database in a query

Will it be faster to use CLR UDF?

rtrim in sql server 2000

Appending to a Text field

How can I swap rows to columns in a SELECT statement

Joining 2 tables via 3rd table

Q280468 Bug/Error SELECT with GROUP BY ALL still occurs in 2000, 2005!

CLR UDF deploy failed

Put constraint on this

add words to FTS dictionary

Cannot get serverproperty

My convert vchar to numeric - why doesn't it work?

Select from Two Tables each Column that is Different

N - curious?

Query Parallelism (maxdop)

SQLDMO.dll Redistribution Best Practice

Bulk Insert from Memory (without using physical file)

Transact-SQL

data replication

C# UDF project call C++ model (SQL Server 2005)?

List tables used in a SP

Text data type support in trigger, SQL 2005

Auto-Increment

Scripting Primary Key creation with DTS

Connect to SQL Server over the internet

Error Handling in procs etc.

Frequency of joins between two tables in a database.

CLR function performs worse than T-SQL

SP return status

Transactions

show which material was most ordered

looking for a T-SQL Parser for .NET

Add Images to table of SQL Server 2005

About Disconnected Record Sets

Structure M:M

SQL Server 2005, .Net / CLR Support and Event Handling

How to browse INFORMATION_SCHEMA in Enterprise Manager

Query HELP!!!!

Unable to create login instance--why? what it means?

Formatting Dates

Temporary table causes slow execution

index fill factor

How to Send a Windows Message from a Trigger?

KB article 246133

sql reporting services

Looping through sysDatabases to perform maintenance

select group/ max items

Info!!

Cast as Decimal

Concatenate All values to one select

XML Export

MSDTC on server <Server-Name> is unavailable.

SQL 2005 - transferring objects in ASP classic - vb6 dll - can I do this?

Invalid column name

unable to drop a sql server database

Trigger on main table to update itself with detail totals

Retrieving other table-column properties

returning row from function

Change NOT NULL to NULL?

add coll in specific order

Query Timeout Issue with .NET

insert * from the same table

VStudio 2005, SQL Server 2005 and CLR integration: How to do it within IDE?

Call Stored Procedure Within Stored Procedure!

Weird..

Add text to paramaterized query result in SQL Server -- is it possible?

query help pls

Timeout Expired on SQL 2000

Whoopsie Trigger

Bday dates revisited

Syntax error converting datetime from character string.

representing months with columns

When to use stored function Vs. stored procedure?

wrong result set

Condition in FROM vs WHERE

Performance problem

how to write a stored procedure that adds a record to a table and returns the value of the ID Column

Help: select DATEDIFF(day,date1,getDate())/DATEDIFF(day,date1,date2) as r_ratio from test;