How can i get result "My Dream Table Result" CREATE TABLE #temp( [count] 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 ([count], [Time]) values(0,@DateNow) set @i=@i+1 end ...
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 "...
I am trying to decypher an absolute monstrosity of a stored procedure and i'm not having much luck. Are there any free tools that will help visualise the query or at least format the syntax into a more readable format ? Any hints and tips are also welcome. The type of database i am using is MS sql server 2005 ...
My application keeps running into Timeout Expired SqlExceptions. The thing is that this query is one that will simply have to run for a decent amount of time. I'm having trouble figuring out where this exception is occurring though. Is this a timeout that's created at the database server or is it happening in my program? Or if it cou...
Hi, What is the best practice for SQL testing (stored procedures, views, queries, triggers...) excluding automated testing? Most of the time a query works or returns thousands of rows, so it is a bit difficult to check them. I want to know how you usually do the testing of a SQL query. Can you point me to a good (online) reference? ...
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...
I have a script that was generated in SQL Server 2008, but I need to execute it against a SQL Server 2005 database. What would an equivalent statement for the following be in SQL Server 2005? ALTER TABLE dbo.Event SET (LOCK_ESCALATION = TABLE) ...
I'm trying to work out what the best method to passing a large number of parameters into a stored procedure. Some methods i was thinking about are; Create the relevant database objects, including a parameter object for each item and invoke the command object Pass in an XML document and have the stored procedure unpack it. (The app wil...
I want to be able to invoke an SSIS package at will from a web application. I've found that I can do this successfully with sp_start_job when running on my local machine. However, when I publish to our test site, I get: The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema dbo' So I tried this USE...
I have a host which runs SQL Server 2005, I have the ability to login to my database and copy/read/write/drop all tables/views/sp's. I tried simply right clicking on my server and selecting backup but I don't seem to have permission to do any backing up. This cannot be an uncommon occurrence (someone using host/providers db). Has ...
Hello, I would like to create a query from a single table with the following columns. SEQNO is a unique key Name ID Amount Date JOBID SEQNO Mark 9 200 1/2/09 1001 1 Peter 3 300 1/2/09 1001 2 Steve 1 200 2/2/09 1001 3 Mark 9 200 3...
Hello everyone, I have two tables, and they are using different collations. It is not allowed to concatenate columns from tables with different collations, for example the following SQL is not allowed, select table1column1 + table2column2 from ... My question is, how to change the collation of a table without destroying the data of t...
as usual, we'd like to think that there won't be any trouble migrating our applications to the latest sql version... but... but... but... well, just to be prepared, has anybody stumbled into some nasty incompatibility issue? (for example, I remeber that after migrating from 2000 to 2005, one linked server we had to a sql 6.5 server ...
i have a table. i have a user defined function(UDF). My UDF returns me a table. i need left outer join my table and my UDF table. ...
I am getting xp_sendmail: Procedure expects parameter @user when I run xp_sendmail. How do I associate a given SQLService login profile (SQLMail_Whatever) to outlook? ...
I would like to create a report from a project management system that has been created in Lotus Notes. I have connected to ORACLE from SSRS before, can I connect to the Lotus Notes DB? ...
I have done a database diagram in Microsoft Visio but I don't know how to import it to Sql Server 2005? By the way, which is the best tool you think you can use to do database diagrams, and that allows you to import it to Sql Server? ...
How can i join ( or union) #Temp with scr_SecuristLog Time. Look please [Time] column CREATE TABLE #Temp (VisitingCount int, [Time] int ) DECLARE @DateNow DATETIME,@i int,@Time int set @DateNow='00:00' set @i=1; while(@i<48) begin set @DateNow = DATEADD(minute, 30, @DateNow) set @Time = (datepart(hou...
We have a need to be able to dynamically change the Data source for a report built/managed in Report Builder. Basically its the same DB structure but each client has their own DB so depending on the parameter for the ClientID it needs to dynamically go to the appropriate DB. Anybody have any good solutions for this? Thanks in advance,...