Hi, I'm not sure I understand outer joins. I have a join like this: -- CREATE VIEW vPeople AS SELECT Id,Name FROM People WHERE Deleted<>1 -- SELECT * FROM vPeople it returns like 1000 records then I have: SELECT P.Id, P.Name, A.Address FROM vPeople P LEFT OUTER JOIN Addre...more >>

Hi, Iam using the bcp api to load data. i find that the performance is good for most data types. However it slows down drastically for datetime data. In my program the datetime data is bound as SQLCHARACTER. I guess this leads to a lot of conversion/parsing at the server side. Is there a be...more >>

Hi, How to use variable as parameter in stored procedure using OPENROWSET ?? I try the query below, but get an error. Please help me. DECLARE @job varchar(5) SET @job='RefreshTLMReport' SELECT * FROM OPENROWSET('sqloledb' , 'server=reportsvr;trusted_connection=yes' ...more >>

When I run below code on Northwind, I get records between 5/6/1998 and 5/1/1998,which is the max or last date minus 5 days. I want it to return records between 5/6/1998 and 4/30/1998 which would be the last 5 entered dates. Basically, I want to ignore the days that have no data. Anyone h...more >>

does sql server havethis parsing mechanism with regard to data type discovery. for example: select 8/2; how does it know that the operands are ints, etc? ...more >>

I have the following query which returns the latest SAMPLE in a table containing power usage samples SELECT SAMPLE FROM powerdb.sms_history.dbo.log_samples s INNER JOIN powerdb.sms_history.dbo.log_sample_values sv on s.sample_id = sv.sample_id WHERE s.device_id = 12 AND s.sample_time = (sele...more >>

Hello, I got error when i trying to import data from foxpro table to sql: SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'select record_dt from [MY.DBF]') Server: Msg 8114, Level 16, State 10, Line 1 Error converting data ...more >>

Just assume for the moment I have good and sufficient reason to want to use a cursor, it may even be true. My question is about whether it is matters how an update is done. What I want to do could be done by: update mytable set myfield=@newval where current of mycursor. .... but it turns...more >>

Is there a way to do a select from table and assign names to it, such as: SELECT Amount=Col1,Type = Col7 from tablex I need this to read a .csv file into a DataTable that has no column names on it. Thanks, Tom ...more >>

I am trying to filter out old records from a search. I tried this but it still seems to display the old records. gofind is the input box text query and it passes through fine. It searches perfectly, just doesn't filter out old records. It should show everything from today and into the future...more >>

Ok I have been trying to get use to this new interface for SQL server 2005 for a few weeks now and honestly I am halting ALL of our migrations to SQL 2005 because this tool is so bad. For the record the back end DB is nice however the interace for this thing sucks. My main compaint is the re...more >>

I am in the process of creating triggers on a few tables in our company database so that we can track or audit row changes. Each table we are auditing has a corresponding table that pretty much mirrors it with a few additional rows (auditdate and modificationtype). I am creating a separate ...more >>

We have a product and product_log table with the following data: Product table Product_code Prod_desc retail_price Is_seachable avaialbility 30001 Product one 35.50 0 L 30002 Product two 45.50 0 D 30003 Product three 15.99 1 I Product_log table Product_log_id Product_code Ins-user Ins_d...more >>

hi guys, I created one table which mainatains employee hierarchy with parent and child relationship..and the table structure goes like this Create TABLE dbo.Samp_Hierarchy1 (emp_id varchar(90) NOT NULL, emp_name varchar(255) NULL, emp_parent varchar(90) Not Null,...more >>

I want to install SQL Server 2005, but am conserned with being able to continue developing on databases that are still version 2k. Will I or could I have both versions installed and running on my machine at the same time? If not, will I be able to work on a v.2000 db from the new tools in VS ...more >>

Hello, I need to run a SQL script in MS SQL 2000 Query Analyzer that will... 1. create a new table 2. copy values from an old table into this new table (a kind of backup) 3. modify the original table by dropping and then adding some columns I have written some SQL script that should handl...more >>

Hi ! I am using Win2K and Microsoft SQL Server 2000. I have a text file of more than 10000 lines of T-SQL. Depending on some condition to be determined in another SQL script at run time, I should like to have the T-SQL in that text file executed or not. Can I have a SQL script executed f...more >>

It looks like the cidump.exe utility that can be used to extract indexes from a free text catalog was pulled out from SQL Server 2005 retail version. It was included in earlier betas. Was this tool renamed? Why was it pulled it? If it was pulled out, is there an alternative way to extract all ...more >>

Hello everybody, I used to have a cross-tab query which gives me the number of orders and the total value per year for each customer in the northwind database. The result looks like this: Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997 Now I try to rewrite it using the new PIVO...more >>

When attempting to edit diagrams in Server Explorer from Visual Studio 2005 I receive the following error: This database does not have a valid dbo user or you do not have permissions to impersonate the dbo user, so database diagramming is not available. Ensure the dbo account is valid and e...more >>

This allowed me to create a linked server, but when I try and view the tables, it does not show any tables. I assumed the tables would be the sheet names, no? EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'H:\IT\Shared\tblInItem.xls', NULL, ...more >>

I think the following will append items from an Excel file (tblInItem) into a sql table (hyc.dbo.tblInItem) and exclude possible records already exiting in the sql table. I am initializing a linked server to the excel file (the name is not full in this script), and the columns in Excel are ...more >>

I was wondering if there is a way to cancel an insert on the insert trigger based on whether valid data was entered. I tried putting a rollback transaction in there but it gives an error saying field does not accept null values. The rollback transaction seems to work for canceling an update ...more >>

DDL is posted here: http://68.6.120.147:8080/DDL.html I'm getting better with SQL everyday, but still confused about some things, such as this: phnnumber can be either an email address or a phone number, depending on whether phntype = 'email' or 'cell'. As such, each phnid will have two r...more >>

When creating columns in MS-SQL there is a description field. How can I query this description field? I know how to return the Table Name, Column Name, Data Type, Is_Nullable, etc. but I don’t know how to return the Description....more >>

Hello, I am on server A and tried to retrieve the tables from a database on a server B with their identity values. I use SELECT TableName = o.name, ColumnName = c.name, Ident = ident_current (o.name) FROM B.MyDataBase.dbo.syscolumns c JOIN B.MyDataBase.dbo.sysobject...more >>

hello, can I connect to foxpro db in sql server? or good way to connect to it? Please advice. Thanks. ...more >>

I have a table called order with a field called orderid and orderdate. How can I group the order id by week ending date. Thanks,...more >>

Hi, in brief the following occurs; in a trigger I call a stored proc to determine an account number this then should get inserted into a temp table with some other data for later re-use when creating a financial journal. to illustrate: create trigger someTrigger on someTable .......... ...more >>

Hi, in brief the following occurs; in a trigger I call a stored proc to determine an account number this then should get inserted into a temp table with some other data for later re-use when creating a financial journal. to illustrate: create trigger someTrigger on someTable .......... ...more >>

Is it possible to return a row counter in a single select statement? For example, I want to return Col1 and Col2 in TableA, but I need a counter to identify the returned row's positioning. RowCount ColA ColB 1 A B 2 C D 3 E F I'd like ...more >>

Hi I am getting quite unglued by problems with time-based data types in SQL Server/ADPs. We run the ADPs on machines that have either W2K or XP (prof). When a time is entered on an XP machine, it looks fine at the raw table level. If that record is then observed on a W2K machine, the time has ...more >>

Hi, I am looking for a template to create stored procedures to keep tracking of changes in the data. I have a staging tableA that contains new data, and another table B contains data from yesterday. I just need a script to insert/update data onto table B with an appropriated create/updat...more >>

I'm trying to set up a query to check several fields for the value of 'Debug' for example. I've been successful in doing this with LIKE 'Debug' or LIKE 'Debug%' however, I'd like to do an = comparison. Some fields are varchar, some are text. The text ones seem to be a problem. Is there a ...more >>

given it would appear i can use a cusor as an OUTPUT parameter to a remote stored procedure, what is the best way to retrieve/return a batch of data from a stored procedure....more >>

I have a Query as follows`: UPDATE Table1 SET vehicle_id = b.vehCorrect FROM marc_vehicle_bug_tmp_duff2 a, marc_vehicle_bug_tmp_duff_and_correct b WHERE a.vehicle_id = b.vehDuff The basis for this Query is as follows: SELECT COUNT(*) FROM marc_vehicle_bug_tmp_duff2 a, marc_vehicle_bu...more >>

I need to be able to strip out newline characters from data stored in a textarea, but I've been unable to locate data so far that tells me how newlines/carriage returns are stored in SQL Server. Any help on this is greatly appreciated. ...more >>

Hi, I have data stored as in below sample : -------------------------------+---------------------------------+-------------- DateBegin | DateEnd | Rate -------------------------------+---------------------------------+-------------- 2005-11-13 00:00:00 200...more >>

Hi How can I trim the seconds off the following datetime example? 6/10/2002 15:57:44 PM I need to put this in my where clause: Update stage_fact_account set fire_fee_balance = whjointdata.dbo.stage_fire_fee.stage_fire_fee_bal from whjointdata.dbo.stage_fire_fee where whjointdata.dbo.s...more >>

hi all, I want to write a stored procedure which returns duplicate row from table via parameter, get_duplicate_rows @table_name='table1' is it hard to do? or is there already such code out there? ...more >>

Hi, I am searching for a searching tool. I want to search in the wohle Database (all Tables) for a string-value . Any idea? Thx Wolfgang...more >>

I am trying to a find which date range in my data has the greatest combined quantity. For example for the following data DateFrom DateTo Qty 1/1/2005 10/1/2005 1 4/1/2005 7/1/2005 0 6/1/2005 12/1/2005 3 1/1/2005 2/1/2005 2 The period contai...more >>

Hi all, I am having a database with around 600 thousand records. Due to its huge size, its performance has decreased. Are there any commands or queries which I can execute to optimize the performance of my database? I also want to know whether there is any command in SQL Server ...more >>

Hi I wanted to create a history table, but it doesn´t really works: CREATE TRIGGER [Helpdesk_History_Copy] ON [dbo].[Helpdesk] FOR INSERT, UPDATE AS INSERT into dbo.Helpdesk_History ( Helpdesk_History.Computer_Idn, Helpdesk_History.DeviceName, Helpdesk_History.InsertDate,...more >>

This may be more of a SQL question. If so, please let me know so I can post it elsewhere. I have a high-usage web service (C#, 1.1) app that must create SQL logins on the fly. The app attempts to connect as the inbound user, and if the login does not exist, creates the login (via several ...more >>

I wanna create a trigger using c#. basing on the SQL 2005 Sample, I have written following code: [SqlTrigger(Name = "TestForInsert", Target = "[MyUser].[TestTable]", Event = "FOR INSERT")] ……// c sharp code when compiling the source code, VS 2005 pops a error which means that it can...more >>

How do you edit the value of a returned row in the new version? In version 2000 i can just click on the cell and the text cursor will show up. i can't figure out how to do that in the new version. ...more >>

I need to perform regular maintenance on a table who is referenced through FK from many other tables. Instead of attempting to bluntly delete the rows and let SQL report RI errors , I would like to first check whether the row is currently being referenced without having to check each referenci...more >>

hi how do i avoid using a cursor in this situation. i want all the payment installment_nos to be ordered by their due dates. only 'unpaid' payments can be ordered. /* drop table payment1 create TABLE payment1 (installment_id integer IDENTITY(1,1), amount float, installment_no int, due_dat...more >>

Hi All, when I run a stored procedure thru Query Analyzer, I get this error msg. Server: Msg 8941, Level 22, State 102, Line 5 Table error: Object ID 5810549, index ID 1, page (0:782677886). Test (offset + len < PAGESIZE) failed. Slot 25, offset 0x1089 is invalid. But when I tried to f...more >>