Answer Questions

• MikeyFunk Stored Procedure that return Dataset(Select Query)

  Hi all, I have a SP that return a dataset and I was thinking to execute that SP inside of other SP then catch the dataset to put into a variable or put into a temp table. What I know is you can not use recordset on output and input parameter in SP correct me if im wrong. I'm just wondering if I there is a work around in this scenario. Thanks and have a nice day to all. Teo: If I understand you correctly (1) you have a pre-existing stored procedure that you want to use as an internal component of another stored procedure. If your first stored procedure returns a record set then you can use a temp table inside of your new stored procedure to capture the output of your existing stored procedure ...Show All

• mobigital @@ERROR logic not working

  Hi There I am having a little trouble with something very simple: I have a stored procedure with the following sql in it: UPDATE mfsWS_CustNewCustomer SET Result_Id = 0 WHERE QueryGUID = @Guid IF ( @@ERROR <> 0 ) BEGIN RAISERROR ( 'Error setting Result_Id' , 16 , 1 ) UPDATE mfsWS_CustNewCustomer SET Result_Id = 1 WHERE QueryGUID = @Guid RETURN END To test if this works i SET the Result_Id = 'X' this is a smallint column so this should not work. My issue is that all i get is the error: cannot convert char to numeric etc etc However the @@ERROR logic is not executed , my error is not raised and the update does nto happen, it si as though the entire sp aborts at ...Show All

• pst_grant Parameter Display

  I need to display parameter value on my report.This parameter is coming from web application. Example:I am passing parameter Dealer Name from web application to Report. How to display that Dealer Name on Report. Drag a textbox control in the report. go to edit expression for that textbox. select parameter on ur left side, it displays all the parameters listed in ur report on the right hand side.(dealer name as u said for example). incase it is not used in query, u can create one parameter which also be listed in the right hand side. double click the parameter you want to display in the text box. ...Show All

• mohasad How to insert only new records (not already in destination)

  Greetings from a SSIS newbie still on the learning curve... I have a SQL query OLE DB Source that yields a result set that I'd like to put into a SQL Server Destination, but only those records that don't already exist in the destination. Is there a recommended (read: easy) way to accomplish this Perhaps a handy transformation I have tried to incorporate a subquery in my source query along the lines of: SELECT fields FROM table1 WHERE keyfield NOT IN (SELECT keyfield from table2) which works in design time but fails at the server with a cryptic: "Error: Unable to prepare the SSIS bulk insert for data insertion." and "Error: component "<component>" (16) failed the pre-execute phase and ...Show All

• frigorific Error: The product level is insufficient for component, while executing SSIS package

  While executing SSIS package from command prompt as follows: dtexec.exe /DTS "\MSDB\etl-hyundai-package" /SERVER tvmwindev02 /CONNECTION "TVMWINDEV02.HISNA_POC";"Data Source=TVMWINDEV02;In itial Catalog=HISNA_POC;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /CONSOLELOGO while executing the package following error message is received as : Error: 2006-07-28 15:12:36.60 Code: 0xC00470FE Source: Data Flow Task DTS.Pipeline Description: The product level is insufficient for component "Data Conversion" (202). End Error and at the end as : DTExec: The package execution returned DTSER_FAILURE (1). Same ...Show All

• St&amp;#233;phane Beauchemin negative values on a measure

  have a measure called total sales amount in a FACT table some values are reported back as a negative number. anyone know the issue What I believe is happening here is that the measure in your cube is still set with an int data type. To fix this, edit the cube, go into the cube structure tab, click on the measure in question and change its data type from int to bigint. The re-process your cube. BI Dev Studio (BIDS) does not check that the data types are the same (or greater) in the cube when compared to the DSV. If any of your fact table records overflowed the data type you would get an error message when you processed the cube. But if the aggregation of the measures exceeds the data typ ...Show All

• mattdawg Bug in Management Studio : Cannot add procedure article for heterogeneous transactional replication

  Hi, I'm setting up a heterogeneous transactional push replication with Sybase ASE 12.5.3 as subscriber. With management studio I try to create an procedure article with following properties Copy extended properties : false Destination object name : pGS_RefuseRequest Destination object ownere : dbo Action if name is in use : keep existing object unchanged Replicate : Execution of the stored procedure Create schemas at Subscriber : false When I save the article and then the publication I got following error message: Can not add artice 'pGS_RefuseRequest'. Object was not found on server. Check if this object exists on the server. (Microsoft.SqlServer.Rmo) That's realy strange because the wizard offered the procedure ...Show All

• vasudupe DBCC CHECDB REPAIR does not fix errors ?

  Hi There This is on Sql Server 2000, SP3. When i run DBCC CHECKDB i get the following errors: ============================================================================================ Server: Msg 8928, Level 16, State 1, Line 1 Object ID 2, index ID 255: Page (1:76914) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 255, page (1:76914). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1. CHECKDB found 0 allocation errors and 2 consistency errors in table 'sysindexes' (object ID 2). CHECKDB found 0 allocation errors and 2 consistency errors in database 'OLTP3PRO'. repair_allow_data_loss is the min ...Show All

• Glyn Harper Schema modification advice

  We have a production database (SQL 2005) that is being used by at least 20 different applications. Some of the tables need to be modified (column added). There is also a need to add new tables that will have referential integrity relationships with existing tables. Does anybody have a good way or best method of changing table schemas, objects, etc. while minimizing the impact on the current operations The key is minimal downtime if there is a need to have one. 1. Can the table schemas change while it is online 2. How would you account for all the applications that use stored procedures 3. Among the existing SQL 2005 tools, which one/s can handle the job there are 20 applications using the program a ...Show All

• Gunnar Adler Date Format Data Driven Subscription

  I'm getting the typical error that the format of my date parameters is not correct. Although i'm getting my dates in a select from a datetime field. Is there really no universal format that can be delivered to RS that he can interprete the date right, whatever language the report has been set to. Kind Regards ..... You can define your datetime parameters as YYYY-MM-DDTHH:MM:SS. This format is independent of the language of the report. See http://msdn2.microsoft.com/en-us/library/ms159825.aspx for moer details. ...Show All

• AvengerDr Analysis Services SP4 problems

  Hello I hope someone can help me. On a SQL server that has had SP4 recently installed on it for both the SQL Server service and OLAP service, I am unable to browse the data of any of the Shared Dimensions or Cubes of any of the databases when I use the Analysis Manager MMC. At the time I am logged onto the server as an administrator using terminal services. Upon attempting to browse a shared dimension the following error is returned "Unable to browse the dimension <dimension name>, Type mismatch" and when attempting to browse a cube the error is "Unable to browse the cube <cube name>. An unexpected internal error has occured. Please restart the application." The Cubes can be browsed via Excel though an ...Show All

• Kunal Sharma import OpenSSL certificate with private key into sql server 2005 express edtion

  hey, i have a problem, to import a self signed openssl certificate into the sql server 2005. my final idea is to get encrypted columns from the database over an jdbc connection in a java client. when i use a certificate generated by the sql server 2005, i can encryt columns of a table. then i catch the the result in my java client. but in java, i need a keystore with the private key of the certificate. ok. i have export the the certificate and the private key of the sql server 2005. problem: in a keystore i can only import the certificate (signed public key) but not the private key. my new idea is to import an openssl certificate or an certificate generated by the keytool (java) into the sql server 2005 and encypt t ...Show All

• r0d message lost

  Hi... My service broker was working...all of a sudden it stopped working...so i figured out destination queue was disabled and I set it as ALTER QUEUE SERVERQUEUE WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = usp_OnReceiveMessage , MAX_QUEUE_READERS = 5 , EXECUTE AS OWNER ) Also altered my database for trustworthy after going through some posts previously. Now the message is sent...i dont see any message in my tranmission queue but i dont receive the message in destination server. When i checked endpoints... select * from sys.conversation_endpoints I see the last message with st_desc as conversing... how do I solve this... Thanks, Pramod ...Show All

• Kumar Venkat How to set precision of a decimal number

  there is a column which type is float in a table, i want to set the precision of its value, for example if its value is 10.333888, i want to get its value as 10.33, how to complete it in a select Sql thks thks for HTH, i have solved my problem with your method, thks again You can change yourcolumn to numeric or decimal data type, for example, decimal(18,2) or numeric(18,2), which will return 10.33 in your case. You can look up the difference between numeric and float data type from Books Online which covers everything you need for SQL Server. HTH ...Show All

• Ed209 Create vb.net user login form

  Hi all, I made a Windows forms app using vb.net (VS2005) and SQL server 2005 Express Edition (to which I'm new). During the creation of the app I used windows authentication in the connection string. Now I want to secure the app, meaning I want the SQL Server database not to be accessed without a username/password. I guess the logical thing is to create a login form, providing the username and password so it can be used for connecting to the database. I thought this would be a standard (thus easy) thing to do, but no forum or article yet showed me a way to accomplish this in an understandable way. I also want to deploy this app using click once technology. I need to distribute this app on a cd/dvd. If possible, can anyone give me a s ...Show All