Answer Questions

• mobigital Scripting dependecies only scripts objects in dbo schema

  I tried to test this SMO script (below) by adding some views with dependencies but for some reason it is sorting the views that I created. Not the other views in the AdventureWorks database. Can someone tell me why create view view_c as select 1 a go create view view_b as select 1 a from view_c go create view view_a as select 1 a from view_b go private void ScriptViews() { Server svr = new Server ( "localhost" ); Database db = svr.Databases[ "AdventureWorks" ]; List < SqlSmoObject > smoObjectsUnsorted = new List < SqlSmoObject >(); List < SqlSmoObject > smoObjectsSorted = new List < SqlSmoObject > ...Show All

• Scott Allison How SQL 2005 Execute a sp in master db when called from other db?

  In previous SQL server version, if I create stored procedure sp_XXXXX in master database, then call it from other database (session context is other than master) with master..sp_XXXXX. SQL server will execute the sp with the environment from where it was called. However, in SQL 2005, if I call it with "master..sp_XXXXX", SQL 2005 will switch the context to master database and execute the sp in master database environment. I wonder if I did something wrong or that is the way SQL 2005 execute the sp. If this is the way SQL 2005 does business, then, how to exec a sp in master database with local environment Thank you for any help.   It was "likely" to go away in 2005, bu ...Show All

• Joel Martinez SSIS Job Failure

  Can network latency or insufficient memory cause SSIS packages to fail I run an agent job that consists of many individual SSIS packages. Most of these packages read flat files from a network drive, format the data, and load the data into SQL Server tables. Each step normally takes only about 30 seconds. Occasionally, the same step that took 30 seconds would take over 5 minutes to process. Sometimes a step would fail with a package execution error (no other clue given). It seems that after the server is rebooted, the job runs successfully. If a package step fails due to some sort of resource limitation, shouldn't the error be noted somewhere Thanks Michael. I went back into BIDS and defined package l ...Show All

• DixieDog Multi language for Parameter Prompt within SSRS

  I need to generate a multi language report. So I was able to create everything depending on the global variable User!Language except the prompt of the parameters of the report. The prompt seams only to be a text and is not customizable by a expression ore something else. So over all is there an option/way to show to the user the prompt in his language without creating a report for each language Thanks ...Show All

• renemt Rendering report when one dataset times out

  We use Report Server reports to provide our support staff with views into several production databases on various servers. The most commonly used report is a summary of the data on several production servers. The report contains multiple datasets. Each dataset points at a different server. If one of the servers is down some reason, one dataset fails to execute, and the entire report fails to to render. What I would like is some control over this behaviour. If a given data set fails to execute, I would like the report to continue and render the results. The failed data set would obviously show no data but this is fine by me. I know I can place timeout values on each dataset. But this doesn't help me. Can I structure my report or SQ ...Show All

• GlenO SQL Server Agent running SSIS Package. Permission Problem on Destination Server.

  Hi, I have a Problem using SQL Server Agent with a SSIS package running as a proxy. Description: I have a SQL Server Agent Job that uses an SSIS package to copy a file from a share (Network) to our target system. To access the share the step uses a proxy (credential with account to access the share). The step fails! If i add the account under whicht the job step runs (the account to access the share) to the administrator group (on the target system) the Job succeeds. Question: What minimal permission do i have to set for the account (on the target system) that it works! It might be a problem with loading the package. Where is the package stored Can you also turn on logging in your pack ...Show All

• SNorberg SQL2005 Enterprise Database engine performance features (versus standard)

  Can anyone comment on the engine performance difference between SQL2005 Enterprise Edition versus Standard I'm talking generalized performance of the engine and not admin features (parallel index operations) or scaled-storage (partitioning) ( http://www.microsoft.com/sql/editions/enterprise/comparison.mspx ) The marketing literature makes note of two things: Enterprise can use more then 4 processors Enhanced read-ahead and scan (super scan) (note: I cannot find anything about this 'feature') One un-noted Feature: only Enterprise supports 'lock pages in memory' We are in the process of migrating from SQL2000 to SQL2005 in an OLTP environment. Based on the marketing literature; I would have chosen SQL2005- ...Show All

• Rusty_Lane Help with Join Syntax

  I have a query where I need to join a table to multiple tables and alias a field from those tables on each join. I tried the syntax below but received a error. Please assist, first time trying to do this. JOIN dbo . AbsenceReason ar ON ar . AbsenceReasonID = sda . AbsenceReasonID WHERE ar . [Name] = 'DailyReason' LEFT JOIN dbo . AbsenceReason ON ar . AbsenceReasonID = spa . AbsenceReasonID WHERE ar . [Name] = 'PeriodReason' LEFT JOIN dbo . AbsenceReason ON ar . AbsenceReasonID = cio . AbsenceReasonID WHERE ar . [Name] = 'CheckInOutReason' error I receive is : Msg 156, Level 15, State 1, Procedure p_000001_GetAttendanceProfileData, Line 45 Incorrect syntax near the keyword 'LEFT'. Msg ...Show All

• poon23 Create new database as a user instance

  Hello... Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created. Thank you... hi, yse you can, but this does not mean the created database is detached... it's attached and available... Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;Database=master;Trusted_Connection=Yes;") con.Open() Dim cmd As New SqlClient.SqlCommand With cmd .CommandType = CommandType.Text .CommandTimeout = 5 .CommandText = "CREATE DATABASE UserInstanceDatabase;" .Connection = con ...Show All

• Jon Stelly get the Ｅｒｒｏｒ Message in OnError Event hanlder

  Hi, I want insert data into table from csv file, so I put a data flow task on control flow tab, a flat source and a ole db dest on data flow tab. They work well. Then to get the error log I put a script task on event handler tab, in this script task I want to output the error description to a txt file while the data flow task fails for any reasons such as constraint failure etc. In this script task I think I can get the error description by using property Err.Description, but Err.Description is empty while data flow task fails. How can I get the error description in the script task on event handler tab. thanks! The error description is stored in the @[System:ErrorDescriptio ...Show All

• Joe Pickering installing sqlExpress (Advanced Services) will this break existing sqlExpress?

  hiya, I have sqlExpress and sqlServerManagementStudio on my XP pro box. Will the installation of sqlExpress (Advanced Services) cause any problems IS thereanything that I shold be aware of in advance many thanks, yogi thanks, I'll give it a go. yogi hi, it should not cause problems, as up to 16 different instances can be installed side by side, and at different service pack level as well.. so you can install a new instance of SQLX + Advanced Services as long as you provide a different instance name.. regards ...Show All

• Vladimir Chtepa Execute "Select" depending on the input parameters

  Hello, a question please. Could anyone say me if I can create a store procedure with 2 'select's statements into. Besides, I'd want to know if I can execute a "select" depending on input parameters. Something like this: create storeproc Param1, param2 if param1 <> null Select * from table where id = param1 else Select * from table where id = param2 end if Thanks in advance.... Select * from table where id = ISNULL(param1,param2) HTH, Babu You can do exactly what you have typed there, but don't use "end if" ...Show All

• Territorial Musings Incremental Dimension update in 2000

  Hi I have a MSAS 2000 sp 4 server and am doing some testing with incremental loads to try to increase cube availablility. If I fully process the cube and then add the next days worth of data through using the incremental update (dimensions as well) I always get a failure indicating that a member is in use and cannot be deleted. Is the reason for this that the new dimensions is being based on only the most recent days data and if that member existed in the historical data but not the most recent day it attempts to delete it. The diemnsion build itself has both the old and new member in it and I do not allow duplicate keys in any of the dimensions so this should not be an issue. Thanks in advance... Jacob ...Show All

• Olaf van der Spek SQL NATIVE CLIENT - SQLOLEDB

  In reviewing Sql native programming (Sql Server 2005 books on line) The author states: SQL Native Client is a data access technology that is new to Microsoft SQL Server 2005, and it is a stand-alone data access application programming interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic-link library (DLL) while also providing new functionality above and beyond that supplied by the Microsoft Data. Question: Doe's standalone SQL OLE DB take advantage of new SQL Server 2005 enterprise features as does SQL NATIVE CLIENT DLL'S or is SQL OLE DB just a componet of Sql native Client. Scope: We are considering porting our Oracle 9i database to Sql ...Show All

• yousaid selecting date range

  I have an MDX query that I'm using in Reporting Services: SELECT NON EMPTY { [Measures].[NumberOfClaims] } ON COLUMNS FROM ( SELECT ( [Date].[DTM02_Date].&[2003-12-23T00:00:00] : [Date].[DTM02_Date].&[2005-01-01T00:00:00] ) ON COLUMNS FROM [ClaimStatus]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Now this works fine... until I change the dates... such us putting in 2000 instead of 2003 in the date first date. This cube is from two tables: 1. A Claim table (health care claim) 2. A Date table The data table has two columns: 1. a key 2. the actual date The claim table has a key that links to the date table's key, and the corresponding date is the claim date. So when ...Show All