Answer Questions

• S_A_S Error 1429: A server cursor cannot be opened...

  Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too).. We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed. This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute. Once in a while we will get a return from SQL .. "OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. U se a default result set or client cursor..." The OLE error code is 1429. An OLE Exception code 3604 is also returned. When this happens the SP will return the same error when executed for the ...Show All

• Trisk2 query large database

  Hi, I have a very large table to query, it has 11 million rows because it is a detail one. When I try to query against the table it took approximate 9 minutes. Is there a way to make it more faster I've already use partition scheme and indexes but seems 9 minutes is the fast result. Thanks in advance. best regards, The reason why is taking 9 min is because is performing a full table scan operation. Even if you have an index over the trx_date field. You can create a computed field with the year function, then create an index over the computed column and it will resolve to an Index Seek that is much better than the table scan. You can also do something like: where trx_date >= '2004-01-01' ...Show All

• HariAdu Error message running a SSIS package in a Job

  Hello, I'm having problems when I try to run a package from a job in SQL Server Agent. I have the same package in a server and it works fine,but when I try to install it in a different server I keep getting this error: Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 01:19:10 p.m. Progress: 2007-01-30 13:19:10.40 Source: Delete scripts Execute Process Task Operation Complete: 0% complete End Progress Error: 2007-01-30 13:19:10.57 Code: 0xC0029151 Source: Delete scripts Execute Process Task Description: In Executing "C:\Project_SSIS\Delete_Items.bat" "" at "", The process exit code was "1&quo ...Show All

• Wil Burton Loggin userid from trigger using APP_NAME

  Hi I have to implement table level loggin on a bunch of tables. The problem is that I also have to log the user that made the changes Obviously the right way would be to run everything tru stored procs - but that is not possible for me because there is a lot of existing functionality built already - some direct sql and a super large number of sprocs. So I wanted to go with a trigger based approach .However since the changes are being made by a web app SQL has no idea who the actual web application based user is. I originally tried to solve the problem by adding a LastUpdatedBy column to some of the tables and forcing the functions to update that too - so that the trigger can read that column but that has turned out to be v ...Show All

• kkennedy1008 Cascading Parameters - Delay

  I have a report with several cascading parameters, as well as several non dependant parameters. Due to the business flow the sequence not all cascading parametres are in line (the order is - Client - not dependant, AC#s - dependant on Client, Line Type - dependant on AC#s, Charge Type - not dependant, Cost Centre - not dependant, IDs - dependant on Cost Centre, Child AC#s - dependant on IDs, Year - not dependant, Month - not dependant, Include Zero Charges - not dependant). Some of these parameters are populated from stored procedures and some from sql text. I have a delays when the master parameter is selected until the child parameter is available for selection - this I understand. However there is also a significant delay from when the ...Show All

• jamiec Syntax error

  I am receiving a error when running a report. "Syntax error converting the varchar value 'RM' to a column of data type int." I checked both the datasets that return the data and there both set as Char(2). I know that the colum has both numbers and alph. Anyone, is there a syntax error that I am missing in the query's Or knows a work around Query 1 ="Select distinct jomast.fjobno, jomast.fpartno,jomast.fopen_dt " & "from jomast " & "join inmast on jomast.fpartno = inmast.fpartno " & "Where fjobno not in (Select qalotc.fcdoc from qalotc) " & "and inmast.fgroup = 'FG' " & "and jomast.fstatus = 'RELEASED' " & "and jomast.fpartno <> ...Show All

• Olodu SQL 2005 sp_send_dbmail with XML in body or as attachment outputs HEX not XML

  Anyone know how to run a Select statement with FOR XML and have the sp_send_dbmail send it out in the proper format When my Select statement is a simple "Select * from TableName" the results appear normal in the email but as soon as I add the FOR XML I get results that look nothing what SQLWB shows (i.e. nice XML). Instead I get text that looks like HEX or something like that. I'm running SQL Server 2005 SP1 on Windows 2003 SP1 set @strSQLQuery = 'Select * from TableName FOR XML path (''Row''), root(''Root'')' EXEC msdb . dbo . sp_send_dbmail @recipients = 'myemail@myemail.com , @body = 'Body of text is here' , @subject = 'My Subject' , @query = @strSQLQuery , @attach_query_result_as_file = 1 ...Show All

• David Ghikas MDX Calculations Script updates

  I have mdx calculated members defined in my cube calculation script. Is there any tool (other than BI Studio) that would allow a power user (typically a financial controller) to modify formulas for selected calculated members Another option could be to use custom rollup formulas stored in the relational data source and offer a nice front-end for modifying those formulas, but this would require a full process of the cube, where as modifying the calculation script would only require a default process. Any other idea Thanks! I agree, the code Chris put up definitely works. I have tested the xmla using SSMS and then queried the cube and it all works fine. Sorry, I am confusing a couple of issue here ...Show All

• simsen New problem getting data from xml table in sql db (Newbie)

  Hello, I have this xml tablerow in a table in my db. 2 examples <Toyconfiguration version = "1" name = "Lego"> <Ballconfiguration version = "2020" name= "Adidas"> If I would want to get the name of the toy or ball I would write Select Tablerow.value('((@name)[1]', 'varchar(100)') From table This would return Lego and Adidas But what if I want to know which kind of configuration it is How do I do this The answer I want is Toyconfiguration and Ballconfiguration.. Any help would be appreciated! You could use local-name() function: select Tablerow . xml_col . value ( '(//@name)[1]' , 'varchar(100)' ) ...Show All

• pgmdsg predict products ( data mining 2000)

  i want to make a web page and when somebody come in. i want show for him which products that everyone often buy at that time ( month or summer ). how i do in data mining to predict that products more: i want know how much percent of product is like by buyer or i want show products with desc % of the like of people You can use Microsoft Association Rules to resolve this problem: you can include the time info (such as month or summer) in each trasaction, our Assocation Rules will do the counting and find any rules apply. Please check the live sample http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/54.aspx on about the train Association Rules. You might also want to check out the tips ...Show All

• Caliendo SQL Server Event Alerts on 2005

  We recently upgraded from 2000 SP4 to 2005 SP1. All of our alerts carried forward and we set up database mail which works fine. We have 2 event alerts that search for a certain text that we cannot get to fire. I can create a test alert and set it on a performace condition and it will fire off the emails fine. As soon as we change it to a event alert and type in text that we verified is in the event viewer application log we cannot get it to fire. We forward all events and created a custom job that will write to the event log and then forward it to our management server were this alert should be picking up these events becasue of the text search but it is not. I even changed the text to system generated text that is in the log already and i ...Show All

• Jonathan Cran Problem deploying reports

  Hi, I have an application that involves 3 components one of which is Reports. My system's configuration: SQL 2000 - Default instance SQL 2005 - Named instance (Instance1). I provide the user with an option of selecting the SQL Server instance. If the user chooses Instance1, then I need to deply the reports in SQL 2005. The exceptions encountered during installation are logged in the Event log. When I do the installation for the in a machine, I always get the following error in the event log: Could not connect to the Report Server Database http://<MachineName>/ReportingServices.asmx . I tried giving the rs.exe options from commandline. I got an error like: Error in DetermineReportUrlSecurity(). I don't have the exact error ...Show All

• John Clien SqlServerCe.dll

  Hello, Iam devloping a product for a mobile unit using Windows mobile database. (*.sdf) Is it possible from other form applictions, such as ordernary Windows forms Have a wonderful day! This is the purpose of SQL Server Everywhere Edition. One SDF file that can be used on desktop with Windows Forms apps and also on device with .NET Compact Framework Windows Forms apps. Darren ...Show All

• cverdon Generate Scripts option in SQL 2005 Management Studio

  Did anyone even bother to test the Generate Scripts option for a database I changed the following options from their default setting: "Include Descriptive Headers" = True "Include if NOT EXISTS" = True "Script for Server Version" = SQL Server 2000 "Script Indexes" = True and scripted all Stored Procedures, Tables, User-defined functions, and Views Problems: 1.  Generates IF NOT EXISTS (SELECT * FROM sys.objects ... should be ...SELECT * FROM dbo.sysobjects ... 2. Generates WITH (IGNORE_DUP_KEY = OFF) ... on the indexes. That portion is incompatible with SQL 2000 3.Generates EXEC sys.sp_addextendedproperty ... should be EXEC dbo.sp_addextendedproperty ... Is there a hotfix or patch to deal with these issues ...Show All

• muelle60 Self referencing cube for side by side comparison

  A portfolio and a benchmark have the same structure. Every portfolio has a related benchmark to which it is compared. Many securities are shared between the portfolio and the benchmark. When you look at the portfolio from various dimension combinations the user wants to see what the corresponding benchmark looks like from the exact same dimensions. My DimPortfolioBenchmark table is linked to my FactPosition table. A portfolio is compared to a benchmark so each portfolio has a PortfolioBenchmark key indicating the benchmark it is compared to. When I pull up a measure for the portfolio I want to see the corresponding measure for the benchmark (or at least for certain selected ones). I was trying to do this in a calculated field. Wha ...Show All