Answer Questions

• someone030 Report Model missing some fields

  I hope someone can clarify what I observe below. When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is. This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View). Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip Perhaps I'm doing something wrong, or there is a workaround If anyone can shed any light in the issue, I'd greatly appreciate their comment. Thanks in advance and kindest rega ...Show All

• 2V. OLE DB Command and Stored Procedure that returns value and/or error

  Guys, could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value Or is it just supposed to be used to call a straightforward insert statement only What I am hoping to do: I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable. If I AM supposed to be able to do this... then how on earth do I do it I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value. In the Advanced Editor any time I try to ...Show All

• Beauner Where is Auto Save in SQL Management Studio???

  I've done this twice now, I loose my sql script because I inadvertantly shut down management studio and a frantic click of buttons I do not click save! (yes I know, user error). What I am looking for is a function like in Microsoft Word where it saves your current document periodically. Where do I set that in management studio so I don't loose my scripts and that my changes are saved X # of minutes Regards, Matt Please mark an answer, thanks. Does Word's auto-save feature keep the interim files after you close and choose, "no, do not save my changes" I believe the only time when those files are kept around is when the application crashes. In your cas ...Show All

• Quirk Deleting Older rows in the table

  Hi, I have a table which has around 50,000 rows. Can I delete the rows older than one year If no, Can I delete the last 25,000 rows (older rows) from the table Can you assist me with the SQL Statement to do this Thanks Santhosh I agree with you (only as a last chance "trick", of course.) Also, I often use this kind of logic as a "tie breaker" when there is a duplicate date value (which happens sometimes since the datetime datatypes are not really granular enough). And like you say, sometimes when you have to find a way to get rows, you have to do something like this. Clearly I agree with Jens too. Plan ahead so you never have to do this in production code! If your table has an identi ...Show All

• Ken_Bussell DSV Refresh

  Hi, I have an odd problem with the creation of objects in the data source view using AMO. I added some tables and relations with AMO and everything seems to work well. Then, using Analysis Services I selected the data source view and refreshed it. The form that notifies the changes in the data source view showed a lot of changes that I didn’t understand, costraints created and removed, fields changed and other operations that I didn’t made... I try to do the refresh with AMO using all the update and refresh options I found but with no results. Every insight on the question will be of great help. Thanks This is an extract of the data source view refre ...Show All

• Chaseman SQL Express - Access Denied Error Code 5

  Hi, The install of SQL Express with advanced services on my WinXP Pro PC seemed to go fine but now I look at it and see that the actual SQL server hasn't installed. The log files says: Product: Microsoft SQL Server 2005 Log File: ...SQLSetup0003_HKM1_SQL.log Last Action: Install Finalise Error String: Failed to set registry settings for server network libraries. The action is SetShilohRoot. The error is 5 (Acces is denied) Also getting the same error for product: SQL Server Database Services. Any help would be greatly appreciated. Uninstall and make sure you install as an adminstrator account. You might also have a corrupted registry in some way, so you can check on the live.com site for Microsoft's &qu ...Show All

• enric vives Differential rows

  I am using SSIS to replicate data from an AS400 mainframe to a SQL destination. I am using a lookup column to see if the primary key is duplicated and if not, it will INSERT the row. This is all working fine. What I need to know is can I also use the Lookup transformation to look for differential data and then UPDATE the row The primary key of the table will never change, however the data might and I need the package to recognize that this is a modified row on the mainframe and that the same row on the SQL destination server needs to be updated. Thanks for any useful information. Thank you so much for the answer to your own question! I've been using lookup and conditional split transformation to che ...Show All

• Wee Bubba Populate a variable

  During a dataflow - I like to populate a variable with True or False based on a value in one of the data records. How do I do this excercise  if I use a script - can someone provide me with simple script on how to populate one variable   Thanks heaps         Thanks all - finally I got it to worrk. Thanks for answer. I am trying your suggestion - but I am not sure how to populate the "result". If I make a statement like this: Select field from table where 1=1 (only one record) How does the value of "field" get transfered to "result" ( which again will lead to populat ...Show All

• Dylan Morley XML datatype instance has too many levels of nested nodes.

  Hi I am getting the following error when running a SELECT query: Msg 6335, Level 16, State 102, Line 2 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. There are more than 30 columns in the query and when I remove some of them (in random) the query works fine. I tried to read the blog at http://blogs.msdn.com/sqlprogrammability/archive/2006/05/23/605299.aspx but it didn't helped me to solve my issue. Another surprise is that the same query is running fine in another server. Please help me in resolving this issue. Regards Sanjay SQL2005 XML datatype has a limit for nested level which = 128. I can take a look of your FOR XML quer ...Show All

• Paul Hacker Not Closing Connections?

  In some of my databases, I see connections from the same source. Each one has a CPUTime of 0 and DiskIO of 0 as well. It is from an application written by an internal source, and I suspect they are not closing their connections. How can I make sure SQL9 SP1 (in SQL9 compatibility mode) Are you using ADO.NET's DataReader object in your application If you are, you have to explicitly close the connection you use for the Data Reader object. Run sp_who2 and check the ProgramName column. This gives you an idea on the application that is trying to connect to SQL Server What you're seeing may simply be connection pooling in action.  Does the number of connections gr ...Show All

• xLogicTalentedx include column from table in SELECT in an INSERT OUTPUT clause

  Hey All – I am having problems with the OUTPUT clause in an INSERT. I have something like this: INSERT INTO dbo.Person ( PersonID ,Name ) OUTPUT p.AuditVersionGUID ,inserted.PersonID ,inserted.Name INTO dbo.PersonAudit ( AuditVersionGUID ,PersonID ,Name ) SELECT p.PersonID, p.Name FROM #Person p AuditVersionGUID is not and shouldn’t be defined on the Person table but I do have it defined in my #Person table. I get this error: Msg 4104, Level 16, State 1, Procedure spExtractPerson, Line 275 The multi-part identifier "P.AuditVersionGUID" could not be bound. I can accomplish this when I ...Show All

• Ted. For xml auto question:

  Hi, I ran the two queries below on SQL server 2005 enterprise 9.00.1399.06 and 9.00.2047.00. The first ("Top" paginated) query ran fine (from my C# code based dataset) on the older version, but returns results like those below (See Result1) from the newer version. I need to get my data back with the xml parent child nesting intact and table handles as they are designated in the main query text. Perhaps there is another way to do a paginated query that will deliver xml nested as shown in Result2. If so I would like to know how to code it. If you have any suggestions, I would appreciate any help you can give. Thanks, Dave Query 1 (Paginated using Top): select niin, item_name, cage, partno, vendorname, ui, price fro ...Show All

• Victor BA Pass parameters to complex procedure.

  Hello, I have this Stored procedure, a very complex one, and it works fine with the parameters wrote on it. But when I try to change the number to variables @param1, I got errors, can somebody tell me how to replace this please. DECLARE @return_value int, @lvaIndicador varchar(4000), @ecuacion varchar(4000), @numerador int, @denominador int, @lvaIndicador2 varchar(4000), @ecuacion2 varchar(4000), @numerador2 int, @denominador2 int, @porcentajeCumple numeric EXEC @return_value = [dbo].[SP_CUMPLENCONNIVEL] @nmtipoCompetencia = N'12,13,14', @nmdivisioncorporativa = N'18,19,20', @nmciclo = 9, @nmcicloCompara = 12, @nmempresa = 72, @nmcargo = N'20', @lvaIndicador = @lvaIndicador OUTPUT, @ecuacion = @ecuacion OUTPUT, ...Show All

• mobigital Graphic Representation of a cube

  Hi People, Somebody knows how I can represent graphically the cube, dinamically dragging and dropping dimensions, meassures into a graph like Dundas OLAP but with a BI Microsoft Tool I tried with Reporting Services but it is a Static Report, I need something like Business Object or Dundas , dinamyc. Thanks Try using ProClarity. It is one of the best AS client tools on the market. Edward. -- This posting is provided "AS IS" with no warranties, and confers no rights. ...Show All

• Kinju Updating Database User SID in SQL 2005

  Hi all We're currently undertaking the development of our first SQL Server 2005 database and we've stumbled across a problem with our method of deployment from Development through System Test, UAT and, finally, to Production. Under SQL Server 2000 we would build a new database in Development and assign permissions to a specific database user which was linked to an appropriate Windows Login - the whole build process was scripted. We would then hand the mdf and ldf files over to the Operations team who would attach the database to the System Test server, create a new Windows Login (appropriate for the environment), then run a script which would update the SID stored against the database user to the SID of the appropriate login. e.g ...Show All