Help Developing SQL Query
Jun 22, 2001
I need to create a query that will pull all of the records that are statused as 'OUT' from my RECORDS table and then get the last (chronologically) 'OUT' record from my IN/OUT table for each record it pulled from RECORDS.
I can get the records out of RECORDS without any problems but I don't know how to set up a query that will pull the last chronological 'OUT' record from my IN/OUT table. Is there a way to do this in SQL?
Thank you in advance for any help.
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Good Day;I would appreciate assistance developing a query that I haven't beenable to develop without using a second table. I wish to count thenumber of records that are still open on the first of each month.Each record has an open date and a close date or the close date isnull i.e., the record is not yet closed. I've previously beaten thisby building a table, simply a list of the dates for the first of eachmonth for the next ten years or so, and then selecting values basedupon a date selected from that table. However I'd be happier if Icould do it without the second table. I'd be prepared to accept theMin(Date) for each month as being the first of the month.I've included some DDL statements to build and populate the table ifthat helps. Since the selection is rather small and all the opendates are very close together I think the result will be simply adecreasing count from the month the first record is opened till today.A pseudo code select statement might look likeSelect Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]FROM DetailTWhere DateReceived > DateOfInterest or DateReceived is Null andDateOpened < DateOfInterestGroup by Min(DateOpened)Order by Min(DateOpened)I hope I've explained it sufficiently well.CREATE TABLE [dbo].[DetailT] ([Autonum] [int] IDENTITY (1, 1) NOT NULL ,[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[DateOpened] [smalldatetime] NOT NULL ,[DateReceived] [smalldatetime] NULL ,)Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('C15788', '06/04/2005 9:35', 07/04/2005)Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('B16091', '06/04/2005 9:36', '07/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('B15001', '06/04/2005 9:51', '08/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('M18696', '06/04/2005 9:56', '06/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('C14969', '06/04/2005 10:05', '10/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('O10091', '06/04/2005 10:08', '12/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('D01197', '06/04/2005 10:13')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('H15001', '06/04/2005 10:15', '08/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('J15090', '06/04/2005 10:24', '08/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('J01202', '06/04/2005 10:31')Insert into DetailT (QDNumber, DateOpened)VALUES('G01193', '06/04/2005 10:32')Insert into DetailT (QDNumber, DateOpened)VALUES('K01164', '06/04/2005 10:35')Insert into DetailT (QDNumber, DateOpened)VALUES('K01162', '06/04/2005 10:48')Insert into DetailT (QDNumber, DateOpened)VALUES('F01124', '06/04/2005 10:59')Insert into DetailT (QDNumber, DateOpened)VALUES('H01147', '06/04/2005 11:01')Insert into DetailT (QDNumber, DateOpened)VALUES('S15068', '06/04/2005 11:10')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('E12322', '06/04/2005 11:32', '07/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('A12205', '06/04/2005 11:37', '06/04/2005')Insert into DetailT (QDNumber, DateOpened, DateReceived)VALUES('D12259', '06/04/2005 11:40', '07/04/2005')Insert into DetailT (QDNumber, DateOpened)VALUES('C03394', '06/04/2005 11:51')If you made it this far thank you for your patience. Any help would beappreciated.Thank you.Bill
View 5 Replies
View Related
Sep 25, 2004
Hi all,
I would like to develop WAP using ASP.NET which can connect to the Database( MS SQL Server ). Is it possible to do it? I try to find the tutorial but I cannot find it.
Another thing I want to ask is can I connect the WAP to the web services that I create using ASP.NET, anyone can tell me how to do it.
Thanks, Regards.
RED
View 1 Replies
View Related
Oct 6, 2006
Hi
Can i inherit a existing task to a new custom class to add functionality to custom class.
for eg: Inherit lookup transfor which is already present in SSIS to add some funstionality into it.
Thanks
Vipul
View 1 Replies
View Related
Jul 16, 2013
company is developing a database system containing information about cities, towns, and villages in the country. The number of cities, towns, and villages are in thousands and the database program should allow users to search a particular place by name. Users should also be able to search all places that match a particular value or range of values for attributes such as location or population size. This particular feature is known as a range query.
If the database is meant to support range queries that can return many cities that match the query specification, the entire operation may be allowed to take longer. To meet this requirement, it will be necessary to support operations that process range queries efficiently by processing all cities in the range as a batch, rather than as a series of operations on individual cities.Different indexing methods can be used to perform this operation. What will be your choice if:
a) database is created once and has no change over a period of time
b) database is created once and database is changed over a period of time
View 2 Replies
View Related
Feb 7, 2008
Hi,
i'm making my master thesis about a new plug-in algorithm, with the LVQ Algorithm.
I make the tutorial with the pair_wise_linear_regression algorithm and i have some doubts. i was searching for the code of the algorithm in the files of the tutorial and i didn't saw it. I have my new algorithm programmed in C++ ready to attach him, but i don't know where to put him, in which file i have to put him to start to define the COM interfaces? And in which file is the code of the pair_wise_linear_regression algorithm in the SRC paste of the tutorial?
Thanks
View 3 Replies
View Related
Feb 1, 2006
I am in the planning stages of a front-end to access sql server databases. The project involves analysis of stored data as well as storing results of the analysis. The classes for the analytical part is fairly complex so I would like to speed up the data manipulation part of the project. I am already familiar programming with ADO but I was hoping for a more complete solution. Is there a sdk or example code (third-party OK) that could facilitate this?
The client is not especially fond of .NET and would prefer to use VB6. However, with the right solution, C# or VB.NET would be acceptable languages.
View 1 Replies
View Related
Oct 6, 2006
Hi All:
For making a custom Task in SSIS, is it possible to reuse the existing code base? For e.g. If i need to append some functionalities to LookUp Transform. Can I inherit the lookUp transform class?
Thanks,
Vipul
View 1 Replies
View Related
Jul 31, 2007
I'm beginning development on a medium/ sized in-house web based system. Well moving from asp/mysql to asp.net/sql server. Re-doing DB design as well.My question is does it make sense for me to develop the SQL DB in Visual Studio 2005 pro, and then later move it to a real SQL server? Or basically what is the best and/or most practical way to do this development. Any other IDE's out there?Thanks
View 3 Replies
View Related
Sep 7, 2007
how do i view the databases that i have made in my web sites in MICROSOFT SQL SERVER STUDIO EXPRESS? can anyone help?
View 2 Replies
View Related
Mar 9, 2008
Hi,
I am developing a Translator for a mobile phone and I am using MS SQL server 2005 for creating my database.. I am having a problem in creating a relationship between my tables.. I have the following tables:
English(engcode,engword) - engcode is primary key
Esperanto (espcode, espword) - espcode is promary key
EngEsp (engcode,espcode)..
The problem is that the icon "relationship" is disable in sql 2005 and i am nt able 2 create the relationship..
Any Suggestions??? Please help me..
View 1 Replies
View Related
Jun 8, 2006
Hi all,
I am trying to write a program that creates packages on the fly depending on the variables you pass. for eg. It should create connection managers on the fly specific to a certain file in the variable (eg. sample.csv). the package has a dataflow task and it has flat file source and oledb destination.The problem I am facing is the flat file source when assigned to a flat file connection manager(dynamically), it is not giving any source output columns. i.e, the value for DFSource.OutputCollection(0).OutputColumnCollection.Count is Zero. But when I use the same code and reverse the source and destination(oledb as source and flatfile as destination), it is working fine. I searched everywhere for resources on how to develop packages programmatically, but could not find any except one example on msdn. If anyone knows about this prob or any useful resources on this subject, it would be really helpful.
Thanks,
Prithvi.
View 1 Replies
View Related
Oct 22, 2007
Hi,
I have .NEt web application where i have to tranfer large number of records (50,000 to 1,00,000) from one DB to another DB.I want to use ssis pacakge to do this task , but my requirement is to build this ssis pacakge dynamically using .NET framework.
Is it possible to develop SSIS pacakge from scratch using .net programming?
Suggest me any other alternative options if you have ?
Thanks,
Vaishali
View 5 Replies
View Related
Sep 21, 2007
Hi...
I have the full version of SQL Server installed on my machine and am developing an app using BI DTS packages, VB.Net, SQL SPs, etc.. I will need to deploy this app w/SQL Server Express (SSE).
-Do I have to install SSE on my development machine for the purpose of creating the install package of my application? I am afraid that if I do install SSE on my machine, it may mess up my installation of the full version of SQL Server... will it?
-Can you point me to a link with information about how to do the development with the full version of SS, and create the installation package using the SSE?
Thanks for your help.
Steph
View 4 Replies
View Related
Mar 9, 2007
Hi,
I am new to SQL 2005 CE but fairly experienced with SQL 2000 and know my way around SQL 2005. I am creating a PDA app that must support the following scenario:
My company has a SQL 2000 server with a "computer assets" database. This asset database must be able to be transferred to an .sdf file (SQL 2005 mobile) on the PDA when the user docks their PDA to the desktop PC (not the same machine as the SQL 2000 server). This transfer can be via an option in the mobile app that is initiated by the user, or automatic.
The mobile application basically supports stock-taking of the assets, so users go out on site and record information about the assets. This information is stored in a separate .sdf database on the PDA.
When the user gets back to the office they dock with their desktop and then must transfer the data from the second .sdf database onto the SQL 200 server. Lets call this the "asset metadata".
thats basically it! but im having issues finding the right process to attack the data transfer tasks.
My issues:
- Firstly, how do i populate the .sdf on the PDA for development purposes? Microsoft tutorials seem to be geared at SQL server 2005 management
studio only. I only have SQL server 2005 Management Studio Express on
my development machine as well as all the necessary SQL 2005 CE SDKs and VS2005. We
do not use SQL Server 2005 at my organisation as yet and it will be some time before we migrate across. In effect im looking for something that behaves like a DTS package to transfer data across.
- How to populate the "asset database" .sdf file from the SQL 2000 server when the user has docked to their desktop PC? (This needs to be refreshed )
- How to transfer the "asset metadata" from the .sdf on the PDA to the SQL server?
ive looked at merge replication a little but am not sure whether this is what i need and again most online information seem to be tailored to SQL Server 2005.
Can anyone kindly point me in the right direction?
View 5 Replies
View Related
Apr 20, 2007
I can't found my way to connect to local SQL server (On My Computer)
can any body help me
Ahmed Gaber
View 1 Replies
View Related
Feb 1, 2007
There seems to be a BUG in BIDS when developing SSIS packages using the Import/Export Data wizard.
If you use the wizard to import a large number of tables, and then select all the tables, and then choose to delete exisiting data in each table, the PrologueSQL file does NOT get built correctly. Instead of having a
TRUNCATE tablename Go
for each table, it just has a bunch of "Go"s with nothing between them. In the step immediately prior, where you confirm what the wizard will do, it tells you, after each table, that it will delete any existing data...but it doesn't do this.
If, during the wizard, I select each individual table one at a time and tell it to delete existing data, then it will get built correctly, but not if I select them all at once...YET, if I do select the whole block, choose delete existing data, and then select any single table, it shows that table as being set up to delete existing rows.
This is very frustrating when trying to import large numbers of tables.
Am I missing something? or is this really a bug?
Thanks, Jeff
View 4 Replies
View Related
Oct 29, 2007
Hi,
I've been scouring the forums and the web for anything that would substantiate an argument for database application developers to keep developing in SQL 8.0 when we have migrated to SQL 2005.
I read somewhere that compatibility 8.0 mode is an intern stepping stone for migrating from 8 to 9, but it seems 2005 will handle and run compat 8.0 databases just fine.
So am finding it really diffucult to substantiate my argument against running 2005 with all databases in compatibility 8 mode indefinitly.
Any suggestions or links that could help me with this.
Joe
View 2 Replies
View Related
Jul 2, 2006
Hi all,
I need to find all information about distributing SQL Server XE and applications using it . Is it legal ?? should it be used just for educational use?!!! could it be used to develop a commercial software !! what limitations are controlling this ??
thanks in advance !!
View 6 Replies
View Related
Feb 5, 2008
Dear all,
We started to develop a datawarehouse solution for client back in December 2008 based on SQL 2008.
We are convinced that we can use some of the new feature included in the new version which is the reason we we chossed to go down this path.
Due to the delay of the next version we have some question that I would like to hear you opinion on.
The estimatet "go live" date is during spring (march/april)
Is it wrong to contiue the development on the 2008 version?
We would need to run Release candidate at the customer site until the product is released. Is there any major changes coming up that are already known?
So far in the development we have had no big problems with releasecandidate.
Thank you for you feedback.
View 4 Replies
View Related
Jul 25, 2007
I am writing a custom task to import data from delimited files into SQL tables. I use the standard Flat File Source adapter, a custom transformation to add a URN column and a filename column to the data and the standard OLEDB Destination Adapter.
Most of my test data files work fine except for ones with a lot of columns (around 350 columns). I get an error when I call the ReinitializeMetaData() method for the destination adapter.
Q1) Is there a restriction on the number of columns (or data row size) that can be imported into an OLEDB Destination Adapter?
Q2) The reason I use this adapter rather than the SQL Server Destination Adapter is that I need to set the destination table name using a variable. I don't believe I can do this with the SQL Server Destination Adapter. Is this the case?
Q3) Anyone know of a better/alternative way of acheiving the above? One way I have thought of is to create a custom destination adapter using the SQL Server Destination adapter as the base but I'm not sure whether this is a) possible and b) worth the hassle.
Hope someone can help...
Cheers
Nick
View 2 Replies
View Related
Jun 26, 2007
Hi,
I am developing a native C++ application using SQLCE (.NET is not a current option). Am having a problem while using multiple accessors for multiple blobs in a table. After I read the data for the first blob & try to read the data for the second one, I get an error in one of the dlls (something about using heap data that was freed). Where can I get symbol data so that I can use something like Windbg to resolve my problem.
View 1 Replies
View Related
Aug 13, 2007
We are developing Microsoft SQL Server CE 2.0 Merge Replication Using .NET Compact Framework. There is need of sinhronizations between SQL 2005 Mobile Edition witch resides on Barcode terminal (Windows CE) and SQL 2005 Developer Edition (on Windows XP). Everything is done by the book. There is few lines of code that we are using:
public bool Replicate()
{ bool ret = true;
SqlCeReplication repl = null;
try {
// Instantiate and configure
SqlCeReplication object repl = new SqlCeReplication();
string dbFile = ........
repl.InternetUrl = this.InternetUrl;
repl.InternetLogin = this.InternetLogin;
repl.InternetPassword = this.InternetPassword;
repl.Publisher = this.Publisher;
repl.PublisherDatabase = this.PublisherDatabase;
repl.PublisherLogin = this.PublisherLogin;
repl.PublisherPassword = this.PublisherPassword;
repl.Publication = this.Publication;
repl.Subscriber = this.Subscriber;
repl.SubscriberConnectionString ="Data Source=" +dbFile;
if (!System.IO.File.Exists(dbFile))
{ repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
IIS is configured, but in log on the server we've got this error:
2007/08/09 15:33:02 Hr=80004005 ACK:Error for DistributorSessionID = 58 2007/08/09 15:33:02 Hr=80004005 The SQL statement failed to execute. [,,,SQL statement,,] 28560 Please give us posible solution of this problem, we have searched on forums but with no result. p.s. I'ts urgent
View 3 Replies
View Related
May 30, 2006
Hello,
I watched the ondemand video and I am still looking for the source that is referred to. I, of course, would like to download it and work with it.
Please post where I can find it.
Thanks.
View 1 Replies
View Related
Sep 7, 2006
Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.
But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.
View 1 Replies
View Related
May 4, 2007
Help!
I am using Script Transformation to output a new column as image[DT_IMAGE]
field to store serialized object. In the VB script, the sample code as
Row.serializedobject.AddBlobData ( binaryArrayReturnedFromC#dll )
The package always runs fine on my developing machine and will halt on other
machine at AddBlobData after certain number row records were processed. I am
stuck here. Anyone has any suggestion?
What I need is reading data from mutiple tables in one database and writing
into a single table in another datable. In order preserve all the columns
data, I use input column fields to construct a new object and then serialize
it, and store the serialize data into detination db table. (The object and
serialization function is coming from c# dll.)
Dim b As BusinessLicense = New BusinessLicense()
b.ApprovalDate = Row.approvaldate
b.BusinessId = Row.busid
b.BusinessName = Row.busname
b.NaicsCode = Row.naicscode
b.NaicsDescription = Row.naicsdescr
b.OwnerName = Row.ownername
b.Phone = Row.phone
b.Pkey = Row.pkey
b.RenewalDate = Row.renewaldate
b.StartDate = Row.startdate
b.Suite = Row.suite
Row.serializedobject.AddBlobData(Serializer.Serialize(b)) '''----This is blocking line
Row.infoType = BusinessLicense.TYPE
Both machine is xp with sp2. and standard SQL Server 2005 - 9.00.1399.06
Thanks!
View 4 Replies
View Related
Feb 26, 2008
I'm attempting to use RDA to synchronize a pocket pc emulator with SQL Server 2005 database by following tutorial http://msdn2.microsoft.com/en-us/library/aa454892.aspx, which uses AdvWorksMobile database that comes with SQL Server 2005.
When get to testing of "To test application features " ( step 4 of the merge replication setup lab ), I am receiving SQLCe Exception errors.
ORIGINALLY, I was able to deploy an application and view the local database on the emulator, but once I clicked "InitSinc" button, I received an error.
"Failure to connect to SQL Server with provided connection information. SQL Server does not" exist, access is denied because the SQL user is not a valid user on the SQL Server, or the password is incorrect.
Found this error listed in Server Agent Errors log http://technet.microsoft.com/en-us/library/ms172357.aspx, but it wasn't very helpful since it doesn't provide any suggestions on fixing the problems.
I am brand new to SQL CE Server , mobile programming and .NET framework in general so please bare with me
All the connections / security settings, etc that were set up were taken from a tutorial so I am not sure what SQLCeServer is complaining about. Reading up on the error on the web didn't help, it all pointed to making sure that the snapshot folder had correct permissions set up. I verified that MACHINENAMEUISR_MACHINENAME guest user had Read rights to the database so I don't think that piece is the problem. Otherwise ,Merge publication has been setup in Microsoft SQL Server Management Studio by following the tutorial.
I am trying to run this sync on my home pc so there should be no issues with any user permissions
I have IIS installed
I did NOT have SQL Server Agent running in management studio at this time
I am trying to run this tutorial on an XP machine, which is my regular home PC so I dont think there are any special networking settings to consider
I am able to hit http://localhost/AdvWorksMobile/sqlcesa30.dll from my pc OK, but when I try to hit it from a cradled emulator ( after replacing "localhost" with "MACHINE_NAME" ), Internet Explorer on the emulator gives me a "Cannot Connect With Current Settings" error message. This part wasn't part of tutorial but decided so not sure if I am supposed to be able to hit it from my emulator..so I am not sure if the fact that I can't hit it is related to the problem.
AFTER STARTING SQL SERVER AGENTin Microsoft SQL Server Management Studio, I killed the application, and after relaunching it, it hits "The Specified table does not exist [Vendor]" error. This time it doesn't even launch first - that is I dont even get to pressing "InitSinc" button. Debugger is showing that this error is hit on the following line in AdvWorksMobile.AddWorksDataSetTableAdapters
int returnValue = this.Adapter.Fill(dataTable);
I am not sure why the errors changed on me after starting SQL Server Agent.
What can I do to fix this connection problem?
Thank you so much for your help!
Irina
View 3 Replies
View Related
Nov 19, 2005
Greetings,VWD EE and other Tools do not have problems working O.K. on my machine, when I am logged-on as restricted(limited) user, both environment and local web server are functioning, and it was MS effort to do it right.BUT the Problem is, when I try to connect to database->new DB connection(either through vwd or management studio etc). I get an Error - which should be written to event log (according to msg) but it isn't - that I can't connect. I am sure that it is because NO SQL SERVER2005EXPRESS instance is running, which I think is the only prerequisite to have it work (I do not need sqlbrowser service I am doint local development).SQL2005express service is configured on MANUAL start. I can start it as administrator through SQL config manager, but it is not convienient and what I want. I need to start it ONLY as a developer user, I do not want it to be running all-the-time for everyuser using computer. It was congigured as Network Service logon, I tried Local Service logon, and I even tried configuring it to logon as -my developer user- account, e.g. with limited user name and his password.In every case I can't start the service as member of users group and this developer. Then I added limited user to SQL2005EXPRESS group. Still NO help, won't start either.My question is, If I want to start developing ADO2.NET application and I need to have running SQL2005Express instance as a develper, how can I start it? I think I do not have some rights to masterDB or something. Do it allways need to be running when computer starts? Isn't there any other way, to start it JUST when I think I start developing?I know of user-mode of accessing SQL2005express DBs, but it also assumes that SQL2005Express service is already running, what I am trying to prevent. I do not want to have it running for everyone who uses computer, just for someone who neeeds it.Any help explaining me the right way HOW TO SETUP WORKING ENVIRONMENT when developing as limited user welcome, I read the the documentations and haven't found the answers.THANK YOU !
View 1 Replies
View Related
Dec 19, 2003
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
View 1 Replies
View Related
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Jan 22, 2001
Hi,
I get this error dialog when I try to open all the rows of any table from Enterprise manager..
Any help would be really appreciated..
Thanks,
-Srini.
View 1 Replies
View Related
May 24, 2007
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
View 5 Replies
View Related
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
