BNF Basics (SQLServerCentral.com 8/12/2009)

 

 

A community of more than 1,138,000 database professionals and growing In this issue Featured Articles ·         BNF Basics ·         Raw Materials - Rights Restored ·         Q&A: The skinny on Reporting Services 2008 ·         Where is my app.config for SSIS? ·         Networking for Your Career (From the SQLServerCentral Blogs) Editorial ·         Slicing and Dicing Featured Script ·         Automatic Generation of Insert, Update and Delete stored procedures    advertisement   SQL Compare and SQL Data Compare - Together at last With SQL Compare 8.1 and SQL Data Compare 8 you can now compare and synchronize both your schemas and data, moving seamlessly from one tool to the other. Download them both now.   Essential tools for SQL Professionals If you work with SQL Server, then you should have the SQL Toolbelt; "a must for anyone considering serious development and administration on SQL Server." Mustafa Bashiti. Download a free trial of the SQL Toolbelt Featured Articles BNF Basics Tom Fischer from SQLServerCentral.com Learn about the basics of Backus–Naur Form. If you've never heard of this, Tom Fischer explores this way of structuring technical information. More » Raw Materials - Rights Restored Larry Gonick from SQLServerCentral.com Don't forget to watch your language! More » Q&A: The skinny on Reporting Services 2008 Additional Articles from SearchSQLServer Get the inside scoop on SSRS 2008 with Michael Lisin and Jim Joseph, co-authors of the new book, "Microsoft SQL Server 2008 Reporting Services Unleashed." More » Where is my app.config for SSIS? Additional Articles from sqlis.com Sometimes when working with SSIS you need to add or change settings in the .NET application configuration file, which can be a bit confusing when you are building a SSIS package not an application. First of all lets review a couple of examples where you may need to do this. More » From the SQLServerCentral Blogs - Networking for Your Career Steve Jones from SQLServerCentral.com It's hard to actually put a specific value on networking, and I've been skeptical of it in the past. However... More » Editorial - Slicing and Dicing I like music, often listening on headphones while I'm working. We often have an iPod in the car when traveling and take turns picking songs, which prevents arguments and lets us all get a feel for what the rest of the family listens to. I've even heard some interesting songs that way that I might not have heard otherwise. My son also has a Zune that he uses at night and while cutting grass, and while he likes picking his songs, he's also used the FM radio quite a bit to listen to a local station he likes, getting a wider variety of music than he otherwise might hear.. Recently Apple announced that they were looking to re-invent the album with the release of liner notes and booklets along with music. It looks like a way to give more value to consumers, and perhaps convince them to buy whole albums instead of particular songs. I think it's interesting, and I am one of the few that misses browsing through albums and seeing the cover art that used to go with them. CD liners notes aren't the same, and digital downloads seem far removed. For those of you that don't remember what an album is, I've included a photo ;) I remember awhile back the AC/DC released a new album only at Wal-Mart, specifically because they thought that the album deserved to be kept alive, and they didn't like singles being sold. They felt it reduced the value of the experience. I can see that point of view, and some record company executives think that the iTunes model of singles is hurting the business. I think this isn't just a music issue, but more and more a digital information issue. There are lots of companies and business models that depend on bundling things together in order to grow value. In fact, a lot of what we do as database people is find ways to put information together and get more value. There might be some minimum effort (and cost) required to do so, and if we had to slice and dice each section of information up and sell it separately, many pieces of data might not be worth selling or even gathering. Think of television. If the Golf channel had to be purchased separately, it might not exist. But by bundling the Food channel, the Golf channel and a few more in with other selections, they can all get a slice of the revenue and find ways to survive. The same thing might happen with data marts in your company. The inventory mart might not be able to justify its existence if it had to be built separately at its own cost, but if it's a spin-off of a larger data warehouse, the incremental cost can exist. In fact, many IT departments might have problems justifying their costs if they had to exist as a separate entity. We might see more outsourced IT companies if that was the case. By building them into the company and absorbing the cost, the company gets some benefits and synergies that they might not get from an outsourced environment. I am not sure if iTunes is bad for the music business, or if albums are good or bad, but I do like that the model exists and companies are trying to evolve it to work better. I hope that other digital sources of information remember that they don't just exist to server the market today, but they need to evolve and find ways to server it better in the future. Steve Jones from SQLServerCentral.com » Join the debate, and respond to today's editorial on the forums The Voice of the DBA Podcasts The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. You can also follow Steve Jones on Twitter: Overall RSS Feed: or now on iTunes! ·         Windows Media Podcast - 36.6MB WMV ·         iPod Video Podcast - 27.4MB MP4 ·         MP3 Audio Podcast - 5.6MB 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: Your SQL Backup folder is F:\SQLToBeBackedUpToTape\AdventureWorks on a SQL Server 2008 instance and backup compression is set to '1', not the default '0'. You run:   BACKUP DATABASE [AdventureWorks]   TO DISK = N'C:\SQLTOBEBACKEDUPTOTAPE\AdventureWorks\AdventureWorks_compressed.bak'   WITH NOFORMAT, INIT,   NAME = N'AdventureWorks-Full Database Backup',   SKIP, NOREWIND, NOUNLOAD, STATS = 10 The statement to restore this databse is: By Jamie Longstreet Think you know the answer? Click here, and find out if you are right. This question is worth 1 point in this category: Backup / Restore. 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. Beginning Spatial with SQL Server 2008 covers everything you need to know to begin using these new spatial datatypes, and explains how to apply them in practical situations involving the spatial relationships of people, places, and things on the earth. Grab your copy today from Amazon! Yesterday's Question of the Day In SQL Server 2005, what tool should you use in rebuilding the master database? By optradba Answer: Setup.exe Explanation: Rebuild.exe is no longer available in SQL Server 2005. To rebuild master database, you may use setup.exe. Ref: How to: Rebuild the Master Database for SQL Server 2005 - http://msdn.microsoft.com/en-us/library/ms143269.aspx » Discuss this question and answer on the forums If you're a SQL Server database administrator, this book will make your life easier. Windows PowerShell is an administrative scripting tool that allows you to automate many tasks you're probably currently doing by hand. This nuts-and-bolts guide shows you how to create Windows PowerShell scripts to administer almost every aspect of SQL Server. Grab your copy today from Amazon! Featured Script Automatic Generation of Insert, Update and Delete stored procedures Samardeep Khera from SQLServerCentral.com This script automatically generates stored procedures for insert, update and delete operations on the given table 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 : Administering bulk logged insertion - Hi Experts, 1)What happens when i insert just ten records in bulk log recovery model will it get logged? 2)If i insert... DSN connection - We have an application program from DSN uring SQL driver to connect SQL 2000 originally. Now we would like to connect... Checking OS folder permissions - I have a list of approx 20 OS file folders. I want to check that the "Authenticated Users" OS Group... mirroring database - Dear All, can i do mirroing without have domain. i have 3 database server in the same workgroup can i do the... Authentication / logins - Having a problem with users connecting... We have windows groups set up as logins such as Domain\U1B-123 Domain\U1B-456 Domain\U1B-789 They are users in various databases... script - can any one send me the script of finding the total ,used and available free space of datafiles of all... Rebuild Index Task fails - Hi Folks, The scheduled reindexing job has failed with the below error: Source: Rebuild Index Executing query "USE [Msql2d] ".: 6% complete End... Shrinking file grows again - I have partition on one of the table in which data is distributed to different filegroups. for example. I believe... Deny or revoke dropping any object for particular user - Hi all, I want deny or revoke users from dropping object in SQL Server 2008/2005 with create and alter truncate and delete... Error ..while restarting Sql browser - Hi i couldnt able to restart sql browser i am getting following error ----------------------------------------------------------------------------- The SQLBrowser service was unable to log on as... SSMS Issue - Hi ... I get the following error when trying to launch SQL Server Managment Studio 2008: "An error has occurred while... SSMS Oddity (ies) - Hi, Just posting this to see if it also happens to you. In SSMS on which I have with SP3 - go into... Hardware based database partitioning for Optimal Performance and Reliability??? - I have been assigned a task to partition one database into 3 to 5 databases. Each database will be doing... how to execute xp_cmdshell command with non-sysadmin account - how to execute xp_cmdshell command with non-sysadmin account? kindly provide the steps for the same SP won't execute when called from a job - I have a SP which works great when run independently from Mgmt Studio. The same SP, when called from a... SQL 2000 account lockout policy - Hi all, I was wondering how many unsuccessful attempts does it take before a user gets locked out of the database?... Database Mail sending fail after restoring MSDB from another server. - Hi all, Due to some unforeseen circumstances, i had to restore one of my test server's MSDB database. I'd used a backup... Update Times for 2 tables On same LUN - Hi All I am using SQL 2005 (64 Bit) edition on a 6GB server ( 5 GB allocated to SQL ), All... Import data from excel 2007 to sql server 2005 - hello guys i am trying to import data from excel 2007 to sql server 2005 as i have selected the provider... Can't install SQL server 2005 Standard Edition on Windows XP professional SP3 - I tried to install SQL server 2005 Standard Edition on Windows XP professional SP3 while installin MSXML6 it gives error and... Multiple instances on a cluster? - I'm looking at installing a 2nd instance of sql 2005 on a cluster. Since I haven't done this before, I... Can I determine SQL install date using a TSQL script? - Is it possible to determine the date SQL was installed using a TSQL script command? Database Mirroring - Hi, When I set database mirroring I recieve following error.. Database mirroring is disabled by default. Database mirroring is currently provided... dbcc inputbuffer question - I am trying to find out the sql statement run by a process. To do this I am using the... SQL Server 2005 : Backups Log Shipping - I was attempting to configure log shipping from IDQ-SP.DB to IDQ-UK.DB, and, even though I incurred no errors and the... Backup BizTalk Server Databases - In my environment there is two biztalk servers connected to one sql server. For each biztalk server there is different... Back up is not getting - Hi All, I am unable to take back up off the data base. when i run querry it shows 3... Hyperbac - apologies for asking this on a Redgate sponsored site but, ahem, would any current users of Hyperbac out there care... SQL Server 2005 : Business Intelligence how to assign NULL as DEFAULT on rdl - hi, i have a report which pull data based on a store procedure. the procedure has two parameters in which both... Conversion Of Business Objects Universe to SSRS Report Model - Is anybody aware of any third partly tool for converting a Business Objects Universe to a SSRS Report Mode? SSAS Error - All- I get the following error when trying to deploy my SSAS cubes- "Either the user does not have permission to alter... SQL Server 2005 : Data Corruption error 'An inconsistency was detected during an internal operation in database ' - I get following massage repeatedly in duration of 2 or 3 days. Database name is 'DBNAME'. 'An inconsistency was detected during an... Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression. - I want to read an excel file. In that excel file, ID column is generated automatically in stored procedure. I... SQL Server 2005 : Development Transaction Advice - I have a database that has a product table. This database also has a Quote table. For the Quote table the... Change Management \ Version Control Plug-In for SQL Server? - Hello @ all, we want to introduce a [b]Change Management \ Version Control Tool [/b]for our sql server development to see WHO has... How to be notified when a insert/update happens? - Hi! I have a table where the users are always adding information. I'll like to show in my application a message... SQL Server 2005 : Working with Oracle Multiple databases in ORACLE - How? - The scientific application for which I'm the developer uses a SQL Server database to hold data and results. The data and... SQL Server 2005 : SQL Server 2005 General Discussion Invoked Schedule Id of the Job : Urgent! - Hello: When a job step is being executed from SQL Server Agent, I need to determine the "schedule_id" that invoked the... Database Diagrams questions - We've recently purchased a software package, which comes with its own SQL Server 2005 database. I'm curious to know how... Server login assigned to "dbo" user within database - I have 3rd party software which has a server login assigned to "dbo" user within several of the databases. The... Query or create linked server to FoxPro Free Tables? - So I want to make a report over a FoxPro database table FPTable.dbf that resides on ServerB. My SQL 2005... Query Question - Hi, Could someone cast their eye over this query and tell me if what i think it is going to do... moving model - Hi all, Hope someone here can help me out.. I'm using SQL Server 2005 SP2 (I 99% sure its SP2) on... SQL Server 2005 : SQL Server 2005 Security Drop failed for user - How do I find out what it is connected to so I can drop it? Any help would be great!... SQL Server 2005 : SQL Server 2005 Strategies SQL 2005 & SharePoint 2007 Setup - We have just install SharePoint 2007 but I believe the person who install it could have done a better job.... SQL Server 2005 : SS2K5 Replication Log reader error no. 20011 , 18773, 18805, 22037 - Hi, We got Publisher and Subscrber on W2003 R2 with SQL Server 2005 Ent. SP3 CU#1 This is simple replication were Distributor... SQL Server 2005 : SQL Server 2005 Performance Tuning Defrag - Isn't there an option to defrag a table in the GUI (Management Studio)? SOS_SCHEDULER_YIELD - Hello All, I find myself stumped. I've got an automated batch that runs at a users request that seems to think... Job to delete only PDF files older than 3 days from a fodler - Dear All, I want to delete only PDF files, which are older than 3 days from a particular folder. Is it possible... Enterprise Edition SQL Server 2005 Performance issue on Multi Processor Server - We are facing SQL Server 2005 Enterprise Edition and SP 2 installed in Windows Server 2003 Enterprise Edition with SP... SQL Monitoring Tool needed ASAP - My SQL server process is suddenly consuming up to (and sometimes more than) 90% of the CPU. I can't seem... SQL Server 2005 : SQL Server 2005 Integration Services ForEach Loop substitute for speed purposes? - Hello, I have a question regarding a current package I have running which performs well, but takes a long time to... Location of SSIS package execution? - I wrote a sql 2008 SSIS pakcage the runs fine on our server. I was told that and end user... Issue with Lookups, Fast Loads and DTC transactions - Hello, I am having a strang problem while performing some tests on an existing package with a high load. I have... SSIS --> XML output - I have done some 'Googleing' into this area about "SELECT FOR XML" but dont fully understand it. [b]What I Need[/b] I have... SSIS package - Task failure - Hi, I configured a SQL job for SSIS package consists of different SQL tasks and other import tasks. Some SQL tasks with... Is the Script Component hosed? - My SSIS project was humming along just fine until I introduced a script component into the Data Flow. I have... configure logic for bad set of data's - Hello I have created a package which will move a data from sql1 table to sql2 table. And I have... Export SQL Server 2005 data into XML a file - Hello Does any one know how I can export data from sql server 2005 into a XML format which can then... SSIS, Excel, and DT_NTEXT - OK, I have fought this problem for the last three days. I now bow to the extensive and superior knoweledge... SQL Server 2005 : T-SQL (SS2K5) Different results using CONTAINS and LIKE - Hi, [code="sql"] create table Testtable ( ID INT NOT NULL IDENTITY, ColumnName varchar(max), CONSTRAINT [PK_ColumnName_ID_Temp] PRIMARY KEY CLUSTERED (ID ASC) ) -- Create a full-text catalog and set... Carrying Value Across Rows - Hello, I'm looking for a way to carry a continuous value across rows to calculate a weighted average. [code] create table t1 ( ... Issue with assigning candidates to a review list - I'm working on a process, and I can't figure out how to implement one of the requirements. The process is: 1. A... XQuery syntax to flatten Xml - I am having problems trying to flatten the XML below. I cannot seem to figure how to get the LON... checksums and unicode data - Can someone explain why select checksum(convert(nvarchar(3),'674')); select checksum(convert(nvarchar(3),'645')); yield the same value? While select checksum('674') select checksum('645') do not. What is is about unicode data that caused... Executing stored procedures on Linked Servers - Hi, I currently have two DB's (DB1 and DB2) setup on an instance of SQL server. DB2 is essentially a reporting... Distinct going slower than * - ......New problem query posted on page 4..... Link -> http://www.sqlservercentral.com/Forums/FindPost669302.aspx Thanks in advance for any help you can give. Could not create an acceptable cursor - Hi, First time poster here, I'm having problems with an update statement at the moment, with an error I've not seen... SQL Server 2005 : SQL Server Newbies Maintenance Plans - How to Save Copies or Back The Plan Up - Hi Folks I have spent quite a bit of time creating some Maintenance Plans for SQL Server 2005 and I would... SQL Server 7,2000 : Administration SMTP mail issue - I done following steps for configure via SMTP mail notification 1. xpsmtp80.dll - download and placed in Bin folder this path C:\Program... Error creating alert from T-SQL - When I run the following query which came right out of BOL, I get this error -- "Server: Msg 14262, Level... SQL Server 7,2000 : Backups how to take full backup of database on different files using jobs - Hi all, I want to take full backup of a database. To do this I am scheduling a job so that i... SQL Server 7,2000 : General xp_cmdshell PSCP - "The server''s host key is not cached in the registry" - Hi, In the same machine, I can run the following PSCP command to fetch a file from remote server from... SQL Server 7,2000 : Working with Oracle Getting Oracle backup into SQL Server - Hi all, I've been handed a backup of an Oracle DB. We need to get that data into a SQL Server... SQL Server 7,2000 : Performance Tuning SQL profiler does not have 'import performance data' option? - Hi all, I search on the forum but was unsuccessful in locating an answer. We're running SQL server 2000 and... SQL Server 7,2000 : Replication Replication Latency - We are using SQL Server/2000 Enterprise Edition and Windows/2000 Advanced Server replicating between two publishers and one subscriber on 4-CPU... SQL Server 7,2000 : T-SQL how to tuning procedures in database tuning advisor - hi.. In mydatabase I have used storedprocedures to get data from tables.How can I improve performance by using Tuning Advisor. Using a subquery in select list with a GROUP BY clause? - Hi folks, I have a slight problem with a query I'm working on for report. The query is rather complex now and... Self Join - I have a table of Phone Numbers. Employees may have any number of phones in this table Type 1 = Home,... NOT EXISTS ? - Hi, Can anyone just clarify something for me. I'm gradually teaching myself SQL (with help from everyone at SSC:-) and have... 'Add' to a character. - What is an elegant way to Select +1 to a nchar that will always have alpha characters? while persevering the... STATS_DATE using qualified parameters - I'm writing a SP to keep track of and update  statistics on databases in our SQL Server 2000 environment. The... SQL Server 7,2000 : SQL Server Agent How can I disable a Job via TSQL - I want to do an upgrade on a busy SQL 2000 server that has lots of jobs kicking off at... SQL Server 2008 : SQL Server 2008 - General kill connection - Hello How can I kill all connection with T-sql? If I delete a DB with the managementstudio, then I can choose... Network binding order - Hi.. I Am getting the following waring message while installing the sql server 2008 cluster x64 in windows x64 2008 server... SQL Server 2008 : SQL Server Newbies Auto Close Off on restore - Hi, First post here, hope you guys can help. We have a tool that restores a .bak file to an SQL... SQL Server 2008 : Security (SS2K8) Prevent grants to public database role - Hi All, I'm trying to prevent anyone from being able to apply grants to just the public database role. There doesn't... SQL Server 2008 : SQL Server 2008 High Availability SQL2008 Active/Active coming here. - Soon I will be installing SQL 2008 Active/Active cluster. I guess I don't understand the difference in installing Active/Passive vs... how to install 32-bit providers on existing 64-bit cluster - Hello, Here is the issue I have: - 2-node failover 64-bit cluster running SQL Server 2008 Standard SP1 - I've developed a new... SQL Server 2008 : SQL Server 2008 Administration SQL 2008 RTM but SSMS Pre_Release? - Hello All, I have SQL 2008 installed on a couple of servers and on my PC the Client Tools (SSMS).... SQLServerCentral.com : Anything that is NOT about SQL! Managing Useful Scripts and Articles - I frequently discover an article, script, snippet of code or bit of information that I think is useful (many of... WORD UP! - Man, I'm starting to hate Office 2007. My operating system is XP Pro sp2. Everytime I reboot and then go... 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... SQLServerCentral.com : SQLServerCentral.com Website Issues Spel chek? - From a post in another category: (On another topic, I was unable to spell check this post, as every word (except... Reporting Services : Reporting Services Export data from SQL server to Excel in multiple sheets - I have a requirement like We need to export data to excel in different sheets in same excel.In breif i... Reporting Services : Report Development how can i perform ordered numbering of hierarchical data in SSRS 2005 Reporting ???? - well i ve hierarchical data of some categories. and i wanted to render it in the following format in SSRS... Passing Parameters Between ReportViewers Controls - I'm having difficulty in Passing parameters in-between two ReportViewer Control on separate web pages. In simple words, I've created two... report header in SSRs - i am converting crystal report to SSRS crystal report has a report header and page header, how do i incorporate report... Data Warehousing : Integration Services Synchronizing Lookup and record inserts - New to SSIS and have what I'm sure is a "SSIS 101" question. I've searched to no avail, but may... Article Discussions by Author : Discuss content posted by Arup Chakraborty SET NOCOUNT OFF - sorry, it is better to put SET NOCOUNT ON, isn't it?