I got a distance field in my database that stores the distance traveled on a specific route. I need to select all the distance fields and plus them together, then returning the result from my stored procedure. How can this be done? ...
i try to generate a table (look TABLE 1) with the query(below). CREATE TABLE #temp( [VisitingCount] int, [Time] nvarchar(50) ) DECLARE @DateNow DATETIME,@i int SET @DateNow='00:00' set @i=1; while(@i<1440) begin set @DateNow=DATEADD(minute, 1, @DateNow) insert into #temp ([VisitingCount], [Time]) values(0, right(left(conv...
This Codes give me error:Msg 156, Level 15, State 1, Procedure DefaultActivity, Line 2 Incorrect syntax near the keyword 'RETURN'. Msg 137, Level 15, State 2, Procedure DefaultActivity, Line 3 Must declare the scalar variable "@returnTable". Msg 1087, Level 15, State 2, Procedure DefaultActivity, Line 18 Must declare the table variable "...
Let's assume that I have two tables... Foo and Bar. They contain the following data. Table Foo: Foo_Id ------ 100 101 Table Bar: Bar_Id ------ 200 201 As you can see, each table has two records. I'd like to join these tables together in a way where they return two records; the ultimate goal is to create a one to one relationship f...
I need to add an index to a table, and I want to recompile only/all the stored procedures that make reference to this table. Is there any quick and easy way? EDIT: from SQL Server 2005 Books Online, Recompiling Stored Procedures: As a database is changed by such actions as adding indexes or changing data in indexed columns, the origi...
Basically i want to be able to dynamically create a temp table based off of an existing table, and then insert values into the temp table, and select the inserted values. i've got the part where i can create the temp table working just fine, it's just that inserting and selecting form it aren't working too well. here's my current code....
Hi, I'm considering writing some unit tests for my Tsql stored procedures, I have two concerns: 1) I will have to write a lot of SQL to create test fixtures (test data prepared in _setup procedures) 2) I will have to "re-write" my query in the test procedure to obtain the results to compare against the results from the stored procedure...
Background: I have an interview some time next week (for a Jr position) that requires SQL Server. The job descriptions states "Must be able to score high in a C# and a SQL Server design test." I feel I'm solid on the C# portion, and I think I'm pretty good on implementation-agnostic DB design (Reread "Mastering Data Modeling" by Car...
I'm working on a data warehouse project and would like to know how to (preferably in a Derived Column component in a Data flow) strip the date piece off of a SQL datetime record. Once I have the datetime converted to just a time I am going to do a lookup on the time to find the related time record in a time dimension table. Can someone...
Hi there, can anyone help me with construction of an IF in a stored procedure in sql server. Basically I have a simple stored procedure but I now need to pass in a new input parameter which depending if it is true I pass the value D and if its false I pass the value A. But the change is in the middle of a subquery.. let me explain... h...
Hi folks, i have two tables. BoardPosts BoardPostId INT PK ModifiedOn DATETIME NULLABLE BoardComments BoardCommentId INT PK BoardPostId INT CreatedOn DATETIME A board post has zero to many comments. I wish to set the ModifiedOn field to be the most recent comment date, if the board has a comment. Otherwise, just leave it null. How...
I have a collation problem with my database and I have developed my own solution. Solution: DECLARE @new_collation varchar(128), @conflict_collation varchar(128), @cmd_holder varchar(2000), @cmd_complete varchar(2000), @schema varchar(128), @table_name varchar(128), @constraints_name varchar(128), @column_name varchar(128...
This query is taking long time when endDate is null (i think that its about case statement, before case statement it was fast) SELECT * FROM HastaKurumlari WHERE CONVERT(SMALLDATETIME,'21-05-2009',103) BETWEEN startDate AND (CASE WHEN endDate IS NULL THEN GETDATE() ELSE endDate END) What should i use, when endDate is null to ma...
I'm trying to extract monetary sums stored in some poorly formated xml columns (there is no schema defined for the XML column which I guess is part of the problem). I'm getting a conversion error whenever I encounter a node with 0 as its value. Example: select xml.value('sum(/List/value)', 'numeric') sum from (select cast('<List><value...
Hi Guys I have the following SP CREATE PROCEDURE GetAllHouses set @webRegionID = 2 set @sortBy = 'case_no' set @sortDirection = 'ASC' AS BEGIN Select tbl_houses.* from tbl_houses where postal in (select zipcode from crm_zipcodes where web_region_id = @webRegionID) ...
I have quick question for you SQL gurus. I have existing tables without primary key column and Identity is not set. Now I am trying to modify those tables by making existing integer column as primary key and adding identity values for that column. My question is should I first copy all the records from the table to a temp table before m...
I was wondering if anybody knew exactly what permissions where needed on a database in SQL Server 2005+ so that when a person uses SQL Server Management Studio, they could then be able to at minimum see the Database Diagrams. I have tried giving the person db_datareader, db_datawriter, and db_ddladmin, but to no avail. I have also tr...
What is the T-SQL syntax for granting a specific user only insert permission after a create table command? Here is my CREATE TABLE script: CREATE TABLE [dbo].[MyTable] ( [MyColumn1] [uniqueidentifier] NOT NULL, [MyColumn2] [char] (1) NULL ) ON [PRIMARY] ...
Does sql server allow nested transactions? If so then whats the priority of transactions? ...
Sometimes I need to find some strings inside DB usually it is just host-name or ip address. Is there any script which finds string in all Sybase db objects (or at least in all tables) that I have access to. ...