I feel like ssis encryption model has a serious flaw. Especially when linked to SQL Agent jobs.
I have posted and others have posted messages about this. Something is plain wrong with ssis encryption keys and password protection. Also, you do not have the choice not to protect the packages. In my case, protecting packages is completely useless.
I created config files for al my packages connections passswords.
Now, by our IT Policy, I had to change again my password and of course, all packages now return multiple errors when I open them.
Hopefully, the config file did its job and the packages are ran anyways by SQL Agent, however, having to manually retype and resave all packages not to have the errors is just a plain hassle. Not to speak about people not using the config files and the correct "Run As" sql agent account.
I stress the fact that in a real world production environment all packages are driven by SQL Agent jobs and MUST run automatically.
Here is the error I get after opening a package after changing my password:
Error 1 Error loading Constants05.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:projectsssis packagesssis constantsConstants05.dtsx 1 1
So Why is'nt this key automatically adjusted after Windows NT Domain password Change?
How can I refresh the key, not to have to reype all the packages connections passwords and rebuilding, Checkin-in again all the stuff?
I do not think the solution is "Use an application account which password never changes when you create your ssis packages" however at this time, this is the only solution I can think of.
How do you guys deal with this problem?
I still do not understand the ssis security model I feel it is diconnected from the reality and unpracticable in a production environment like mine.
SQL Server 2005 is installed on a brand new 64-bit server (Windows 2003 x64 std. Edition, 2.4 Ghz AMD opteron- 2cpu, 8.8 Gb of RAM). There is barely few hundred rows of data scattered among few tables in one database.
SQL server and SSIS performace grossly degrades overnight and in the morning everything is slow including the clicking of tool bar selection.It takes 3 seconds to execute a simple select statement against an empty table.
It takes15-20 seconds to execute a SSIS package that normally would take 2-3 seconds.
But once SQL Server is restarted, everything returns to normal and the performance is good all day and then the next day everything is slow again.
I've tried setting the "Process Binary as Character" setting to true on the data source. I've tried cast and convert statements in the queries. I've tried conversion steps... Am I missing something simple, or is it really this difficult to get text values pulled from DB2 as text? I'm using Microsoft's OLE DB provider for DB2.
Anyone have experience with conversion problems pulling text from DB2 databases? Thanks in advance... Laura
I'm not sure if this is the right forum, but i'm having an issue where SSIS Variables window will not come up. The most i get is a gray bar that looks like it is supposed to hold the variables.
I've tried getting it to show by: 1) Menu --> SSIS --> Variables 2) Right Click --> Variables 3) Menu --> View --> Other Windows --> Variables
The same thing seems to happen for "log events" in Menu --> View --> Other Windows --> Log Events.
Does anyone know how to fix this? Is anyone else having this problem?!?!?!
Hello, I need some major help, I need to make a database using SQL server for a forum, now I am using pHpBB, but i need that database. I was thinking about it, it doesnt need to be complicated or anything. I really have no idea where to start so any help. Thank you in advance
I have a new business, and a part of that business includes receiving large amounts of data from time to time. I just found out yesterday that I'm going to be receiving about 1TB of data from an new client! I'm not set up at all for this large of a data set.
I want to use SQL Server as my database. Can I load SQL on a Desktop PC without having to buy a server? How?
I don't have a clue as to how I need to get set up for this data...hardware or software. Any advice you can give will be outstanding!!!!!
I have a site that was supposed to go live yesterday.I am using M$ SQL Express 2005 and the Express Manager.I setup everything using Windows authentication on my local computer. I backed up the database through the manager and simply did a restore to the live database server.I copied my aspx files and everything else.I changed my connection string to allow for SQL Authentication (because I was having trouble with Windows authentication).For some reason, my SQL authenticated user can do whatever it wants within the SQL manager, but I am unable to login to the site. I get no errors, just the usual failed login attempt text.Can someone please help. I don't know where to start on this one.Thanks,Joshua Foulk
I hope I haven't messed up! I was importing some data, and it started taking too long and seemed to have locked up, I did a cold boot and when I tried to open the db it would just load...
I have then detached it and tried to reattach the db, but it seems to just load forever.. I let it sit there for an hour and still nothing...
the DB has a 17gig LDF file and I can't attach without it...
For some reason when I'm trying to restore a back up, I'm encountering this problem, I've asked numerous people and been in and out of chatrooms all day and night, has anyone got any idea what to do?
Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array? Any comments are greatly welcomed.
I have created a database with three tables. The database has been up for a month now and contains about 20,000 records. In order to improve performance and resolve some issues I an attempting to change some of the table information. i.e. allow nulls in a few fields. When I use TSQL or the GUI to make these changes I get the following error: Timeout expired. The timeout period elapsed prior to completion of the operation or server not responding.
Source: .Net SQLClient Data Provider
I have SQL Server Express SP2 installed with .Net framework v3.0
Note: This issue appears when I attempt to delete a row from a table as well.
Maintenance Plans Broken Database Mail Broken Reporting Services Report Viewer web part displays US Format dates instead of report design English-Australian. . In Detail:
Microsoft SQL Server 2005 SP2 was downloaded from Microsoft.com. This download occurred after 5 March 2007 and the file version was 9.0.3042.1 (SQLServer2005SP2-KB921896-x86-ENU.exe) During the Installation of SP2 by a Domain Administrator an error occurred on the Database Engine. A Hotfix Exception was raised and the setup completed with errors. The Error Log reported a problem: ---------------------------------------------------------------------------------- Product : Database Services (MSSQLSERVER) Product Version (Previous): 2047 Product Version (Final) : Status : Failure Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log Error Number : 29534 Error Description : MSP Error: 29534 Service 'MSSQLSERVER' could not be started. Verify that you have sufficient privileges to start system services. The error code is (1067) The process terminated unexpectedly.
The SQL Server service could not be manually started. An error was reported in the event viewer: One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
It was noted that a new file mssqlsystemresource.ldf had been copied to the %data% folder. Note: As part of separating data and log files, both the master and mssqlsystemresource databases had been previously moved to another volume %data% whilst their respective log files had been moved to a separate volume also %log%. This had occurred after SP1 was initially installed and months prior to the SP2 install using Microsoft KB articles to detach and reattach the databases. It appears that the SP2 installer does not work correctly if mssqlsystemresource mdf and ldf files are not in the same location. Whilst the Engine could not be started €“ the new mssqlsystemresource.ldf file was manually moved to the %log% file, overwriting the previous version. The SQL Server Service was now able to start without any errors. Since the SQL Server Service has been started two major errors have become apparent: The following error is recorded when trying to create a new maintenance plan: TITLE: Microsoft SQL Server Management Studio ------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'from_msx'. Invalid column name 'has_targets'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
Also when using the maintenance plan wizard the following error is recorded when the Wizard attempts to save the plan at the end: TITLE: Maintenance Plan Wizard Progress ------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------
Database Mail is also broken on the server. The Database Mail Log reports an €œActivation Failure€? when a test email is sent. (the email does not arrive). The SQL Log and event viewer also report the following message: The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Another error affects Reporting Services reports that are accessed using the SharePoint V2.0 Web parts ( rsWebparts.cab) . The date picker in the Report Viewer web part now only recognises US format dates. It is important to note that the same reports run through the Report Manager web site work correctly using DD/MM/YYYY (English-Australian ) date format for the report picker. It is noted that the rsWebparts.cab file was not updated by SQL 2005 SP2 install. However, the behaviour of the Report Viewer web part has definitely been affected by the SP2 install. Are the first two issues related? Are they due to the fact that mssqlsystemresource mdf and ldf files had been moved to an unexpected location prior to SP2? How can this be fixed? Are the Reporting Services SharePoint (V2) web parts going to be updated? We are not in a position to upgrade our Portal 2003/SharePoint Servcies V2 installation at the present time.
Here is a fairly basic question in which I'd like assistance. In determining a standard for a schema, for simplicity, my thinking is that using the long data type would meet our purposes.
Are there any thoughts on mapping out where we would not need to take advantage of long vs short? I realize the space but for this particular table, space won't an issue.
I have a report with a table and three columns. Whenever the data in one of the columns cannot fit on a single page it continues on the next page BUT, there is no header on the next page and the data in the other two columns repeats itself on the next page. (This behavior happens when I export to PDF)
Is this a known issue? I have tried every setting I could think of.
I have a horizontal barchart with integer values, both positive and negative. Major gridlines are shown and I have not specified the interval or the label format. Sometimes the gridline labels (Y axis) show decimal values. Is there anyway to format these? I have tried to format the labels as "#0" but then I see labels occuring twice. I have also played with the intervals, but then sometimes, depending on the values, the zero line is not being shown.
Hi, We have a product that is developed in ASP and works with SQL Server 2000 or 2005. Since it€™s an ERP, we also use Reporting Services 2000 or 2005. Our application needs 3 registered DLLs that were, a long time ago, developed to support our entire application. Since we are using Windows Server 2003 x64 editions in our clients with SQL Server 2005 x64 edition, we managed to register the 32 bit DLLs in the 64 bit system. We installed them as a COM+ component, ran the command €œcscript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 true€? and our application worked fine. This command caused the IIS to use the .NET 2.0 32 bit version so that our DLLs could be correctly invoked. But now Reporting Services doesn€™t work because it needs the 64 bit version of the .Net framework. When I try to connhecto to localhost/reports, I get the error "%1 is not a valid Win32 application". Is there any workaround to this problem so that i can deploy the application and the database in the same machine?
Is there a way to short cut through the fields in a table when updating them or inserting? ie. I have a table with 78 fields and could grow with time. Is there a way to run the UPDATE or INSERT TO [table](blah....) without listing all the table fields?
Hello, i have database front end is Access, and backend is sql server. The front end, there are two dropbox, called 'CountyLongName', and 'CountyShortName'. I want to: when i select a countyLongName, there it will pop up the exact CountyShortName in anohter field.
For example, when i click Howard Couty in the 'CountyLongNmae', it will automatically display "HW" in the 'CountyShortName" Field. How can i do that? Thanks.
I realize that I am confused about SSIS and security.
In BIDS, I work on and modify my packages. That part I understand. Then, I want to build my project, then deploy to SQL Server 2005. I know how to do that too (for the most part, please see below):
My confusion arises around the "Protection Level" options in the package properties. Right now I have everything set to "EncryptSensitiveWithUserKey". My understanding is that I need to change this in order to run my packages from SQL Server jobs, because only the creator of the package can currently run the job.
So my question is, since I want to deploy to SQL Server, don't I want to change the "Protection Level" to "ServerStorage"?
However, it will not let me change the protection level to ServerStorage. It says "The protection level, ServerStorage, cannot be used when saving to this destination"... presumably because it is attempting to save to the file system.
However, even when I built out my packages and saved to SQL Server, I could not change the protection level either.
Which is why I am really confused... Once you save your packages to SQL Server, how do you make changes to the packages, so that the changes are reflected in the packages stored on SQL Server?
There is some concept I am not understanding here.
In DTS we had an Owner password and User Password. The User pasword allowed someone to run and schedule the package, but not edit the contents. Owner allowed full /designer access.
I like the two levels of password security. What is the equivalent of this in SSIS? I can only seem to get ProtectionLevel security to work with a single password that seems to be required to run, deploy and execute. Looks like an all or nothing proposition unless i am missing something.
I have simple package and trying to use the security feature of ssis. I set the protection level as "EncryptSensitiveWithPassword" and also specify the password. But it doesn't prompts me for the password. Can anybody help me in that?
I'm trying to insert data into locally stored database (SQL Server). The data I want inserted, is presented in a Treeview control and the data is fetched from a Webservice. The data is returned in form of a dataset. The treeview contains checkboxes allowing a user to select what to install in the locally stored database.
To sum up:
1. Get data from a webservice' not my problem 2. Present data in a Treview control' not my problem 3. Allow to user to select which data to install' not my problem 4. Insert data that the user has selected into my db' MY PROBLEM!!!!
The Treeview is generated with DataRelations between Group and Rule.
My locally stored database is designed by a third party provider and therefore the database must not be altered. The table I want to store data in is called "Groups" and it looks like this:
GroupID uniqueidentifier ' (newid()) GroupName nvarchar(50) ParentGroupID uniqueidentifier' if grouptype = 0 then ParentGroupID must have a value. GroupType tinyint ' 0 = subgroup, 1 = "top"group
The third party also created a stored procedure called pr_AddGroup taking the following parameters:
@GroupName ' can be both the RuleName and the GroupName @GroupType ' can be 0 for subgroup or 1 for "top"group @ParentGroup ' GUID
The problem with this stored procedure is that it does not have return value, which is here my problem actually lies. If it returned @@IDENTITY I could use this as the parameter for @ParentGroup. Instead I figure I must create two sqlCommand's (one calling pr_AddGroup and another calling SELECT @@IDENTITY to get the newly created record).
My SQL Commands look like this
Dim cmd As SqlCommand Dim Conn As SqlConnection = New SqlConnection Conn.ConnectionString = "Data Source=myServer;Initial Catalog=myTable;Integrated Security=SSPI" cmd = New SqlCommand cmd.CommandType = CommandType.StoredProcedure cmd.Connection = Conn cmd.CommandText = "pr_AddGroup"
dim cmd2 as SqlCommand cmd2 = new SqlCommand cmd2.commandtype = commandtype.Text cmd2.commandtext = "SELECT @@IDENTITY as ID FROM Groups" cmd2.connection = Conn
dim ParentGroupGUID as system.guid
To get the data inserted in the Groups table I would something like the following, but the code is very ugly (and it doesn't work either);
For Each Group In TreeView1.Nodes ' Loop through Groups If Group.Checked Then cmd.Parameters("@GroupName").Value = Group.Text.ToString cmd.Parameters("@GroupType").Value = 1
For Each Rule In Group.Nodes ' Loop through Rules. If Rule.Checked Then cmd.Parameters("@GroupName").Value = Group.Text.ToString cmd.Parameters("@GroupType").Value = 1 cmd.Parameters("@ParentGroup").value = ParentGroupGUID cmd.ExecuteNonquery() End If Next Next
I've spent the last 5 hours figuring out this problem, so ANY help is appreciated :-)
I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.
Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.
Here is some example code from my program: sub Test{ foreach my $table(keys %$HoH){ foreach my $field(keys %{$HoH->{$table}}){ if($table eq "CPU"){ my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName) Values ('$field', '$HoH->{CPU}{$field}{Name}', '$HoH->{CPU}{$field}{MaxClockSpeed}' , '$HoH->{Host}{SystemNetName}')"; print "$CPUstatement"; if ($db->Sql($CPUstatement)) { print "Error on SQL Statement"; Win32::ODBC::DumpError(); } else { print "successful"; } } }
I had a problem today where I could not see column names and alltables had a _1 after them when viewing a Sql Server view inEnterprise Managere.g.TableName Company when added to the view would be named Company_1 andthe only columns available were 1 which was *(All Columns)After looking through the news groups I saw several occurrences ofthis problem but no answers that gave a fixAfter some investigation I found that it is caused when the databasename in SqlServer has a . in it!Test1 >> Fine the view designer works fineTest1.6 >> Problems as listed aboveI don't see why Enterprise Manager allows database names with .'s ifit is going to create such problems.
CREATE PROCEDURE MyBooks_Selling ( @MemberID SMALLINT ) AS
SELECT * FROM v_BookInfo_Sellers_Extended WHERE MemberID=@MemberID
GO GRANT EXEC ON MyBooks_Selling TO bto GO
CREATE PROCEDURE MyBooks_Buying ( @MemberID SMALLINT ) AS
SELECT * FROM v_BookInfo_Buyers_Extended WHERE MemberID=@MemberID
GO GRANT EXEC ON MyBooks_Buying TO bto GO
Is there a way to make it so I could combine those 2 prcedures and choose which table i would like to select from based on another input parameter? I tried it that way but it didnt work...so im asking here to make sure
Hi all, I have a view with a datetime column, when viewing the column data the format is:
" 2006-06-12T12:00:00.0000000+03:00 "
while i'm only want to see the date part
(that is, 2006-06-12, and if possible in the format: dd/mm/yyyy) now, when i'm using convert( varchar(10), [column_name],101) i'm getting the format i need but the problem is that i'm "losing" the date type, is it possible to view it as the format i stated and still as a datetime type ??
This is a question concerning query optimisation. Sorry if it's a bit long,but thanks to anyone who has the patience to help - This is my first posthere...If I have two tables: 'tblContact' and 'tblCategory' where categories arelike:Code Name010101 Short010102 Fat010103 StupidThe junction table 'tblConCat' has fields CctConID, CctCatCode to tell mewhich contacts have which category codes. These are nchar(6) fields, if itmakes any difference.If I need to find all people who are short, fat and stupid I can see twoways:Solution One:SELECT tblContact.* FROM tblContact WHEREConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010101') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010102') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010103')Solution Two:Build a helper table which contains the codes I'm looking forSELECT tblContact.* FROM tblContactWHERE ConID IN(SELECT CctConIDFROM tblConCat INNER JOIN tblHelperON tblConCat.CctCatCode = tblHelper.HlpCatCodeGROUP BY CctConID HAVING Count(*)=3)I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise theyboth provide similar working times (I wait about a second) but I thought thefirst looked rather inefficient and thought my helper table might help.There are about 30,000 contact records, 180 category records and 120,000junction table records.All I am looking for comments on any pros and cons of these two approaches -does one look that bad? The database was migrated from Access where thehelper table really did help, but using SQL Server I might not need it.Thanks again, if you got this far!
