I want to always update the value of an update row in the database. Imagine, i have a table with names and prices Every time a row is inserted or updated, i want to lower the price by a fixed amount. How can I do this with SQL server 2005? I have now something like CREATE TRIGGER LowerPriceOnInsert ON products AFTER INSERT, UPDATE AS...
I need to perform update/insert simultaneously changing structure of incoming data. Think about Shops that have defined work time for each day of the week. Hopefully, this might explain better what I'm trying to achieve: worktimeOrigin table: columns: shop_id day val data: shop_id | day | v...
I have a java JAR file that is triggered by a SQL server job. It's been running successfully for months. The process pulls in a structured flat file to a staging database then pushes that data into an XML file. However yesterday the process was triggered twice at the same time. I can tell from a log file that gets created, it looks lik...
What is the best way to create an Archive of image documents in the database ? Given we have about 2-10 million records and each record includes 2-4 images and about 20 text fields , what is the best way for create this archive so that we have good speed and high security for data? Also, what database is good for this project? ...
Hi everyone, I'm having some trouble with this statement, owing no doubt to my ignorance of what is returned from this select statement: declare @myInt as INT set @myInt = (select COUNT(*) from myTable as count) if(@myInt <> 0) begin print 'there's something in the table' end There are records in myTable, but when I run the code...
I have a query with loads of columns. I want to select rows where not all the columns are equal to 0. select * from table where not ( column1 = 0 and column2 = 0 and column3 = 0 and ... column45 = 0) Is this really the tidiest way to do it? Supposing I then need to change it to ignore when all columns are 1, or negative.. It...
Hello, I have a loop that runs for approx. 25 minutes i.e 1500 seconds. [100 loops with sleep(15)] The execution time for the statements inside loop is very less. My scripts are hosted on GoDaddy. I am sure that they are having some kind of limit on execution time. My question is, are they concerned with "the total CPU execution tim...
I want to tell the user that a record was not deleted because it has child data, but how can I be sure that the exception was thrown because of a foreign key violation? I see that there a sqlexception class that is used for all sql exception. ...
We have a few tables with persisted computed columns in SQL Server. Is there an equivalent of this in Teradata? And, if so, what is the syntax and are there any limitations? The particular computed columns I am looking at conform some account numbers by removing leading zeros - an index is also created on this conformed account number...
Alright, this problem is a little complicated, so bear with me. I have a table full of data. One of the table columns is an EntryDate. There can be multiple entries per day. However, I want to select all rows that are the latest entry on their respective days, and I want to select all the columns of said table. One of the columns is a...
I'm trying to come up with a way to query the values in two different columns in the same table where the result set will indicate instances where the value of columnB doesn't contain the value of columnA. For example, my "Nodes" table contains columns "NodeName" and "DNS". The values should look similar to the following: NodeName D...
I use an API that expects a SQL string. I take a user input, escape it and pass it along to the API. The user input is quiet simple. It asks for column values. Like so: string name = userInput.Value; Then I construct a SQL query: string sql = string.Format("SELECT * FROM SOME_TABLE WHERE Name = '{0}'", nam...
I am trying to insert a value to sql_variant column in sql server from an xml column ex. INSERT INTO [dbo].[TestColumn] ( Id, Attribute, AttributeValue ) SELECT Id, 'TestName', CAST(CustomColumns.query('//TestName') AS nVarchar(MAX)) FROM [dbo].[Clmnt] (NOLOCK) I got this error, Operand type clash: nvarch...
Given that I have a table that holds vehicle information and one of those pieces of information is VehicleType (usually 6-20 characters), what are the technical reasons why it is better to design the tables like this: Vehicles VehicleID VehicleTypeID (INT) (relates to an INT in the VehicleTypes table) versus this: Vehicles Veh...
Jeff Atwood asked the original question about parameterizing a SQL IN clause, but I want to do this with an integer column. If I try the code from the original post I get the following exception, which makes sense: Conversion failed when converting the varchar value '%|' to data type int. Anyone try this before? ...
Input @StartDate = '01/25/2010' @EndDate = '02/06/2010' I have 2 CTEs in a stored procedure as follows: with CTE_A as ( [gives output A..Shown below] ), with CTE_B as ( Here, I want to check if @StartDate is NOT in output A then replace it with the last known date. In this case, since @startdate is less than any date in output A...
Environment: asp.net 3.5 (C# and VB) , Ms-sql server 2005 express Tables Table:tableUser ID (primary key) username Table:userSchedule ID (primary key) thecreator (foreign key = tableUser.ID) other fields I have created a procedure that accepts a parameter username and gets the userid and inserts a row in Table:userSchedule Problem: ...
Is this equivalent to a LEFT JOIN? SELECT DISTINCT a.name, b.name FROM tableA a, (SELECT DISTINCT name FROM tableB) as b It seems as though there is no link between the two tables. Is there an easier / more efficient way to write this? ...
We have recently moved some data from an SQL Database instance to another one in another location. I seemed to have noticed that there are some facets of our old database instance where the date is passed as String to the SQL server and SQL server is able to parse it properly. For example, the application would simply pass a string val...
does BI studio come bundled with another software? i am trying to create a report server for SSRS but am not able to find BI on my computer and cannot find a place where to download. thank you! ...