There was this old sql server tool called Lectoneth or something like that, you'd put sql queries in it, and it would rewrite it for you. I think quest bought them out, but I can't find where to download a free copy of that software. Really helps when you have no dba, and have lots of sql queries to rewrite. Thanks Craig ...
I keep getting this error: Any Ideas? Invalid use of side-effecting or time-dependent operator in 'newid' within a function. I am working with Ms-sql 2005 Create Function [dbo].[GetNewNumber]( ) RETURNS int AS BEGIN Declare @code int set @code = (SELECT CAST(CAST(newid() AS binary(3)) AS int) ) RETURN (@code) END ...
I have a data viewer attached to a transformation to show me the data as a grid. At the bottom is says total rows 22002, rows displayed 9964. How do I get it show me the remaining rows? I've tried clicking and right clicking on everything! ...
SQL 2005: I am trying to create an outer join that will pull records from two different databases. My objective is to determine which records in database B don't have matching records in database A. When I tried running the query, it returned the error below. I am not sure how to get around this error: 'Tables or functions 'Asset...
My ultimate goal is to write a sql script that selects data from a particular table where a nvarchar(max) column contains the character NCHAR(2028). But the obvious: select * from tablename where columnname like '%' + NCHAR(2028) + '%' returns all rows. ...
Suppose I have a table: CREATE TABLE [tab] ( [name] varchar, [order_by] int ) There are 10 rows in the table, and all rows have same value for order_by (Let's say it's 0) If I then issue following SQL: select * from [tab] order by [order_by] What's the order of the rows? What factor decides the row order in this case? ...
Is it possible to improve performance by taking the following SQL: SELECT t1.ID, t1.NAME, t2.SubName, t2.RefValue FROM Table1 as t1 CROSS APPLY ( SELECT Top 1 t2.SubId, t2.SubName, t3.RefValue FROM table2 as t2 INNER JOIN table3 as t3 ON t2.SubId = t3.SubId ORDER BY LastUpdated desc ) as...
What other products are similar to Redgate's SQL Search? Redgate just released "SQL Search" in Feb 2010. http://www.red-gate.com/products/SQL_Search/index.htm I am still amazed this type of search is not already built into SQL Server Management Studio. Updated: If you are curious. Here is the story how SQL Search got developed. htt...
I have a query which is not working as expected Q1: SELECT id, name FROM vw_x WHERE id NOT IN (select pid from table_x) GROUP BY id, name Having max(c_date) > GETDATE() Q2: SELECT id, name FROM vw_x GROUP BY id, name Having max(c_date) > GETDATE() Q1 is not returning anything even though i know those ids are not in table_x Q2 run...
So I made a package in SSIS to read data in from a text file and load it into a database table. What's the best way to set this up for non technical end users to run this when desired? My boss was thinking to have a SP launch it, and then have a report made in reporting services launch the stored procedure. Surely there's a better w...
I'm developing a datawarehouse to be part of my company's enterprise application suite. So I've been learning a lot about DW concepts but the rules engine seems difficult and I can't find much information about various ways to implement. The focus of the rules is to validate data quality, and also alert when certain business metrics ar...
I tried exec sp_depends @objname = 'sfel.elpc' but I did not get any results, but I know the synonym is referenced in at least one stored procedure. ...
I have a database with data that i don't want anyone to copy around. Now, how can i prevent other users to have access in the local machine, but whenever i want to work with, i am allowed. And another issue I'm thinking, how can i lock the DB so if someone copy the .mdb file and try to attach it on another machine he/she couldn't see ...
Background: I have an application that I have nicely separated my interface logic from my middle tier logic which handles the queries to the database. I do a lot of custom sorting and narrowing so I'm not using many SqlDataSources and instead calling a lot of stored procedures with SqlCommands. I am using Forms Authentication to create...
When we develop an ASP.NET client site, we deploy a SQL Server Express database along with it. We need to run a clean-up script to check for records older than n. hours, but unfortunately I cannot schedule a job do to the limitations of SQL Express. Are there any other alternatives or suggestions for automating clean-house that anyone ...
Does anyone know if it is possibble to use SSIS with Amazon RDS? Since RDS is essentially MySQL - would this be possible using OLE DB or something...? ...
Hi Guys, I have a table in my SQL Server database called "items" which has a column called "dateFinished". I have a script that will run on the 1st day of each month which needs to select all items that finished in the previous month. So, for example, on the 1st February it will need to select all items where the dateFinished is great...
I need to pass the error to the application. I wrote the stored procedures as below: Does it pass the error to the application or we need to do the something more? Eg. create procedure insert_emp as begin try begin tran insert into emp values(..........) commit; end try begin catch if @@trancount >...
Using Visual Web Developer 2008 which comes with SQL Server 2008 by default. Is there a way to change a SQL2008 database to SQL2005 version from within Visual Web Developer? Cheers ...
Hi folks: What is the problem with following SQL. Can table variable not be used in JOIN clause? Error msg is of "Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near 't1'." Declare @t TABLE ( _SportName varchar(50), _Lang varchar(3) ) insert @t VALUES('Basketball', 'ENG') -- ENG UPDATE tblSport t1 SET ...