Database Server Performance Against SSIS Process
Dec 18, 2007
Dear all,
Right now I built an SSIS package to transform data from external source into local database server. I schedule it to be processed at that database server (ex Server A). Is there any difference performance if I replaced the SSIS package to be processed at another server (ex Server B) ? I'd like to separate the process because I want to reduce workload in Server A by removing the SSIS process to Server B. Am I correct ?
Thanks in advance,
Hery
View 2 Replies
ADVERTISEMENT
Oct 6, 2015
We are using SQL Server 2014 and SSDT-BI 2013. We have a reporting environment where business users create objects which need to be persisted for fiscal year reporting. Let's say for instance SQLSERVER1SRVR1 they create table objects like below in the reporting environment.
Accounting2014, Accounting2015 in AccountingDB;Â
Sales2014, Sales2015 in SalesDB;Â
Products2014, Products2015 in ProductsDB;Â
Inventory2014, Inventory2015 in InventoryDB etc....
These tables are persisted for auditing in a different environment SQLSERVER2SRVR2 for finance & audit folks.We would want to automate this process using SSIS to create tables in corresponding database and load data. I tried using For Each Loop container but the catch is I could loop the source or destination but how do we loop on Source & Destination at the same time (i.e when source is in AccountingDB destination to be AccountingDB, source SalesDB then destination SalesDB so on etc....
View 6 Replies
View Related
Nov 16, 2006
Can a SSIS server, i.e. staging server be used to create packages that update SQL Server 2000 Analysis services objects on another server running SQL server 2000 OLAP?
It appears that the OLAP connection manager ion SSIS supports only connections (and thus updates) to 2005 OLAP objects. I work for a company that has a huge investment in a 3rd party DW that uses Analysis Services 2000. the DW tool vendor will not support an upgrade to SSAS 2005. We wish to extend the DW from other data sources. My thought was to use a staging server with SSIS, used solely as the ETL tool for all new development (thus no more DTS development), and to update the sql server 2000 operational data store on another box.
I can find no documentation on how to process sql server 2000 analysis services objects from within an SSIS package. Any ideas?
View 4 Replies
View Related
Apr 1, 2008
Hello
I'm trying to run a task that executes a script file (cmd). When i run it with in bids with my own users (domain admin) it works. When i start a cmd prompt and try to run the cmd file directly from the network location where it is it works (with my own rights and with the sql server agent user).
Now when i try to run in from smss > agent jobs > job and run job it never completes. Im not getting any error message either it just keeps on running on the step ??? It seems like a rights issue, but the account running the sql server agent is able to execute the cmd file directly from the command prompt.
There are no errors in any error logs anywhere and no error is displayed...
Ps. Im running the job step as a integration service pacgake.
View 8 Replies
View Related
Jul 21, 2015
know if running performance counters during ETL process is running will impact performance on the server, I have win 2008 r2 server with sql server 2008 r2. I want to measure performance of server while ETL is in progress.
View 4 Replies
View Related
Feb 7, 2007
I can't find 'SQL Server: SSIS Pipeline' performance object in performance monitor on a 64-bit SQL Server. I see it on a 32-bit. Does anybody know why?
Thanks
View 3 Replies
View Related
Mar 24, 2006
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.
Thank you for your help.
View 3 Replies
View Related
Jul 28, 2006
Hi
I have a database that stores all the data in one table that is only the data we are required to store hence it didnt require any other tables
the table has five colunms
Orderid, ordercost, orderdate, ordersystem, orderref
it is used in an web application that seraches for all the rest of the information using an orderid and displays the rest of the details the size of the table currently is about 123000 records that is increasing by 20000 every week.
i have an archive of the same data that has 7,666,000 records that are also going to be placed into the same database.
my question is is that a good idea of doing this?
are there any performance issues that i need to be aware of as currently the application runs quite quick?
View 15 Replies
View Related
Sep 21, 2006
Hi there.I'm on a SQL 2000 SP4 machine.This is a development machine, with only a couple of small databases onit.Yesterday I needed to recover a table from backup, so I went throughthe following process:-Used 'Create SQL script' to generate a create db statement from thecurrent db.-Changed the db name, and all file paths to not conflict with thecurrent db.-Created a new db in a new directory with this script.-Restored last night's backup from the 'real' db to the new one.-Went into the 'recovered' database, located the table which needed tobe restored, and renamed it to "<table_name>_RECOVER"-Used DTS to transfer that table to the 'real' database-Truncated the table to be recovered-did a 'Insert into select * from' statement to recover the records.-dropped the table that I had copied in via DTS. This table was small -8000 rows or so.The database seemed fine at the time.Now (the next morning) I am finding that performance of the 'real'database is agonizingly slow. Even doing a 'select count (*) from <asmall tablesimply does not return a result. the 'processing' globeicon spins merrily away, and I get no result set.However, the same query, submitted against the 'recovered' database(the one I restored in order to get the data I required) respondsinstantly, as it should.These databases are both hanging off the same named instance of thisserver (there are three instances).A quick Perfmon check shows the CPU to be nearly idle.I'm not sure what to look at here - can anyone suggest a direction?Thanks!
View 3 Replies
View Related
Mar 15, 2006
Hi there.
I´m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.
The tests i´m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.
My question here is: What if i use Sql Server failover mechanism?
I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?
View 8 Replies
View Related
Feb 27, 2007
I would like to have a SSIS package which loops through each xml file (.xml files) in a folder on the network. And then for each file pull out the data and insert into a sql server table.
Please kindly guide me through this i.e. What task(s) are required, etc.
Thanks
View 1 Replies
View Related
Feb 8, 2006
There is a function called "proactive caching" in Analysis services. It can:
----Automatic synchronization with the relational database
----No more explicit "cube processing
But I cannot have the latest data in the cube even I set the proactive mode as "real time"
Do I need SSIS to process cube in this case?
Following is the procedures I have done:
1. test the data
1.1 use the bi dev studio to browser the cube, ensure no new data are there
1.2 process the the cube and browser the data, ensure new data are there
1.3 delete new data from source database and reprocess the cube, ensure no new data are there
1.4 add new data again
2. configure the proactive setting of cube
2.1 use sql server management studio to open the cube and open the properties window
2.2 in the option of "proactive caching" select "low-latency MOLAP" (even real-time ROLAP later), then click ok
3. configure the proactive setting of cube
3.1 open the patitions view properties window
3.2 in the option of "proactive caching" select "low-latency MOLAP" (even real-time ROLAP later), then click ok
3.3 in the notification tab, select "sql server " and specifiy tracking tables to the "fact table", which is a view to get data from real fact table.
4. wait a period of time...
5. test the data again
5.1 use the bi dev studio to browser the cube, but no new data are there (even I selected real-time ROLAP later). I even tried the reconnect and refresh options in the tool bar
So my questions are :
1. Did I do the right thing to achieve the target "Automatic synchronization with the relational database "
2. Can I monitor the procedure of synchronization, such as monitoring the log of processing, viewing the schedule setting and status of the process?
Thanks a lot!
View 5 Replies
View Related
May 9, 2008
OK, I just want to know if I can use SSIS to open one text file create a table with the info in
SQL Server. Then open another fixed length file and insert rows in this table. I want to do this from an application in .Net. for e.g. I have a file that says "Col1 String 20, Col2 String 20,". This will create a table "Table1" in database DB1. then it will open a text file that has 200 rows for Col1 and Col2 with fixed length as 20. The table will be filled with the rows in the second text file. I want to give the user the ability to select the above files and when he clicks submit, the table will be filled. Is it possible?
View 1 Replies
View Related
Nov 13, 2006
Hi,
I would like to process an AS 2000 cube in SSIS.
I'm using the 'Analysis Services Processing Task', but I just get the error 'A connection cannot be made. Ensure the server is running'.
It would be an unfortunate limitation if you can only process 2005 cubes in SISS. Does anyone know if its possible to process 2000 cubes in SSIS?
Many thanks
Jeremy
View 4 Replies
View Related
Apr 28, 2006
Hi,
I'm working with SSIS and I would add the Integration Service project to the daily build process but I need to know how to generate the <name>.SSISDeploymentManifest other then invoking the devenv.exe.
Thanks,
View 3 Replies
View Related
Mar 19, 2008
Hi I am using VWD 2008, SQL Express 2005, Reporting Services, Win-XP, IIS5Basically let's say I have 2 pages:Page1: has a SQLDataSource control that populates a GridView from a table from a database file myDB.mdf (no code behind)Page2: has a reportviewer control that show a report with data from the same table from myDB.mdf from the reportserver, (no code behind)I have attached myDB.mdf to the SQL Server Express using the SQL Server Management Studio Express.If I first open Page2 to display the ReportViewer it works ok. or using the Report ManagerNow this is the problem:If after that I try to open Page1 then a get an error message:Cannot open user default database. Login failed.Login failed for user 'myServerASPNET'. Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.Login failed for user 'myServerASPNET'.Then I have to restart the SQL Server to fix it,Now I can open Page1 ok, but if after this I try to open Page2 (ReportViewer) againThen I get this error:" An error has occurred during report processing. o Cannot create a connection to data source 'my_Datasource'. &And this error if open the report using the report manager:" An error has occurred during report processing. o Cannot create a connection to data source 'my_Datasource'. § Unable to open the physical file "C:InetpubwwwrootWebsiteApp_DatamyDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)". &Now if i check the Management Studio Express again, you can see that myDB.mdf was detached. It seems to be there by it has no Tables or definitions, so I have to attach it again..Do you know how to fix this?Thanks in advance,Ed
View 5 Replies
View Related
Mar 26, 2008
I have a command to decrypt a file that I can run from the command line and it works beautifully. However, when I stuff it into an execute process task, it errors out every time or does nothing.
Here is the command I can run from the command line:
gpg -d --passphrase-fd 0 < c:Dip_Fif.WUZ -o c:Someoutputfile.in1 -r "KeyName" c:Someinputfile.in1.pgp
I've pointed the execute process task object to the gpg.exe executable on my system and am stuffing the remainder in the arguments line. I have also tried changing around all the timeout settings and sucess values. I have found I can change the success value to 2 and it will show up as being green when complete, but the file doesn't decrypt. It just in turn will throw an error on the next piece because the required file is not there.
I will probably end up writing a script to get this to work and use a script task but I really want to know why this will not work.
--Thanks--
View 12 Replies
View Related
Jun 29, 2006
Anyone seen this SSIS error when importing data? I have a 64bit quad processor with 8gb and am importing from Oracle 9 using 32bit DTExec.exe from the command line.
OnInformation,Myserver,MyDomainSQLAdmin,J001OracleDimExtract,{CEB7F874-7488-4DB2-87B9-28FC26E1EF9F},{1221B6EB-D90A-466E-9444-BA05DBC6AFD8},6/29/2006 10:58:08 AM,6/29/2006 10:58:08 AM,1074036748,0x,The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Thanks
View 5 Replies
View Related
Apr 29, 2015
I have a ETL ( SSIS ) Process in which i am loading around 150 tables in each run. ( Truncate and Insert ). I have four packages each from different sources. ( Each package loads different tables and different numbers )These are run on weekly basis one after the other. Each package is taking around 60 to 90 minutes each. Now i want to track the progress of the ETL on my front End application.Â
We want this in two ways.Â
First Way : I need to show the user how much percent of ETL Process is completedÂ
Second Way : I need to show the No of tables completed and how many rows have been completed in the ongoing table ( which is in process )
how to design the table and ssis process.
View 3 Replies
View Related
Jun 23, 2006
It is possible to program part of the process of load of data within the SSIS. The origin is a Flat file (.txt and .dat) and the destiny a SQL Server 2005. All the fields of the file are not mapean origin with the destiny table and data are needed other tables that are in the Data Base.
View 1 Replies
View Related
Jan 11, 2008
Hello,
At First sorry for my English.
I have a Problem with a SSIS Project with a Process Task.
If I start the Project in Visual Studio then it work with no problems. But when I start it in a Order with
the Sql Server-Agent, its run infinitely.
There is no error message, the Server Agent says that the job work, I can see the .exe file in the task manager.
but it will no really executed.
What can I do?
View 13 Replies
View Related
Feb 20, 2008
Hi
Can anyone help me in for the following.
i want to execute a exe file with two variable parameters
Executable : C: empMyExe.exe
Parameter1 : User::Category type is string (below example A)
Parameter2 : User::Amount type is string (below example 1)
in dos it looks like this
c: empMyExe A 1
This will executes fine.
Thanks,
Madhu
View 1 Replies
View Related
Jul 22, 2006
I have an Execute Process Task within my package that executes a BCP command which outputs the resultset of a query to a file in the network share. It works fine most of the time, however sometimes for unknown reasons, the following error message gets logged in my log table -
In Executing "c:Program FilesMicrosoft SQL Server90ToolsBinncp.exe" "Select Comments, SoldToCustomerNbr, ProductGroupingCode, ProductGroupingName, RevSumCategoryCode, RevSumCategoryName, ValidFromDate, DTSCollectPct, DTSPrepaidPct, DTSPickUpPct, DCCollectPct, DCPrepaidPct, DCPickUpPct From ShipmentTypeCustomerBlend" queryout \xxxLOGShipmentTypeCustomerBlendLog_060719201440.txt -c -t" " -SDummyServer -T -e"d:SSIS Error LogsJob ExecutionBcpErrors.log" at "", The process exit code was "1" while the expected was "0".
The above error was captured from the System::ErrorDescription variable, by the error event handler, that was attached to the Execute Process Task. This error does not help me to debug the issue.
On running the below statement from the command prompt, i get the actual error message, which is the expected behavior -
"c:Program FilesMicrosoft SQL Server90ToolsBinncp.exe" "Select Comments, SoldToCustomerNbr, ProductGroupingCode, ProductGroupingName, RevSumCategoryCode, RevSumCategoryName, ValidFromDate, DTSCollectPct, DTSPrepaidPct, DTSPickUpPct, DCCollectPct, DCPrepaidPct, DCPickUpPct From ShipmentTypeCustomerBlend" queryout \xxxLOGShipmentTypeCustomerBlendLog_060719201440.txt -c -t" " -SDummyServer -T -e"d:SSIS Error LogsJob ExecutionBcpErrors.log"
Expected Error Message -
Unable to open BCP host data-file.
This error message indicates that either the network path - \xxxLOG is not available for the output file creation or the file - \xxxLOGShipmentTypeCustomerBlendLog_060719201440.txt could not be created for some reason.
I 've tried to capture the error message from the StandardErrorVariable and the StandardOutputVariable properties of the ExecuteProcess Task, but in vain.
Is this a bug ? If so, is there a way to get the actual error message from the task ?
Thanks,Reni
View 5 Replies
View Related
Jan 29, 2008
Trying to run a SSIS package from a SQL job, and the package itself has a step that calls a process task that runs a batch file. The syntax in the process task I have is the following:
executable: c:windowssystem32cmd.exe
Arguments: /C e:SungardPTAencryptfile.bat
Working Directory: e:sungardpta
I keep getting the following in my log:
PackageStart,MIMKEIMC11N,MI rustserviceadmin,PTADailyTransactionExtract,{46F7381F-B345-47DC-BFC0-17CCF02A935A},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:11 PM,1/29/2008 1:59:11 PM,0,0x,Beginning of package execution.
OnError,MIMKEIMC11N,MI rustserviceadmin,EncryptFiles,{FCF5B653-CC05-4183-981B-F5EF4906DD09},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,-1073573551,0x,In Executing "c:windowssystem32cmd.exe" "/C e:SungardPTAencryptfile.bat" at "e:sungardpta", The process exit code was "1" while the expected was "0".
OnError,MIMKEIMC11N,MI rustserviceadmin,PTADailyTransactionExtract,{46F7381F-B345-47DC-BFC0-17CCF02A935A},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,-1073573551,0x,In Executing "c:windowssystem32cmd.exe" "/C e:SungardPTAencryptfile.bat" at "e:sungardpta", The process exit code was "1" while the expected was "0".
OnTaskFailed,MIMKEIMC11N,MI rustserviceadmin,EncryptFiles,{FCF5B653-CC05-4183-981B-F5EF4906DD09},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,0,0x,(null)
PackageEnd,MIMKEIMC11N,MI rustserviceadmin,PTADailyTransactionExtract,{46F7381F-B345-47DC-BFC0-17CCF02A935A},{F82C7944-D28C-4F70-8CB7-F0BD7ED748D2},1/29/2008 1:59:12 PM,1/29/2008 1:59:12 PM,1,0x,End of package execution.
Any help would be greatly appreciated
View 4 Replies
View Related
Apr 7, 2008
i'm going nuts with SQL server notification thing. I have gone throigh this artical which tells how to set user http://www.codeproject.com/KB/database/SqlDependencyPermissions.aspx. This article show how to create new user and setup for sql server notification.But In my case user was alredy existing in database. which is very common senario in most cases. So i did following( check the SQL script below) but then i get this error
"A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
this my sql script
use [master]Go
-- Ensuring that Service Broker is enabled ALTER DATABASE [DatabaseName] SET ENABLE_BROKERGO
-- Switching to our databaseuse [DatabaseName]GO
CREATE SCHEMA schemaname AUTHORIZATION usernameGO
ALTER USER username WITH DEFAULT_SCHEMA = schemaname GO
/* * Creating two new roles. We're not going to set the necessary permissions * on the user-accounts, but we're going to set them on these two new roles. * At the end of this script, we're simply going to make our two users * members of these roles. */EXEC sp_addrole 'sql_dependency_subscriber' EXEC sp_addrole 'sql_dependency_starter'
-- Permissions needed for [sql_dependency_starter]GRANT CREATE PROCEDURE to [sql_dependency_starter] GRANT CREATE QUEUE to [sql_dependency_starter]GRANT CREATE SERVICE to [sql_dependency_starter]GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_starter] GRANT VIEW DEFINITION TO [sql_dependency_starter]
-- Permissions needed for [sql_dependency_subscriber] GRANT SELECT to [sql_dependency_subscriber] GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]
-- Making sure that my users are member of the correct role.EXEC sp_addrolemember 'sql_dependency_starter', 'username'EXEC sp_addrolemember 'sql_dependency_subscriber', 'username'
View 10 Replies
View Related
Oct 23, 2006
Hi
I couldn't find an 'SQL Server Agent' Forum , so I'm posting this here...sorry.
I'm using a job to run a batch file with several robocopy commands in it.
The step in the scheduled job runs a SSIS package saved in the Server Package Store.
The SSIS package uses an 'Execute Process Task' control flow item.
The 'Execute Process task' simply calls and execute the batch file. (WindowStyle property = Maximized)
The package/job runs fine in both Visual Studio and in Management Studio. However when run from the latter (SQL Management Studio Job) the command prompt window (dos screen) doesn't open so I can't tell at what stage the batch file is in at any given time.
How can I get the window to open? By the way the window does open if I run the SSIS from Visual Studio.
Thank you
View 1 Replies
View Related
Apr 22, 2008
Hi,
Can any one give me a solution how to call exe. Also, i need to pass 1 input to that exe.
I tried using Execute process task but it is not working.
My task is:
I have DFT , it generate a output file then after the output file generated i need to upload it SharePoint server.
So i developed one .exe using .net, C#. ( it is running fine when cmd line)
But i want to embbed in SSIS package.
Like :
DFT ( DataFlowTask) --------------------> OutPutFile.txt
EPT ( ExecuteProcessTask) ----------> Upload it to SharePointPortal.
Kindly give the solution ASAP.
Thanks
Senthil
View 4 Replies
View Related
May 12, 2015
I am trying to do robocopy of files from one server to another using SSIS package in order to automate and schedule the task.
So, int the Execute Process task editor I put the following
Executable:Â C:WindowsSystem32Robocopy.exe
Arguments:Â robocopy SourceServerNameE$BackupTestSource DestinationServerNameE$BackupTestDest
TestSource and TestDest are folder names,
And I want all the files in the source folder to be copied to the destination folder.
I am getting this error when I execute the task:Â The process exit code was "16" while the expected was "0"
View 6 Replies
View Related
Apr 10, 2014
I need find out the number of columns in flat file before i process that particular file.
I have file name in @filename variable and file path is @filepath variable.
But do not not that how i will check the column name in before i will process that file.
@filePath = C:DatabaseSourceFilesCAHCVSSourceFiles
And I am using for each loop container to read the file one by one and put the file name in @filename variable.
and my file name like
Product_20120607060930.txt
Product_20130708060930.txt
My file structure is:
ID,Name,City,Country,Phone
1,Riya,Pune,India,454564
2,Jiya,New Jersey,India,454564
3,Riya,St Louis,USA,454564
4,Riya,Belleville,USA,454564
5,Riya,Miami,USA,454564
Now what i have to do is i need to make sure that ID,Name,City,County,Phone is there in flat file. if it is not there then i have to send mail to client saying that file is not valid.
Let me know how i will do it.I need to also calculate the size of flat file.
View 0 Replies
View Related
Nov 3, 2015
Got a powershell script to split a large XML file to split in smaller chunks. I have Execute ProcessTask in SSIS with:Â
Executable: %windir%system32WindowsPowerShellv1.0powershell.exe
argument:Â -ExecutionPolicy ByPass -command ". 'C:WorkspacesSplitToytPMFile.ps1'"
I need to pass File Name as parameter to the PS script. I tried using the StandardInputVariable but it doesn't work.Â
View 11 Replies
View Related
Jul 10, 2015
I have an execute process task set up to run ftp.exe and a script argument. Â The ftp.exe is referenced in the executable field without a qualified path. Â The package just seems to know it's there relatively. Â I need to change this to run a secured ftp executable that I recently installed on my pc. Â I put the new executable in the WindowsSystem32 folder where the old ftp.exe is stored. Â But when I put the new executable in the executable field, it says the 'File/Process "FTPS.exe" is not in path'. Â I get the same error when I fully qualify the path. Â Is there something I need to do with the new executable for SSIS to pick it up without having to fully qualify the path?
View 8 Replies
View Related
Apr 19, 2008
Hi I am wondering if anyone knows of a way of synchroniseing two versions of a database one in access and one in sql server so they can both run in parrallel in ssis for updates etc.
Also can anyone recommend a book which is easy to use or resources as I can't find what I am looking for in the online help.
Thanks in advance
View 4 Replies
View Related
Apr 23, 2008
Hi all,I come up with a problem aleady discussed in some posts especially in the post http://forums.asp.net/t/1235761.aspx. but I got in not finally solved.The main problem is connecting to a database twice which causes errors. So I think this might be the right place to ask my questions. Here ist the problem in short:I'm using VWD on XP-Professional with SQL-Server Express and Reporting Services Express. All with Windwos Integrated Security and User Instance (SQLExpress). In my web-application I had for all Datasources the connect string:Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|mydatabase.mdf;Integrated Security=True;User Instance=True This works fine so far. I also have Reports designed with the Report designer using the same connection string. I can preview the reports and I deployd thre reports successfully. I want to call these report using a report viewer web control.Now the problem:when I call the remote remote report from my web-application, the database cannot be opened because an other process is using the database (see post mentioned above). It seems that this is a problem with attaching the database twice: from the sql-server and from the report-server.So I used now a new connection string without "attaching" the database (in vwd and in report designer):Data Source=.SQLExpress;Initial Catalog=C:inetpubwwwrootmyappAPP_DATAmydatabase;Integrated Security=True Now everything works...but ... only on my machine. On the production machine this does not work. I wonder anyway, why its working on my machine, because I never "attach" the database (there is no connection string with AttachDbFilename and I have not opend the developer and have not opened the sql management studio).On the production machine the concurrent (SQL-Server and Report-Server) connection to database does not work with either connection strings, no matter if i attach the database with sql server manager or not).This all drives me crazy for days now. All I want is to use SQL-server and Reporting- Services with its nice features and not repairing my tools. I thought I have a standard Installation, nothing special, but the standard obviously makes problems.Here finally my question:Does this environment ( web-application with remote reports) with Express edition cause normally no problems?Can I "attach" a database only once? How should I connect with my web application that uses the sql-server connections and also the report-server-connections (attaching twice does not work).What connection strings to use and when (AttachDbFilename or Initial Catalog).Are these problems specific to the Express Editions? I thank you in advance for any help Dieter
View 2 Replies
View Related
