Answer Questions

• Tracey Macias BACKUP RESTORE with no data

  Hi champs, Is there a way in SQL2k5 to backup and restore a database, without the data that is stored in the tables I know I can script the whole database, but is there do this with a backup restore /Many thanks Hi Rogvi Is this something that you would need to do regularly and, if so, for what purpose Thanks Chris Is there any way of scripting the database out automatically I mean, is it possible to make a scripting backup of the database as a "Maintenance plan" /Many thanks No, Backup backups up the database, data included. Otherwise it wouldn't be a backup, would it The easiest way is to script the datab ...Show All

• Endif Cannot Run on this Edition of Integration Services Error Message

  Hi All, I am little new to 2005 so this may be an easy one. I have a IS package that I created on my local 32 bit machine. I have copied this package to one of our servers running SQL 2005 EE x64. The package executes fine until it gets to a VB Task at which point it errors with the message of "The Task "DefalutValues" cannot run on this edition of Integration Services It requires a higer level edition" Is this a known bug/error that you can not build on a 32bit machine for a 64bit machine. I have set the precompilescriptINtoBinaryCode value to true. If anyone has any suggestions please let me know. Thank you. Did you ever find a solution to this problem. I'm having the s ...Show All

• Zhen Encrypt and ??Decrypt Data??

  I Also Post this to SQL Server Security section no one aswered. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1047735&SiteID=1 Here is the post: CREATE TABLE TabEncr ( id int identity (1,1), NonEncrField varchar(30), EncrField varchar(30) ) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'OurSecretPassword' CREATE CERTIFICATE my_cert with subject = 'Some Certificate' CREATE SYMMETRIC KEY my_key with algorithm = triple_des encryption by certificate my_cert OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE my_cert INSERT INTO TabEncr (NonEncrField,EncrField) VALUES ('Some Plain Value',encryptbykey(key_guid('my_key'),'Some Plain Value')) CLOSE SYMMETRIC KEY my_key OPEN SYMMETRIC KEY my_key DECRYPTION BY CERTIFICATE ...Show All

• Kymbo Items collection

  Hi , I have a small question I need to pass a list which contains a list of business object to a stored procedure I need to pass as an example List<BuzObject> to stored procedure to hit database only one time instead of loop through List and call the stored procedure to each object Which one of them is best performance and how to implement the one hit thanks ...Show All

• Andrew Beery xp_sqlmaint maint plan jobs fail to run on named instance (sql2000)

  we have sql 2000 w/sp4 and 3 instances: (local), bkupexec, and FLA. maintenance plans for the (local) instance run okay. Maintenance plans for the FLA named instance 'hang' - as in the server agent starts the job, but the job never runs, never finishes, no errors, only thing to do is kill the process. The job step should run a backup of a single database. killing the server agent initiated process leaves a process in 'killed\rollback' state til restarting the server. running the job step command in query analyzer gives same result. running sql profiler when command run in query analyzer shows that it starts the cmd, starts the SP, there are some performance counter related commands logged (not sure if they are from the sqlma ...Show All

• prodonjs COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

  Hello, We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions. while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine. Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 ( http://support.microsoft.com/kb/918251 ). We have installed this hotfix on top of SP1 but without results. Is somebody familiar with this ...Show All

• RubenPieters Variable not getting updated and passed into data flow

  My package sets a variable in an ExecSQL task. This variable is then used as a parameter in a DataFlow task that follows. Normally everything works just fine. However, sometimes if the package fails inbetween the step that sets the variable and the DataFlow, the default value of the variable is stored in the checkpoint file. (It is not failing in the step that sets the variable) When the package is restarted I can see that the variable is not set to the data value in the database, but rather it has its default (design time) value. Anyone else see this bug Under normal circumstances, the variable is set and passed along just fine. I noticed the problem after the package abended in an earlier step for a ...Show All

• simmons Procedure or function sp_MSupdatesysmergearticles has too many arguments specified.

  Hi, I've been on this one all day - and now tearing my hair out. I have a large SQL2005 database (migrated from SQL2000) which I'm trying to replicate using a VB6 front end. The application and database concerned have been working with no problems using merge replication for the last 5 years - however, I now need to port to SQL 2005. In the VB6 app, I'm using the SQLMerge9 control to handle the replication side of things. The code I'm using is exactly as per the code which works under SQL 2000, however, I'm getting an error message "Procedure or function sp_MSupdatesysmergearticles has too many arguments specified." when I try to initialize the subscription. I've seen one other item on the web with this problem, and this was so ...Show All

• jwagner20 SSIS "File System" path assigned randomly

  We have been storing packages in the File System folder. We had noticed that there seemed to be times when re-importing an existing package did not seem to update it properly. We tried deleting the existing package first rather than overwriting it, but to no avail. Today we noticed that there were two \DTS\90\Packages folders, one on the C drive and one on the D drive. The dates on the files in those folders showed that sometimes the import put the file on one drive, and other times on the other drive. The MsDtsSrvr.ini.xml file shows this: "<StorePath>..\Packages</StorePath> ". We intend to stored the packages in the msdb database instead in order to work around this problem. Has anyone else noticed t ...Show All

• Jassim Rahma Avegage from 4 columns

  I have 4 columns and i need average from this columns. I can sum values from this columns and divide by 4, but this is inproper in case of nulls. Is there any easy way to do that Right now I'am using: Nice solution to a unnice problem :) I am always impressed by the many ways one can write SQL to get past improper design. It just goes to show you that a few minutes upfront can often save you hours later. If the data was in rows: select sum(value) from table Oh well, certainly not as many creative solutions required :) Case approach is faster since the query using CASE expression does less I/O than UNION ALL approach or CROSS JOIN or UNPIVOT. You can perform the calculations in one pass on each row. Another way to wr ...Show All

• adi151478 Dynamic transformation to upload flat files into database

  Hi, My scenario: I have 4 different flat files types each having different no. of column, order of columns etc. I want to upload all the 4 types into the same destination table in the SQL database. Before uploading I need to apply transformation to each column in the flat files. The transformations could be like 1) Multipying the source column by 100 2) Put an if condition for 2 source columns and then select one column to be copied into the destination. I have the flat files schema with me and also all the transformations that are required. Question: Can SSIS provide me with a component that can read the flat file schema and the transformations from the database and apply them to the source data and then upload it to ...Show All

• Joe Horton Match numbers

  I have a problem using the sql builder since i am trying to match a list of 5 numbers in TAble A with other 5 numbers in Table B All i want to know is when there will be a match it will indicate in the report. Example code of what i tried: SELECT     DBA.*,DBB.* FROM         (DBA INNER JOIN                       DBB ON DBA.[ 1] = DBB.[ 1] OR DBA.[ 1] = DBB.[ 2] OR DBA.[ 1] =DBB.[ 3] OR                       DBA.[ 1] = DBB.[ 4] OR DBA.[ 1] = DBB.[ 5] O ...Show All

• meFox Insufficient Memory Errors - 12GB RAM - 64-bit???

  hi, it was suggested to try this forum, so I am hopeful I can get some more ideas. Please see this link where i posted my issue: http://sqlforums.windowsitpro.com/web/forum/messageview.aspx catid=82&threadid=49973&enterthread=y Summary: I have just installed a brand new 64-bit system, with 64-bit SQL Server 2005 sp1, and 12GB of RAM. The same queries and processes that worked on SQL Server 2000 Std Edition successfully, are failing on SQL2k5 64-bit After migrating the databases and jobs, I am frequently getting this msg: 701:Insufficent memory to run this query Nothing else in the logs. I also made sure that the SQL Service account is granted Lock Pages in Memory - no help. First, I thought that SQL 2005 can take all ...Show All

• TemporaryNick cannot access database properties

  Hello, The code: Database db = new Database(localServer, database); SqlPropertyCollection collection = db.Properties; foreach (Property property in collection) { //DoStuff } The error: Microsoft.SqlServer.Management.Smo.PropertyNotSetException: Set property ActiveConnections to accomplish this action. at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname) at Microsoft.SqlServer.Management.Smo.PropertyCollection.PropertyEnumerator.get_Current() The error takes place at the first property. Debugging this, I noticed a lot of the properties throw this exception. Why is that The user that I use dbowner on the database and sysadmin on the whole server. Thanks For some reason the datab ...Show All

• Tin Aung Soe signalling a bug on the Data dimension security set up

  Hi everybody. I tried to set up the security on the members of dimension attributes throught the "Dimension Data" tab of the "Edit role" form in the OLAP database. If I set up an "Allowed Set" directly on a "cube-dimension", everything works fine, whilst if I set up an "Allowed Set " on the "Database dimension" (which I suppose should apply to all the cubes), then, if the "cube dimension" name is different from the "database dimension" name, then I get an error in the string parsing (referring to the first element in the "Allowed Set", which cannot be found in the cube, since the name is not correct). I'd like to have a confirm if it is a bug ...Show All