Impossible SQL, Or Am I Being Stupid?
May 7, 2001
I have a table that has three fields:
CATEGORIES
----------
catID
description
parentCatID
Some categories have sub-categories. Therefore, some entries will have null
for parentID while others will point to another category.
I need to select all of the categories, but have them returned in the
correct parent/child order (parents BEFORE children). I can't seem to get this to work correctly. Is this not possible or am I just being stupid?? :)
Thanks for any assistance.
Mike V.
View 5 Replies
ADVERTISEMENT
Jan 12, 2005
Hi! all
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 0 ] = @datetime1
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 1 ] = @datetime2
[SELECT date_part( 'minute', datetime1 - datetime2 )] = @result
AS UPDATE [Time_Result]
SET [Comment] = @Comment_2
WHERE
( [result] = @result)
Thanks in Advance
View 1 Replies
View Related
Jan 12, 2005
Hi! all
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 0 ] = @datetime1
[SELECT Coming
FROM Student_Log
where datepart(hour, AL_Coming) = 9
and
AL_State = 1 ] = @datetime2
[SELECT date_part( 'minute', datetime1 - datetime2 )] = @result
AS UPDATE [Time_Result]
SET [Comment] = @Comment_2
WHERE
( [result] = @result)
Thanks in Advance
View 3 Replies
View Related
Mar 12, 2008
First of all, here's a description of the three tables involved in the query:
clients
id
name
services
id
name
clients_services
id
client_id
service_id
As you can imagine, I have a list of clients and each client can have several services assigned to him.
So, I have trying to come up with a query that would allow me to do display the following information inside a DataGridView without the use of any additional code:
I would like to be able to display all the services included in the services table and then, depending on the client that is currently selected, to have each of the services display a checkbox in the DataGridView showing the services currently assigned to that particular client. Something like this:
The services table has the following entries: Research, Consulting, Development, Data Protection and Design
The selected client id is "2"
That client has the following services assigned to him: Development and Design
The DataGridView should then display:
[ ] Research
[ ] Consulting
Development
[ ] Data Protection
Design
Is this possible without writing any code?
View 5 Replies
View Related
Aug 24, 2005
I'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 Related
Sep 1, 2004
We currently have a pull transactional subscription running from a remote 'PublicationServer' in another country to our local server.
After restarting our fire wall we get the following error message:
"The process could not execute '{call sp_MSget_subscription_guid(16)}' on 'PublicationServer'."
This message is followed by another error message: "Another distribution agent for the subscription(s) is running.", although nothing was changed to the configuration and no SQL processes were started or restarted.
When we restart the distribution job manually it runs as if nothing happened.
Does anyone know what exactly caused the failure, and how to prevent it ?
Philip
View 1 Replies
View Related
Aug 21, 2007
Hello everybody,
I'm trying to create a merge between 2 tables, the first coming from Oracle and the second resident on SQL 2005. The result I'd like to retrieve would be a table composed from the original data contained in the SQL table, update with new or changed rows coming from Oracle (the 2 tables are identical, I'm building an update job..). The final table will be the same table used for data source.
I setup connections and everything seems to works, but when I link the Merge block with the 2 sources blocks, double clickin on the Merge Block, I notice that every SQL table's column is set to "ignore".
When I try to change it, SSiS says:
Failed to set property "OutputColumnLineageID" on input column ...
What's wrong? I've set IsSorted property to True and each column with the righ position...
I've also another question: actually my tables hasn't a "last update" column, so I think the merge "block" can't just update the SQL existing rows but it will add only newest rows. Is that right? How can I easily implement an "update check" in your opinion?
Thanks in advance and sorry for my bad english
View 1 Replies
View Related
Jul 20, 2006
I've been fighting with this all day there has to be an easy way to do this in a single query:
FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:
INSPECTION_IDFAC_IDSTART_DATE
3007200406280045662004-07-07
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003020600314582003-02-05
30072003012100214802003-03-25
30072003012100314802005-02-02
20082003123000114362003-12-30
30072004061600615662004-08-26
30072004061600115662001-08-26
30072004061600515662002-08-26
30072004061600215662003-08-26
30072004061600315662004-08-26
30072004061600415662006-08-26
30072004050400415692004-09-10
DESIRED OUTPUT
INSPECTION_IDFAC_IDSTART_DATE
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003012100214802003-03-25
20082003123000114362003-12-30
30072004061600115662001-08-26
30072004050400415692004-09-10
Am i missing something...
any help would be greatly appreciated...
View 1 Replies
View Related
Jul 20, 2006
I've been fighting with this all day there has to be an easy way to do this in a single query:
FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:
INSPECTION_IDFAC_IDSTART_DATE
3007200406280045662004-07-07
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003020600314582003-02-05
30072003012100214802003-03-25
30072003012100314802005-02-02
20082003123000114362003-12-30
30072004061600615662004-08-26
30072004061600115662001-08-26
30072004061600515662002-08-26
30072004061600215662003-08-26
30072004061600315662004-08-26
30072004061600415662006-08-26
30072004050400415692004-09-10
DESIRED OUTPUT
INSPECTION_IDFAC_IDSTART_DATE
3007200306180025662003-06-25
20082002121901114362002-12-19
30072003020600214582003-02-03
30072003012100214802003-03-25
20082003123000114362003-12-30
30072004061600115662001-08-26
30072004050400415692004-09-10
View 2 Replies
View Related
Jul 23, 2005
Hello there!I am working with MS SQL Server 2000.I have a table function that takes an integer parameter and returns atable, and I can successfully use it like this (passing a literalas a parameter):SELECT * FROM MyTableFunction(1)or like this (passing a variable as a parameter):DECLARE @i AS intSELECT @i = 10...SELECT * FROM MyTableFunction(@i)Now, if I have another function -- a scalar function that returns aninteger, I cannot find a way to use it to specify the parameter valuefor the first function. For example, when I writeSELECT * FROM MyTableFunction( dbo.MyScalarFunction() )SQL Server issues the following complaint:Incorrect syntax near '.'.I am really perplexed: what I am doing wrong?Interestingly, if I re-write the second snippet asDECLARE @i AS intSELECT @i = dbo.MyScalarFunction()...SELECT * FROM MyTableFunction(@i)everything works just fine; however, this trick cannot be used as aworkaround because I need to pass result of one function as a parameterto another inside a view's code -- I cannot declare variables and writeany procedural code...Any ideas, especially workarounds, would be greatly appreciated.Thank you,Yarik.
View 2 Replies
View Related
Sep 23, 2005
Hi all,
I'm Trying to replicate the creation of an "xml" file that is currently created using a C++ application. I want to take that application out of the picture, but need to create the same format XML file because a step later in the production process uses this file, and I cannot change it.
The output format I am looking for is:<?xml version="1.0" encoding="utf-8"?>
<FUNDS>
<AMRGX>
<NAME>AMERICAN GROWTH D</NAME>
</AMRGX>
<AHERX>
<NAME>AMERICAN HERITAGE FUND</NAME>
</AHERX>
<AMRVX>
<NAME>AMERICAN INVESTORS GROWTH FUND</NAME>
</AMRVX>
.
.
.
</FUNDS>The problem I am having is that I cannot seem to get the level/node of the fund symbol (AMRGX, AHERX, and AMRVX in the example above) as it needs to be. I think this must be some non-standard use of XML, since the tag is really the data itself (?)
The closest I have been able to get so far is:
<FUNDS>
<SYMBOL>AMRGX</SYMBOL>
<NAME>AMERICAN GROWTH D</NAME>
</FUNDS>
<FUNDS>
<SYMBOL>AHERX</SYMBOL>
<NAME>AMERICAN HERITAGE FUND</NAME>
</FUNDS>
.
.
.As you can see (hopefully) I am able to get the data I need but cannot get:
(1) the FUNDS tag(s) to be the very highest level/root.
nor (2) the SYMBOL part (tag label?) to be the actual variable stock fund.
Am I 'splaining this well enough? I don't necessarily need all the code, since I know I haven't given enough info to help with that, but my basic question is - - Is it possible to get a variable TAG based on the table DATA?
I want my SYMBOL tag to be the actual SYMBOL for the stock fund.
Confused? Not as much as I am *LOL* I am new to the use of all but XML EXPLICIT use, so any help would be appreciated - at least regarding my two formatting questions.
Yes, I have (and am still) searching around BOL for my answers, but so I have found nothing that helps me out. Meanwhile, suggestions are welcome!
Thanks!
View 6 Replies
View Related
Sep 21, 2006
Hello,I am using SQL Server 2000 with SP4. I have a database with two fullbackups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30PM. Is there a possible way to do a point in time restore to 4:30 PM,that is between two full backups?When I try to use the transactional log backup that is taken at 5:30, Ican never specify a time before 5:00 PM. Is the transaction logtruncated at each full backup? If so, even if you take transactionallog backup every ten minutes, and full backups every once in a while,there will be some point in time which cannot be recovered to, namelythe time between a transactional log backup and a full backup. Take alog backup at 4:50, and full backup at 5:00 and you can never recoverto 4:55, can you?Any insight on the topic will be appreciated,Regards,M. Baris Caglar
View 3 Replies
View Related
Aug 31, 2006
I hope anyone can help me with this. I am having problems connecting to the aspnet database. It
all started when I noticed that I could not log in to my webapplication
anymore under debug mode, although I was able to log in when remotely
accessing the website. I got the error message that the machineaspnet
user did not have access to the database: "Login failed for user
'GM-FEDORSASPNET' "This was highly peculiar and rather annoying so I tried several things to straighten this out, but it only made things worse!I
read that this had to with permissions of the database in question, al
though I could not understand why permissions suddenly had changed. I
tried to change the rights by attaching it to Management Studio Express
and defining an aspnet login, but that failed because a login under
that name was not accepted. I tried to make a copy of aspnet.mdf, delete the original and rename the copy to aspnet.mdf. When that didn't work, I then deleted the aspnet.mdf, counting on it that it would be regenerated automatically, but alas. I located the aspnet_regsql.exe tool to regenerate the database, but got the following error message: System.Web.HttpException:
Unable to connect to SQL Server database. --->
System.Data.SqlClient.SqlException: 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: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)What can I do?
View 4 Replies
View Related
Aug 17, 2007
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View 9 Replies
View Related
May 13, 2008
Hi There,
I have a database which I attached to Sql Server 2005 Express (With advanced features) and enabled full text search. I created a FT catalog and FT index on a column and can successfully run the following query:SELECT expertise
FROM faculty_user
WHERE FREETEXT (expertise, 'Berry' )My issue is that full text search requires user instances to be turned off. Simply setting "User Instance=False" over in my web.config's connection string (in VWD) does not solve the problem.I still get the following error and I think it may be caused by my ASPNETDB. I can't find my connection string to ASPNETDB to turn off user instances and I can't diagnose my other connection strings because of it.Generating user instances in SQL Server is disabled
Here is a sample of my other connection strings:
<add name="myConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabase.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient"/>
From what I understand, I should also not be attaching the database from my connection string like this because it relys on User Instances? I am officially confused! Thanks for reading, -Derek
View 1 Replies
View Related
Mar 7, 2008
I am using the xp_cmdshell to run a batch file that is trying to open the Adobe Acrobat Reader.
When I run the batch file in the command prompt, it opens perfectly.
When I run it in SQL with xp_cmdshell, it returns the contents fine, and I notice it tries to open Acrobat, but it is under the username "system". And I cannot get it to load in a visible area, and for some reason it doesnt seem to load properly under system.
Any ideas? Any Suggestions?
Please advise.
Marco
View 3 Replies
View Related
Jul 24, 2007
When attempting to use the FTP task to download a file from a Mainframe system the task fails stating the filename as invalid because it doesn't begin with a "/".
Adding the slash to the front of the file name causes the mainframe to be unable to locate the file.
Commandline version of FTP.exe mimicks this behavior by working perfectly when the filename has no slash, and being unable to find the file when the slash is present.
Why does microsoft force a filename to start with a "/" and is there a way to make SSIS skip the validation phase for the FTP task?
View 7 Replies
View Related
Mar 21, 2007
Relative newb to SSRS here, but the answer to this question evades me; answers and insight are appreciated.
Report in question is an invoice form. It requires an absolutely bottom-of-page aligned footer that has databound elements.
This is so that whatever page that footer finally appears on will print in such a way that the address will align in a windowed envelope.
Ironically, Books Online gives this exact scenario in explaining headers and footers in SSRS, but they cleverly don't explain how an absolutely bottom-of-page-aligned and data-bound footer can be made to happen. Headers at absolute page top is obviously no problem. Footers at page bottom, not so much.
So, this is not a "page footer"--page footers are employed in the body. Also this footer is databound, so a page footer as it's known in SSRS is out the window anyway.
Most of the time this will print on a single page, but if it breaks to multiple pages, that footer needs to go all the way to the absolute bottom.
I grasp that the "report footer" for SSRS is just what appears at the end of any repeating controls that you've implemented in your body. Because SSRS uses this kind of repeating-control based idiom rather than a section-based idiom as Crystal does, this kind of (what I would consider very basic) positioning control is looking fairly impossible right now.
Among what I've tried:
--Page footer (can't; databound)
--Specifying a page break after the pre-footer controls, and/or a page break before the controls that make up the footer in their properties. This leads to unpredictable results with blank printed pages (as many as 8 for what previews as a 2-page report, how silly is that?).
--Putting in a page-height rectangle as part of the footer (with and without the page breaks mentioned above), with the idea of forcing a basically blank page at the end of the report so that the footer will go to the bottom. SSRS will go ahead and break the page anyway on long elements like that, which again leads to the "footer" being printed in the middle or top of the final page, or whereever it happens to fall.
I may be having to explain to my client that you can't get there from here, and they may have to redesign their report. Does anyone have any insight?
Thank you for your time in reading this.
View 14 Replies
View Related
Mar 4, 2008
Hello,
SQL Server Reporting Services 2005 (SSRS) is installed on Vista Ultimate.
I cannot access to the http://localhost/ReportServer page. IE posts an internal error HTTP 500.0 (after IE required a Windows account).
All the configurations of Reporting Services are OK. In the Log file, there is nothing: the Web Services does not seem to be launched.
By continuing the investigations in IIS 7 (activation of the €śRules of follow-up of the requests having failed€?, I found the error following:
IIS Trace Detail Highlights
No. EventName Details Time
53. MODULE_SET_RESPONSE_ERROR_STATUS Warning
ModuleName="IsapiModule", Notification="EXECUTE_REQUEST_HANDLER", HttpStatus="500", HttpReason="Internal Server Error", HttpSubStatus="0", ErrorCode="Syntaxe du nom de fichier, de répertoire ou de volume incorrecte. (0x8007007b)", ConfigExceptionInfo="" 09:02:33.716
Translation of the french sentence: ErrorCode="Incorrect syntax of the file name, the directory or the volume."
I made a full research on Internet and in the documentation at my disposal and I did not find anything which can advance me towards a beginning of solution€¦
Thank you with all for the assistance which you will be able to bring to me.
View 4 Replies
View Related
Oct 12, 2001
OK, I did something stupid and now cannot get access to my server...
I was experimenting with the security/login settings and denied access to BUILTINAdministrators. Now I cannot get on my server.
Anyone else ever done this? What can I do to get back on without loosing some of my data?
Any help would be appreciated.
Thanks!
View 1 Replies
View Related
Aug 15, 2006
Still in the world of SAMS Teach Yourself SQL in 24 Hours with SQL 2000. The book says:
CREATE TABLE EMPLOYEE_TBL
(
EMP_IDVARCHAR(9)NOT NULL,
LAST_NAMEVARCHAR(15)NOT NULL,
FIRST_NAMEVARCHAR(15)NOT NULL,
MIDDLE_NAMEVARCHAR(15)NOT NULL,
ADDRESSVARCHAR(30)NOT NULL,
CITYVARCHAR(15)NOT NULL,
STATECHAR(2)NOT NULL,
ZIPINTEGER(5)NOT NULL,
PHONECHAR(10),
PAGERCHAR(10),
CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)
);
Query Analyser says:
Server: Msg 2716, Level 16, State 1, Line 3
Column or parameter #8: Cannot specify a column width on data type int.
If I take the (5) off the end of the INTEGER data type it runs through but how come you can't specify the width of an integer column?
Ta muchly.
View 6 Replies
View Related
Jan 29, 2008
Hye all, running WHS 2008, I try to install SQL Server 2005 with reporting services, but on install, in "components to install" wizard, the reporting services box is hided. I can't check this box :/ Do you know whats wrong ? if anyone knows the problem, thanks in advance.
View 1 Replies
View Related
Sep 10, 2004
Just a simple one I think?!!!!!
Is there any way of copying a DTS from one server to another?
I just need to play with this DTS on our test server but can't copy the thing!!!
Thanx!!!!
View 2 Replies
View Related
Oct 25, 2004
is there a way to format data on the tables. Such as Social security numbers 00/00/0000 or formating of dates 99/99/0000;0;_
is there a way of doing that on a sql table maybe??
View 3 Replies
View Related
May 12, 2004
What is the "Microsoft SQL Server 2000 Resource Kit"
I was persuming it was a programme or patch for SQL server, however it's lookign more and more like a book.
Could some please clarify and if it is a programme/patch how can I get hold of it?
View 5 Replies
View Related
Apr 13, 2008
I have two tables:
1) Table that holds all available ports.
2) Table that holds users for each port.
There may be times where one user is getting more than one port at a time.
I've built up an ASP .NET page that will display each user its port/s in one table.
On another table I want to display all the other available ports which the user doesn't posses and can buy to own.
My problem is where I try to build up the query. I just can't get all the other ports in normal display.
For example, this is what I need:
Ports table:
1/1
1/2
1/3
1/4
Users table:
User A , 1/1
User A , 1/2
ASP page display:
------------
User A Holds:
1/1
1/2
------------
Available:
1/3
1/4
------------
Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it !
I've tried all kinds of Joins and nesting SELECT queries with no luck.
I hate SQL. I want to die.
View 4 Replies
View Related
Nov 24, 2005
Afternoon everybody...
Here is my noob question of the day, is there a query I can run to display the STRUCTURE of a table? fieldnames, datatypes, sizes etc?
Select * from users where clue > 0
View 4 Replies
View Related
Apr 5, 2006
here is my problem.
i have a table that looks like this
barcode |movetype | qty
as3 | T | 4
as3 | R | 2
as3 | T | 1
here is my problem and i am probably just stupid. i need the total amount Transfered minus the Received per barcode. so here i would need to see a total of 3.
i don't know how to do this.
View 4 Replies
View Related
Aug 23, 2007
I've getting the error message at the time ntbackup starts the backup of the server:
Event Type: Error
Event Source: SQLWRITER
Event Category: None
Event ID: 24579
Date: 22-Aug-07
Time: 20:00:26
User: N/A
Computer: DBS
Description:
Sqllib error: Database DYNAMICS of SQL server instance XXX is stored on multiple volumes, only some of which are being shadowed.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 53 51 4c 53 4e 41 50 43 SQLSNAPC
0008: 33 36 31 00 00 00 00 00 361.....
0010: 53 51 4c 53 4e 41 50 43 SQLSNAPC
0018: 32 34 36 00 00 00 00 00 246.....
Where is the problem?
How to fix this?
Does this mean that I would not be able to restore server in the case of failure?
SQL - version SQL Server 9.0.3054 (2005 SP2)
OS - 5.2.3790 (W2k3 R2 SP2)
MS Dynamics GP 9 is installed on this platform.
Any ideas?
Thanks in advance.
View 7 Replies
View Related
Mar 7, 2007
I am trying to insert form data...three form sections into three different tables....i can insert into one table just fine....is this not possible ...or what? If so can someone please send me a small sample... thank you ...by the way i am using visual studio...vb asp.net 2.0 with ajax. Thanks in advance!
View 6 Replies
View Related
Jan 3, 2006
I haven't tested it, because at the moment I can't access my SQL database
I know this is probably a stupid question, but...How can I test for DBNull when I'm using SqlDataReader.Item(string)?
I use to check it using: if(!reader.IsDBNull(2)) ...But I have found that can not count on a particular ordinal reference because some of the stored procedures select the columns in different orders.
can I do something like: if(reader["Column1"] != DBNull.Value)or with the reader["Column1"] throw an error because it is not found??
I too hate when people ask questions that they could test themselves...but I can't really test it at the moment. Any input would be greatly appreciated.
View 2 Replies
View Related
Jun 8, 2000
I'm trying to backup a database onto a different server
within the same SQL Server Group, but the Enterprise
Manager only offers the drives on the local server as
destination options. Do I have something configured
incorrectly or is there a way to do this manually?
Thanks in advance.
Doug
View 2 Replies
View Related
