Table tblStudentClass stores all information about classes that student was in, and will be in.
So there are plenty of records with the same lndStudent field.
What I need is to pull out information about all students from table tblStudent, and the last classes
the were/are in, or will be in if they are future students.
So the needed output should in this form with no duplicates
------------------------------------------------
|anStudentId|txtFullName|txtMobile|txtClassCode|
------------------------------------------------
What I have so far is this query:
SELECT tblStudent.anStudentId,tblStudent.txtFullName,tblStudent.txtMobile,tblClass.txtClassCode
FROM
(tblStudent
INNER JOIN
(
SELECT tblStudentClass.lngStudent, MAX(tblStudentClass.lngClass) AS LastOflngClass
FROM tblStudentClass
GROUP BY tblStudentClass.lngStudent
) AS s3
ON tblStudent.anStudentID = s3.lngStudent)
INNER JOIN tblClass
ON s3.LastOflngClass = tblClass.anClassID
WHERE
tblStudent.txtMobile LIKE '447_________'
ORDER BY tblStudent.txtMobile;
Which is not exactly what I want because it aggregates by greatest classid, not
by latest date, and it is slow on very large dataset. Maybe there are ways to optimize
it (temporary tables,stored procs,views)?
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
I hate access, I wish we could get away from it. I cant view employee pictures anymore, and every time theres and upgrade to a different version that means different problems. We are going to upgrade from 2003 to 2007. Good grief SQL doesnt need access, its too good for it anyways. Theres gotta be some different software out there that I can change too. Its just that I'm not a programmer. I keep hearing about Visual Fox Pro, that its easy but I have no idea. Anyone got any suggestions??
I have setup SQL Reporitng Services on Windows 2003 server. Environment is like this on server: - Framework 2.0 - SQL Server 2005 Enterprise Edition (with SQL Reporting Services) - IIS 6.0 - Internet Explorer 7 - Microsoft Ajax 1.0 - Active Direcotry
I have configured SRS and it was done successfully, I got two Virtual Directories (ReportServer and Reports).
My problems are like this: - When I type http://localhost/Reports in browser, It display ReportBuilder page with "Home" hyperlink. Nothing else is displayed on this page!!! (I am able to execute http://localhost/ReportServer/ I have login as Administrator on my server.)
- I created SQL Report Project in Visual Studio 2005 and created a Shared Data Source and a Report, I am able to preview the report in IDE. Then I tried to build and deploy it on above server, For it I modified project properties and changed TargetServerURL to above server (I tried following combinations for this property without any success http://192.168.115.17/ReportServer / http://ss6/ReportServer/ / http://localhost/ReportServer).
Exact error message is: A connection could not be made to the report server http://192.168.115.17/ReportServer/.
Additional Information: The attempt to connect to the server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer)
What could be the problem and solution? SRS is already running.
i have a severe problem here regarding production DBs
the system heavily utilizes UDFs in queries in bad behaviour
being used in select and where clauses , the queries can take considerable time
i have managed to enhance issues regarding indexes and select clauses , but UDf in where clauses still a problem bcz they contain some logic i cann't make it inline
here is an example : select col1, col2, col3, dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) , dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice) ,from listing inner join ResidentialSeller on listing.id=ResidentialSeller.idwhere (@TotalAreaFrom is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) >= @TotalAreaFrom ) and (@TotalAreaTo is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) <= @TotalAreaTo ) and (@SalePriceFurnishedFrom=0 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)>=@SalePriceFrom)) and (@SalePriceFurnishedTo=1000000000 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)<=@SalePriceTo))
the two UDFs fn_ConvertUnit is used to convert from measure unit to another (like from Meter to feet)
and fnCurrencyConvert is from currency to another (like from $ to Euro) based on predefined rate
CREATE FUNCTION [dbo].[fn_ConvertUnit] (@Uvalue float,@CfromID tinyint,@CToID tinyint)RETURNS float AS BEGIN if ((@Uvalue is null) or (@CfromID is null) or (@CToID is null))begin return nullend declare @fac intdeclare @temp floatif (@CfromID=@CToID)or (@Uvalue=0)beginreturn @Uvalueendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac-- set @temp=@fac end if (@temp<@Uvalue) begin-- return 1.79E10return 1000000000-- A Big number enough to be compared by any number user can type end else begin RETURN @Uvalue*@fac endendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@Uvalue) begin return 0 end else begin RETURN 1/(@Uvalue*@fac) endendreturn -1END ------------------------------------------------- -------------------------------------------------
CREATE FUNCTION fnCurrencyConvert (@FromCurrency int, @ToCurrency int, @Country int, -- This prameter is useless @AmountOfMoney float)RETURNS floatASBEGINif( (@FromCurrency is null ) or (@ToCurrency is null) or (@AmountOfMoney is null))begin return nullenddeclare @temp float -- will carry the max value without exceptionif @FromCurrency=@ToCurrency or @AmountOfMoney=0beginreturn @AmountOfMoneyenddeclare @fac floatif exists(select * from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 1.79E308 end else begin RETURN @AmountOfMoney*@fac endendif exists(select * from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 0 end else begin RETURN 1/(@AmountOfMoney*@fac) endendreturn -1END
i will appreciate any help regarding these two Udfs as they r the most problematic ones , other suggesions r highly appreciated
Hi everyone. I'm new to all this, and having a nightmare accessing my database. I'm using IIS 5.1 a desktop sql server (MSDE) and Visual Studio.NET Enterprise Architect 2003. If I try to access a sql database, I get an error "Login failed for user (ServerName)ASPNET". If I try to use an Access database, it tells me that the file is already opened exclusively by another user or that I need permission to view it. From looking through the forums, it appears to be a security thing (an account for ASPNET?) but how do I fix it. In very plain English...
Hi There, I've created a couple of search pages which look at sql server. whenever words or values like "?@~:&£^" etc, or words like for, the and so forth, the page the nasrty error page: Execution of a full-text operation failed. A clause of the query contained only ignored words Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words. In short: is there a way I can stop it doing this. It looks rather horrible. I've looked at form validation but cant find anything that seems to fit. I would imagine there is a simple solution, but I haven't been able to find it so far. Many thanks Stuart
added subreport to main tbl (by putting in detail row). Linked to tbl by 3 params. customer wants subrpt to be side-by-side to main tbl.
Unwanted symptoms: subrpt adds extra space in main tbl if subrpt contains more than 1 row; also, subrpt repeats if main table has more than 1 row. Got all of that? How do I resolve? THANK YOU
Here is my nightmare!! : I have 3 database tables. ( START, STOP, TICK )START ( Start_ID, Start_Time )STOP ( Stop_ID, Stop_Time )TICK ( Tick_ID, Tick_Time ) I have 3 records in each tables with the time fields being in datetime format. What I want to accomplish: I want to compare Tick_Time to see if it is between Start_Time and Stop_Time. ie. IF Tick_Time > Start_Time AND Tick_Time < Stop_Time THEN that Tick_Time.colour = Green elseTick_Time.colour = Red Can datetime fields be easily compared like I have shown above? Else can anyone give me an idea of howwwww Thanks and any help is appreciated
I installed Reporting Services and forgot to name the directories. So when the installation was complete I made the virtual directories myself. But now when I try to view them It says "You are not Authorized to see this page".
I've written an app in C# with ASP.NET in VS. I can connect from my development computer (laptop - XP) to MY server (Win 2003) running MS SQL just fine.
However, when I try to deploy it on the server that it is supposed to run on, I can't connect. I can connect through the network to the SQL server, but I just can't do it with the app. This tells me that my connection string must be wrong... Of course, suggestions would be great...
The SQL server (7.0) is on Windows NT. The app is on a windows 2000 server, and yes, .NET Framework is installed. The Windows NT server is the web server, and the 2000 has been set up as www2. I can see the app from the net, but when I try to log in (requiring connection to the SQL server to verify user and pword), no go.
These are my connection strings, please correct me if I'm wrong...
I've also tried them without the trusted connection, and with the server name... Same result (or lack thereof).
There's two connection strings, because one is used for the select statements for the public, and the other has all permissions for the back-end. I know it's redundant, but it's just one more safety feature I built in for the paranoid 'client'.
I have also tried to run the SQL server off of the 2000 box, but there's an error with the MMC... Does anyone know how to fix that, if it's a better solution???
Greetings,I have a couple of stored procedures that interact to deliver a resultset of data. The first one is "spStoreList", it receives a few parameters and returns a listing of stores in a given company or division. The source is below:CREATE PROCEDURE [DBO].[spStoreList]( @p_companyX varchar(20) = NULL, @p_divisionX char(1) = NULL, @p_storeX char(4) = NULL ) AS CREATE TABLE #Output( Company char(3), Division char(1), Store char(4), Nickname varchar(40) ) IF @p_companyX IS NULL BEGIN insert into #Output SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store] ORDER BY CompanyNo,Division,StoreNo END IF @p_companyX IS NOT NULL AND @p_divisionX IS NULL BEGIN insert into #Output SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store] WHERE CompanyNo IN (SELECT DISTINCT CompanyNo FROM [TBL Company] WHERE [TBL Company].[CompanyGroup]=@p_companyX) ORDER BY CompanyNo,Division,StoreNo END IF @p_companyX IS NOT NULL AND @p_divisionX IS NOT NULL AND @p_storeX IS NULL BEGIN insert into #Output SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store] WHERE CompanyNo IN (SELECT DISTINCT CompanyNo FROM [TBL Company] WHERE [TBL Company].[CompanyGroup]=@p_companyX) AND Division IN (SELECT DISTINCT DivNo FROM [TBL Division] WHERE DivNo=@p_divisionX AND [tbl Division].[CompanyNo]=[tbl Store].[CompanyNo]) ORDER BY CompanyNo,Division,StoreNo END IF @p_companyX IS NOT NULL AND @p_divisionX IS NOT NULL AND @p_storeX IS NOT NULL BEGIN insert into #Output SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store] WHERE StoreNo = @p_storeX ORDER BY CompanyNo,Division,StoreNo END SELECT * FROM #Output ORDER BY Company,Division,Store DROP TABLE #Output GO The second procedure, "spEmployeeService" uses this procedure to return a list of how many years the employees have worked for stores that get returned in the list. THe source is here:CREATE PROCEDURE [dbo].[spEmployeeService]( @p_company char(20) = NULL, @p_division char(1) = NULL, @p_store char(4) = NULL ) AS DECLARE @target int DECLARE @newLabel varchar(30) set @target=0 CREATE TABLE #Output( SortCode int, Label varchar(30), Years int ) CREATE TABLE #Service( Cnt int ) CREATE TABLE #Dock( SortCode int, Label varchar(30), Years int ) create table #Stores( Company char(3), Division char(1), Store char(4), Nickname char(40) ) insert into #Stores exec spStoreList @p_companyX=@p_company,@p_divisionX=@p_division,@p_storeX=@p_store insert into #Service select DateDiff(yyyy,HireDate,GETDATE()) AS YearsOfService from [tblPyoNet] where terminatedate is null and company <> '' and [tblPyoNet].StoreNo IN (Select Store FROM #Stores) insert into #Dock (SortCode,Label,Years) select CASE WHEN Cnt <= 1 THEN 1 WHEN Cnt = 2 THEN 2 WHEN Cnt = 3 THEN 3 WHEN Cnt = 4 THEN 4 WHEN Cnt = 5 THEN 5 WHEN Cnt = 6 THEN 6 WHEN Cnt = 7 THEN 7 WHEN Cnt = 8 THEN 8 WHEN Cnt = 9 THEN 9 WHEN Cnt = 10 THEN 10 WHEN Cnt <= 15 AND Cnt >10 THEN 11 WHEN Cnt >15 THEN 12 END, CASE WHEN Cnt <= 1 THEN '1 Year' WHEN Cnt = 2 THEN '2 Years' WHEN Cnt = 3 THEN '3 Years' WHEN Cnt = 4 THEN '4 Years' WHEN Cnt = 5 THEN '5 Years' WHEN Cnt = 6 THEN '6 Years' WHEN Cnt = 7 THEN '7 Years' WHEN Cnt = 8 THEN '8 Years' WHEN Cnt = 9 THEN '9 Years' WHEN Cnt = 10 THEN '10 Years' WHEN Cnt <= 15 AND Cnt >10 THEN '11-15 Years' WHEN Cnt >15 THEN '> 15 Years' END, 1 FROM #Service insert into #Output select SortCode,Label,SUM(Years) FROM #Dock GROUP BY Label,SortCode while @target<12 begin set @target=@target+1 if (select count(*) from #Output where SortCode=@target) = 0 begin if @target = 11 begin set @newLabel = '11-15 Years' end if @target = 12 begin set @newLabel = '> 15 Years' end else begin set @newLabel = STR(@target)+' Years' end insert into #Output (SortCode,Label,Years) VALUES(@target,@newLabel,0) end end SELECT * FROM #Output ORDER BY SortCode DROP TABLE #Stores DROP TABLE #Dock DROP TABLE #Service DROP TABLE #Output GO I can get these two procedures to work in Query Analyzer, but if I try to pull pack a datatable in ASP.NET I get the following error:ERROR:Invalid column name 'Store'. Invalid column name 'Division'. Invalid column name 'Company'. Insert Error: Column name or number of supplied values does not match table definition. Insert Error: Column name or number of supplied values does not match table definition. Insert Error: Column name or number of supplied values does not match table definition. Insert Error: Column name or number of supplied values does not match table definition. Any ideas why this would work in Query Analyzer and not in .NET. By the way, I send the following SQL to an SQLOLEDB driver, "exec spEmployeeService 'EFS','B'" to be returned in a datatable.Help is appreciated.
I converted an Access Database to SQL Express. The dates were converted to datetime I'm using VWD 2005 Here is the source of my date and the query. sqlDate = (DateTime.Now.AddDays(-7)) sqlTxt = "SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate + "', 101)) ORDER BY Service_Orders.SDate_Entered DESC" This retrurns 0 records. sqlDate = 11/28/2005 12:23:27 AM from the function above. The query will return records with : sqlDate = "2005-11-01 21:56:20" I tried changing the CONVERT(DATETIME, '" + sqlDate + "', 1XX from 100 to 120 with no luck I know this must be an easy fix, but it is beyond me. I need to know how to 1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20" or 2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly Thanks for any help in advance! Bill
In PHP, connecting to a (MySQL) database is quite a simple matter. There are PHP functions for connecting, database user and password, database name, etc. Is there something equally simple for Visual Web Developer Express? Hell, I can't even find a way to configure any aspects of my SQL Server Express Edition anywhere! The Wrox Visual Web Developer 2005 Express Edition Starter Kit seemed promising, but it's useless too if I can't get the database to work at all. I cannot wait until my organization finally uses PHP so I can be rid of this M$ BS. Too bad I can't get my 102 hours and 48 minutes (so far) back.
I bet every SQL-Server guy in this forums has fought pageiolatch issues from one time to another. Either bad indexing, overloaded disks / controllers etc. I know I have, with a great variety of systems and solutions.
Anyways.... I simply have to ask for any of your experiences, as I'm currently stuck with a Navision client, that ocassionally stalls on pageiolatch on even simple queries.
User selects a customer account, asks to see his / her details, scrolls up and down a bit, and whammo, stall, and pageiolatch appears on the server.
Something tells me that this might be an ODBC driver issue rather than actual sql-server issues. Fetching the initial data isn't the issue, but scroling back and forth in the recorsset it seems to cause the cursor to freeze or at least lose track or stall in the progress of paging through the records.
All of the above sounds very unclear, I know, I'm just trying tro track down or center on the problem, so that I might find a solution. So if you have any experiences with clients, cursors and odbc driver version for sql-server, that really make a lot of trouble, let me know.
I am not a DBA, but have dealt with a number of POS applications that use SQL Server 2005 as their back end.
That said, I'm no pro, but I can generally keep a DB running. Here is my problem scenario:
Everyday, at least three times a day, the server spontaneously starts rejecting all connections. The event log shows repeated messages of Event ID 18456 which is a logon issue. The server continues to reject all connections from that point until the machine is restarted. Restarting the SQL server without restarting the machine accomplishes nothing...
Looking at the SQL logs gets some further information:
Event ID: 18456, Severity: 14, State: 10
Apparently, state is of some significance though all of the links I have found that explain what the various states mean leave out number 10. (such as here (http://www.eventid.net/display.asp?eventid=18456&eventno=8175&source=MSSQLSERVER&phase=1))
This problem did not start happening until 4 days ago. The only event I can think of that also happened at this time was the installation of PHP (which doesn't even touch SQL Server...)
in short, what i'm trying to do is add another field called CompanyId ( uniqueidentifier) to the aspnet_Membership table and aspnet_Users table which comes in the classifieds starter kit,
Curently, when a usere registers, a UserId ( uniqueidentifier) is created and stored both the aspnet_Membership table and aspnet_Users table, i just want to duplicate this functionality and have CompanyID in the same tables in the same way.
Ive been messing around with the stored precedures for a while but i'm getting nowhere, if anyone could help, or even explain a little how the UserId is passed from one table to another, i'd be so greatful
I have added the two stored precedures bellow, first is called ALTER PROCEDURE dbo.aspnet_Membership_CreateUse
seccond is called ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
thanks a lot odxsigma
ALTER PROCEDURE dbo.aspnet_Membership_CreateUser @ApplicationName nvarchar(256), @UserName nvarchar(256), @Password nvarchar(128), @PasswordSalt nvarchar(128), @Email nvarchar(256), @PasswordQuestion nvarchar(256), @PasswordAnswer nvarchar(128), @IsApproved bit, @CurrentTimeUtc datetime, @CreateDate datetime = NULL, @UniqueEmail int = 0, @PasswordFormat int = 0, @UserId uniqueidentifier OUTPUT AS BEGIN DECLARE @ApplicationId uniqueidentifier SELECT @ApplicationId = NULL
IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END
SET @CreateDate = @CurrentTimeUtc
SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId IF ( @NewUserId IS NULL ) BEGIN SET @NewUserId = @UserId EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT SET @NewUserCreated = 1 END ELSE BEGIN SET @NewUserCreated = 0 IF( @NewUserId <> @UserId AND @UserId IS NOT NULL ) BEGIN SET @ErrorCode = 6 GOTO Cleanup END END
IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END
IF( @ReturnValue = -1 ) BEGIN SET @ErrorCode = 10 GOTO Cleanup END
IF ( EXISTS ( SELECT UserId FROM dbo.aspnet_Membership WHERE @NewUserId = UserId ) ) BEGIN SET @ErrorCode = 6 GOTO Cleanup END
SET @UserId = @NewUserId
IF (@UniqueEmail = 1) BEGIN IF (EXISTS (SELECT * FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK ) WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email))) BEGIN SET @ErrorCode = 7 GOTO Cleanup END END
IF (@NewUserCreated = 0) BEGIN UPDATE dbo.aspnet_Users SET LastActivityDate = @CreateDate WHERE @UserId = UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END END
IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END
IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END
RETURN 0
Cleanup:
IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END
RETURN @ErrorCode
END
ALTER PROCEDURE [dbo].aspnet_Users_CreateUser @ApplicationId uniqueidentifier, @UserName nvarchar(256), @IsUserAnonymous bit, @LastActivityDate DATETIME, @UserId uniqueidentifier OUTPUT, @CompanyId int OUTPUT AS BEGIN IF( @UserId IS NULL ) SELECT @UserId = NEWID() ELSE BEGIN IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId ) ) RETURN -1 END
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
I've been given this access database to look at which could eventually be upgraded to SQL, I am conducting a kind of initital research in to how we would go about it. The previous system has developed over time and is pretty messy. I'm not really a database developer, but as everyone is busy on other things, this side project has been given to me to look at as a warm up!
Basically I've managed to upsize all the tables and their contents, and have begun copying the forms over to a .adp - creating most of the queries as stored procedures.
I'm working through cronologically, and this is the 2nd query the system requires... It was full of IIf statements which I have commented out. The adp works, but is missing a lot of data - can't really work out where it was meant to be feeding from, I personally can't stand access - but it's what the original was created in and what they want to use.
2) right click a page in the vs2005 IDE and browse
3). browse a page from the IIS snap in
it doesn't work from other peoples PC on our intranet say if someone types:
http://xxx.xxx.xx.xx/mywebapp/default.aspx because I get this result:
Server Error in '/FocusII_Website' Application. --------------------------------------------------------------------------------
User does not have permission to perform this action. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: User does not have permission to perform this action.
Source Error:
Line 82: Line 83: SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["sqlExpress"].ToString()); Line 84: da.Fill(ds); Line 85: RadGrid1.DataSource = ds; Line 86: RadGrid1.DataBind();
This morning one of our client's database appeared as Suspect. I checked the event log and saw the following error:
Could not redo log record (413:835:2), for transaction ID (0:156861), on page (1:1246), database 'CLP' (database ID 26). Page: LSN = (412:2642:4), type = 1. Log: OpCode = 4, context 2, PrevPageLSN: (412:12236:2). Restore from a backup of the database, or repair the database.
I then tried to run a DBCC CHECKDB on the database and got the following output:
Msg 7987, Level 16, State 1, Line 1 System table pre-checks: Object ID 13 has chain linkage mismatch. (1:339)->next = (1:771), but (1:771)->prev = (1:341). Check statement terminated due to unrepairable error. DBCC results for 'CLP'. CHECKDB found 0 allocation errors and 0 consistency errors in database 'CLP'.
After a bit of research I began to get very sad, for a number of reasons. First I can't find much information on this error and what there is implies that I'm totally up the creek. Secondly and worst of all, backups have not been working so we don't have a viable backup of the database.
I did go as far as to run repair_allow_data_loss and received the same Msg: 7897 error as above.
Is there any hope? Can this be repaired? Any help is greatly appreciated.
I've been working 4 days non stop on this project, lost a complete weekend on it and I totally had it. Please have a look at this "simple" question:
I have a for each loop that checks for csv files in a folder. The path of the file(s) is stored in a variable varFileName. So far so good. But then I start with a data flow task and inside that data flow task I need to access one of those csv files at the time whenever it loops.
So my best guess is use a flat file source because that's the only task I see in the list that fits my question. But the thing is, you set up a connection to a....yes right, a flat source connection and there you have to select a flat file.
But no, I don't want to select ONE file, I need to access them all as the loop goes through all files. I'm sure this is something easy but I don't see it anymore.
I'm off taking a nap, need sleep Could someone please point me to a direction?
We have a setup with a web server and multiple databases, and a live, stage, and dev environment. We use SQL Server standard 2005 and use the ASP.NET ReportView control. I have spent countless hours now trying to get this to work and am about to give this up and go back to Crystal.
First I wanted a report that would work from dev to stage to live without modification, so we set up shared data sources on each environment to point to the appropriate database. No problem, I can publish it to each environment and it works, though sometimes I have to go into Report Manager and fix the data source.
Next I wanted to be able to work with multiple databases, identical in structure. For this we did a hidden parameter with the database name and used a formula for the query string. This works pretty well.
Next I wanted to be able to run against multiple database servers from a single web server. This has been nearly impossible. I've read a million posts about this, and nothing seems to work well. I've tried a dynamic connection string, and passing the server in as a parameter, but this doesn't work, because I can't get the credentials set on the ReportViewer.ServerReport, so it doesn't work from dev to stage. You can't programatically change the shared data source - that would make it too easy. Linked servers are not an option.
I guess I need to either publish a copy of the report for each database server, or set up an instance of SQL on the web server for each database server.
Any other reasonable options out there. I just can't imagine my setup is all that unique.
My hosting company will give me a user with db_ddlAdmin how do I change my current database which has all the tables,store procedures and views owned by dbo.
I've used a few sql scripts to change the owner using
Exec sp_changeobjectowner 'object', 'user'
and I've used
Grant Execute on object to user
These have changed ownership but the site still doesn't work
This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...
I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.
SELECT * FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE '%' + @binomen + '%' AND Locality LIKE '%' + @locality + '%' AND ParentID1 = COALESCE(@taxparent, ParentID3) OR ParentID2 = COALESCE(@taxparent, ParentID2) OR ParentID3 = COALESCE(@taxparent, ParentID3) OR ParentID4 = COALESCE(@taxparent, ParentID4) OR ParentID5 = COALESCE(@taxparent, ParentID5) OR ParentID6 = COALESCE(@taxparent, ParentID6) OR ParentID7 = COALESCE(@taxparent, ParentID7) OR ParentID8 = COALESCE(@taxparent, ParentID8)
The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.
Has anyone got a good tip to help me resolve this puzzle?
I have been trying to uninstall SQL Server 2005 Express Edition for a day and a half to no avail. I have cleaned it out of the registry, and I have done everything possible to get rid of this, er, Microsoft virus software. I have erased every folder that has any SQL Server name after I stopped all SQL server services in the task list. All I want to do is install SQL Server 2005 Developer Edition so that I can use the BI Manager to create a DTS package. Does anyone have any utilities that might help?
I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is. Once I have established which SP is the main problem I need to debug the line of code.
What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..
Now what i thought is to write an SP or Function where I pass :
SP name and Parameters that profiler genererates and returns me Declare Statements and Set Statements with parameters filled.
EG
Profiler Returns Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'
I would call my new SP =PopulateSPParams and cut and paste the profiler's string PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'
this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.
I just spent the better par of 3 days creating a prototype in ASP.Net 2.0 and SQL Server Express only to discover that nobody from outside can see it... ERROR with impersonation=true User does not have permission to perform this action. ERROR with impersonation=false Unable to open the physical file "c:inetpubwwwroot------.mdf". Operating system error 5: "5(Access is denied.)".An attempt to attach an auto-named database for file c:inetpubwwwroot-----.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. What makes this so difficult? What am I missing?
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results Table B Contains an Icon, this image is displayed in the results Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA UNION ALL Select title, description, icon as image from tableB UNION ALL title, description, ( inner Join SELECT top(1) from imageTableC where imagetableC.FK = tableC.PK) as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
When I add a constraint to the where part of the SQL statement below it will only show the nc_department.department where siteid equals it. How do I pull and display all of those departments regardless of where the siteid = the selected item? In other words doesnt the Left outer Join supposed to grab all contents from the left table regardless?
SQL = "SELECT nc_department.order_id, nc_department.department, Count(nonconformance.department_id) as 'events', ISNULL(SUM(nonconformance.nc_wafer_qty),0) as wafers FROM nc_department LEFT OUTER JOIN nonconformance ON nc_department.department_id = nonconformance.department_id WHERE nc_department.active = '1' AND nonconformance.site_id = '" & siteid.SelectedItem.Value & "' GROUP BY nc_department.department , nc_department.order_id"
Soory if this is a bit basic, but I'm chasing my tail: I have two select statements: SELECT MyRows from Table1 where X = 1 SELECT OtherRows From Table 2 Where y = 3 I want to produce a LEFT JOIN between the result of each query, and return the resultant rows I know this is not correct syntax:
(SELECT MyRows from Table1 where X = 1) LEFT JOIN (SELECT OtherRows From Table 2 Where y = 3) ON Tabel1.Row1 = Table2.Row2
but I think it illustrates what I want to do If I do the WHERE after the join I only get where there is a righthand table I cant get the syntax right, an example with the correct grammar would be very much appreciated
