Database design - best practices

SQL Server Tools General

Index ‹ SQL Server

- Previous
  - 1
    - looping to drop temp tables Dear Moderator, Thank-you very much for the code snippet. This method worked beautifully. Just one question...why is it necessary to use exec('drop table '+@table) Why doesn't it recognize the plain old drop table @table immediately following the "is not null" statement as written in my original attempt Tag: Database design - best practices
  - 2
    - Column count max for Merge replication still 246? Is the maximum number columns per table still 246 for a Merge replication in SQL Server 2005 Tag: Database design - best practices
  - 3
    - rsExecutionNotFound ! I have been frequetly visiting your blogs with a solution for rsExecutionNotFound. I used johngaallard's script to extend the accesstimeout(to 6000 ) eventhough that was not the base of the problem. My Reports run in less than 15 sec (max). I have enabled caching for about 12 hours ( there are nightly updates) using [Cache a temporary copy of the report. Expire copy of report after a number of minutes option]) The reports are rendered using a ReportViewerControl in an ASP.Net2.0 web application.(The report is rendered as PDF and then shown in another IFrame using Adobe plugin).The ASP application session timeout is 100mts. The problem i am facing is that after sometime, the rsExecutionNotfound starts occuring for some report with a specific set of parameters. No amount of rerunning the report will stop. The same report with different parameters renders fine. The only way to stop this is to set the cache option 'Do not cache temporary copies of this report'. If i set ''Do not cache temporary copies of this report'' there are no errors. I am doing caching mainly to bring down report rendering from 15 sec to 2-3 secs. I am using PDF format . I am now thinking of working around by trying to catch the Exception that occurs and trying to re-render the report. If this is the only way, how can i do it. I had fought fiercely to use SSRS and now I am in a fix Any help is appreciated. Thanks, Saj Tag: Database design - best practices
  - 4
    - Programmatically create a Report History Snapshot Hi, To programmatically invoke a subscription we can use - exec ReportServer..AddEvent 'TimedSubscription', '575F96C6-A1BD-49FD-9C2F-934FC9658780' How can we programmatically cause a Manual Report History Snapshot to be created. I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters. TIA, Sam. Tag: Database design - best practices
  - 5
    - User of the Authorized Windows Group Failed to Login Thanks for the answer. When I run the query, I can see both windows group, domain group and individual user. What I have done to solve the problem, I add the Domain Group individually to the SQL Server. Although it's a bit hassle, but the domain user of the domain group now can login to the sql server. I am still not sure why it cannot work if I add the domain group to the windows group, and add the windows group to the SQL Server. Tag: Database design - best practices
  - 6
    - SQL 2000 Database upgrade to 2005 Also take a close look at the compatibility information from SQL Server 2005 books online. Tag: Database design - best practices
  - 7
    - Proactive caching notification error I have defined a partition to use proactive caching with a polling query notification. The polling query simply returns the maximum value of a timestamp column from the measure group transaction table. The query parses and runs from the create polling query dialogue box. Have set this to run every 30 seconds. When this is deployed runs the trace on Analysis Services returns this error : OLE DB error: OLE DB or ODBC error: Invalid object name 'fcsUltForecasts'.; 42S02. http://support.microsoft.com/kb/225256 this article suggests can be caused by non dbo owned tables. However this table is owned by dbo (and is the table used when the parition is processed normally) Tag: Database design - best practices
  - 8
    - Connecting to SQL Express after Unattended Install Good afternoon, I have written a .NET app that kicks off an unattended install of SQL Server Express Advanced. After the install, the same app tries to connect to SQL Express adn run a script that creates a database plus a few other things. However, the problem I am having is that my app cannot connect. I get the error: "An error has occurrred while establishing a connection to the server. This failure may be casued by the fact that under the default settings SQL Server does not allow remote connections." However, the unattended install goes great. No problems there. I know this because I can Open SSMS, run queries, created DB's, etc. I also know that it is not the remote connections issue because the unattended install is set to allow remote connections (both names pipes and TCP/IP). So, why can't i connect I don't need to reboot after the install do I Please tell me i don't...and please tell me it is something simple I am overlooking. Thanks... Tag: Database design - best practices
  - 9
    - Accessing sub totals in a matrix Well, I meant how to access the contents of a subtotal in a running matrix for use in computation in another column/text box. How to access a particular sub total cell value, and say, subtract it from the cell value of another matrix column at corresponding level and display that in a third column. Tag: Database design - best practices
  - 10
    - SQL Server Installed, now what? is there a fee to pay You just need to login with your hotmail Id ! its free of cost ! you need to click the register online and provide the hotmail credentials and take the lab ! there is a pdf manual which you can use and work the exercises in the lab in the virtual server Regards Deepak Tag: Database design - best practices
  - 11
    - installtion program dies when installing Native Client next time if you have this kind of a issue please refer setup bootstrap Tag: Database design - best practices
  - 12
    - BCP on client machine Hi, What is the best practice to use BCP.exe to load text file from client machine to the SQLSERVER database installed in another server machine , without installing SQLSERVER client tools on the client machine . Any suggestions is appreciated. Thanks in advance. -Anand Tag: Database design - best practices
  - 13
    - foodmart database? Where can i find the foodmart sample database for SQL Analysis Services 2005. thanks in advance. Tag: Database design - best practices
  - 14
    - SQL Server 2005 JDBC Driver v1.1 and SSL Hi folks, I'm trying to figure out if the SQL Server 2005 JDBC driver supports SSL at all. I've downloaded the driver documentation, looked at more web pages than I care to count, and even went as far as to take a stab at guessing at different driver property names in the hopes I might discover some undocumented feature. Each time I try, I come up dry. The following exception shows up in my JBoss log: 2007-01-03 12:13:36,812 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The SQL Server login requires an SSL connection.) I know I have things working properly on the SQL Server side, as both osql and Query Analyzer work fine, and packet sniffing yields no plain text information. I've also tried swapping in the open source JTDS driver, and once again, sniffing shows that the data between the two machines in encrypted. Does anyone know if I'm just missing something completely obvious, or can someone state with absolute certainty that the driver does not support SSL Any ideas on how to find out if there are plans to support this feature and what the timeline might be Thanks in advance. Trev. Tag: Database design - best practices
  - 15
    - Loading excel file(.xls) into sqlserver (ASP.net web application)(ASP.net framework 1.0) i think i should explain my project... i am doing a web application... Scenario: User goes into the upload page. User selects file to upload through the html control "File field" user press upload button... the upload button should trigger the codes to load the selected .xls file to the database thanks Tag: Database design - best practices
  - 16
    - compact sqlce database :-( excuse me, SQLSerer Management Studio must be full, not only Express !!! This is very big download, so it may dissappoint you. BTW, best is to do small project itself and use code :-) Petr Tag: Database design - best practices
- Next
  - 1
    - How do can I do this? Hi, thanks to all but here is the solution: SELECT * FROM ( SELECT * FROM tbl ) AS t3 EXCEPT SELECT * FROM ( SELECT t1 .* FROM tbl AS t1 INNER JOIN tbl AS t2 on t1 . colA = t2 . colB AND t1 . colB = t2 . colA ) AS t4 better to know the new operators... :) Tag: Database design - best practices
  - 2
    - OLE DB Source to Flat File Destintation using Fixed Width Columns - determining source column width Hi, I am trying to create a program that transfers tables to flat files. At this point in time, I have suceeded in created one that creates delimited files. However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically. Is there a way to programatically determine the width of a column from the source table I can not seem to find any kind of function or member that stores this information or allows me to retrieve it. I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one. Tag: Database design - best practices
  - 3
    - date picker format In IE but I think you said you already checked this: IE: tools - options - general - [languages...] - There may be something else to check, if the above doesn't work let me know and I'll try and find out Tag: Database design - best practices
  - 4
    - Data disappearing during Run time Hi J_Dude, Sorry, could interpret a type in your last response... J_Dude2003 wrote: however if I close the application and re open it the data that I'd previously saved is nw there Is that "nw" supposed to be now or not Could you also clarify a couple points Are you using a User Instance (i.e. your database is embedded in your application project) or a connection to the main instance with an attached database Is this problem happening when you're debugging your application or when running a deployed copy Thanks, MIke Tag: Database design - best practices
  - 5
    - Old problem, but... Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. I didn't use SQL Express before, now I am trying to use it. I thinkk it is convinient if you just want to copy db files to user's machine. like this <add name="schsecurity" connectionString="Data Source=.\SQLExpress;Integrated Security=true;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" /> as long as user has sql express installed, he should be able to use the db once he copy the file aspnetdb.mdf over. but when I tried it, I got Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. it looks simple, SQL express can not verify user 'NT AUTHORITY\NETWORK SERVICE' so I connect SQL express with SQL server management studio, and add the user into login of the SQL express and set the user as sysadmin. But I still get the error. Any idea thanks! Tag: Database design - best practices
  - 6
    - Data Conversion Its a lot of fields 300... but doing something flexible as you described I dont know without using code... In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS... regards! Tag: Database design - best practices
  - 7
    - Results from Aggregate() are wrong The measure I am using is just a simple base measure, not a calculation. CurrentMember doesn't become a concern in this example unless the CurrentMember of [Product].[Product Indexing] is being thrown off for some internal reason. The Product Species hierarchy is unnatural with two attributes. The Product Indexing hierarchy is natural with two different attributes. As I understand this, the slicer (multiselect on the second level of Product Species) is somehow throwing off the Product Indexing hierarchy which is used on rows. Attributes in Product Species are not related to attributes in Product Indexing - unless you go all the way back to the dimension key attribute which is not present in either hierarchy. I've spent quite a lot of time attempting to reproduce this in AdventureWorks but am not able to since I can't find a meaningful pattern behind the problem in my own cubes. I will open a support case and see if I can get somebody to look behind the curtain. Thanks, David Tag: Database design - best practices
  - 8
    - Inconsistent Linked Server Query Results Hello, I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query; SELECT * FROM OPENQUERY ( Charlie_file , 'SELECT * FROM [Feb$]' ) Sometimes, on subsequent runs of the above query, I get the following message; Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file". There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span Thank you for your help! cdun2 Tag: Database design - best practices
  - 9
    - Can't get Dynamic Security working ... My filter expression in the AllowedSet is different for each level of the hierarchy. For DDO, it's: FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester")) For ROM, it's: FILTER([DynManager].[ROM].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("ROM Access List"), "JJester")) For RVP it's: FILTER([DynManager].[RVP].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("RVP Access List"), "JJester")) and on and on .... Do you have any idea why my test query would work in SQL Server Management Studio, but then I get the "more than one hierarchy" error when I try to apply the same FILTER expression in the AllowedSet Tag: Database design - best practices
  - 10
    - Sql Query to create nested XML Sitemap Hi Kent, I had gone through that post earlier and I think that it is different from what I want to achieve. The database contains all the details for a sitemap (such as Url, Title, Description and Roles) and the XML file which I have to generate using sql query is the Site Map file which is used in asp.net to implement the site navigation features on a website. Hope that helps!! Tag: Database design - best practices
  - 11
    - How Extract the data of TFINF when doing text mining? Thanks How Extract the data of TFINF when doing text mining Thanks Tag: Database design - best practices
  - 12
    - Passing SELECT ALL from URL OK, Here is the code for the 2 functions. one is SQL the other is CLR. You do not have to use CLR, there are multiple other ways to do this. See http://www.sqlservercentral.com/forums/shwmessage.aspx forumid=399&messageid=386759 Also, note that the CLR I show here is limited to 4000 chars and miss a build-in LTRIM... Count chars Code Snippet CREATE FUNCTION dbo . fn_countchar ( @source varchar ( max ), @charval varchar ( 255 )) returns int as BEGIN DECLARE @len int , @icount int , @count int SET @len = len ( @source ) SET @icount = 1 SET @count = 0 WHILE @icount <= @len BEGIN IF substring ( @source , @icount , 1 ) = @charval SET @count = @count + 1 SET @icount = @icount + 1 END RETURN @count end Split multi-param Code Snippet using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server. SqlFunction (IsDeterministic = true , IsPrecise = true , TableDefinition = "SQLStr nvarchar(4000)" , FillRowMethodName = "FillSplitCommaDelimitedStringToStr" )] public static System.Collections. IEnumerable clrfn_SplitCommaDelimitedString( SqlString str) { string x = str.Value; if (! string .IsNullOrEmpty(x)) { return x.Split( ',' ); } else { return null ; } } private static void FillSplitCommaDelimitedStringToStr( object obj, out SqlString str) { if (obj != null ) str = ( String )(obj); else str = String .Empty; } }; Tag: Database design - best practices
  - 13
    - configure sql xml support IIS Hope someone can help me...anything would be appreciated. I am using sql server 2000...When I open the Configure sql xml support IIS tool to create a Virtual Directory the only thing that shows up is a computer icon and a IIS Virtual Directory Management for SQL Server. I can't expand ... there isn't a default web site. The other tool called Enterprise Manager works fine with XP. Matter of fact all of the other tools work except for the Virtual Directory Manager. Tag: Database design - best practices
  - 14
    - Clone new database from existing All I got was some meaningless generic "operation failed" type of error, plus, the odd behavior I described above when sanitizing (running update queries against) the cloned database. I did try restoring the source db and specifying clone db as target, but was prevented from doing so with a message to the effect of "target database not in dataset so not allowable." If somebody wouldn't mind trying to clone a db from the same server through Sql Express Manager, and let me know the results, I'd feel better about trying it again. When I tried it, I discovered I was simultaneously sanitizing both databases. Rick Tag: Database design - best practices
  - 15
    - How to restrict a custom component to be used only in the event handler tab? UITypeEditor and usage is fully documented as part of the .Net framework. Even if you have not installed the .Net help, the MSDN site has it all for free. You should read up on the PropertyGrid control and see what options you have. A TypeConverter may do. Try these to start with http://msdn2.microsoft.com/en-us/library/aa302326.aspx http://msdn2.microsoft.com/en-us/library/ayybcxe5(VS.71).aspx http://msdn2.microsoft.com/en-us/library/53c49eck(VS.71).aspx Tag: Database design - best practices
  - 16
    - catch me if you can / bug in the "bug"? 1. Well actually it does, it states that compilation errors are not caught at all. Like "SELEKT *" (which has a typo and a incomplete syntax) 2. Well, its documented :-) Hey, wait a minute, this is a feature :-) 3.Well it won��t as it would break users code. Tag: Database design - best practices

Captain Rantflaps

Does anyone know the location of an offical (Microsoft if possible) document that shows the best practices for databse design

For instance what to call tables now schema's exist

Should it be

Countries

LookCountry

Geography.Country

Geography.Countries

What about Views and Stored procedures prefix with VW_ sp_ or some alternative

Thanks

Top

Re: Database design - best practices

Madhu K Nair

I have never seen any Microsoft Official document on this. But SQL Gurn Vyas has his own view which is very helpful. Refer these links

http://vyaskn.tripod.com/object_naming.htm

http://vyaskn.tripod.com/coding_conventions.htm

Do not use SP_ for procedure since those are there for system storedprocdure. Ucan have USP(User Storedprocedure) its again depends. I never uses "_" (unserscore) in my tablename. It may be easy to read but difficult to use.

Madhu

Top