|
|||||||||||||||||
In this issue |
|||||||||||||||||
|
|||||||||||||||||
In This Issue |
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
From the SQLServerCentral Blogs -
|
|||||||||||||||||
|
|
|||||||||||||||||
Editorial - Writing |
|||||||||||||||||
They're not contrived or made up examples, or situations that only mimic a portion of the real world. You're writing about things in the real world. With that in mind, we have an easy poll for you this week: Why Do (or Don't) You Write?I expect that if you don't want to write articles you might not respond here, but I'm still curious to see what thoughts some of you have. For me, it's an easy question to answer: I really enjoy writing. The challenge of coming up with new thoughts, new ideas, trying to explain things to people, getting feedback in comments, it is just very exciting.. Often my wife will look up and see a goofy smile on my face after I've read some comment and ask me about it. I've even laughed out loud at times. And I've been ready to curse people at others. But overall, the thing that I hated most in high school has become the thing I enjoy doing most of the time. I think about writing all the time and spend almost every day working on some editorial here or there. So let us know why you write, or why you don't. Maybe I'll be able to convince a few more of you to participate and share your knowledge in the next year. » Join the debate, and respond to today's editorial on the forums The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.podshow.comto get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
Overall RSS Feed:
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it. » To submit an article, rant or editorial, log in to the Contribution Center |
|||||||||||||||||
|
|
|||||||||||||||||
Question of the Day |
|||||||||||||||||
|
Today's Question: Which of these are true for subqueries? (select 3) Think you know the answer? Click here, and find out if you are right. This question is worth 1 point in this category: Subquery. We keep track of your score to give you bragging rights against your peers. We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center. |
|||||||||||||||||
SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros". |
|||||||||||||||||
|
|
|||||||||||||||||
Yesterday's Question of the Day |
|||||||||||||||||
|
We have following 6 sql statements. Run each, one at time on SQL Server 2008. Which queries will fail? (select 2)
-- Query #1
USE master;
GO
IF DB_ID('DBDB') IS NOT NULL DROP DATABASE DBDB
GO
-- Query #2
USE master;
GO
IF OBJECT_ID('DBDB') IS NULL CREATE DATABASE DBDB
GO
-- Query #3
USE DBDB;
GO
IF OBJECT_ID('dbo.TBTB') IS NULL CREATE Table dbo.TBTB (tb1 int)
GO
-- Query #4
USE DBDB;
GO
IF OBJECT_ID('dbo.uspUSP') IS NULL
CREATE PROCEDURE dbo.uspUSP AS SET NOCOUNT OFF
GO
-- Query #5
USE DBDB;
GO
IF OBJECT_ID('dbo.TYTP') IS NULL CREATE TYPE dbo.TPTP AS TABLE(TYTB int)
GO
-- Query #6
USE DBDB;
GO
IF OBJECT_ID('dbo.udfUDF') IS NULL
CREATE FUNCTION dbo.udfUDF(@f int) RETURNS INT AS BEGIN SET @f=1 RETURN @f END
GO
Answer:
Explanation: The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch. The same rule applies to Functions as well, but this is not noted in MSDN.
Ref: http://msdn.microsoft.com/en-us/library/ms187926(v=sql.100).aspx |
|||||||||||||||||
SQL Queries 2012 Joes 2 Pros® Volume 4: Query Programming Objects for SQL Server 2012The SQL developer needs to be able to create processes for the working database by using one of the many programming objects (like functions, stored procedures, constrains, or triggers). By creating objects that talk with SQL you simply the way other programs (like applications or web pages) can interconnect. These eternal programs only need to call on the names of your programming objects by name rather than needing to submit large pieces of advance code. With SQL Queries 2012 Joes 2 Pros® Volume 4, you learn how programming objects work in SQL Server. For those of you who have read the 2008 series for the 70-433 Exam you will find a lot of the same material from the SQL 2008 book in this SQL 2012 book. This is because much of the 70-461 test covers the same material as the 70-433. I have added material that is new to the test and removed material that is no longer relevant. If you have already read this series or have already passed the 70-433 exam you may choose to read my book which covers only the changes from 70-433 to 70-461 entitled "Joes 2 Pros SQL 2012 Queries 70-461 Exam for SQL 2008 Pros". |
|||||||||||||||||
|
|
|||||||||||||||||
Featured Script |
|||||||||||||||||
Identify Obsolete IndexesDrop indexes no longer being used. More » |
|||||||||||||||||
|
|
|||||||||||||||||
Database Pros Who Need Your Help! |
|||||||||||||||||
|
Here's a few of the new posts today on the forums. To see more, visit the forums. SQL Server 2005 : AdministeringUPDATE failed because the following SET options have incorrect settings: - I Created a UniqueIndex on a Test Table with Some condition as below and user were getting error msg as... sql server 2005 issues with nightly jobs - Hi, I am just starting to learn about these things, and i was hoping someone could give me some pointers to... SQL Agent troubles - The system cannot find the file specified. - Hi All I have a SQL 2005 system. The SQL Service is started with no issues - However, I can't seem to start... SQLServerAgent could not be started (reason: Error creating a new session). - I can't start the SQL Server Agent Service on a new installation. During the install I chose not to start the... shrink fails with error - File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty - This is an fyi (no response necessary). I got this error and researched it and found no good answer for how... SQL Server 2005 : BackupsDifferential backup question - I do FULL backups every two weeks and daily differentials on a number of SQL instances. The backup command I use... Transferring Backups - we need to copy a backup file between two very slow network Data centers (transfer rate: 350 kbps), Our full... SQL Server 2005 : DevelopmentWhen would be the order by faster ? - [code="sql"]use DOMAIN_event go CREATE table email_destination_bk ( msg_stub ut_stub not null , acct_id int not null , evt_stub ut_stub not null, evt_code nvarchar(20) ) create clustered index idx... SQL Server 2005 : SQL Server 2005 General DiscussionReducing Initial LDF/MDF size - Hello, I have a database (version 9.0.4060) that has an initial log size set to 322GB. I would like to change... SQL Server 2005 : SQL Server ExpressRemove Time from DateTime - What is the difference between these 2 lines in SQL Server 2005 Express? [code="sql"]DATEADD(d, 0, DATEDIFF(d, 0, @Today));[/code] and [code="sql"]DATEADD(d, DATEDIFF(d, 0, @Today),... SQL Server 2005 : SQL Server 2005 Integration ServicesHelp Passing a variable using OLE DB source - I'm having trouble passing information from a variable to a query. I currently have 3 variable for this: _QueryVariable, _ExcludeInfo,... Setting Package protection level - Hi All, I have setup the package configurations(for both source database and the SSIS database) from SQL Server table, as they... IS IT POSSIBLE ?--NOT to CREATE Flat File If it is empty? - I have a SSIS packages that does a SQL command on OLEDB Source and finds the records and outputs to... SQL Server 2005 : T-SQL (SS2K5)Query to find items purchased from multiple vendors - Hi All, I want to find item that purchased from more than one supplier, so i am running following query :- SELECT... SQL Server 2005 : SQL Server Newbiesscheduling job - Hi friends, We need to monitor a user session for locks to troubleshoot a problem in the program. When the... SQL Server 7,2000 : AdministrationSQL SERVER services get stopped - Hi, Everyday we notice SQL SERVER services get stopped and we have to manually then start both services and agent.We... Memory Error - Failed to reserve contiguous memory - I had the following warning in the sql log file 'WARNING: Failed to reserve contiguous memory of Size= 65536'. (SQL... SQL Server 2008 : SQL Server 2008 - GeneralInsert Exec Performance issue ?? - Hi, I have a procedure which has a code something like My SP is dynamic something like WHILE(@i <= @max) BEGIN SELECT... Fixed column names for Dynamic PIVOT Result - Hello everybody, I'm using two dynamic Pivot queries(which are basically the same except for the filter on date) to build a... Populating fixed destination table with different source tables - I have a stored procedure which basically takes a table, performs some data manipulation then inserts the data into a... Query for extended properties - Hi, Can anyone help me with a query which will select all the stored procedures with their Extended Properties. Thanks a lot. sql server stored procedure logic problem - HI friends i have small doubt in sql server.plese tell me how to solve this issuse i have 2 tables... IN Vs INNER JOIN - Which one is more performance oriented query? 1. SELECT 1 FROM table1 WHERE Id IN (SELECT Id FROM table2) 2. SELECT 1... Sql Server 2008 Stops when dbo.Decrypt('') used. - Hi there, We use Microsoft [b]SQL Server Enterprise Edition (64-bit) Version- 10.50.1600.1[/b] in our company. Whenever I use dbo.Decrypt() with blank... Result of dynamic sql with parameteres into a variable - Hello, i have problem with dynamic sql in cursor and i want to set to variable first I had problem, when... Asking If I can Create a table for prices - Hi, I wanna ask if I can put just one table for both prices an cities. In the beginning I... Droping or moving old tables - How to validate the last updated date for each of the tables. I want you to check if the tables are... SQL Instalation Issue - Hi All, I have tried to install SQL 2008 Ent evalution edition on Win 2008 R2 Datacenter edn. and SQL Engine... SQL server version Requirements For installing Dotnetnuke - What are the server requirements in order to install dotnetnuke? Can any one kindly help me to evaluate the technical requirement... Insert help - I am trying to insert records into the database as follows insert into lookuptable select * from lookuptable1 which produces Msg 2627, Level 14, State... Granting Explicit View Definition Permissions on Stored Procedure to dbo - The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they... Read a backup file to find the version - Is it possible to read a backup file directly and determine the version of the server that created it? I've... Custom print? - Has anyone built or seen a custom Print function for SQL? I'm running into the 4000 character limitation and before... Synonyms and performance - How does the use of synonyms effect performance? More specifically, are synonyms replaced with fully qualified names in execution plans? It... SQL migration - Whats the best way to copy logins, users, logins with password along with exact status of enable/disable from source 2008... LiteSpeed impact on msdb database? - We have a customer who wants to use LiteSpeed for backup process on few SQL Server 2008 EE machines. We... create procedure which contains special caracters from .sql file with sql powershell - Hello, I have a .sql file which is actualy the body of a stored procedure. The procedure containts characters like [b]'é,... Alter User Defined Data Type - I'm trying to alter a column in a table that corresponds to UDDT, this column has a primary key and... Virtualization - yet again ... - I spent some time searching this forum and the interwebs and haven't found a lot of definitive insight/agreement on virtualization... Formatting issue in SQL - Hello all, I have seen some strange things in my SQL Server 2008 enterprise edition. whenever I do sp_helptext for... Query to get rows if value contains result of another query - hi, I have searching for a query that returns a row if column value contains result of another query. Its like... query performance - I have a table with 6 million rows. The table has around 200 columns. The data can be sliced and... Just accepted a Sr. DBA job. Time for a gut check? - Hi SQLSC! Buckle up, I apologize in advance for the long post. In many ways, typing it out is as much... Memory leak on witness instance - We have a high availability sql server environment with synchronous mirroring and witness which has been working mostly fine for... Getting minimum of top n rows without using subquery - Hi, We use SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date]) query for selecting... Virtualization reviews - i have to Assess server landscape –Server reviews –Virtualization reviews –OS/patch level reviews can any one help me out regarding Virtualization reviews. what does it... Archiving - MSSQL2008 with 1 TB Harddisk space Databases: DB1 - data for 2012. DB1_Arch - data for 2011. *** Inherited this server; looks like he idea... Failure to calculate super-latch promotion threshold - I saw this error message in the error log of one of my SQL Server 2008 R2 servers today (with... Msg 911, Level 16, State 1, Line 1 Database 'databasename' does not exist. Make sure that the name is entered correctly. - Dear All, Would anyone be able to let me know how to resolve the following error message please? Msg 911, Level... Error 7391; Unable to Begin a Distributed Transaction - Hey All, I am trying to get a list of error messages on a server using the sp_readlogerror stored procedure. I... SQL 2008 DDL Auditing - A Full Self Installing/Updating Solution For Whole Server - [size="4"][b]Introduction[/b][/size] We will see a totally free DDL Auditing solution (and source code) for the whole server regardless of SQL Server... SQL Server 2008 : T-SQL (SS2K8)Cursor replacement help - Morning, Thank you for your help in advance, I have built a cursor to run through list and with the... ORDER BY = Bubble Sort ? Quick Sort ? Insertion Sort ? - Hi All, When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer? 1)... sql server stored procedure logic problem - HI friends i have small doubt in sql server.plese tell me how to solve this issuse i have 2 tables... How to Convert Semi colon Separated Values into Column - I am working on a integration project where I am receiving three string parameters ItemCode ItemName Amount and values will be... remove duplicates IF matches - So I have the following statement: SELECT ca.Caseid,[weeknum],[monthName], Response1_Num , Response2_Num, Response3_Num, Response4_Num, comments,[userlogin], [supervisor], [customer id], comm_ID FROM HMD hm join... Performing strange conditional COUNT - Hey guys, Can someone show me how to go about solving this problem? I thought it would be pretty straightforward... Delete large data from live table - I have to delete data from live table( Size of DB:terabytes) (system is becoming slow). without affecting the end users... Add login, user, roles in mult db's dynamically - I am attempting to create T-SQL that I can re-use on different db’s, users, domains, roles, etc… The trouble I’m having... Grouping records by time interval - Hi everyone, I have been searching for a few days now to try to solve a problem, but without success. What... SQL Server 2008 : SQL Server NewbiesImport records in MS Access table to native SQL Server 2008 table - Hi SQL server experts I am trying to use the SQL Server Import and Export Wizard to perform this simple task.... Query hangs on table variable - I have a query that consistently runs fast and returns the expected output, but when I try to insert the... Stuck on new fault with my update - Hi Professionals I am running the following query as advised previously which updates the source table based on a column... View results into string - I have a table where I need to take any number of financial distributions and stack the accounts into a... Move Database From One Server to Other - Hi All, What I am trying to do is that I am trying to copy database from Other Sever. I am... SQL Server 2008 : SQL Server 2008 High Availabilitysql server mirroring synchronization not happening - Hi all, I have mirroring setup, Seems to everything fine.But synchronization not happening.i don't understand what was the problem. Please any one... Help moving SQL instance folder structure from one SAN to another (cluster environment) - At the office, we have a 2-nodes cluster configuration running a SQL Server instance on each node (active/active). We need... how read from mirror server ? - Hi all, I have a production server with 3000 online users, I set up SQL server database mirroring, Now I want to... SQL Installation on Windows 2008 - Hello, I have task of SQL 2008 installation (2 installation) on windows 2008 cluster server ( 2 Node). There are 2 approaches comes... Transactionla Replication - Very first step while configuring the Ditribution.. error comes on last step :w00t: Property LoginSecure cannot be changed or read after... The Recycle Bin is corrupted on :/drive do you want to empty recyclebin on the drive - We have the windows server 2008 R2 enterprise in the cluster and i each time i login i am facing... SQL Server 2008 : SQL Server 2008 Administration#Files and FILEGROUPS - Hi, In an 8 CORE machine with 3 RAID10 (data files, log files and tempdb) what's the appropriate number of files... Data Archival/Purging - through table partition switch IN/OUT - Hi All, I have a daily partitioned table 'TBL' and I hold data for 30 Day's. I archive data to TBLHistory... Duties of a DBA - Could someone please brief me about the daily duties of a production support SQL Server DBA? Database getting into recovery mode - Database getting into recovery mode is every morning. Question about DBA authority versus responsibility - Hi, I'm looking for some advice for how I can assert myself in this situation without alienating my colleagues or causing... Mirror failover - Hi All, I have an issue with mirror failover 1. I configured mirroring on 3 databases. if in 3databses one got failover... rename SQL server - After we changed computer name, and I renamed SQL server name too following ms articles. by using sp_dropserver, sp_addserver. But I... Does the adrenaline rush ever subside when rebooting a DB server. - I work in a small shop as the accidental DBA and for the first time rebooted our production DB server... Starting up in Single user mode - I am testing a disaster recovery process but am unable to restart any 2008 systems in single user mode so... Programming : GeneralWeb dev suggestions - In some circles this could be as touchy a topic as asking which religion to follow. I'm a full time... SQLServerCentral.com : Anything that is NOT about SQL!The Beer Cooler Thread - On popular request - at least one! - a dedicated topic about beer. I shall start with my favorite beer: Duvel. It is... Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene... Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in... Reporting Services : Reporting ServicesReport doesn't run from Reporting Server - Dear All, I'm trying to run a Report on the Reporting Server but for some reason the reports executes but hangs.... MultiValue Parameter not working - Hello all, I have a table that displays different statistics about "Properties". The table only has 1 row, this single row... Reporting Services : Reporting Services 2005 Developmentdynamic sql with IN operator error - In my dataset I have this script. Report parameter @acct is set as multi-value. When I select a single value... Line hight for barcode - font 3 of 9 Barcode - I am developing a GRN label print report and have installed font 3 of 9 Barcode. I want the barcode... Database Design : Design Ideas and QuestionsDatabase describing databases and references to tables - Hello, I asked a question at dba.stackexchange.com [url=http://dba.stackexchange.com/questions/35855/database-describing-databases-and-references-to-tables]here[/url], that's not getting a lot of interest. Now that it has fallen... Data Warehousing : Integration ServicesProblems with Excel source extraction - I have an excel file with sheet containing 210000 records..I am trying to extract this file into a SQL table. In... Discover the column types of a DB2 DB without Data Dictionary and Documentation - Hi all, I just want to discuss a method with you to discover the column data types of a table in... Documentation in SSIS - I am just starting learn and use SSIS, and was wondering how you document changes? In a stored proc I... IS 2008 Triggering an Excel Macro via Script Task - The Binary Code for the script is not found - I have a Macro which works in Excel. It basically just formats headers and creates a validation list so the... Integration services Configuration - Just wanted to find some information on SSIS configuration. Consider this scenario: I have a datasource on server A in... |
|||||||||||||||||
|
|
|||||||||||||||||
|
To be removed from this list, please click here If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Feel free to forward this to any colleagues that you think might be interested. If you have received this email from a colleague, you can register to receive it here. This transmission is ©2013 Red Gate Software Ltd. All rights reserved. Contact: webmaster@sqlservercentral.com |
|||||||||||||||||
Deleting large number of rows from a table in a VLDB
Brent Ozar's Six Scary SQL Surprises
Using the SQL Server MERGE Statement to Process Type 2 Slowly Changing Dimensions
Help Guide the Future of SQL Compare
The Saddest Thing I Ever Heard a Manager Say
Dust Off That Resume
