Me and my colleagues have a question regarding SQL Server 2008 query length and the SQL Server Optimizer.
We are planning to generate some stored procedures that potentially have a lot of parameters. Inside of our stored procedure we will simply select some values from a table joining other tables.
Our stored procedures will look like ...
Hi,
Say I have the following table:
ID|Read
-------
1|true
2|false
3|false
4|false
... and I need to read the smallest ID, that has [Read] == false; plus, update that I have now read it.
So if i execute my Stored Procedure dbo.getMinID, it will return ID: 2, and update [Read] -> true.
CREATE PROCEDURE [dbo].[getMinID]
(
@Quer...
I am on SQL Server 2008 and I have a table containing WA metrics of the following form :
CREATE TABLE #VistitorStat
(
datelow datetime,
datehigh datetime,
name varchar(255),
cnt int
)
Two days worth of data in the table looks like so:
2009-07-25 00:00:00.000 2009-07-26 00:00:00.000 New Visitor 221
2009-07-25 00:00:00.000...
Hello.
Is there an easy way to get if string is an integer number (consists only of digits) in MS SQL 2005?
Thank you for your help.
...
Is there a valid way in SQL Server 2005 to read a row of data into a string?
For example if my table looked like the following:
ID | First Name | Last Name | Color
-----------------------------------
1 | Timmy | Jones | pink
2 | Martha | Fisher | green
That I could get back as a result:
'1 Timmy Jones pink'
'2 Mart...
So I know this problem isn't a new one, but I'm trying to wrap my head around it and understand the best way to deal with scenarios like this.
Say I have a hypothetical table 'X' that looks like this:
GroupID ID (identity) SomeDateTime
--------------------------------------------
1 1000 1/1/01
1 1001 2/2/02
...
My database has two tables, one contains a list of users, the other a list of roles. Each user will belong to one or more roles, and of course each role will have multiple users in it.
I've come across two ways to link the information. The first is to add a third table which contains the ID's from both tables. A simple join will the...
When I run this query
SELECT CustomerId FROM Stocks.dbo.Suppliers
It gives me this error. Invalid column name 'CustomerId'. This error is valid as there is no column CustomerId in Suppliers table; but when I use same query in subquery it does not give any error E.g.
SELECT *
FROM SomeOtherDb.dbo.Customer
WHERE CustomerId In( SEL...
I inherited a table with identifiers in a format [nonnumericprefix][number]. For example (ABC123; R2D2456778; etc). I was wondering if there was a good way to split this in SQL into two fields, the largest integer formed from the right side, and the prefix, for example (ABC, 123; R2D, 2456778; etc). I know I can do this with a cursor,...
I am transforming data from this legacy table:
Phones(ID int, PhoneNumber, IsCell bit, IsDeskPhone bit, IsPager bit, IsFax bit)
These bit fields are not nullables and, potentially, all four bit fields can be 1.
How can I unpivot this thing so that I end up with a separate row for each bit field = 1. For instance, if the original table...
I have a table that contains file paths, like so:
--------------------
|Files |
--------------------
|path nvarchar(500)|
--------------------
I want to split it into two tables, one containing unique directories and one containing filenames:
---------------------------
|Files |
------------------------...
I am getting an error when trying to create a relationship within two tables in Sql Server 2005. I am attempting to create the relationships using the database diagram feature. I have a Player table and a Message table. I want to create two relationships from the Message table to the Player table. I can successfully create the first rela...
C++Builder ADOQuery SQLServer
I'm using a stored procedure with this select
SELECT Name,
COALESCE(
(
SELECT TOP 1 0
FROM TbUserParam
WHERE TbUserParam.ID_User = @ID_User
AND TbUserParam.ID_Param = CfgListParIzm.ID_ListParIzm
), 1) Visi
FROM CfgListParIzm
WHERE ...
Hi,
We're having an issue with a poorly coded SQL function(which works fine in live, but not in our test environment). This function is used to provide a default value in many tables, trying to ALTER the function returns a "Cannot ALTER ### because it is being referenced by object" error.
Is there any way round this error message? T...
I want to do query as below. Query is wrong but describes my intentions.
SELECT name, dateTime, data
FROM Record
WHERE dateTime = MAX(dateTime)
Update: Ok. The query describes intentions not quite good. My bad.
I want to select latest record for each person.
...
We have a table of transactions which is structured like the following :
TranxID int (PK and Identity field)
ItemID int
TranxDate datetime
TranxAmt money
TranxAmt can be positive or negative, so the running total of this field (for any ItemID) will go up and down as time goes by. Getting the current total is obviously simple...
I was wondering if there are any tools similar to Pex that analyze T-SQL stored procedures and functions (instead of managed code) in order to generate meaningful and interesting input values for parameterized unit tests.
...
I have two tables. I am trying to find rows in one table which do not exist in second table based on values in two columns. (I have simplified the tables to include the two columns only). There are no primary/foreign keys between the two tables. Seems simple enough but I am having a brain block now!
DDL:
CREATE TABLE [dbo].[Table_1](
...
Say I have a text file that looks like this:
date 1/1/2010
a,b,c
a,b,d
...
I want to import it into a table so it looks like this:
1/1/2010,a,b,c
1/1/2010,a,b,d
...
What is an elegant way to do that?
My best idea so far is to use a data flow package, and use a flat file source to read in the file (ignoring the first line) and lo...
I have a database with legacy data that stores transactions and uses a "bucket" method for determining account balances. I need a way to get aged past due for accounts.
Table Transactions
TransactionId
TransactionType (CHARGE,RECEIPT)
Amount
PostDate
To get the current balance:
SELECT SUM(CASE TransactionTypeId WHEN RECEIPT THEN Amoun...