Wednesday, September 4, 2013

[SQL Server 2008 issues] create proc error

[SQL Server 2008 issues] create proc error

create proc error

Posted: 03 Sep 2013 06:35 PM PDT

hi my friendscreate procedure yerde@phone decimal(18,0),@unvan nvarchar(200),@yunvan nvarchar(200),@Sentar smalldatetime,@naryadN smallint,@naryadtar smalldatetime,@sebebi nvarchar(200)asinsert yerdeys(phone ,unvan ,yunvan ,Sentar ,naryadN , naryadtar ,sebebi )select@phone,(select top 1 *from phone where phone=@phone),@yunvan,@Sentar,@naryadN,@naryadtar ,@sebebi update phoneset telUnvan=@yunvanwhere phone=@phone---- error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Path to a Database Architect

Posted: 03 Sep 2013 05:06 PM PDT

Hi Guys,Would you please provide some links or books details to learn to become Database Architect.

Generate script for database Restore

Posted: 03 Sep 2013 09:30 AM PDT

I found a very good script to generate script for database Restore for native sql server.http://sequelserver.blogspot.com/2009/04/generate-restore-scripts-from-backup.htmlCan you please let me know how we can modify the same to work with Litespeed?CREATEPROCEDURE[dbo].[usp_GenerateRestoreScripts] @DBname VARCHAR(100)ASSETNOCOUNTON-- required because we're going to print T-SQL for the restores in the messages 'tab' of SSMS/* Script creates the T-SQL to restore a database with info from MSDBIt helps by creating RESTORE command constructed from the last FULL backup, the last DIFFERENTIAL backup and all the required TRANSACTION LOG backups after this.Neat when you have a high frequency of differential or log backupsThe variable @DBName should be set to the name of the database you want to restore.!!! BE AWARE: include MSDB in your backup plan for this T-SQL script to work in all circumstances !!!I usually include MSDB in the log backup schedule (set the db to full recovery mode)*/DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)DECLARE @i INT, @logBackupPath VARCHAR(1000)-- remove temp object that might existIFOBJECT_ID('tempdb..#MSDBBackupHistory')ISNOTNULL DROPTABLE #MSDBBackupHistoryCREATETABLE #MSDBBackupHistory ( id INTIDENTITY(1,1), backup_start_date DATETIME, backup_type CHAR(1), physical_device_name VARCHAR(1000))INSERTINTO #MSDBBackupHistory (backup_start_date, backup_type, physical_device_name) SELECT BS.backup_start_date, BS.type, RTRIM(BMF.physical_device_name) FROM msdb..backupset BS JOIN msdb..backupmediafamily BMF ON BMF.media_set_id=BS.media_set_id WHERE BS.database_name= @DBName ORDERBY BS.backup_start_date-- dump the last backup first in table-- get the last Full backup info.SET @lastFullBackup =(SELECTMAX(id)FROM #MSDBBackupHistory WHERE backup_type='D')SET @lastFullBackupPath =(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastFullBackup)-- Restore the Full backupPRINT'RESTORE DATABASE '+ @DBNamePRINT'FROM DISK='''+ @lastFullBackupPath +''''-- IF it's there's no backup (differential or log) after it, we set to 'with recovery'IF(@lastFullBackup =(SELECTMAX(id)FROM #MSDBBackupHistory)) PRINT'WITH RECOVERY'ELSEPRINT'WITH NORECOVERY'PRINT'GO'PRINT''-- get the last Differential backup (it must be done after the last Full backup)SET @lastDifferentialBackup =(SELECTMAX(id)FROM #MSDBBackupHistory WHERE backup_type='I'AND id > @lastFullBackup)SET @lastDifferentialBackupPath =(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@lastDifferentialBackup)-- when there's a differential backup after the last full backup create the restore T-SQL commandsIF(@lastDifferentialBackup ISNOTNULL)BEGIN -- Restore last diff. backup PRINT'RESTORE DATABASE '+ @DBName PRINT'FROM DISK='''+ @lastDifferentialBackupPath +'''' -- If no backup made (differential or log) after it, set to 'with recovery' IF(@lastDifferentialBackup =(SELECTMAX(id)FROM #MSDBBackupHistory)) PRINT'WITH RECOVERY' ELSEPRINT'WITH NORECOVERY' PRINT'GO' PRINT''-- new line for readabilityEND-- construct the required TRANSACTION LOGs restoresIF(@lastDifferentialBackup ISNULL)-- no diff backup made? SET @i = @lastFullBackup +1 -- search for log dumps after the last fullELSESET @i = @lastDifferentialBackup +1-- search for log dumps after the last diff-- script T-SQL restore commands from the log backup historyWHILE(@i <=(SELECTMAX(id)FROM #MSDBBackupHistory))BEGIN SET @logBackupPath =(SELECT physical_device_name FROM #MSDBBackupHistory WHERE id=@i) PRINT'RESTORE LOG '+ @DBName PRINT'FROM DISK='''+ @logBackupPath +'''' -- it's the last transaction log, set to 'with recovery' IF(@i =(SELECTMAX(id)FROM #MSDBBackupHistory)) PRINT'WITH RECOVERY' ELSEPRINT'WITH NORECOVERY' PRINT'GO' PRINT''-- new line for readability SET @i = @i +1-- try to find the next log entryEND-- remove temp objects that existIFOBJECT_ID('tempdb..#MSDBBackupHistory')ISNOTNULL DROPTABLE #MSDBBackupHistory

make VARCHAR field longer on a large table

Posted: 03 Sep 2013 09:05 AM PDT

>>>>Scenario:SQL Server 2008 R2 on a Windows cluster, 256 GB RAM, 32 CPUthere is a non-partitioned table with 2.5 Billion records, 1TB in size, 100 fields.Due to a changed Business logic, one field in the table needs to be modified from VARCHAR(65) NULL to VARCHAR(85) NULL. There is some 20% NON-NULL values in the field.database recovery mode is simple.<<<<END ScenarioWhat is the risk [if any] of running simple ALTER COLUMN DDL to make this conversion? ALTER TABLE MyTable ALTER COLUMN MyCol VARCHAR(85) NULLWhat is the best way to approach this in your opinion? My understanding that ALTER COLUMN will not have to allocate any new pages to the table, so it should run fast, but there are some other opinions in the house..Thank you.

Difference Between Restore And Attach

Posted: 03 Sep 2013 02:53 AM PDT

What is the difference between Attach And Restoring a Database in Sql Server?Looks like a Simple question?please folks share your comments?

SQL 2000 Install

Posted: 03 Sep 2013 12:15 AM PDT

I need to install SQL 2000 on a VM that already has 3 instances of SQl 2008 R2 installed.Something in the back of my head tells me from experience that this is not possible. I would need to have installed the oldest version first ie SQL 2000, then the 2008 instances.I'm sure I had this issue some time ago. But I have slept many times since.Again I get that nagging feeling telling me it's not possible.

Sql Server Objects Naming Conventions

Posted: 02 Sep 2013 10:32 PM PDT

Hello allI have recently been tasked with putting a naming convention in place for a new Sql Server Database/Project. I haven't set any naming conventions in a while as more recently I have found myself in environments where there is one in place.I guess for naming conventions I don't have any strong preferences the one way or the other, other than for it to be consistent and easy to type. Is that wrong or right... can't say.I wondered what others were using and if there any reasons why you would use the one convention over another?

Condition requirement

Posted: 03 Sep 2013 02:52 AM PDT

Hi,I am trying to do a lookup in SQL and have to create a SQL code for the following condition under the where clause------------------------------------------------If the Continent_cd (tableA) value is in ("EUR","MED") for the matching POL_Location_cd (tableB) value in the Location_cd (tableA) column then it should be classified as OUTIf the Continent_cd (tableA) value is in ("EUR","MED") for the matching PDL_Location_cd (tableB) value in the Location_cd (tableA) column then it should be classified as INelse it should be NON------------------------------------------------The DDL and sample data are as follows-----------------------------------insert into [tableA] values('UYMVD','ESM') insert into [tableA] values('BEANR','EUR') insert into [tableA] values('DEHAM','EUR') insert into [tableA] values('USSSI','MED') insert into [tableB] values('UYMVD','PHMNS') insert into [tableB] values('BEANR','SGSIN') insert into [tableB] values('DEHAM','PTLIS') insert into [tableB] values('MXVER','USSSI')----------------------------------------------Does anybody know how this can be accomplished ? Any suggestions on using lookup in SQL would also be helpful.Thanks.

Query Help

Posted: 03 Sep 2013 05:39 AM PDT

Hello EveryoneI need to develop something in my logic. I have one code '7MTWRF', this is string and i need to convert asM:7;T:7;W:7;R:7;F:7please help me to do soThanks

SSRS, merge/use data from 2 .rdl

Posted: 03 Sep 2013 04:49 AM PDT

Hi,I have SickDays.rdl and Encounters.rdl, they both independent reports in SSRS and implemented as stand alone RDLs. Now I want to produce one more report Average.rdl which as it says will use data from those 2 reports, so Average = SickDays_Number / Encounters_NUmber.I' about to merge those 2 codes together to make wanted sql, but wandering if there are any other smart ways to use/share data between reports.Appreciate you feedback/thoughts..BestMario

How to install SSIS/SSAS/SSRS

Posted: 03 Sep 2013 02:19 AM PDT

Hello,Was curious if someone could help or point me in a direction. We have SQL 2008 installed and as part of the installation we didn't install SSIS/SSAS or SSRS. How can i go about installing those other components? This is in a production environment. Would it require a restart? Does it require to be installed on the same server as SQL Server or does it work like SMS?Thanks for any help.

Activity Monitor - % Processor Time Grayed Out

Posted: 14 Feb 2010 01:13 PM PST

Does anyone know why the % Processor Time area within Activity Monitor would be grayed out?

Cannot open New SSIS Project in SQL Server 2008 R2

Posted: 02 Nov 2012 12:03 PM PDT

I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer use the SSIS to create New Projects. I cleaned out the registry with a Registry cleaner from Wise. I deleted the SQL Server and Visual Studio folders as well.When I create a New SSIS Project, I immediately get an error as follows:"Microsoft Visual Studio""An error prevented the view from loading.""Additional information:""Specified Module cannot be Found. (Exception from HRESULT: 0x8007007E)""(System.Windows.Forms)"How do I resolve this problem? What this means is that I cannot create any new SSIS Projects.Cecil

[Microsoft][ODBC SQL Server Driver]Login timeout expired

Posted: 03 Sep 2013 01:10 AM PDT

hi everyone! i am a student working with SQL SERVER 2008R2 on windows 8 64-bit os.. from a month i am facing this error whenevr i try to login after 6:30 p.m. this creates a great mess for me.. as i can't do any work after this error..please help me.. its urgent as i am pissed off with this error.i am using SQLServer authentication mode..i even tried to change the connection timeout and running as an administrator..but no improvement-error remains the same Microsoft SQLServer Login //this is the title of the dialog boxConnection Failed:SQLState:'HYT00'SQL Server Error:0[Microsoft][ODBC SQL Server Driver]Login timeout expiredi have come to this forum with great hope..i want to get rid off with this error..PLEASE help me!!!!!!!!!

Transaction Log Functionality Question

Posted: 02 Sep 2013 11:50 PM PDT

I know Gail will have the answer, maybe someone else will too.I have a database that will fill up the log on the next execution of a specific job. This database & log are on one hard drive. The increased disk space request is already under way, but that takes time.In the meantime, I am adding a second log file to the server's other drive where there is some temporary extra space.My question is this: When a database has two log files, does it stripe the transaction log data across both files or does it fill one then switch to the other? I'm thinking it stripes, but my google fu can't seem to find the definitive answer (I keep getting the basic transaction log articles & BOL links). So I would appreciate any insight you might have on this.

There is insufficient system memory in resource pool 'internal' to run this query"

Posted: 10 Mar 2009 04:29 AM PDT

Hi,We have a certain customer running SQL 2008 64bt Standard, windows 2008 std. The server is using about 3.8GB of its 4GB of Physical memory. We can run queries in Query Analyser but when our web application tries to run even a simple SQL query against the database we get "There is insufficient system memory in resource pool 'internal' to run this query"Is this purely down to the fact the server is running out of Physical memory. Will SQL not use the page file, will it just generally throw up this error when it runs out of Physical memory. Also I've never come across this problem before, so it is a specific 2008 issue or 64 bit issue?Any help would be much appreciated.Many Thanks,Andy

Servr patching install

Posted: 02 Sep 2013 08:45 PM PDT

Hi,I need to update sp2 and cumulatives in our sql server r2 2008. i could see in microsite site that there are around 5 cumulatives patches after Sp2.do i need to apply all cumulatives after sp2 or only the latest cumulatives is enough?Please clarify.Many thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog