I have a simple query: var results = from k in db.tree_nodes join s in db.stocks on k.tree_nodes_id equals s.tree_nodes_id into tmpTable from rowtmp in tmpTable.DefaultIfEmpty() select new { stock = (rowtmp.amount == null) ? ...
I would like to extract some data from three tables in a SQL Server 2005 database. While this can surely be done in code, it seems like this could be done reasonably well in SQL (bonus points for LINQ!). Basically, I would like to know for each month how many calls and meetings each employee has held with each of our clients. Somethin...
Update: After some experimentation, it appears that the thing that is slow is when I call "SELECT min(column) FROM theTableValuedFunction()". I'll call the local server ServerLocal and the other remote server ServerForeign. I have a SQL query in a table valued function on ServerLocal that looks like this: SELECT columns FROM ServerFo...
Hello, I'm not a SQL Expert. I'm using SQL Server 2005 and I'm trying to figure out how to structure a query so that it can meet several requirements. I have two tables defined as follows: Classroom - ID - DepartmentID - TeacherName - RoomNumber Student - ID - Name - ClassroomID I'm trying to build a query that says, "give me...
I am having a problem uploading data from tab-delimited flat files (TSV files) into SQL Server 2005 using the SSIS Data Import wizard. I did not experience this problem using the equivalent procedure in SQL Server 2000, and I have checked that the internal structure of the files I am trying to import is unchanged since well before the SQ...
I have a SQL Server 2005 stored procedure that performs a query. This stored procedure takes in three parameters. The parameters are as follows: @StateID as int, @CountyID as int, @CityID as int These parameters are used to query a list of customers. I want to basically do a "AND" if the parameter value is not null. However, I ...
Have an inline sql udf When called with variable DECLARE @COMNO CHAR(3), @CUNO VARCHAR(6) SELECT @COMNO='020',@CUNO ='000022' select * from ftItemsOfCustomer(@COMNO,@CUNO) TAKES 9 SECONDS, WHERE AS select * from ftItemsOfCustomer'020','000022') TAKES ONLY A SECOND ! WHY AND HOW CAN I RESOLVE THIS TO PERFORM WITH EQUAL RESPONSE ...
Hi all, I have a client that absolutely insists that their database should have no Identity columns, and has manually implemented his own incrementer in a stored procedure that creates table rows. It has been written so that the next ID value is determined and then used in an insert statement. Obviously this is vulnerable to a host of p...
How can I generate the dates for the entire range? Thanks ...
Hello All, I'm new to SQL. I have a large number of stored procedures in my production database. I planned to write an audit table that would be used by these stored procedures to keep track of changes ( these stored procedures would write to this audit table ). But the issue is that when a transaction rolls back, the rows inserted into...
Hello All, I'm new to SQL CLR programmability. I have a CLR stored procedure that writes to a local file. But when I have multiple connections calling the same stored procedure, how do I synchronize access to the local file? Any inbuilt features? Or any other input is welcome. ...
Following is the sample data. I need to make 3 copies of this data in t sql without using loop and return as one resultset. This is sample data not real. 42 South Yorkshire 43 Lancashire 44 Norfolk Edit: I need multiple copies and I have no idea in advance that how many copies I need I have to decide this on the basis of dates. Dat...
I'm in the process converting our CakePHP-built website from Pervasive to SQL Server 2005. After a lot of hassle the setup I've gotten to work is using the ADODB driver with 'connect' as odbc_mssql. This connects to our database and builds the SQL queries just fine. However, here's the rub: one of our Models was associated with an SQ...
I'm new to ASP.NET MVC and I'm creating an app that will search a contact using using the autocomplete functionality along with jquery. When I run the project it loads fine and when i click the submit button to search a lastname i receive an error. Server Error in '/' Application. The resource cannot be found Requested URL...
Can we return null value from stored procedure. i dont want to use collase or isnull. I want to capture NULL at the frontend. Is it possible ? Edit: I am using Sql Server 2005 eg. where i want to use CREATE PROCEDURE [Authentication].[spOnlineTest_CheckLogin] @UserName NVARCHAR(50) AS BEGIN TRY BEGIN TRAN ...
Visual Studio, c#, SQL 2005 server. I"m trying to match the .dbml table data type with my .cs file. The goal is to allow an image to load to the database. So far it is not working. The issue seems to be related to the file type for the FileContent column. I have tried several different variations, but none have worked. <Column Nam...
Hi everyone, I've been playing with cast()s and such with this and can't seem to get things to work. I have a varchar string that's 18 characters long that I'd like to convert or cast to a decimal, with five decimal places. So for instance, this string: 00000001987600130 Would become 19876.00130 It's the case that I'll always have ...
I removed some rows from a very large table. Then I ran a query that usually runs within a few seconds and found it to be running very slowly after deleting the rows. I re-built my index and ran my query and found it to be fast again. Could deleting those rows caused the index to be fragmented? ...
Hi, i need a query to list every stored procedure that can be executed by a specific user and This for each user of my mssql server 2005. output example: sa: sp_MSrepl_startup sp_MScleanupmergepublisher administrator: xp_foo sp_bar Any help will be appreciated phillipe ...
Today at work we got into a discussion about which is the best way to do a query like this : For instance lets assume a users table : tblUsers ID = Autoint Name = String and a login table : tblLogin ID = AUtoint UserID = Int IP = String Browser = String OS = String timestamp = DateTime What would...