SQL Select - How To Make This One Work?
Aug 15, 2006Select *
From HSRSeason
WHERE
SeasonID='1'
HSRID=(Select HSRID FROM HSR WHERE HotelID='1' AND ServiceID='1' AND RoomID='1')
ADVERTISEMENT
How Do I Make It Work?
Jan 30, 2008I have an OLAP database which is required to update daily. First of all back track a bit about this OLAP database, It is built from an OLAP data source consists of a dozen "Dimension" tables and half a dozen "Fact" tables and they are set up in a normalized relationship. This 20 or so OLAP tables are created from a single "Production" data warehouse de-normalized table. By the way the database engine, SSAS, SSRS and SSIS are installed in one virtual server using Sql 2005 SP2.
Here are the steps I update the OLAP database manually.
At Sql database engine
Drop the existing data warehouse table at the virtual server
Copy the up-todate data warehouse table from a remote server and paste to the database at the virtual server
Drop all the OLAP Fact tables
Drop all the OLAP Dimension tables
Create the Dimension Tables using the Sql script I saved when first created
Create the Fact Tables using the Sql script I saved when first created
Populate the data to the Dimension Tables from the data warehouse table using the Sql script I saved when first created
Populate the data to the Fact tables from the data warehouse table using the Sql script I saved when first created
Assign indexs and constrains to the Dimension Tables
Assign indexs and constrains to the Fact Tables
At Sql Server Analysis Services
Process the 15 Dimensions
Process the 2 OLAP cube
I want to automate the whole process using SSIS. I am new to SSIS and I need some direction on how to make it work?
Thanks
Can't Seem To Make This Query Work
Jul 24, 2006Hi,
I have a query thatI need to make into one query for the sake of an application reading only one cursor.
Here's the syntax:
select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrowinner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled
from a10 a inner join escrow d on a.escrow = d.escrowinner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer
The error message i'm recieving is the following:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Any ideas? any suggestions would be great.
Thanks
I Can't Make SqlBulkCopy Work
Feb 7, 2008I have a scenario whereby I'd like to insert multiple rows into a table on a SQL server database as efficiently and easily as possible.
After some research, it looked like .NET 3.0's SqlBulkCopy class would do what I want. I've tried to set something up, but it's not working. It's not even throwing an error. The code executes but it simply hasn't done the insert by the end of it!
My table structure is simple. The name of the table is LPSTUnavailableDate. It has just two columns, one of them an auto-populated ID field:
LPSTUnavailableDateId, INT, PRIMARY KEY, IDENTITY(1,1)
How To Make Two Separate Query Work Together
Oct 15, 2014I am currently stuck on how to make this 2 separate query work together, both work as i want them to individually, please note the syntax is according to a application i use that uses mysql to manipulate columns in an imported csv file.
CONCAT('at-',
REPLACE([CSV_COL(18)],'http://www.homebuy.co.uk/product.php/','')
)
removes last character i.e. /
SUBSTRING([CSV_COL(18)], 1, CHAR_LENGTH([CSV_COL(18)]) - 1)
basically i need these 2 to work together to give me an output like this
at-09fd8903
from the this. URL...url above minus the "" as i said both work on there own, but not together.
Can't Make This Update Work - Advice??
Feb 26, 2008Hi - I'm in a situation with a very large table, and trying to run an update that, any way I've approached it so far, seems to be taking unnacceptably long to run. Table has about 20 million rows, looks something like this:
ID - int, identity
Type - varchar(50)
PurchaseNumber - varchar(50)
SalesAmount - Money
ID Type PurchaseNumber SalesAmount
1 A 3834AA38384 20.32$
2 B 3834AA38384 11837.32$
3 C 3834AA38384 666.32$
4 C 887DF88U01H 23423.32$
5 A 887DF88U01H 12.32$
6 B OI83999FH28 4747.1$
7 D 38438495985 9384.6
8 E 02939DDJJWI 22.22$
9 F 07939SDFDF2 33.33$
The goal of the update is to make the [Type] uniform across [PurchaseNumbers], according to the max sales amount. For each PurchaseNumber a, set the type = the type of the row that has the MAX salesAmount. If there is only one entry for PurchaseNumber, leave the type alone. Expected update after completion would look like this:
ID Type PurchaseNumber SalesAmount
1 B 3834AA38384 20.32$
2 B 3834AA38384 11837.32$
3 B 3834AA38384 666.32$
4 C 887DF88U01H 23423.32$
5 C 887DF88U01H 12.32$
6 B OI83999FH28 4747.1$
7 D 38438495985 9384.6
8 E 02939DDJJWI 22.22$
9 F 07939SDFDF2 33.33$
I got this out of a warehouse, and it definitely isn't normalized well. Was considering breaking down into a better model, but I'm not yet sure if that would make the update easier.
I've been approaching this with sub-queries (finding all the PurchaseNumbers with more then one entry, then the max sales purchase of that purchase Number, then the type of that purchase number and sales amount to update all of that purchase number) but this not only ends up a little messy, but also very slow.
The only other detail that may be important is that out of the 20 million total rows, about 19.5 million purchaseNumbers are unique. So, really, there are only about 500k rows I actually have to update.
I've thought of a few ways to make this work, but none of them seem fast and wanted to see if anyone had a pointer. Thanks!
SQL Parameter And Wildcards, How To Make It Work?
Oct 22, 2007
Hello, I have what should be a very simple problem, but I cant solve it.
I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)
Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:
DECLARE @Dv_id nchar(15)
SET @Drv_id = '%'
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id
SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'
OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows.
The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between:
WHERE Drv_id LIKE @Drv_id
and
WHERE Drv_id LIKE '%'
?
The wildcard statement '%' is supposed match everything, correct??
It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.
Is there any way around this?
Error 26 - Followed The Guidelines, Still Unable To Make It Work
Nov 7, 2007
Good Morning, I've been searching through all the tutorials and questions, have tried many things. I am still getting "[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]"
as an error. This is what I've got:
I€™m using SQL Server Express 2005, installed with the default settings. Had not touched this program for anything until I started to follow directions to fix Error 26. Visual Web Developer Express ASP.NET is what I€™ve used to build the webpage. I was using the ASP.NET web configuration to add users to the database, which is set to use the provider ASPSqlServerProvider.
SQL Server 2005 Surface Area Configuration
Database Engine Remote Connections €“ set to €śLocal and Remote Connections €“ Using TCP/IP Only€?
SQL Server Browser is Enabled and Running
- is set to Active, under Built In Account €“ Network Service
I have created Windows firewall exceptions for:
sqlservr.exe
sqlbrowser.exe
udp port 1434
SQL Server Configuration Manager
both SQL Server and SQL Server Browser are running.
Under SQL Server 2005 Network Configuration
Shared Memory and TCP/IP are enabled only.
SQL Native Client Configuration
Shared Memory 1
TCP/IP 2
Named Pipes 3
all enabled
I read through the post at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx but as I'm new to SqlServer I do not know how to check on the first three items.
I'm getting extremely frustrated, and would just like the login portion of this website to start working before my boot ends up through the computer. Please help, lol, thank you.
Transact SQL :: How To Make Procedure Work For Multiple Values In Parameter
Jun 7, 2015Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO
[code]....
Can’t Make Vs2008pro, Sql2005dev, Sqlexpress All Work Together Via F5run And Vista Localhost At Same Time??
May 16, 2008Clean vista install,
Clean full office 2007plus install
Clean visual studio 2008pro install
Many failed sql 2005dev installed, did SKUUPGRADE-1
Now have office2007 Smallbiz, VS2008pro, SqlExpress and sql 2005dev installed.
Note outlook 2007 error on first run after sql2005dev.
Seams to have broken and created a new sql connection, don€™t think its the original.
but that€™s not the current issue !
web admin page error, could not connect to db
I did regsql.exe from the net dir and the web admin work and the db is created for roles and such in vs 2008 via web admin page and mssql.
Managed to create db in apps data folder and modify connection in server explorer.
Added table to aspx page from apps folder and it runs via f5, but errors under localhost.
€śCannot open user default database. Login failed.€?
€śLogin failed for user 'NT AUTHORITYNETWORK SERVICE'€?
Have seen some reference to iis permissions, but not sure what to change?
Is it a file level permission, a virtual permission
Works in vs2008 using f5 run!
http://localhost:50115/vs2008/
Can work with apps folder db file in vs 2008 server explorer
Had to modify db connection back to sqlexpress in server explorer
Can work with db in sql management studio, have three local instances;
Pc
Pcsqlexpress
Pcsmallbiz
Can display db data in aspx page via f5 , but not localhost unless vs2008 is not running!!!
Have not been able to make vs2008, Sql2005dev, sqlexpress all work together using iis in vista via localhost and vs2008 f5 at same time??
Can€™t do anything with db in solutions explorer, but can work in server explorer if I modify connection back to sqlexpress
Can€™t display data aspx page via localhost
http://localhost/vs2008/
Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITYNETWORK SERVICE'
Localhost works in iis via virtual directory if I remove db grid in page or vs is not running
Could someone test and tell how to use all of these together?
What is going to happen if I get it to work local and then try to upload to shared host?
<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Server=wabpc;Database=VS2008test;Integrated Security=true" providerName="System.Data.SqlClient" />
<add connectionString="Server=SQLEXPRESS;Database=Database.mdf;Integrated Security=true" name="sqlexpress" />
</connectionStrings>
If I close vs2008 and the management studio, localhost works and displays the db info!!
Open vs2008, modify connection back to sqlexpress and the aspx page breaks via localhost
Close vs2008 and local host works
Open vs2008 and get a error 25 in server explorer connection string invalid, but f5 works after the offline clears.
I change db connection back to sqlexpress to work with db in server explorer!
That breaks localhost, but f5 works
Any ideas?
Need more Tylenol
(Select All) In Multi-select Enabled Drop Down Parameters Doesn't Work
Apr 29, 2008Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType
from Product
order by ProductType
Any suggestion? thx
Ways To Make This Work: Several Selectable Related Record For One Main Record.
Apr 6, 2007Hey all!
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
Thanks in advance,
Johan
Impossible To Make A Select
Aug 24, 2005I've created a database : hotelselect * from hotel worksselect * from hotel where dpt='5' doesn't work : generate a errorAnyone has a explanation ?create table hotel ([hoteID] int IDENTITY (5000,1) NOT NULL,[dpt] ntext NOT NULL) ON [primary]
View 3 Replies View RelatedHow To Make A Correct Select
May 24, 2006Could anyone help med with a select statement with a join between totables. It is to be used in a OLAP cube.I Havde table LedgerBudget and Table Admin. In table admin I can setupa from and to date and also a budgetmodel.The admin have ONE record per OLAP cube.The statement below works fine if I have stated a budgetmodel in tableAdmin.But if no budetmodel stated in table Admin, I want the statement toselect every ledgerbudget with active = 1 and allocatemethod = 0Could anyone help me with this.SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE, - dbo.LEDGERBUDGET.AMOUNT ASBUDGETFROM dbo.LEDGERBUDGET INNER JOINdbo.ADMIN ON dbo.LEDGERBUDGET.STARTDATE >=dbo.ADMIN.FROMDATE ANDdbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATEANDdbo.LEDGERBUDGET.MODELNUM =dbo.ADMIN.BUDGETMODELIDWHERE (dbo.LEDGERBUDGET.ACTIVE = 1) AND(dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0)BR/Thanks
View 6 Replies View RelatedWant To Make A Select With Special Chars?
Jan 4, 2006Hello,
I have a problem....
In a table i have some descriptions and i notice that at least one is as follow:
"some product 'delay' to every"
now if i try to make a insert in another table with that description i get an error...
because of the char '
is there any way to make this insert?
the database is MSSQL 2000
Take Id-s From Returned Rows, And Make A New SELECT
Jun 14, 2006Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like
SELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.
My question is: Can I do the same in my stored procedure? (without sending it to page)
Here is it:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path
fileCategories table: (for information)Here is the screenshot of the table (10 kb )http://eai.w2o.ru/screen1.gif
Thank you
Artashes
How To Make Query Select Option Results
Jul 12, 2014I have a Managers table in my SQL DB and it has a ManagerID, MgrName and MgrPhoto field base...
I can code a form with a select button that displays a drop down with the managers in it as choices, but am a little confused as to how I would make the PHOTO (MgrPhoto) change to the corresponding ManagerID that is selected from that option drop down since the SELECT CODE in the form only queries the ManagerID / MgrName combo for the choice.. The photo is below that drop down box and how to make it change to whatever photo is assigned to the selected ManagerID / MgrName ?
Here is the form code with query :
<form enctype="multipart/form-data" action="updatemanagerphoto.php" method="POST">
<select name="ManagerID" id="manager" style="width:400px" class="form_textbox">
<?
$db_connect= mysql_connect($db_host, $db_username, $db_password);
mysql_select_db($db_name, $db_connect) || die(mysql_error());
$sql_query= "SELECT * FROM Managers ORDER BY MgrName ASC";
[code]....
Can I Make A Temp Table With A Union All Select?
Mar 26, 2008I'm having trouble creating a temp table out of a select statement that uses multipe union alls.
Here's what I have, I'm trying to get the results of this query into a temp table...
select
parent,
(select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id,
(select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp,
(select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets,
sum(own_assets) as Total_child_own_assets
from
(
Select parent,
Child,
(select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets
from
(Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,1) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,1) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,2) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,2) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,3) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,3) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,4) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,4) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,5) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,5) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,6) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,6) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,7) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,7) is not null )as c
) as d
group by parent
having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)
How To Make Select Query To Access View In Other Server
Mar 7, 2007Dear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help
thanks
SQL 2012 :: Select Multiple Values From Same Column And Make Them To Show In A Row
Jun 10, 2015I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
My script is here, and the sample result is attached. How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
[code]....
How Do I Make 30 Sec Running Query (select C1 Sum(x) From T1 Where C1 &&> 1000 Group By C1) Run Faster?
Aug 10, 2007It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
Thanks in for you help in advance
SQL Server 2008 :: Select Multiple Values From Same Column And Make Them To Show In A Row
Jun 10, 2015I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
[code]....
Why Won't This Work? Select Stmt
Sep 20, 2006select uid, OrderID, Count(OrderID)As DupCnt
from OrdDetails
group by uid, OrderID
having count(OrderID) > 1
this returns no rows, can't I show another column to identify which uid goes with the dups, I did it before and now it doesn't work, probably something silly I'm missing.
thx,
Kat
Does 'SELECT INTO' Not Work In SQL Mobile?
May 15, 2006I'm trying to programmatically create a new table that is a copy of another. The SQL statement I'm making is:
"SELECT * INTO sensor_stream_temp FROM sensor_stream"
and various combinations thereof (such as specifying the columns, etc)
The error I keep getting is:
There was an error parsing the query. [Token line number 1, Token line offset 10, , Token in error = INTO ]
Is it not possible to copy a table using this statement? *sigh*
Thanks in advance for any help.
-Dana
Help! Select Top 10 Does Not Work On Another SQL2000 Server...
Sep 6, 2001I use 'select top 10 customerID, customerName from customers' on one
testing maching 'Test1', it works.
But after restore the database from 'Test1' to 'Test2', the same sql
select top statement does not work. Both machine are SQL2k.
appreciate your help!
-D
Why Doesn't SELECT @columns... Work?
Aug 12, 2005Bob writes "Your FAQ asks an intersting question:
Why doesn't SELECT @columns FROM @tablname work?
No answer is provided however.
I need to write a stored procedure the passes in a fieldname, retrives next key type int data from that field, increments the field, and returns the NextKey.
Would be nice if something like this worked:
DECLARE @iNext INT
SELECT @iNext = SELECT @columnName FROM Next_Keys WHERE ID = 1
SELECT @iNext = @iNext +1
UPDATE Next_Keys Set @columnName = @iNext WHERE ID = 1
RETURN @iNext
SQL Server 2000 - Win Server 2003
Editing SP from VS2005
TSQL newbe writing first sp"
Case Does Not Work In My Select Statement
Dec 13, 2007Hello everybody
I have problem with CASE statement. Here is select it
Code Block
select
mev.Id
,mev.MetaElementId
,mev.ElementValue
,mev.DocumentId
,me.ElementTypeId
,castedValue =
case
when me.ElementTypeId =3 then cast(mev.ElementValue as integer)
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)
end
from dbo.tbMetaElementValue mev
inner join dbo.tbMetaElement me
on mev.MetaElementId = me.Id
where mev.MetaElementId =7
it returns
Id
MetaElementId
ElementValue
DocumentId
ElementTypeId
castedValue
49
7
2006
28
3
6/30/1905 0:00
53
7
2004
30
3
6/28/1905 0:00
61
7
2006
36
3
6/30/1905 0:00
67
7
2005
38
3
6/29/1905 0:00
70
7
2004
39
3
6/28/1905 0:00
105
7
2003
63
3
6/27/1905 0:00
166
7
2006
109
3
6/30/1905 0:00
195
7
2005
129
3
6/29/1905 0:00
220
7
2005
150
3
6/29/1905 0:00
223
7
2006
151
3
6/30/1905 0:00
As you can see it should return castedValue as integer but it cast to datetime which is wrong. If I commented line
Code Block
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)
it casts everything normal, but as soon as it has more than one condition in CASE it will choose anything but not right casting
Looks like I am missing something really fundamental. Any help is apreciated !
Thanks
Multi Value Select Doesn't Work
Jan 31, 2008My report runs from a stored proc, which gets the user to input a project id and a status. So the where statement in my stored proc looks like this:
Where (p.project LIKE @project_ID + '%' AND p.status IN(@Active))
On my report, I have set the @Active parameter to multi-value, and entered the available values(active and inactive). When I run the report, and only select one of the values, the report runs just fine, but when I use the "select all" option, I get no results at all.
I can't see where I am going wrong. Anyone got any ideas?
Thanks in advance!
2 Sql Select Statements In 1 Sql String Just Wont Work!
Jul 5, 2007hi there, i have a bit of a strange problem, and i must be doing something wrong because this works with insert and update statements in other pages. What is going on is that i am trying to do 2 select statements in the same bit of code so i dont have to open a datareader twice, but if i put the delivery charge select before the other it seems to get it, but if i put it after it does not, i just cant work it out! i have posted my code below for you all to have a look at because im stumped!
//gets order id and customer id and puts them to strings and int to be used laterstring strCustID = Request.QueryString["qsnOrderCustID"].ToString();
int intCustID = Convert.ToInt32(strCustID);string strOrderID = Request.QueryString["orderID"].ToString();int intOrderID = Convert.ToInt32(strOrderID);
//This is the sql statement.string sql = "SELECT [sub_total], [VAT], [Total] FROM tbl_order WHERE order_ID = " + intOrderID + ";" + "SELECT [del_cost] FROM tbl_del WHERE order_ID = " + intOrderID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn);
myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader();
//This reads the first result from the sqlReader
dr.Read();
//This sets the title label text to the value of the description column.
try
{
//fill these labels with the first select statement lblSubTot.Text = dr["sub_total"].ToString();
lblVAT.Text = dr["VAT"].ToString();lblOrderTotal.Text = dr["Total"].ToString();lblDelCharge.Text = dr["del_cost"].ToString();
//fill this label with the second select statementlblDelCharge.Text = dr["del_cost"].ToString();
}catch (Exception except)
{string strError = Convert.ToString(except);
}
finally
{
myConn.Close();
}
any ideas would be great! thanks
Jez
Linked Servers: SELECT * INTO Doesn't Work
Sep 21, 2001I administer several servers. My login is a SysAdmin on all servers with not only the same Name and Password, but also the same SID. In setting up Linked Servers, I can successfully SELECT data from a server other than the host server, but if I try to SELECT * INTO I get the following error:
The object name 'DB1.ogSerial.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Here are the queries I've tried (using DB3 as the host, DB1 as the linked server).
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB3.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM DB1.MyDB.dbo.TestTable
SELECT * INTO DB1.MyDB.dbo.TestTableNew FROM MyDB.dbo.TestTable
Exec('select...into #temptable...) Doesn't Work
Mar 11, 2008Code:
exec('select RIGHT(00000 + CAST(dh.zipcode AS varchar(5)), 5) as zip, '+@fieldname +' as distance into #distance from sumplicity.dbo.t_distancetohospital dh')
This runs normally and returns the number of rows created. Yet when I do a 'select * from #distance' query, I get a message that #distance doesn't exist.
Where is it storing all of this and how do I access it?
Need To Return X Rows But Select Top Doesn't Work With Variable
Jan 26, 2006I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
Thanks!
Data Comes Correct With Select But When Convert To Update - It Does Not Work
Jun 10, 2015Consider the below script
CREATE TABLE #TEMP(Id int,CreatedBy varchar(30),ModfiedBy varchar(30))
CREATE TABLE #TEMP2 (ID int,SearchedBy varchar(30))
INSERT INTO #TEMP VALUES(1,'James',NULL)
INSERT INTO #TEMP VALUES(1,'James','George')
INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
SELECT CASE WHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
WHERE #TEMP2.ID=CTE.ID
Only the first record gets updated...