I am trying to configure an SSIS connection manager to recognise a SQL Server 6.5 database but I can't get it to work. Testing the connection works but I am unable to select a database from the drop down list.
Which OLE DB provider should I use to access a SQL Server 6.5 database?!
_________________________________________________________________________________________________________________________
Inability is a disaster; patience is bravery; abstinence is a treasure, self-restraint is a shield; and the best companion is submission to Divine Will.
In step one of this Blog ,what's this mean Step 1: Create or obtain a blank SQL database instance and how do i go about doing it http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
Since I activated the aspnet_regsql.exe tool, it has been a bad day - here's why:
Originally I installed VS2005 Pro (without Sql Express) and then Sql Server 2005 Dev. I then started to learn asp.net from the ".NET FRAMEWORK 2.0 Web-Based client Development" Training kit from Micorsoft. I have compiled and run every exercise up till Chapter 9 lesson 2 exercise 1, without the need to start the Sql Server 2005. In exercise 2 I should learn about asp.net profilers and the exercise showed me to use the aspnet_regsql.exe tool if I was using Sql Server 2005 - and so I did, and exercise 2 worked fine after a couple of adjustment in the connection string, because it was configured to use the SQLEXPRESS server by default.
However - in Chapter 9 lesson 3, I should not use asp.net providers, so I shut down the Sql Server 2005, because I don't have that much memory to spend. Now the trouble comes - every website I have made afterwards require that the Sql Server 2005 is running or else I get some errors.
How can I get back to the way it was before I activated the aspnet_regsql.exe, so I don't have to have the Sql Server 2005 running every time I want to make a website using aspx?
I have a DTS package which is using OLEBD provider for Oracle, it was working fine. I tried to open this package and I got this error message "There was a problem retrieving the list of OLE DB Providers"
I tried to intall MDAC8.2, it didn't help, should I reinstall SQL Server?
I lost in SQL Server 2000 with SP3a on W2K Server Ole DB Provider. When I trying make new linked server I cannot select Provider name, because menu is empty. Existing link between SQL servers returning error message Error 7302: Could not create an instance of OLE DB provider 'SQLOLEDB'. OLE DB error trace [Non/interface error: CoCreate of DSO for SQLOLEDB returned 0x80004002]. I tried completly uninstall SQL from server and I installed server again without success.
I have Windows server 2003 SR2 and wich to install on it OLE DB Providers. I mainly have an application that needs OLE DB provider for Excel and Dbase to access read excel and dbase Files on disk. I have searched all over the web for a download link. I installed MDAC on the server and restarted bu still no luck. My ODBC applet in the control panel only shows the SQL native drivers.
PS: Windows is 64 bit. I believe this might be why. But what can I do in this situation?
I have installed the latest 64-bit Oracle10g Release 2 ODAC for Windows x64 (10.2.0) client software in a 64 bit server but I am having the following issues:
After I installed the software and I try to create a new connection in SSIS using the OLE DB or .NET provider, the providers do not show as an option in the connections.
I believe I am missing one step in registering the driver in the server but I do not known where, Could it be a permissions issue?
In addition, I tested the ODBC connection and I had to declare the path for the Oracle path in the environment variable manually since it was not set during the installation. ODBC connections are working correctly.
I am planning on using SSCE in a desktop embedded scenario. In considering deployment options, in addition to the SSCE runtime, I would also need to bundle either MDAC (OLEDB) or .NET Framework 2.0 (ADO.NET).
From the installer footprint standpoint, it's a no-brainer since MDAC is much smaller in size than .NET Framework. However, an article from the MSDN library (http://msdn2.microsoft.com/en-us/library/aa719514(VS.71).aspx) talks about the .NET Framework Data Provider for SQL Server and how it performs much better than the OLEDB counterpart.
So my question is, how much of a performance difference is there between using the OLEDB provider vs. the ADO.NET provider? I am primarily concerned with insert speed.
Also, it does seem a little silly when you need a 22.4Mb installer (.NET Framework) for a provider to a database that is under 2Mb in size.
For the second time in as many months I've been asked the question. What is the license requirement for me, the service provider, for offering my clients the ability to use SQL Server Express?
I understand the license to say that as long as I'm registered (no cost) there is no further licensing needed in order to offer this to my clients.
Could someone clarify this for me? If I'm incorrect I'd appreciate the right answer.
I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
"pRecordSet" is an ADO recordset. The database column "MyColumn" is of type "decimal(19,10)".
The most important question for me is, if the regional settings of the database server or the regional settings of the client PC are considered during the conversion from the string to the decimal value. For example in standard French regional settings the "." would not be recognized as decimal separator.
I am also wondering if the language of the database instance, in which this data is saved, is considered during this conversion or any other settings of this database instance.
So my general question is: Does anybody know exactly what rules apply during the above mentioned conversion?
My vendor requires data to be sent in Excel format. Some of my tables have rows over 65,536 so I need to use Excel 2007 (Max of 1,048,576). Right now my data sits in SQL 2000. I am using MS SQL Enterprise Manager 8.0 to prepare the data. Is there some kind of add on or selection I am missing to use DTS to export from SQL to Excel 2007?Thanks in advance.
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData ( Id BIGINT, --is actually an identity column Manifest_Id BIGINT, Doc_Num BIGINT, Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X) FROM #InvLogData GROUP BY X HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column: thisisavalue thisisanothervalue thisisanothervalueagain shortval
replaced with xxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx xxxxxxx
My current function is replacing the data like this: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
While executing the tasks, I got the error Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
I have a column in SQL server which is of type ntext. Selecting the specific column to view it in report builder, an error message appears with the following description:
- Cannot run this report. The grouping expression 'nameofcolumn' returns the datatype binary. The Grouping Expression cannot return binary data.
Report Builder recognises this as if it was an image...
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios: Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import. How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server. I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do. Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task. I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert. Thanks
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
Hi I have written a piece of code for Login form which reads the user id and password from db. It works fine with the Sql server 2000 but I get a error with Sql server 2005. SqlConnection conn = new SqlConnection("Data Source=D\SQLEXPRESS;Initial Catalog=model;Integrated Security=True"); SqlCommand cmd = new SqlCommand("Select * from JsLoginDetails", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if ((Login1.UserName == dr.GetValue(0).ToString()) && Login1.Password == dr.GetValue(1).ToString()) { Response.Redirect("MainJs.aspx"); } else { Login1.FailureText = "Invalid Userid Or Password"; } } dr.Dispose(); conn.Close(); } I get and error Invalid object name 'JsLoginDetails'. pls help thnksdiv
I took our SYBASE DDL and created the tables on an SQL Server Express datbase on a PC and then downloaded our data from UNIIX using the WinSCP utility and BULK Inserted the data into the SQL Server database and everything uploaded fine apparently, but when we go to use the data we are getting some data differences.
Do you know of any data conversion issues that may be coming into play here maybe?
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name) Dim selectSQL As StringDim con As SqlConnection Dim cmd As SqlCommand Dim reader As SqlDataReader Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;") Dim username As String username = Request.QueryString("username") selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con) con.Open() reader = cmd.ExecuteReader() lbl.Text = selectSQLCatch ex As Exception lbl.Text = ex.Message Finally If (Not con Is Nothing) Then con.Close() con.Dispose() End If End Try End Sub '------------------------------------------------------------------------------------------------------------------------------------------------------------------------- May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password