sql-server

MySQL "IS IN" equivalent?

A while ago I worked on a MS-SQL project and I remember a "IS IN" thing. I tried it on a MySQL project and it did not work. Is there an equivalent? Workaround? Here is the full query I am trying to run: SELECT * FROM product_product, product_viewhistory, product_xref WHERE ( (product_viewhistory.productId = product_xref.product_id_1 ...

SQL Server stored procedure beginner's guide

Please recommend some websites with a quick guide. MSDN is good but it's hard to pick up some basic stuff in a short time. Thanks. ...

SQL 2000 - How to automate incremental backups

How to you automate SQL Server 2000 incremental backups using SQL Server Agent and change the backup up name so that it has the current timestamp of when it gets backed up? ...

Application self aware of external database record modifications.

How do you make your application aware that a database record was changed or created by an external application rather than the application itself? Do you add a special check sum to the database record or what do you do to stop external changes to the database? (in case it was hacked and the hacker decides to add a new record or change ...

SQL Command for the following table.

I have a table named with "Sales" having the following columns: Sales_ID|Product_Code|Zone|District|State|Distributor|Total_Sales Now i want to generate a sales summary to view the total sales by zone and then by district and then by State by which distributor for the last/past month period. How can i write a Sql Statement to do this...

How can I automate updating my SQL Server database?

I get multiple text files daily to update my database from my client side and I am not a DBA expert. Every day I update it manually. Is there any method I can use to make this easier? I think there is a way to automate it but I don't know exactly what to do. ...

An Erroneous SQL Query makes browser hang until script timeout exceeded

I have an admin page in a Classic ASP web application that allows the admin user to run queries against the database (SQL Server 2000) Whats really strange is that if the query you send has an error in it (an invalid table join, a column you've forgotten to group by etc) the BROWSER hangs (CPU usage goes to maximum) until the SERVER scr...

Inserting Multiple Records in SQL2000

I have a web app that currently is inserting x (between 1 + 40) records into a table that contains about 5 fields, via a linq-2-sql-stored procedure in a loop. Would it be better to manually write the SQL Inserts to say a string builder and run them against the database when the loops completed rather than 30 transactions? or should I j...

Problem with sql like

Hi, I'm writing sql query to my SqlCommand, and some part of them looks like: WHERE Path like N'+(select top 1 path from [Paths] where objectcode=@objectCode and objecttype=@objectType)+%' order by path desc,objecttype desc I get no records , bu when I wrote the same in sql server 2005 i have plenty rows... where path like (select ...

SELECT with a Replace()

I have a table of names and addresses, which includes a postcode column. I want to strip the spaces from the postcodes and select any that match a particular pattern. I'm trying this (simplified a bit) in T-SQL on SQL Server 2005: SELECT Replace(Postcode, ' ', '') AS P FROM Contacts WHERE P LIKE 'NW101%' But I get the following error;...

How to add Transactions with a DataSet created using the Add Connection Wizard?

I have a DataSet that I have added to my project where I can Insert and Add records using the Add Query function in Visual Studio 2010, however I want to add transactions to this, I have found a few examples but cannot seem to find one that works with these. I know I need to use the SQLClient.SQLTransaction Class somehow. I used the Add...

SQL Server 2005 Full-Text Search - can I search for forward-slash characters?

I'm trying to use SQL Server 2005's Full-Text Search to find single forward-slash characters within my indexed column, without success. Can anyone tell me if this is possible at all? Example: In my CentralSearchCache table, the SearchData column contains a row with the text "This/string/contains/forward/slashes". This query: SELECT ...

SQL CHECK constraint issues

I'm using SQL Server 2008 and I have a table with three columns: Length, StartTime and EndTime. I want to make a CHECK constraint on this table which says that: if Length == NULL then StartTime <> NULL and EndTime <> NULL else StartTime == NULL and EndTime == NULL I've begun to try things like this: Length == NULL AND StartTime <...

SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Once i run this query, getting the following error. please help. select OrderDetails.Sku,orderdetails.mf_item_number,orderdetails.Qty,orderdetails.Price,Supplier.SupplierId, Supplier.SupplierName,Supplier.DropShipFees, cost = (Select Supplier_Item.Price from Supplier_Item,orderdetails,Supplier where Supplier_Item.SKU=OrderDeta...

sp_help with wildcards

There is a command in SqlServer sp_help 'table_name' (table_name or other stuff) Is there a possibility to use it when you don't know the whole table name, with wildcards or something like this: sp_help admin_% EDIT: Run sp_help (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME like '%admin_%') but had a strange result....

Problem to create of mapping schema file in SQLXMLBulkLoad

I want create a mapping schema file in SQLXMLBulkLoad. My property XML have repeating tags below: Now i want to insert image1, image2,.... in different column in a table(such as ...

Microsoft SQL Server 2005 Inserting into tables from child procedure which returned multiple tables

I've got a child procedure which returns more than table. child: PROCEDURE KevinGetTwoTables AS BEGIN SELECT 'ABC' Alpha, '123' Numeric SELECT 'BBB' Alpha, '123' Numeric1, '555' Numeric2 END example: PROCEDURE KevinTesting AS BEGIN DECLARE @Table1 TABLE ( Alpha varchar(50), Numeric1 int ) DECLARE...

MS SQL report error description

While runing MS SQL report I get an error "Error: Subreport could not be shown.". Is there any way to get more information regarding an error? ...

In SSRS what is the correct way to change a date from the standard "d" offered in the properties to a custom one?

I have a date as shown in expression: = Fields!last_sales_date.Value will show: 04/16/2010 Applying standard date properties allows only a few ways to display this date. I don't want to use the system dates supplied. What is the correct way to customize the display format to the following: Want to show: 4/16/10 Using VS 2003 ...

An Alternative to Views?

Hello all, I am just reading this article and I came across this: Filter: Remove any functions in the WHERE clause, don't include views in your Transact-SQL code, may need additional indexes. If I do not use views, what are the alternatives? I mean, in my situation, I want to select some data from a table and then use a few ...