SqlConnection Issues,Unable To Extract Data From Db
Oct 31, 2007
Hi all,
I've created a database named HyperCodexDB.mdf and I'm trying to extract some information from, I've no error, but the code after Open() method is even not executed:
Here is the Web.Config
and in the .cs file :
Hashtable names = new Hashtable();
int i = 0;
string conn_str = ConfigurationManager.ConnectionStrings["hcConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(conn_str);
SqlCommand cmd;
cmd = new SqlCommand("SELECT Nom FROM TableRecueil", con);
SqlDataReader rdr = null;
try
{
Label1.Text = "Hello"; ////////????Here changes the value of the label
con.Open();
Label1.Text = "Bye"; ////////????But here the value of Label1.Text remains "Hello"
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
names.Add(i, rdr["Nom"]);
i++;
}
Label1.Text = names[0].ToString();
}
catch (SqlException ex){ }
finally
{if (rdr != null)
{rdr.Close();}
if (con != null)
{con.Close();}}
Thanks in advance
Ali
View 8 Replies
ADVERTISEMENT
Oct 2, 2014
I have a MySql Database i need to extract data from based on matching info in a local SQL Server database,
I will ultimately need to cycle through 20 of these MySql databases , but the query below is taking 1 minute and 20 plus seconds...Just for one..
Is there a simple tweak that would speed it up?
USE [CAS]
GO
DECLARE @PhoneDB varchar(max),
@SQL NVARCHAR(MAX);
set @PhoneDB = '[PHONEDB_PHI]';
set @SQL = 'SELECT
[Code]....
View 4 Replies
View Related
Jul 30, 2007
hi everyone ive written a stored procedure which returns a value depending on the i/p parameterive executed it in the object browser but i cant extract it from the stored proc to the front end create procedure checking ( @username varchar(20), @password varchar(20) ,@result int out)asif @username ='admin'set @result = 1else set @result = 0 return @resultGOand this is what i wrote in the code behind SqlCommand check = new SqlCommand("checking",con);//checking is the stored procedure check.CommandType = CommandType.StoredProcedure; check.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar)).Value = TxtUsername.Value; check.Parameters.Add (new SqlParameter("@password", SqlDbType.VarChar)).Value = TxtPassword.Value; check.Parameters.Add(new SqlParameter("@result", SqlDbType.Int)).Value = 0;int status = ( Convert.toint32 )check.executenonquery();// the line which is prolly causing the error , can u tell me what should be written here? the status returns -1
View 2 Replies
View Related
May 3, 2008
<code>
public static DataTable GetCountries() { SqlConnection myConnection = new SqlConnection(ConfigurationManager.AppSettings["omegaloveConnectionString"]);
if (myConnection.Connect()) .............. problem
{ SqlCommand cmd = new SqlCommand("prcGetCountries", myConnection); cmd.CommandType = CommandType.StoredProcedure; DataTable countries = new DataTable(); countries.Columns.Add("CountryId", typeof(string)); countries.Columns.Add("Country", typeof(string)); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { DataRow dr = countries.NewRow(); dr[0] = reader["CountryId"].ToString(); dr[1] = reader["Country"].ToString(); countries.Rows.Add(dr); } reader.Close(); return countries; } else return null; } }
</code>
View 1 Replies
View Related
Jun 13, 2006
Hi everyone,
I have to extract, dayly a list of contacts on a exchange server in a table on our EDW on sql server 2005. Is it possible to get the information directly from a dataflow or i will have to developpe a script task ?
Need help desperatly !!!
View 3 Replies
View Related
Mar 28, 2008
Hi All,
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.
The SQL datable fileds are
i) ID - Int
ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)
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.
Please do help me out.
thanks
Sanra
View 4 Replies
View Related
Apr 23, 2007
I have lots of problems with the connection. I've connected many dropdownlists to the database and i didn't have problems at all. I also try an example of an ASP.NET book:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString= "<%$ ConnectionStrings:CIPPEC%>"
InsertCommand=
"INSERT INTO
[member] ([first_name], [last_name], [malefemale], [yearborn])
VALUES
(@first_name, @last_name, @malefemale, @yearborn)">
<InsertParameters>
<asp:FormParameter Name="first_name" Type="String"
FormField="FirstTextBox" />
<asp:FormParameter Name="last_name" Type="String"
FormField="LastTextbox"/>
<asp:FormParameter Name="malefemale" Type="Int32"
FormField="MFRadioButton" />
<asp:FormParameter Name="yearborn" Type="Int32"
FormField="YearDropDown" />
</InsertParameters>
</asp:SqlDataSource>
And it worked well. But when i try to connect my page with:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=glgrand-arSQLEXPRESS;UID="WindowsStartupUser";password="WindowsStartupPass"; database=CIPPEC")
connect.Open()
Dim cmd As New Data.SqlClient.SqlCommand( _
"INSERT PRUEBA (PersonaJuridica, NombreRazonSocial, Nombre, Segundo_nombre, Apellido) " & _
"VALUES (txtPerJur, txtRazSoc, txtName, txtSecondName, txtApellido)", _
connect)
cmd.ExecuteNonQuery()
connect.Close()
The error message says: Login failed for user 'username'.
I tried lots of things but it didn't work:
In SQL Configuration Manager:
Client Protocols: TCP/IP and Named Pipes are enabled.
In SQL 2005 Services: Server Properties: Built-in Account: Local System
SQL Server Surface Area Configuration: Remote connections: Using both TCP/IP and Name Pipes connection is selected.
In SQL Management Studio i created a new login glgrand-ar/UserStartupName , with sysadmin server role and user mapping my CIPPEC database.
I don't know what else i could try, i tried it at home and at work and i couldn't connect to my database and make an INSERT. It stops at Connect.Open() with the error that i wrote in this subject message.
It's evident that i'm doing something wrong (because i'm new): Could you please help me with a solution and explain me what are the data that i have to put on the Data.SqlClient.SqlConnection ?? I'm using the Windows authentication: i'm putting my username and password and it doesn't worked.
Thank you!!
View 1 Replies
View Related
Jan 22, 2008
I have a package which establishes connection with DB2 server.I dont have any db2 application where i can format query for db2.This is my query in access/sql.
can someone help me converting it into a db2 query.i tryed to google and use the functions but i failed and even the error that ssis gives don't help asthey are same for any type of error.
Thanks in ADV
SELECT table1.YYYY & table1.MM as MO_YR,
table2.CNTRYCD AS CNTRY_CD,
Sum(table1.AMT) AS [VALUE]
FROM table1
INNER JOIN table2
ON (table1.MM = table2.MM)
AND (table1.YYYY = table2.YYYY)
WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM
View 2 Replies
View Related
Jul 23, 2005
I need to get some data from an enormous, creaky old SQL 6.5 database.I know nothing about either the data schema (though I believe some sortof documentation exists), nor 6.5 for that matter, having come to SQLServer at 7.0.My clients need the data in comma delimited format.Please, can anyone suggest any possibilities? One thing that occurredto me might be to create an Access application, use an ODBC link to theSQL DB, and then leverage Access' not inconsiderable functionality toget the data out.Does anyone foresee any problems with this, or any better ways?Forever in your debt.Edward--The reading group's reading group:http://www.bookgroup.org.uk
View 2 Replies
View Related
Jan 10, 2008
I have the following data example,
Street
Forest Ridge Dr
Whites St
Alba Rd
I wish to achieve the following,
Street StreetSuffix
Forest Ridge Dr
Whites St
Alba Rd
I have the following code
INSERT Addr2 (AddressID, LotNo, FlatNumber, HouseNum, Street, StreetSuffix, Locality,
PostCode, [State])
SELECT LotNo, FlatNumber, HouseNum, SUBSTRING(Street, 1, (PATINDEX('% %', Street))),
LTRIM(SUBSTRING(Street, (PATINDEX('% %', Street)), 20)), Locality, PostCode, [State]
FROM Addr1
But i get the following result which is no good!
Street StreetSuffix
Forest Forest
Whites Whites
Alba Alba
What am I doing wrong in the Street & StreetSuffix Functions above?
I have this working with a cursor but I'm trying to work out a set based solution as the cursor
takes way to long to complete.
In the Cursor I'm performing the code below per row of AddressID
SET @SpaceLength = (SELECT PATINDEX('% %', @Street))
SET @Suffix = (SELECT SUBSTRING(@Street, @SpaceLength, 20))
SET @Street = (SELECT SUBSTRING(@Street, 1, @SpaceLength))
View 6 Replies
View Related
Aug 5, 2005
Hi, I'm hoping someone has an idea or two on this topic.
Basically I have three tables of data say tContact, tQuestion, tAnswer
tContact
-----------
ContactID
Email
Name
tQuestion
------------
QuestionID
Question
tAnswer
------------
QuestionID
ContactID
Answer
I need to extract the data for the client and they would like to see
the data with one line per contact, but showing every answer to every
question... they would like the data formatted like this:
ContactID, Email, Name, Question1Answer, Question2Answer, Question3Answer, Question4Answer, etc........
Obviously to get the data I cansimply do an outerjoin to get all
contact data then all questions, and answers that exist... but that
will obviously return tabular data with one row per each
answer... Does anyone have any ideas on how to do this using just
SQL? I can pull the data and write a function that spits it out
to text using the Stringbuilder class and some logic, but I'm thinking
this must be possible in SQL natively... any help would be more than
appreciated. Thanks in advance.
-e
View 1 Replies
View Related
Oct 5, 2005
hi there
i have a field name(fil_srt_cond) with the values of
cmpnt_name,ASC,1,2
cmpnt_stuff,DESC,2,3
i used the char index:-
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +1 ,3) ord,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +5 ,1) len,
substring(BSSF.fil_srt_cond,charindex(BS.column_na me,BSSF.fil_srt_cond) + LEN(BS.column_name) +7 ,1) str
to display them in their specific fields like
NAME STAR_CHAR LENGTH ORDER
Tag 1 2 ASC
however for cmpnt_stuff,DESC,2,3 i'm getting
NAME STAR_CHAR LENGTH ORDER
Stuff , , DES
therefore i'm not getting the required values 2 and 3.
can u pls help me to find a way how to get the data after the comma. thank you in advance
View 4 Replies
View Related
Mar 15, 2007
I've two table.......
One table Name Form1
Ex
----------
Name|F1No
----|-----
23wa|1
xyzx|2
abcd|3
......
......
......
---------
F1No====>primary Key
Second table Name Form2
Ex
--------
F2No
----
3
1
2
2
2
1
......
......
......
--------
F2No===>Foreign Key
i want top 3 Name(field) in first table(Form1).
Result
------
xyzx
23wa
abcd
View 7 Replies
View Related
Jul 23, 2005
Hello,Please see the original data below. I would like to extract the accountwith the earliest opening date only, and leave all the accounts openedafter that out. Could anybody help me with the query? Thanks a lot!Original Dataperson_idaccountopen_date1000111111115/15/20031000122222226/20/20041000133333332/16/2005Ideal Outputperson_idaccountopen_date1000111111115/15/2003
View 7 Replies
View Related
Jul 20, 2005
Hi,Anybody have an idea of copying data from tables of a database to anotherdatabase. It should be a choice to select all tables, single table orseveral tables.For them knowing Oracle it is possible to do it with 'exp', where you canchoose to script the database with or without data. So I am trying to getalike to MSSQL. The job is to unload data from a database with onestructure to another database with another structure.Thanks in advanceBest regardsTom Frank
View 4 Replies
View Related
Jan 30, 2007
Hello,
I have a table containing 3 columns Department Name, RiskScenario and Cost. I am trying to create a data extract that contains the top 3 Risk Scenarios (sorted by Cost) per Department.
I tried using this sql statement in MSQuery but it doesn't work. Any ideas where I'm going wrong or if there is a simpler way to do this?
Select * from (
Select DepartmentName, `Risk Scenario`, Cost, row_number() OVER (PARTITION BY DepartmentName order by Cost) rn
FROM 'Departmental Risks`) where rn <=3
Please help. Just can't figure this out!
Meera
View 7 Replies
View Related
Nov 12, 2001
Hi all,
I would like some information on extracting data from an ODBC connected database. (oracle is the current master database).
I can connect to the oracle database, and view tables.
But I would like to create a job (in SQL server) that runs every hour to retrieve current data from the oracle database.
Has anyone accomplished this, or is there a tutorial about this procedure?
Any help would be appreciated....
thanks
tony
View 1 Replies
View Related
Sep 28, 2006
Hi everyone. I need help extracting information from a concatenated field delimited by a '/' character. I know that the logic is to basically find the position of the '/' character and use the substring function to extract the data but I am unfamiliar with how to do this in SQL Server 2000. Please see examples below. Thanks in advance.
FIELD
----------
1234/4567
2345/6754
1234
I need to extract the data before and after the '/' character. There are records however with on the leftmost part of the data such as row number 3.
FIELD 1 FIELD 2
------------------
1234 4567
2345 6754
1234
View 3 Replies
View Related
Jun 30, 2015
I need to extract the information from the following Formula:
prj(10517)(a*w/100)+prj(10742)(a*w/100)+prj(10804)(a*w/100)+prj(10808)(a*w/100)+prj(10809)(a*w/100)+prj(10810)(a*w/100)
What would be the simplest and fastest way to do this...
I did write a fetch statement but i want to do something simpler as it is a bit hectic...
Basically i require the information from prj(10517) etc.
View 2 Replies
View Related
Jun 12, 2014
I need to extract records for the last day of previous months (Up till January of the same year) from a table SALES, according to a date parameter ASOFDATE that the user enters.
For Example
If user keys in ASOFDATE as 10-May-2014, I would have
ASOFDATE Data1 Data2
10-MAY-2014 123 443
30-APR-2014 222 234
31-MAR-2014 544 875
28-FEB-2014 546 908
31-JAN-2014 957 896
How do I do that?
View 2 Replies
View Related
Mar 11, 2008
I'd need to have a function that allows me to extract 'fields' fromwithin the stringI.E. (kinda pseudo code)declare @foo as varchar(100)set @foo = "Robert*Camarda*123 Main Street"select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'select EXTRACT(@foo, '*', 0) ;-- would return entire stringselect EXTRACT(@foo,'*' , 9) ;-- would return nullExtract( string, text delimiter, occurance)Anyone have something like this as a user defined function in SQL?TIARob
View 1 Replies
View Related
Apr 28, 2008
Hi,
I have just started learning SSIS. Could someone please tell me if where can I find step by step instructions on how to simply extract data from two excel files and populate the relevant table. What I simply want to do is:
Excel File 1 (With Columns FirstName, DateJoined)
+
Excel File 2 (with column Summary)
|
|
->Add these three columns to a new table called CustSummary
Any thoughts and suggestions will be really appreciated.
Thanks
View 1 Replies
View Related
Nov 17, 2007
In Integration Services I am trying to extract pieces of information from Excel. I would like to select just information from one cell but when I do that I get an error. What I'm typing in is:
SELECT *, Now() FROM [Sheet1$A1]
Here is the error:
------------------------------
Error at Data Flow Task [Excel Source [170]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine could not find the object 'Sheet1$A1'. Make sure the object exists and that you spell its name and the path name correctly.".
Error at Data Flow Task [Excel Source [170]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
The only way that I can get the contents of one cell is if I type in a range and indicate in the Connection Manager that the first row has column names. Given that, to get the contents of A2 I am having to type in:
SELECT * , Now() FROM [Sheet1$A1:A2]
This gets me what I want but by using this method I am never able to get information in Row 1. That could pose a problem in the future and I'm sure that there is a better way to do it.
So my question is, how can I get something from a single cell without having to name a range?
Two other questions I have are: 1. Is there a way to get information in one statement from cells that aren't adjacent to one another. For instance, if I wanted the contents of just A2 and C4?; 2. Is there a way that you could select a named range from Excel? That would be good because then I could have Excel control the range and then just call that.
I know this is a lot but I have an impending project where I'll need to do much of this and I have only ever imported the contents of an entire sheet and not specific cells from a worksheet.
Thanks.
View 4 Replies
View Related
Jan 8, 2008
First of all I am a novice here. I am working on a table with a column of URL. I want to seperate the data in the URL delimited by '/'.
Eg:
http://www.simpletech.com/upgrades/aopen/s661fxm/s661fxmintelp4/
Here I want aopen as manufacturer, s661fxm as model_number and intelp4 as submodel_number.
I solved this problem in Oracle using substring and instring. But I have no ides how to achieve this in SQL server.
Please..advice me.
Thanks in advance.
View 17 Replies
View Related
Aug 14, 2006
I have searched everywhere for an answer and seems I am either blind or there is no simple solution. Basically I have a need to report on data stored in an Exchange 2003 data store. There is mention of a OLE DB for Exchange 2000 but nothing for Exchange 2003. For example, A company report on time spent on various categories in a staff's calendar, etc. I would like to use SQl 2005 SSIS to extract and reporting services to report. Why is this so difficult? I come from a Lotus Notes / Domino background and there are numerous articles and ways to do this? Please can some MS guru please give me some direction on where to look?
Cheers
Damien
View 3 Replies
View Related
Aug 31, 2006
Hi all,
Having some fun with an excel import.
I have a excel sheet that has data that needs to be UpSert'ed into 2
different tables. In addition, I need to use a value in the spreadsheet
to determine the PK from a reference data table, for one of the UpSert
oprations.
That is all working now.
The thing I'm struggling with is something I am sure is quite simple -
but I'm not seeing a solution from attempts, googling or BOL.
2 of the columns I receive have either nothing, or X in them. The columns they go into are defined as BIT, NOT NULL.
So, in SQL it would be something relatively simple like:
CASE
When IsAvailable = 'X' then 1
When IsAvailable is null then 1
ELSE 0
end
But I don't know how to do this to data that was in a spreadsheet, and
now is a resultset being handed from a task to another task.
to outline my current solution:
---- table 1 = this all works -------------
Excel Source --> MultiCast (For Table 1)-->Data conversion for
table1-->:Sort for Table1--> Merge Join for table 1 (left Outer
join) as 'left' leg
Table1 Source --> Sort Table1 --> Merge Join for table 1 (left Outer join) as 'right' leg
Merge Join for table 1 --> Conditional Lplit for table1
Conditional Split for table1 (table1 source PK is null) -->Insert Into Table1 Destination
Conditional Split for table1 (table1 source PK is not null) -->Update Table1 OLE DB Command
---- table 2 = this needs to be able to convert X/NULL to BIT -------------
MultiCast (For Table 2)-->Copy Column for Table2 -->Data
Conversion for Table 2-->table3 lookup to get FK-->Sort for
Table2 merge-->Merge Join for table 2 (left outer join) as 'left' leg
Table2 Source --> Sort Table2 --> Merge Join for table 2 (left outer join) as 'right' leg
Merge Join for table 2 --> Conditional split for table 2
Conditional split for table 2(table2 source PK in null) -->insert into table 2
Conditional split for table 2(table2 source PK in not null) -->update table 2 ole db command
-----------------------------------------
Now, if I correct the spreadsheet to have 0's and 1's in the two
column, then the process above works. But I cannot (yet) force business
to do that.
If tried to use SQL Command for the excel source, but there is limited
functionality on the command - I cannot do SQL coalese, isnull or case
statements, which would allow me to resolve that data at source.
I've tried to use derived columns to alter the columns. I think that
the REPLACE (IsAvailable, VariableContainingX,VariableContaining1)
might work to change X's to 1, but that doesn't resolve the NULL issue.
I've tried to use a script component to handle the conversion - which
REALLY feels like a bad way to do this - the .Net script is wrote was:
-------------.net script code-------------
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Not (Row.EndOfRowset) Then
If
(Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X"))
Then
Row.IsDotComVanEnabled = "1"
End If
If
(Row.IsStoreCollectionEnabled_IsNull) Or
(Row.IsStoreCollectionEnabled.Contains("X")) Then
Row.IsStoreCollectionEnabled = "1"
End If
Row.NextRow()
End If
End Sub
End Class
------------------------------------------
I also tried it like this:
----------------1st draft .Net script code--------
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If
(Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X"))
Then
Row.IsDotComVanEnabled = "1"
End If
If
(Row.IsStoreCollectionEnabled_IsNull) Or
(Row.IsStoreCollectionEnabled.Contains("X")) Then
Row.IsStoreCollectionEnabled = "1"
End If
End Sub
End Class
-------------------------------------------------
Those both threw the following error;
-------------error information-----------------
Script Component has encountered an exception in user code:
PROJECT NAME: ScriptComponent_a333140d269b413bb1bddef390da7e16
Object reference not set to an instance of an object.
at ScriptComponent_a333140d269b413bb1bddef390da7e16.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at ScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at
xScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.ProcessInput(Int32
InputID, PipelineBuffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
-----------------------------------------------
Any ideas?
View 4 Replies
View Related
Apr 10, 2008
Dear all,
I have a table containing some information that needed to generate a XML file periodically.
Do anyone has idea how to achieve it in SSIS?
Thanks a lot!
lokman LM
View 6 Replies
View Related
May 8, 2001
Hi,
I am interested in writing a stored procedure that will compute and select data from SQL Server 2000 database and store it into a .XLS or flat file.
how can i do that ?
need help.
Thanks.
-- Parag
View 3 Replies
View Related
Mar 24, 2014
How to extract data from 3 excel sheets (same excel doc having multiple sheets with different # of columns & rows) using SSIS 2008. The end result will be 6 tables loaded in the database.
View 1 Replies
View Related
Apr 21, 2014
There is a simple SQL table ("mytable"). Say, it has 100 rows and 5 columns. One of the columns (say "time") contains timestamps across the whole day and the data in this column has the following format: hh:mm:ssAM/PM. So, the table looks like this:
time var1 var2 var3 var4
12:00:01PM value ...
12:00:05PM value
12:00:08PM value
12:00:20PM value
12:10:12PM value
...100 rows
How to create simple SQL request for extracting data between any 2 timestamps? For example, I need sub-table of the initial table containing all data values between 12:00:05PM and 12:00:20PM:
time var1 var2 var3 var4
12:00:05PM value
12:00:08PM value
12:00:20PM value
I consider something like this:
SELECT * FROM mytable WHERE time BETWEEN ...?
View 2 Replies
View Related
Apr 28, 2014
Let us suppose I've 3 tables in which 2 columns data are same in all 3 tables & rest fields are distinct
Table 1 :
ID1 ID2 Gender Code towncode
1 12 1 234 1234
2 13 2 543 5463
89 187 1 125 N21Q
Table 2 :
ID1 ID2 Relation Name DOB
1 12 13 XYZ 21/01/1967
1 12 1 QAS 01/10/1987
1 12 2 NHS 09/12/2001
2 13 1 NHG 10/01/1987
2 13 2 GHS 16/12/1999
89 187 2 KJA 31/03/2000
89 187 1 KLA 09/11/1986
89 187 5 KOP 19/12/2001
Table 3 :
ID1 ID2 Period Date
1 12 1 01/01/2011
2 13 1 01/01/2011
89 187 2 01/01/2011
I want result as :
ID1 ID2 Gender Code towncode Relation Name DOB Period Date
1 12 1 234 1234 13 XYZ 21/01/1967 1 01/01/2011
1 12 1 234 1234 1 QAS 01/10/1987 1 01/01/2011
1 12 1 234 1234 2 NHS 09/12/2001 1 01/01/2011
2 13 2 543 5463 1 NHG 10/01/1987 1 01/01/2011
2 13 2 543 5463 2 GHS 16/12/1999 1 01/01/2011
89 187 1 125 N21Q 2 KJA 31/03/2000 2 01/01/2011
89 187 1 125 N21Q 1 KLA 09/11/1986 2 01/01/2011
89 187 1 125 N21Q 5 KOP 19/12/2001 2 01/01/2011
May be some duplicate entry of same record is available in Table 1.
View 3 Replies
View Related
Sep 29, 2014
I'm trying to extract data from our database for the number of phone calls our reps are doing.
In counting the calls I only want to include up to 3 calls to the same customer (field name is CompanyID) per day - anything more than this is ignored.
The query at the moment is something like:
SELECT COUNT(CallID) AS CallCount FROM Sales_Calls WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' AND RepID = 1
Using MSSQL 2012.
View 13 Replies
View Related
Oct 26, 2007
Hi,
I have created one Linked server to fetch the data from Oracle server.
I have two tables at Oracle server
1. EMP_Tbl1 (Emp_Cd VARCHAR2(10))
2. EMP_Tbl2 (EMP_ID NUMBER, Emp_Cd VARCHAR2(10))
I can connect to EMP_Tbl1 table through my linked server at SQL Server 2005.
WHILE I cann't connect to EMP_tbl2.
ERROR:
The OLE DB provider "OraOLEDB.Oracle" for linked server "Linked_Facets" supplied inconsistent metadata for a column. The column "EMPID" (compile-time ordinal 1) of object ""SYSTEM"."EMP_ASH"" was reported to have a "DBTYPE" of 5 at compile time and 130 at run time.
OR "The OLE DB provider "OraOLEDB.Oracle" for linked server "Linked_Facets" supplied invalid metadata for column "JOINING_DATE". The data type is not supported."
Reason:
EXECsp_addlinkedserver
@server='Linked_Facets'
,@srvproduct='ORACLE'
,@provider='OraOLEDB.Oracle'--'OraOLEDB.Oracle'
,@datasrc='ameri.corp.ads.valuelabs.net'
I am using 'OraOLEDB.Oracle' as the provider. This has its own limitations. How to conquer this?
View 1 Replies
View Related