Bcp Command To Give Colums
Jan 8, 2004Can someonte tell me that if i
bcp bda..mytable out c:discounts.xls -c -p , how can I put the first row as my column names since I get only data
ADVERTISEMENT
Rows As Colums
May 8, 2007I know this is possible in DB2 and Oracle, but what about for SQL-server 2005
1) select X number of rows from table1
2) I need colums for each row of table1 in a new table
3) As such, Select (select * from X where x.id = @ID), a,b,c from table Y where y.Id = @ID
And I dont want to use IfExists.
Thanks
DK
Query Across All Colums
May 18, 2004Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassa
Updating Colums
Nov 20, 2006Hello all.
I have a column named 'RELATED' of type text in a table. I want to update the contents of this colums...........but still keep what is currently stored in it. Basically i want to add some text onto the end of the string it currently contains.
Any help would be much appreciated.
Pivoting Row Values Into Colums
Oct 11, 2004I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:
Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91
I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%
This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.
But is there a way to “Pivot� or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.
I can restructure the database, if needed.
I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.
It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?
Convert Rows To Colums
Aug 26, 2004I am getting this from my client
TableA
StorID LineNumber Text
30000 1 ClientName
30000 2 ClientSurname
30000 3 3333333
30000 4 20-05-2004
I actually want this to look like
TableB
StoryID Name Surname Policy Date
30000 Name Surname 3333333 20-05-2004
Can you help with the script to convert this to one record with many fields (as in TableB) if this is
Repated Colums Returned
Jul 23, 2007Hello All, first time post here and really stuck on this issue.
I have 4 Tables that i am Joining Across. Each one of the tables has around 800,000 Rows in it. I am Joining them all on a field Called MDRReport Key.
my query looks like this
SELECT Mdrfoit.MDRReportKey, Mdrfoit.DtReport, Mdrfoit.DateReceived, Mdrfoit.EventType, Mdrfoit.AdverseEvent, Mdrfoit.ProductProb, Mdrfoit.ReportOccup,DevTable.BrandName, DevTable.ManName, DevTable.ModelNumber, DevTable.ProductCode, DevTable.DeviceFamily, TextTable.Text
FROM Mdrfoit
INNER JOIN DevTable ON Mdrfoit.MDRReportKey = DevTable.MDRReportKey
INNER JOIN TextTable ON Mdrfoit.MDRReportKey = TextTable.MDRReportKey LEFT OUTER JOIN probLink ON Mdrfoit.MDRReportKey probLink.MDRReportKey
WHERE Mdrfoit.DateReceived BETWEEN '1/1/1995' AND '1/1/2007') AND
AND (DevTable.BrandName LIKE '&PLV%')
My problom is that when i do this i get back 3 rows in my result set for every 1 result that i want So ill get like
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
2615681/24/200000:00.0MNY114PLV-100RESPIRONICS, INC.35001CBK
So my result set is on average 3 time larger then its suppose to be, This is also causing my query time to be very very slow.
There are no Duplicate entries in the database where all of the fields are the same. I am confused on if this is a problom in my query. Any advice would be great
"When you have everything to loose and nothing to gain, All-In is not an option"
Validating And Updating Colums
Jul 23, 2005Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,
View 3 Replies View RelatedMatrix Subtotal But Not All Colums
Feb 21, 2007Hello,
I have this problem that I do not now how to solve it:
I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).
If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.
Nr. Of Orders Nr. of Lines Average Delivery Time
Open Order 10 20 3.2
Closed Order 15 30 5
Total 25 50 8.2 (it should be, for example, 4.5)
Is there a way in which I can tell the system to not calculate the total for the third column?
If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.
Thanks to all!
Andrea
Joined View Does Not Contain All Colums
Jul 20, 2006The joined view is named "dbo.viewExecView" and is like:
SELECT Bank_No, data_center
FROM [ALPHASQL2000].ev_db.dbo.Bank
The new view that joins to the above view is like:
SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)
Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.
I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.
Regards,
Joe
INNER JOIN: Joining Two Different Colums With One Table?
Nov 15, 2004Hello everyone,
I'm stuck on something here. Any help would be great. This is a relational database question.
I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_SubCategories.SubCategoryName,
_SubCategories.SubCategoryName,
LinkName
FROM
_Links
INNER JOIN
_SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId
INNER JOIN
_SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:
Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_Links_Categories.CategoryName,
_Links_StatusCategories.StatusName,
LinkName
FROM
_Links
INNER JOIN
_Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId
INNER JOIN
_Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable
I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.
Table_Categories: CategoryId (Primary Key), CategoryName
Table_SubCategories: CategoryId, SubCategoryId (Primary Key), SubCategoryName
So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.
If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.
Alec
Variable Colums In Stacked Table
Dec 14, 2007Hello, and thanks for taking the time to read this.
NOOB question:
In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:
CREATE TABLE [dbo].[T_Catalog](
[StlyeID] [int]
) ON [PRIMARY]
with data:
1
2
the SIZES table (filled in by the customer with all the size ranges they carry) is:
CREATE TABLE [dbo].[T_Sizes](
[SizeID] [int],
[SizeName] [nchar](10)
) ON [PRIMARY]
with data:
1,Small
2,Medium
3,Large
4,Xtra-Large
and the AVAILSIZES table would be:
CREATE TABLE [dbo].[T_AvailSizes](
[StyleID] [int],
[SizeID] [int]
) ON [PRIMARY]
1,1
1,2
1,3
2,1
2,3
2,4
Basically, then, we know that:
style 1 comes in Small, Medium, and Large
style 2 xomes in Small, Large, and Xtra large
WE know that, but getting SQL to tell us that is a major PIA!!
Now,
SELECT
t_Catalog.StyleId, t_AvailSizes.SizeID
FROM
t_Catalog
INNER JOIN t_AvailSizes ON
t_Catalog.StyleId = t_AvailSizes.StyleId
will give me a nice list of each item number with a separate row for each size number.
My questions are:
How do I get the size NAMES?
How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?
Is this the right way of doing this?
In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.
Thanks for any help and information you can provide.
I Want To Join Two Colums From Two Differet Database
May 1, 2008
Hello I have a Source database and a Target database.
I want to join one table from the source to the other table in the target.
Please can some one write a sql query for this.
i gues its something like
select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination
Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.
Order By Colums Asc Does Not Work In SQL SERVER 2005
Feb 12, 2008Hello
Iam usng sql server 2005 database
I had this table
CREATE TABLE [dbo].[Companies](
[CompanyID] [smallint] IDENTITY(1,1) NOT NULL,
[Company] [nvarchar](40) NOT NULL,
I entered Arabic Names
so I write this query
select * from companies order by company asc
--and this one
select * from companies order by company
and I try to order desc
but the row didnt appear out ordered
and the big prtoblem happened when I try to update a company Its go to the last row when it appears
please help me
thanks
How To Dynamic Specify The Colums In Full-Text Search?
May 14, 2004My problem is simple: i want to dynamic specified the columns in the ContainsTable, this is possible? Please see the example.
Declare @Test1 int
Declare @Test2 int
Declare @Query varchar(50)
Declare @Temp varcahr(50)
--Test
Set @Test1=1
Set @Test1=0
Set @Query='something'
--Add the column to put in containstable
IF (@Test1=1)
Begin
Set @Temp='ID'
End
IF (@Test2=1)
Begin
Set @Temp= @Temp + ',Name'
End
SELECT *
FROM
<table>
INNER JOIN
CONTAINSTABLE (<table>, @Temp, @Query) AS KEY_TBL
ON <table>.ID = KEY_TBL.[KEY]
Thanks
Showing Colums And Rounding 2 Decmial Places
May 8, 2007Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this:
the formatting is messed up though
a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.
DNAMELOCNumber of PeopleSalary
-------------------------------------------------------------------
ACCOUNTINGNEW YORK3 2916.67
RESEARCHDALLAS52175
SALESCHICAGO61566.67
Auto Fill In Colums Trough Foreign Key Relationship
Jun 10, 2004Hi,
I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,
Can someone help me with this?
Cheers Wim
Is There A Way We Can Implement Business Logic By Storing It In Colums Of A Table And Calling It?
Jul 20, 2005What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
View 1 Replies View RelatedMultiple-step OLE DB Operation Generated Errors. Adding Colums Using ADOX
Nov 26, 2007Hi
I'm trying to add columns to an existing table using ADOX. I'm using C++/CLI. My code is as follow
Table->default->Append("new_column", ADOX:: DataTypeEnum::adWChar, 255);
Table->default["prev_instance_id"]->Properties["Jet OLEDB:Compressed UNICODE Strings"]->default = true;
The first line of code works fine but when I try to set the value of the property I get the following excpetion:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll
Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Any idea?
Marco
Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE
Feb 23, 2007i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View 2 Replies View RelatedHow To Copy A Column(or Colums) From A Table In One Database To Another Table In A Different Databas
Oct 1, 2001How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies View RelatedCannot See The Colums In The Design View Of Queries SQL 2000 And MSAccess 2000(adp)
Nov 21, 2006Cannot see the Colums in the "design view" of Queries. All i see when i want to design a new query is *columns
This happens in only one database, in other databases using same server i can see the colums and can tick them to view then in the query.
In enterprise manager i see all the columns.
Using SQL 2000 and MSAccess 2000
Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..
Nov 4, 2006Hi All,
i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,
enterName - String packageLevel (will store the name I enter)
myVar - String packageLevel. (to store the query)
I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"
Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".
Can Someone guide me whr am going wrong?
myVar variable, i have set the ExecuteAsExpression Property to true too.
Please let me know where am going wrong?
Thanks in advance.
Do Somebody Know How Long (in Chars) Script(command) Can Be Solved By SQL Command?
Aug 30, 2004Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks
What Command Is Used To Get Back The Privileges Offered By The GRANT Command?
Mar 10, 2007reply.
View 1 Replies View RelatedI Give Up On This One...
Jul 20, 2005I have tried and tried to get this to work and I'm doing something dumb.I'm going to include sample data and my format file. Can someone helpme figure out my dumb mistake on a bulk insert using a format file?Don't worry about data types, I'll handle that later. Thanks a lot!!!CSV File:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,ADDRESS1,CITY, STATE,ZIP,PHONE,COMPETITOR,PLAN,HARVESTDATE,EIDs,EIDCount,sourceIds,minS core,maxScore,hasContract,matchFilter,matchFilterBGR,matchFilterCAS,matc hFilterMTV,forceMatch,forceMatchUserId,paymentPlatforms,matchStatusColor ,recordId,dupId,NetworkComparedTo1,name1,Family Practice General Practice,FP,PCP,address1,DeerfieldBeach,FL,33442,phone1,C1,PPO,5/15/2004,1000067851,2,BADG_0874992,9,14.4,Y,Y,Y,Y,Y,0,,3,G,1, ,netcomp12,name2,Family Practice GeneralPractice,FP,PCP,address2,Margate,FL,33063,phone2,C 2,PPO,5/15/2004,1000067851,2,BADG_0874992,9,10,Y,Y,Y,Y,Y,0,,3,G,2, ,netcomp23,name3,General Practice,GP,PCP,address3,DeerfieldBeach,FL,33442,phone3,C3,PPO,5/15/2004,1000067851,2,BADG_0874992(14.4Y)|BADG_1901859,9,14.4,Y,Y,Y,Y,Y,0,,3,G,3, ,netcomp34,name4,ReproductiveEndocrinology,OBEN,OB,address4,Davie,FL,33328,phon e4,C4,PPO,5/15/2004,1000083687,1,CAS_650410436,10.3,10.3,Y,N,N,N,N,0,,0, Y,4, ,netcomp4Format File:8.0311 SQLCHAR 0 1 "
" 0 quote Latin1_General_CI_AS2 SQLCHAR 0 3000 "," 1 Provider_Raw_ID Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_name Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS6 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS7 SQLCHAR 0 3000 "," 0 none_Address Latin1_General_CI_AS8 SQLCHAR 0 3000 "," 0 none_City Latin1_General_CI_AS9 SQLCHAR 0 3000 "," 0 none_State Latin1_General_CI_AS10 SQLCHAR 0 3000 "," 0 none_Zip Latin1_General_CI_AS11 SQLCHAR 0 3000 "," 0 none_Phone Latin1_General_CI_AS12 SQLCHAR 0 3000 "," 0 none_Competitor Latin1_General_CI_AS13 SQLCHAR 0 3000 "," 0 none_Plan Latin1_General_CI_AS14 SQLCHAR 0 3000 "," 0 none_HarvestDate Latin1_General_CI_AS15 SQLCHAR 0 3000 "," 0 none_EIDs Latin1_General_CI_AS16 SQLCHAR 0 3000 "," 5 EIDCount Latin1_General_CI_AS17 SQLCHAR 0 3000 "," 0 SourceIds Latin1_General_CI_AS18 SQLCHAR 0 3000 "," 6 minScore Latin1_General_CI_AS19 SQLCHAR 0 3000 "," 7 maxScore Latin1_General_CI_AS20 SQLCHAR 0 3000 "," 8 hasContract Latin1_General_CI_AS21 SQLCHAR 0 3000 "," 9 matchFilter Latin1_General_CI_AS22 SQLCHAR 0 3000 "," 10 matchFilterBGR Latin1_General_CI_AS23 SQLCHAR 0 3000 "," 11 matchFilterCAS Latin1_General_CI_AS24 SQLCHAR 0 3000 "," 12 matchFilterMTV Latin1_General_CI_AS25 SQLCHAR 0 3000 "," 13 forceMatch Latin1_General_CI_AS26 SQLCHAR 0 3000 "," 14 forceMatchUserId Latin1_General_CI_AS27 SQLCHAR 0 3000 "," 15 paymentPlatforms Latin1_General_CI_AS28 SQLCHAR 0 3000 "," 16 matchStatusColor Latin1_General_CI_AS29 SQLCHAR 0 3000 "," 17 recordId Latin1_General_CI_AS30 SQLCHAR 0 3000 "," 18 dupId Latin1_General_CI_AS31 SQLCHAR 0 3000 "," 4 NetworkComparedTo Latin1_General_CI_ASHere's a script for the table!if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Provider_Results]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[Provider_Results]GOCREATE TABLE [dbo].[Provider_Results] ([Provider_Raw_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[Category] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SpecialtyCode] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[NetworkComparedTo] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[EIDCount] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[minScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[maxScore] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[hasContract] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilter] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterBGR] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterCAS] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[matchFilterMTV] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[forceMatch] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[forceMatchUserId] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[paymentPlatforms] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[matchStatusColor] [varchar] (3000) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[recordId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[dupId] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThanks again. I am dying on this one. With everything includedhopefully someone (smarter than me) can figure it out.Thanks,Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
Nu Ger Jag Upp (I Give Up)
Feb 5, 2008
Jag har installerat sql server 2008 (I have installed sql server)
I cannot find enterprise manager - I think that the sql server is useless unless there is a manager.
Command Text Was Not Set For The Command Object Error
Sep 19, 2006Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:
Description: An OLE DB Error has occured. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".
.........
Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".
This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:
private bool BuildPackage()
{
// Create the package object
oPackage = new Package();
// Create connections for the Foxpro and SQL Server data
Connections oPkgConns = oPackage.Connections;
// Foxpro Connection
ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");
oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere
oFoxConn.Name = "SourceConnectionOLEDB";
oFoxConn.Description = "OLEDB Connection For Foxpro Database";
// SQL Server Connection
ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");
oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere
oSQLConn.Name = "DestinationConnectionOLEDB";
oSQLConn.Description = "OLEDB Connection For SQL Server Database";
// Add Prepare SQL Task
Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");
TaskHost thSQLTask = exSQLTask as TaskHost;
thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");
thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);
thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);
thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");
thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);
thSQLTask.FailPackageOnFailure = true;
// Add Data Flow Tasks. Create a separate task for each table.
// Get a list of tables from the source folder
arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");
for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)
{
// Get the name of the file from the array
sDataFile = Path.GetFileName(arFiles[iCount].ToString());
sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);
oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
oDataFlow.AutoGenerateIDForNewObjects = true;
// Create the source component
IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();
oSource.Name = (sDataFile + "Src");
oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = oSource.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oSource.RuntimeConnectionCollection.Count > 0)
{
oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;
oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);
}
// Set Custom Properties
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);
srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);
// Re-initialize metadata
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create Destination component
IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();
oDestination.Name = (sDataFile + "Dest");
oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper destDesignTime = oDestination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Add the connection manager
if (oDestination.RuntimeConnectionCollection.Count > 0)
{
oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;
oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);
}
// Set custom properties
destDesignTime.SetComponentProperty("AccessMode", 2);
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");
// Create the path to link the source and destination components of the dataflow
IDTSPath90 dfPath = oDataFlow.PathCollection.New();
dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);
// Iterate through the inputs of the component.
foreach (IDTSInput90 input in oDestination.InputCollection)
{
// Get the virtual input column collection
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the column collection
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
//Map external metadata to the inputcolumn
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();
externalColumn.Name = inputColumn.Name;
externalColumn.Precision = inputColumn.Precision;
externalColumn.Length = inputColumn.Length;
externalColumn.DataType = inputColumn.DataType;
externalColumn.Scale = inputColumn.Scale;
// Map the external column to the input column.
inputColumn.ExternalMetadataColumnID = externalColumn.ID;
}
}
}
// Add precedence constraints to the package executables
PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);
pcTasks.Value = DTSExecResult.Success;
for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)
{
pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);
pcTasks.Value = DTSExecResult.Success;
}
// Validate the package
DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);
// Check if the package was successfully executed
if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))
{
string sErrorMessage = "";
foreach (DtsError pkgError in oPackage.Errors)
{
sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";
sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";
sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";
sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";
sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";
sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";
sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";
sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;
}
MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
// return a successful result
return true;
}
Sql - If Not Found, Give Name
Mar 13, 2007Been trying to get this one but need a little input.
SELECT a.Id,g.LabName......
FROM someTbl a,table g
WHERE a.id = @idAND (b.calLab = g.ID OR g.LabName = ???)
With the above, if b.calLab = 0 then there is no entry in table g.(I didn't develop the table or would have included NONE)
So, how to tell g.LabName to come back as 'None' if b.calLab = 0?
Thanks,
Zath
Give All Results Even When Not Available
Oct 15, 2007Hello I have a problem with a query..
What I want:
I have a query that selects from different tables:
Code:
SELECT * FROM table1 as t1 ,table2 as t2 WHERE t1.t2_Id = t2.Id
But now I want all results from table1 even if it's not linked with table2, if that's the case i want to add something like:
Code:
CASE
WHEN 'notlinked' THEN 1
ELSE 0
END HighLight
that way I can show the rows that have to be manually linked
hope you understand my problem...
thanks!
Someone Can Give A Litgh?
May 17, 2004Hi Everybody,
In a table there´s a field for store real values,
so...in the database, using the SQL Server Enterprise Manager
and execute query as 'select * from' and it return the value
'15,35' just like stored before, why using Delphi via an ADO conection
in my result set there is a '15,3500003814697' value if in the record time
the inputed value was '15,35'?
What I have doing wrong?
Would You Like To Give Me Some Advice
May 28, 2004hi,everyone
i use sybase before,and i want to learn ms sqlsr2000,can anybodu give me some advice,or give me some matrial
thx in advance
Please Give Me Answer
May 20, 2008batch processing means
View 3 Replies View Related