Is there anyway to loop through a table variable in T-SQL? DECLARE @table1 TABLE ( col1 int ) INSERT into @table1 SELECT col1 FROM table2 I use cursors as well, but cursors seem less flexible than table variables. DECLARE cursor1 CURSOR FOR SELECT col1 FROM table2 OPEN cursor1 FETCH NEXT FROM cursor1 I would like to be ...
Hi, I am starting to experiment with using InnoDB in web applications. I've setup some tables with a foreign key, but they are not behaving as expected. Here are my table CREATE statements: CREATE TABLE sections ( section_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(section_id), title VARCHAR(30), created_at int(10) NOT NULL, upda...
I try to use SQL Server Compact Edition with Entity Framework in Visual Studio 2008 SP1. Here's what I do: 1) I create a new project, of type Console Application. 2) I right-click on the project, select Add->New Item. 3) I choose to add a Local Database called Something.sdf 4) In the next page of the "Add New Item" wizard, I choose t...
I have two named scopes... both which work separately, but when combined do not work. named_scope :total, :select => "COUNT(*) as days, AVG(price) as price, SUM(price) AS total", :group => :parent_id named_scope :currency, lambda { |code| { :select => "*, price * #{(CurrencyRate.get_rate("USD", (code ||= "USD") ,1))} AS price" } } ...
how can i decide this problem? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetDataById] () RETURNS INT AS BEGIN DECLARE @query NVARCHAR(500) DECLARE @j INT SET @query=N'select * from catalog' EXEC sp_executesql @query RETURN @j END When I try to exec this one: select dbo.GetDataById() I get an ...
So, I've been searching around and I've found things similar to my problem, but I need more help to get a real solution. I'm trying to construct a query that will return 2 columns of data, the first column should be a list of the column names themselves and the second should be the value of that column. Visually it would look like this...
Is there any to get the an AUTO_INCREMENT field of a InnoDB to start counting from 0 not 1 CREATE TABLE `df_mainevent` ( `idDf_MainEvent` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`idDf_MainEvent`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ...
I've got a problem I was wondering if there's an elegant solution to. It is a real business problem and not a class assignment! I have a table with thousands of records, some of which are groups related to each other. The database is SQL 2005. ID is the primary key. If the record replaced an earlier record, the ID of that record is i...
I'm using Sqlyog to run queries on a db. Is there a way to view the all the current running queries on the db (and possibly kill some of them)? If not, what is a recommended tool for this purpose? ...
Hello All I have a huge problem with the REPLACE SQL function in Microsoft SQL Server Management Studio Express. When I do following query SELECT REPLACE('ArticleNumber', 'S401', 'I0010') SELECT REPLACE('ArticleNumber', 'S302', 'I0020') SELECT REPLACE('ArticleNumber', 'S303', 'I0030') SELECT REPLACE('ArticleNumber'...
I am developing a little data warehouse system with a web interface where people can do filtered searches. There are current about 50 columns that people may wish to filter on, and about 2.5 million rows. A table scan is painfully slow. The trouble is that the range of queries I'm getting have no common prefixes. Right now I'm using sql...
We want to distribute / synchronize data from our Datawarehouse (MS SQL Server) to external customers (also MS SQL Server). The connection has to be secure, because we are dealing with trusted data. Transmission of data from our system to external client system must be via the http/https In addition it is possible that the clients still...
Hi, I'd love to hear people's views on the pros and cons of mixing SQL Cache Dependency with Linq to SQL, as described in this article: http://code.msdn.microsoft.com/linqtosqlcache This works for me, but I'm interested to know if anyone has any alternatives? Cheers, Tim ...
Using a TSQL update command against a SQLServer database, how can I update a column of type FLOAT with the smallest possible double value? The smallest possible double value in hex notation being 3ff0 0000 0000 0001 (http://en.wikipedia.org/wiki/Double%5Fprecision) ...
Consider this T-SQL: CREATE USER my_test_user WITHOUT LOGIN; SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name(); EXECUTE AS USER = 'my_test_user' WITH NO REVERT; SELECT USER_NAME(), USER, SUSER_SNAME(),SYSTEM_USER, suser_name(); I'm looking at using these accounts WITHOUT LOGIN for auditing purposes. Basically, my "get...
var adminCov = db.SearchAgg_AdminCovs.SingleOrDefault(l => l.AdminCovGuid == covSourceGuid); adminCov keeps coming back null. When I run SQL profiler, I can see the generated linq, when I past that into management Studio, I get the result I expect. LinqToSql generates this: exec sp_executesql N'SELECT [t0].[AdminCovGuid], [t0].[Admin...
AIM : Convert a resultset to XML and assign the result to a variable Result Set : M000017690 324067342 324067349 324067355 324154449 Converted XML : <Products> <Product ProductId="324067342" /> <Product ProductId="324067349" /> <Product ProductId="324067355" /> <Product ProductId="324154449" /> <Product ProductId="M0000176...
So, I have been trying to fix this for about two months. It all started when my "dev" machine went kaput and I set it up on my laptop. It was working fun on my old PC but, it does not work on my new PC and never did on laptop. I structured the SQL Server as much like the first one as I could remember but, it started giving me SQLExcep...
We sometimes write dataport scripts that rename tables or columns and have other complex logic. We also have SQL in IF statements referencing those old columns or tables. We have a standard to use IF statements to make our SQL Scripts multi run friendly. However occasionally, even though the if statement evaluates to false, the code w...
How to check if the global Temporary table exists in SQL server, if yes then delete that global temporary table? I am trying to execute this: IF OBJECT_ID('##Table', 'U') IS NOT NULL DROP TABLE ##Table ...but it is not working. ...