I have one table in my database that has a primary key and its data type is varchar(10) but it stores only 5 char, so I want to change its data type from varchar(10) to varchar(5) without losing any data. Currently I'm creating a temp table, moving data from the original table to the temp table, then altering it, and lastly restoring th...
I hate this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code, so that I will never see this error message again? I mean, I could add a where clause so that my divisor is never zero. Or I could add a case statement, so that there is a special treatment for zer...
Is there a way to know the size of a database full backup file before we actually do the backup? I did some analyses and figured sp_spaceused could give a close figure but thats not all, there seem(s) to be one/more factors other than the used space that go into determining the size? Has anyone been down this path before? Any ideas? ...
I have a pretty standard database sitting on SQL Server. To manage the data (instead of creating a solution using an IDE) I've linked to the database using MS-Access (4 users as a start. I'm testing a concept and a full solution can be developed once there's enough data). *MS-Access > New file > Project using exiting data... *Specify SQ...
This query give me error:Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'where'. declare @date1 datetime,@date2 datetime , @COUNT INT , @countgap int, @order int seLECT @date1='2009-05-11' , @date2 = '2009-05-12' seLECT @countgap = 30 , @COUNT = 0, @order=23 select VisitingCount from ( select count(page) as Visi...
How can i join these two queries (Query1-Query2) Query1: declare @date1 datetime,@date2 datetime , @COUNT INT , @countgap int seLECT @date1='2009-05-11' , @date2 = '2009-05-12' seLECT @countgap = 30 , @COUNT = 0 select @date1 TARIH , @COUNT SIRA INTO #TMP WHILE @date1 < @date2 BEGIN SELECT @date1 = DATEadd(minute,@countgap...
I have a database (using Microsoft SQL Server Management Studio Express) that is currently being used quite heavily in a functioning application. I am porting this application over to Windows Authentication rather than the current basic authentication system (or lack thereof) that exists. The easiest way that I know to set this up invo...
On SQL Server 2005, you're supposed to be able to find dependencies in Manglement Studio by right-clicking an object and choosing "View Dependencies". The dialog gives you a choice of "Objects that depend on this" (what I'm calling down-stream) or "Objects that this depends on" (up-stream). Both directions seem to work adequately for Ta...
I need to change the owner of a database diagram in SQL Server 2005. Currently it is owned by me (domain\username.diagramName) and I would like to change it to be owned by dbo (dbo.diagramName). I thought I could use sp_changeobjectowner, but I believe that is only for tables, stored procedures, etc... I think this is pretty easy, I just...
I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column. so for example i have a table like Table(ID, Name, RecordStatus) Record status can only have a value 1 or 2 (active or deleted), and i want to create a unique constraint on ID, RecordStatus only when RecordStatus = 1,...
I want to be able to find out how much space is left in my database files so that I can know when to increase the space so it doesn't do it when the application is running hard out. I would prefer to be able to script this so I can run it across multiple databases ion a regular basis. I have SQL Server 2000 and SQL Server 2005 database...
On SQL Server 2005, I have a complex multi-level allocation process which looks like this (pseudo-SQL): FOR EACH @LVL_NUM < @MAX_LVL: INSERT INTO ALLOCS SELECT 'OUT', * FROM BALANCES(@LVL_NUM) INNER JOIN ALLOCN_SUMRY(@LVL_NUM) INSERT INTO ALLOCS SELECT 'IN', * FROM BALANCES(@LVL_NUM) INNER JOIN ALLOCNS(@LVL_NUM) INNER JOIN ALLOCN_SUMRY...
Looking for guidance as to the standards compliance of the generated html by reporting services e.g. XHTML, CSS, WCAG etc ...
How to Insert Only Date and Only time in the table(column datatype is DateTime) using SQL Server 2005. ...
I wish to move a database between two servers, I have backed the database up from the first server and done a database restore on the 2nd server, so far so good. However our application make use of a lot of database users that are defined in the database, these have to be linked to logins that are defined in the master database. The se...
I have a SQL 2005 database with approx 250 tables. I want to temporarily enable ON DELETE CASCADE to all of the Foreign Keys so that I can do a bulk delete easily. I then want to turn off ON DELETE CASCADE on all Foreign Keys. The only way I know of doing this, is to use Management Studio to generate a full database create script, do ...
i need to take all data if Page!=@page, get AllDATA select count(page) as TARIH, (datepart(hour,Date)*60+datepart(minute,Date))/@countgap as SIRA from scr_SecuristLog where Date between @date1 and @date2 and Page=@page or Page = AllDATA ...
Is there an easy way to check if a foreign key exists for a column in a table? I am writing a script which will add the foreign key only if it does not exist. Thanks ...
Is there a way to apply a schema to an xml column to type values such as Date, int, decimal, etc? Also, when querying the contents with XPath is there a way to do check against the actual type and not its string value? Should we even try this, is there a performance hit with doing raw string checks for these values, or should we even b...
Is there a way to add files to a sql server index without adding them as blobs to the database? Currently we do extract the text content of all our pdf files usind pdf2text and store them as a blob. This way, sql server can add the texts to his index. But this is a big waste of space. We are using sql server 2005. Can the new filestream ...