|
I'm building the dts from scratch... The goal is to build a dts to export some data (sql statement is a must) from one table to a text file. I've built a package, connections (two of them, one for the sql server, and one for the text file), steps (only one), one task with one custom task, and two transformation objects (there are two columns in the select clause in sql statement). There are absolutely no transformations included, only copy od the selected data. However, when I execute the package, I get the following error:
"Incomplete file format information - file cannot be opened."
DataSource property for the text file is "DTSFlatFile". I can't figure out what's wrong... help...
|
|
|
|
|
may be u didnt selected the right option!
|
|
|
|
|
Hi ,
I want to replicate the database beetween two sql 2000 servers .
And i am going step-by-step as suggested in the code projects replication article , in that article the have suggested that we have to change the
MSSQLSERVER service startup account to the any account which is defined in SQL
SERVER under systemadmin server role. And i am also going exatly they have written , but sql server is not permitting me to logon using any logon account which is defined under sysadminserverrole.
If anybody have the solutions/sugestions regarding this problem please
send me.
Thanking You,
In advance.
param
|
|
|
|
|
Hi,
I am looking for a good article that will explain to me the BASIC's of the dataset and datatable and in particular performance issues. The article should not be overly complex particularly as to how to access them etc - just an overview.
In particular, I'm putting together my first real program of any substance and the database is growing with tables galore and so far at least 15 datatables that I use to varying degrees. I am now thinking about memory and processor demands particularly as I'm aiming the program at the home PC user and not even a so-called power user either.
I'm now considering creating another datatable that consists of parts of other datatables allready in existence. Does creating "child" datatables use up substantially more resources or does the datatable only reference data that is already in memory in the other datatables?
There are of course many ways of skinning a cat and using the VS2005 IDE seems to tempt one to take the easiest course which may not be the most efficient in terms of memory usage, CPU usage etc.
Am I being paranoid?
Glen Harvy
|
|
|
|
|
I can't think of any articles off the top of my head, but you could look into the PerformanceCounter class or keep it simple and monitor your application's performance in terms of memory/cpu usage with the Task Manager. Hope this is a start for you because there are many ways you can go abouts with testing the performance
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
Thanks for the tip - amazing how you can overlook the obvious ie Task Manager
Of immediate concern is my assumption that once a dataset is created and tables loaded I assume another datatable referencing tables within the dataset is only going to increase resource usage minimally.
Is this the case?
Glen Harvy
|
|
|
|
|
Glen Harvy wrote: my assumption that once a dataset is created and tables loaded I assume another datatable referencing tables within the dataset is only going to increase resource usage minimally
I'm not 100% percent sure what the ramifications are on the resources when tables reference each other
too much daily WTF for someone... - Anton Afanasyev
|
|
|
|
|
hi all,my table contain 5 rows with the id of 1,2,3,4,5.if i delete the 3 record then my table will contain 4 records(1,2,4,5).when i enter new record the the new record id will be 6 but i need it like 3..is it possible...is yes tell me how to do...?
ayyp
|
|
|
|
|
ayyp wrote: is it possible
Yes, but it will be an expensive operation. I don't recommend trying.
|
|
|
|
|
Not a good idea. However, if you must do this the easiest way would be:
When you delete from the table [A], insert the deleted id into another table [B].
When you insert a new record into [A] read the minimum from [B] and delete the record from [B]. If none exists in [B], get the max from [A] and add 1 to it.
Make sure that the calls are wrapped up in a transaction or stored procedures.
Ian
|
|
|
|
|
I'd NEVER actually do this:
NOTE: the idcolumn must have a default value of zero.
CREATE TRIGGER CheckIDCol ON dbo.MyTestTable
FOR INSERT
AS
declare @IDVal as int
declare @nCount as int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
select @IDVal = max(idcolumn) from MyTestTable (TABLOCKX) --<code>bad idea</code>
set @nCount = 1
while (@IDVal >= @nCount)
begin
if not exists(Select IDColumn from MyTestTable where IDColumn = @nCount)
begin
break
end
set @nCount = @nCount + 1
end
update MyTestTable set idcolumn = @nCount where idcolumn = 0
COMMIT TRANSACTION
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I have an excel spreadsheet as a database for a small project I am working on.
I have two sheets "Billing" and "Statement".
Basically the excel spreadsheet is a mess, and I cant change to much, as I only have read access to the file.
Now what I do is, I give the columns and worksheets new names, when getting info using a SELECT statement.
Here is my statement:
SELECT [DATE] as tblS_Date, mid([2ND REFERENCE_],1,9) as tblS_CellNr, mid([Cell Number],2,9) as tblB_CellNr, [Total___] as tblB_Total FROM Statement as tblS INNER JOIN Billing as tblB ON tblS.tblS_CellNr = tblB.tblB_CellNr
Say values in db:
2ND REFERENCE_ = "885551234"
Cell Number = "0885551234"
I convert these to with the use of the MID function:
2ND REFERENCE_ = "885551234"
Cell Number = "885551234"
My problem is, I cant get the JOIN part to work(join on cell phone numbers as primary/foreign keys)... It does not pick up the new names, and I cant use the defaultvalue from the spreadsheet as primary keys, as the info first need to be converted using "MID". How can I work around this?
Really, really appreciate the help!
-- modified at 5:35 Friday 18th August, 2006
|
|
|
|
|
You should be able to put your MID functions in the JOIN clause:
SELECT
[DATE] as tblS_Date,
MID([2ND REFERENCE_],1,9) as tblS_CellNr,
MID([Cell Number],2,9) as tblB_CellNr,
[Total___] as tblB_Total
FROM
Statement as tblS
INNER JOIN
Billing as tblB
ON MID(tblS.[2ND REFERENCE_],1,9) = MID(tblB.[Cell Number],2,9)
|
|
|
|
|
Michael Potter wrote: You should be able to put your MID functions in the JOIN clause:
SELECT
[DATE] as tblS_Date,
MID([2ND REFERENCE_],1,9) as tblS_CellNr,
MID([Cell Number],2,9) as tblB_CellNr,
[Total___] as tblB_Total
FROM
Statement as tblS
INNER JOIN
Billing as tblB
ON MID(tblS.[2ND REFERENCE_],1,9) = MID(tblB.[Cell Number],2,9)
Hi Michael.
Thank you very much, the query worked perfect.
Regards
Playout
|
|
|
|
|
Hi,
I have two Access databases each with different tables in. There are two tables in each of them I need to join, using ado.net and vb.net...
costToCompany.mdb (tblEmployees)
Accounts.mdb (tblLoanAmount)
The relationship is EmployeeID in both tables.
How would the select/join statement look like?
Appreciate the help.
|
|
|
|
|
There are two other solutions to this...
1. You could just create another Access DB that has all the tables from both DB's as Linked tables, then just query that DB.
2. You could load data from both DB's into an ADO.Net DataSet, relate your tables and work on the data from there.
Steve
|
|
|
|
|
playout wrote: How would the select/join statement look like
Something like this should work, this is from memory so any errors you will have to work out. One work of caution, I did this a very long time ago, and the performance was terible. It was a temporary hack job in the first place and didnt last to long (thank goodness).
SELECT tblEmployees.*, tblLoanAmount.*
FROM [C:\costToCompany.mdb].tblEmployees
LEFT JOIN [C:\Accounts.mdb].tblLoanAmount ON tblEmployees.EmployeeID = tblLoanAmount.EmployeeID
Your better off as the other poster recommeneded selecting the tables and inserting them into one database. Or even better just combining the tables.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
I've created a Stored Procedure called GetFlashExportValues. See Below:
CREATE PROCEDURE [dbo].[GetFlashExportValues]
@WhereClause varchar(1000) = NULL
AS
DECLARE @sql varchar(5000)
SET @sql=
'SELECT DISTINCT
mr.GroupCode , md.PC, mi.RootControlName, md.YearNumber, fc.FISCAL_PERIOD_NUMBER , mm.ServiceType, md.StringMetricValue, md.NumericMetricValue
FROM
MetricIdents mi INNER JOIN MetricData md ON mi.RowKey=md.MetricIdentKey
INNER JOIN MetricMappings mm ON mi.RowKey=mm.MetricIdentKey
INNER JOIN MetricReportTypes mr ON mr.ReportTypeID=mm.ReportTypeID
INNER JOIN (SELECT DISTINCT FISCAL_PERIOD_MONTH, FISCAL_PERIOD_NUMBER from fiscalcalendar
WHERE SUBSTRING(FISCAL_PERIOD_NAME,1,3) = SUBSTRING(FISCAL_PERIOD_MONTH,1,3)) fc
ON md.MonthText=fc.FISCAL_PERIOD_MONTH
WHERE
mm.ExportToWareHouse=1 AND mm.RollupExport=0'
IF @WhereClause != NULL
BEGIN
SET @sql = @sql + ' AND ' + @WhereClause
END
EXEC (@sql)
GO
I then created a package to call this stored procedure and wanted to pass a value for the parameter @WhereClause from the command line. I was thinking of using the /A of dtsrun..
e.g. 'DTSRun /S "(local)" /U "user" /P "pass" /N "FlashExportValues" /A "@WhereClause":"8"="""MonthText=''August'' AND YearNumber=2006"""'
but I wasn't sure how to map it to the @WhereClause I declared in the code of the stored procedure.. can somebody show me how please? I'd really appreciate it. Thanks a bunch! 
|
|
|
|
|
Hello,
Is there ant in built query where in I can get all the field names from all the tables of my DB in SQL 2005
Regards,
Vipul Mehta
Regards,
Vipul Mehta
Chenoa Information & Software Services Pvt Ltd
|
|
|
|
|
Vipul Mehta wrote: Is there ant in built query where in I can get all the field names from all the tables of my DB in SQL 2005
If you want the column names you can use the syscolumns table or the INFORMATION_SCHEMA.COLUMNS view
|
|
|
|
|
I have a question about SqlDependency.
When using the SqlDependency class to get a notification of a change to a table, what is the best way to actually find out what data has changed? Is this even possible?
We are working on a stock trading system in which stocks are valued based upon notifications in the changes of the price, quantity, etc. Any light that you could shed would be helpful! Thanks a ton!
Ryan Heaney
rheaney@sgfallc.com
|
|
|
|
|
Not having much luck here either Ryan?
Look at the bottom of this page and you will see links to other forums...
You may have to create a TRIGGER to inform you of changed rows.
Steve
|
|
|
|
|
I am trying to move some records from table A to table B.
This is what I have thus far:
declare<br />
@PartID varchar(50),<br />
@SectionNumber varchar(50),<br />
@TopWidth float,<br />
@BottomWidth float,<br />
@boltDiameter float,<br />
@pickOrder int<br />
<br />
Select * from SSVLStandardBracingDesign<br />
set @PartID = SSVLSTandardBracingDesign.PartID<br />
set @SectionNumber = SSVLStandardBracingDesign.SectionNumber<br />
set @TopWidth = SSVLStandardBracingDesign.TopWidth<br />
set @BottomWidth = SSVLStandardBracingDesign.BottomWidth<br />
set @boltDiameter = SSVLStandardBracingDesign.BracingBoltDiameter<br />
set @pickOrder = SSVLStandardBracingDesign.PickOrder<br />
<br />
<br />
insert into BracingKits (PartID, SectionNumber, topWidth, bottomWidth, boltDiameter, pickOrder, <br />
S3TL29, S3TLVL, S3TLUL, S3TLMD, S3TLHD, S3TLVH, S3TLUH,S3R, S4TL,S4A, [1200TLWD],[1800TLWD], [1800SRWD],<br />
[2400SRWD],[3600SRWD] , [4400SRWD])<br />
VALUES (@PartID, @SectionNumber, @TopWidth, @BottomWidth, @boltDiameter, @PickOrder,<br />
0,1,0,0,0,0,0,0,0,0, 0, 0,0,0,0,0)
I need to set my variables to values from the table. I tried using commas but that didn't work, and my multiple set statements do not work.
What is the correct syntax?
Thanks!
|
|
|
|
|
|
Thank you for the link, that helped. I have another question though that it does not answer. I need to insert some 1's and 0's that are bit fields that don't come from the original table so they are not part of the select statement.
This is what I have now:
insert into BracingKits (PartID, SectionNumber, topWidth, bottomWidth, boltDiameter, pickOrder, <br />
S3TL29, S3TLVL, S3TLUL, S3TLMD, S3TLHD, S3TLVH, S3TLUH,S3R, S4TL,S4A, [1200TLWD],[1800TLWD], [1800SRWD],<br />
[2400SRWD],[3600SRWD] , [4400SRWD])<br />
SELECT PartID, SectionNumber, TopSread, BottomSpread, BracingBoltDiameter, PickOrder from SSVLSStandardBracingDesign
But all those that are bold need to be the 1/0's. How can I inculde this as part of the statement?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
