I am runnning a package which loads 3 million records, if my package fails after loading 1 million records, How do I execute my package in order to load the records from 1 million record? My guess is checkpoints will not help in this case. Do we have any other option to recover the session?
View 5 Repliesin terms of functionality, are there major differences between the two?
View 7 Replies View RelatedHi,
I worked with informatica before and bit new to SSIS,
i have one simple question..
How can i achieve the same functionality as External Procedure Transformation (Informatica) in SSIS?
like..i want to use a DLL in my package which contains some set of business rules.
Please let me know.
Charles
i have a problem connecting to sql server. I want to connect to sql server using informatica repository server.But i am unable to connect because i couldn't find the connectstring for sqlserver from informatica repositoryserver.
View 2 Replies View RelatedI have SQL 2005 mmirroring sucessfully working in an ASP.NET 2.0 web application. I also have session state being maintained in SQL Server using the built-in functionality in ASP.NET. The problem is, even with "allowCustomSqlDatabase=True" and specifying a failover partner in the DSN, it appears that ASP.NET does not work with the failover partner. It always tries to get the session info from the "data source" server specified in the DSN.
Is there a way to get the session state to fail over to the mirrored server without writing a custom session state provider?
I have an ASP.Net (C# 2.0) application that has been using SQL Server 2005 Standard Edition with Service Pack 1 to hold the session state in a testing environment. Currently, the session state is being stored in TempDB, rather than the ASPState database. This has worked very well for us until yesterday. We installed SQL Server 2005 Service Pack 2, as well as the Critical Update for Service Pack 2 (KB933508). Once the SQL server was rebooted, I got the following error message when I tried to access the web application.
The SELECT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.The SELECT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.The INSERT permission was denied on the object 'ASPStateTempApplications', database 'tempdb', schema 'dbo'.
In the web.config file for the application, I have a SQL username and password defined that can access the ASPState database. To correct this issue, I had to give this user db_datareader and db_datawriter access to tempDB.
Has anyone else run across this problem, and is it related to SQL Server 2005 Service Pack 2?
Hi all;
I want to implement "Auto disconnection" (or kill) of user session that exceed a specified idle duration in SQL Server 2005.
I know how do that in oracle by creating a profile and set the IDLE_TIME paramter and asign that profile to all users. But in SQL Server i don't know how to do it.
Can anyone help me.
Kindly
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
Hello, I am pretty new with SQL Server 2005.
I have installed SQL Server Express Edition. I have migrated a set of tables from Oracl10g (by using Microsoft's Migration Tool Kit).While I am trying the following simple update command, the session hangs and it never finishes !!!!!!!!!!!!
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
select pos_key from pos_station where staff_key = 1105
POS_KEY
=======
NULL
update pos_station set pos_key = 1 where staff_key = 1105
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The table has a few constraints and a couple of indices in place.
Then I create another table (but no contraints or indices), just copy the data from the problematic one and the update WORKS (in msecs) :
update pos_station_new set pos_key = 1 where staff_key = 1105
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Is there any way to tell if the table (any table in SQL Server) is corrupted or not ?
How can I tell if a session is waiting for something and what is that something ?
Thank you very much for your help.
Tom
Hi,
Does any know how to increase a session timeout for Report Creation uing SQL Server 2005 Reporting Services. Iam trying to export a report (to a .pdf) using SQL 05 reporting services. However, it seems that the the query is timing out coz of session-time out. Can anyone tell me how to increase this value ?
Thanks
Nik
We have 10 SSIS packages which are deployed on 2 servers. (at different geographical locations)
On both the servers we will install all the 10 packages, but on server1 we will enable 1 - 5 and server2 we will enable 6 to 10.
the idea is that if server1 goes down we enable all the 10 on server2 (and vise-versa).
Is this the best way to implement disaster recovery of SSIS packages? We want that in all cases of failure the SSIS packages are ready to process the ETL: tasks.
regards,
Abhishek.
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
Hi
I have a backup of msdb on sql 2005 version 9.00.2047
I need to use this backup to restore msdb on the same machine.
But the version has been upgraded to 9.00.2153
All of my attempts end up with the same messages
"...cannot be restored because it was created by a different version of the server..."
Thanks.
Followed this article to move my model and msdb databes but I think I messed up. http://msdn.microsoft.com/en-us/library/ms345408.aspx
The instance will not start because it says one of the files does not match it's primary file (not sure if it's model or msdb)
I can bring the instance up in single user mode (NET START mssqlserver /f /t3608) but when I try to do a query to re-do my alter statements, it says i can't attach because only one seesion is allowed. in 2000 i used to be able to start query analyzer without starting enterprise manager..
When i try and start with a minimal config (sqlservr -c -m) it won't start. I get an error saying that one of my files does not match it's primary file (either model or msbd don't know which) any ideas????????
Hi All,
Does somebody know where can I find a guide or white paper regarding to the subject?
It should contain the procedures needed to recover Analisys, Reporting and Integration services as well.
Thanks,
Miska
While working with SQL server 2005, as there is new resouce database which is hidden read only. According to books online we should be backing up that database as NTFS file backup (i.e. .exe file). In what type of secnario that we need Resource database required for SQL 2005 system recovery? Do we really need to backup this file and how often?
View 1 Replies View RelatedWe have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies View Relatedis bulk logged recovery model support point in time recovery
View 9 Replies View RelatedPages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies View RelatedHello,
Any body helop me in such respect that my log file was corrupt. I have mdf file only. Is there any tool to recover data from mdf file or export data to any other db.
thanks
Mansoor .net developer
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
View 3 Replies View RelatedWe have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
We are trying to restore from a backup (.bak) of a sql 2005 database with full text catologs to sql 2012.
We are getting errors saying that the backup file context are different from what we are trying to restore to, even if we try to create a new database from the backup.The new database is just a test database. The source is a production database with full cataologs.
The normal restore from a bak file does not seem to work.
We dont need the full text catalogs in the new database, but cannot change settings in the source production database.
Is there a way to take a backup of the sql 2005 database without the full text cataologs being included ? , without changing setting in the source database beforehand ?
If you are doing a disaster recovery of an entire SQL 2005 cluster, can you just install SQL server and restore the system database to get the configuration?
View 4 Replies View RelatedWe have SQL 2008 in development but only SQL 2005 in production. I have an SSIS package that was created in 2008 but need to deploy it to a SQL 2005 server. The '05 server will not import the package because of its version. Is there a way to convert back or 'save as' SSIS '05?
View 6 Replies View RelatedIs it possible to parm in a value to a SSIS
in my SSIS i have a variable;
Name : FileName
Scope : PackageName
Type : String
Value : ""
I have tried adding the following code in my vb.net project ;
pkg.Variables("filename").Value = "C: emp estfile.001"
pkg.execute()
but come up with the following error
A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll
Public member 'Variables' on type 'IDTSPackage90' not found.
can anyone help ?
Hi All,
Parameter passing in SSIS 2005 sometimes appears to be a cumbursome task. I have been digging into this topic for quite some time and here i note down some simple steps to demonstrate parameter passing at Package level.
(1) Create a SSIS project using Business Intelligence 2005 Or VS 2005.
(2) Create datasource (.ds) and Data Source View as required.
(3) A default SSIS Package by the name Package.dtsx is created. Double click this and you are shown tabs for Control Flow, Data Flow, Event Handlers, Package Explorer. On the Control Flow, drap and drop Execute SQL Task from Control Flow Items in the toolbar.
(4) Lets now create a variable at Package level. Right click anywhere in the control flow box (not on the Task created in Step 3 above). Click on the Variables on the context menu displayed. Variables window appears on the left of the screen. Click the Add Variable box in this window to create a variable. Name it var1 (or whatever you may like), Scope as Package, DataType as String and Value as MyValue. This is only the default value.
(5) Now let us edit the SQL Task created in Step 3. Double on it, on the General tab you can change its Name, Description. Set ResultSet as None. We shall proceed to execute a stored procedure by name MySPName and pass it a parameter. Set ConnectionType as OLE DB. Select the connection you creates in step 2. Set SQLSourceType as Direct input. SQLStatement as MySPName ? . Note the ? mark after the name of the stored procedure. This is important to accept the variable value (var1) created in Step 4.
(6) Select Parameter Mapping tab now. Click on Add button. Select the Variable Name as User::var1. This is the user created variable of Step 4. Select Direction as Input, DataType as Varchar and Parameter Name as @var1. Click on OK now.
(7) This sets up the basic of parameter passing. Compile the project to verify everything works. Right Click on the SQL Task and select Execute Task. This will execute the package taking default value of the variable. This can be used along with dtexec command with /set option to pass the parameter at command prompt.
Hi everybody.
I'm looking to upgrade to SQL Server 2005 (Standard Edition). I'm interested in using SSIS, SSRS and SSAS. I hear that BIDS runs inside of Visual Studio 2005, and comes with the SQL 2005 software. I currently have VS 2003. My question is: Do I need to purchase anything besides SQL Server 2005 Standard, or do I need to buy anything separately, like VS 2005? (if so, which version would you recommend?)
Thank you very much!
- Trevor
Hi all,
I need some solution..
When i execute a program it first deletes all datas from a
'X' table .Then fetches data from various other tables and
inserts into this 'X' table.I finally generate a report from
this 'X' table.
Now the problem is,
USER 1 : Executes the program - Datas in 'X' table got deleted and
filled up with new data.Report is getting processed.....
USER 2 : Executes the same program - Datas in the same 'X' table
getting deleted ..
So the report process of USER 1 got affected.
Solution 1 : Using IP address of local machine i solved the problem.But
i need a concrete solution for this ..something related to sessions..
CAN ANYONE HELP ??
Thanx and Regards
Prabhu
Does Session term exist in SQL Server?If yes, when it creates, how can monitor it. And where does itsinformation store?Thanks alot
View 1 Replies View RelatedHow do you return the ID of an item to a Session in a ASP forum?
I have a MS-SQL Server 2005 database.
My table looks like this QuoteID, QuoteNumber, Date.
In SQL:
INSERT INTO table (QuoteNumber, Date) values ('123490WC1", '06/2/2008')DECLARE @ItemID INTSET @ItemID=IDENT_CURRENT('table')
What I would like to do is return @ItemID to the ASP forum; I assume Session would be the best.
After I get the @ItemID I have a Grid which I will to insert the Quote Items into.
Thanks,
marly
Hi, Can somebody tell me what's wrong with this statement or why the Session parameter is not working.
<asp:SqlDataSource ID="datalist_SQL" runat="server"
ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString2 %>"
SelectCommand="SELECT * FROM tbl_subProduct, tbl_material, tbl_product, tbl_subCategory, tbl_topCategory, tbl_topLevelProduct WHERE [tbl_subProduct].[numProductID] = [tbl_product].[numProductID] AND [tbl_product].[numTopLevelProductID] = [tbl_topLevelProduct].[numTopLevelProductID] AND [tbl_topLevelProduct].[numSubCategoryID] = [tbl_subCategory].[numSubCategoryID] AND [tbl_subCategory].[numTopCategoryID] = [tbl_topCategory].[numTopCategoryID] AND [tbl_material].[numMaterialID] = [tbl_product].[numMaterialID] AND @fldArray ">
<SelectParameters>
<asp:SessionParameter Name="fldArray" SessionField="ids_array" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource> Many Thanks