No ISSQLROLE column in sysusers?

The query below complains of "Invalid column name 'ISSQLROLE'." when run on
installation that uses the Turkish character set.
SELECT * FROM sysusers WHERE NAME = 'BOB' AND ISSQLROLE = 0
I'm confused; why would the column be missing/renamed? Reinstalling SQL Ser
ver to use Latin character set works as always. Any words will be very much
appreciated. (I don't have much experience with character sets outside of t
he one that I use here in California.)
Thanks in advance,
James Hunter RossHi James,
Try the column name in lower case. Turkish has both dotted and non-dotted I'
s, and sometimes it is difficult to tell them apart if you're not used to th
e alphabet. In this case it seems to me that the uppercase I in ISSQLROLE in
interpreted by SQL Server as a non-dotted I, where it should be a dotted i.
--
Jacco Schalkwijk
SQL Server MVP
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message news:ujLG32q
CEHA.688@.tk2msftngp13.phx.gbl...
The query below complains of "Invalid column name 'ISSQLROLE'." when run on
installation that uses the Turkish character set.
SELECT * FROM sysusers WHERE NAME = 'BOB' AND ISSQLROLE = 0
I'm confused; why would the column be missing/renamed? Reinstalling SQL Ser
ver to use Latin character set works as always. Any words will be very much
appreciated. (I don't have much experience with character sets outside of t
he one that I use here in California.)
Thanks in advance,
James Hunter Ross|||I change only the "I" in "ISSQLROLE" in our query to "iSSQLROLE" and it work
s! What the...
I'm freaking out. I can't even think. Then, "I" is not actually upper case "
i", we are doomed. We have never been particularly disciplined when writing
queries to match the case of the query to the case of the column/table names
.
I don't suppose there is any easy way to dance around this, is there?
James|||Hi James,
Indeed in the Turkish collation "I" is not upper case "i", it is upper case
"i". Upper case "i" is "I".
I don't think that there is a way around it except a big search and replace
to change all the I's into i's.
The other option involves rebuilding the master database with a
Latin1_General collation (while keeping your own database at Turkish), but
your customer(s) might not be very happy with that, and if you have string
comparisons with columns in temporary tables they will fail most likely.
Jacco Schalkwijk
SQL Server MVP
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:uk1xlPrCEHA.3280@.TK2MSFTNGP09.phx.gbl...
I change only the "I" in "ISSQLROLE" in our query to "iSSQLROLE" and it
works! What the...
I'm freaking out. I can't even think. Then, "I" is not actually upper case
"i", we are doomed. We have never been particularly disciplined when writing
queries to match the case of the query to the case of the column/table
names.
I don't suppose there is any easy way to dance around this, is there?
James

No help from the error messages

I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :

1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.

2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.

3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.

I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :

CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit datetime
AS
SET NOCOUNT ON

DECLARE @.tempDate datetime
DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END

DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (' + @.ntid + ', ' + @.page + ', ' + @.thisHit + ', 1, 1)'

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
end
else
begin
DECLARE @.theColumn varchar (100)
SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Exec @.theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
Exec @.insString
end
GO

And to my surprise, the only 3 errors I got were :

Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45
Incorrect syntax near the keyword 'Exec'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50
Incorrect syntax near the keyword 'end'.

However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?

At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...

begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else

An alternative is finding out how to do something like an if not exists()

As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.

|||

LD50:

At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...

begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else

An alternative is finding out how to do something like an if not exists()

As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.

That do nothing must actually have been a slightly larger error, because when I changed it to declare a small variable (basically a bit, just to avoid too much memory usage), 2 of the errors went away. I'm still having ti gripe at me about the Exec (@.theColumn) line. I don't think in this case a temporary table will do the trick only because I'm basically only trying to get one value, which is the current value of the month counter. Any other ideas?

|||

I'm not quite sure what you're trying to do, or what the column names in tbltracking are, so you'll have to modify this a bit. But it will compile.

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!

DECLARE @.theColumn1 varchar (100)
end
else
begin
Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else

|||

LD50:

An alternative is finding out how to do something like an if not exists()

That's exactly how you do it. IF NOT EXISTS(SELECT ...)

|||

Oh, THANK you! I did have to slightly modify what you put in there, but you had the gist of it, so that's awesome. I would go ahead and mark an answer here, and I probably will since you answered that concern, but I was testing the logic, and trying to input the same record twice, which should have simply done nothing, but instead it inserted a duplicate record. Any idea why the logic failed me there?

|||

well why don't you paste up your new code and we'll let you know...

A good troubleshooting tip is to add some select statements in each part of the if. That way you can run it and see which if statement is firing and/or see if the columns in the table contain the data you expect.

Example:

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
select * from tblTracking where lastVisit = @.thisHit
DECLARE @.theColumn1 varchar (100)
end
else
begin

select * from tblTracking

Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
select * from tblTracking
Exec @.insString
end

|||

I also noticed that your if is illogical

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)

should be:

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)

|||

LD50:

I also noticed that your if is illogical

if exists(select * from tblTracking where ntid = @.ntid and @.page = page)

should be:

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)

Actually, I noticed that myself and had already corrected it... *sheepish shrug*

OK, well I've added in a SELECT statement for the 'do-nothing' branch of logic and changed the two Exec statements to Print statements so I don't get a ton of duplicate records. But the logic is still somehow faulty, as I'm using the Query Analyzer to test it, and trying to input the same record that already exists in the table still prints out the INSERT string, instead of the do-nothing SELECT statement. Here's what I have now, see if you can help me figure out what the problem is...

CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit smalldatetime
AS
SET NOCOUNT ON

DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END

DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking(ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (''' + @.ntid + ''','' ' + @.page + ''', ''' + Convert(varchar(15), @.thisHit, 101) + ''', ''1'', ''1'')'

if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
DECLARE @.bs bit
SELECT * FROM tblTracking where lastVisit = @.thisHit
end
else
begin
DECLARE @.theString varchar (100)
SET @.theString = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND page = @.page'
CREATE TABLE #theColumn (theColumn varchar (100))
INSERT INTO #theColumn
Exec @.theString
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND page = @.page) + 1
SET @.monthCount = (SELECT theColumn FROM #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + Convert(varchar(15), @.thisHit, 101) + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND page = @.page'
Print @.updString
end
end
else
begin
Print (@.insString)
end
GO

|||

Woo! Nevermind, I have finally figured it out. Turns out I needed to use LIKEs instead of =s when I was comparing the varchar values, and I needed to throw in some CASTs to turn some of the ints into varchars for the UPDATE string. If anyone cares to see the corrected, working code, I will post it, but it is working now.

Thanks for the help!

No Cursor

How would I take the total of the failures column below so I can do %'s of
the total. I can do it with an ADO recordset from VB, but I'm trying to do
it in TSQL 1st.
Thanks
Paul
DECLARE @.BeginDate DateTime
DECLARE @.EndDate DateTime
Set @.BeginDate = '2005-05-12'
Set @.EndDate = '2005-05-16'
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),Dept
ORDER BY Dept, Sum(NumFailures) DESC
Dept Failure
Failures PCT
-- ---- --
6600 Misc-No Speed Sensor Signal 28
28/70
6600 Seam Leaks-Rear Retainer to Rear Case 21
21/70
6600 Noise-Neutral
10 10/70
6600 Misc-Stud/Bolt Missing 6
ETC
6600 Noise-All Modes
5
Total
70SELECT TOP 5
dept,
SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
SUM(numfailures) AS 'Failures',
SUM(numfailures) /
(SELECT CAST(SUM(numfailures) AS REAL)
FROM dbo.FTC_TESTRESULTS)
FROM dbo.FTC_TESTRESULTS
WHERE dept = '6600'
AND testdate BETWEEN @.begindate AND @.enddate
GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
ORDER BY dept, SUM(numfailures) DESC
David Portas
SQL Server MVP
--|||Dave,
Will I need to further narrow the divisor... I want to divide by 70...
the total of the top 5, it is currently dividing by the entire population .
Bottom of post is the original code that returns top 5 with no divisor.
dept Failure
Failures
-- ---- -- --
--
6600 Misc-No Speed Sensor Signal 28
8.5995086E-3
6600 Seam Leaks-Rear Retainer to Rear Case 21
6.4496314E-3
6600 Noise-Neutral 10
3.071253E-3
6600 Misc-Stud/Bolt Missing 6
1.8427519E-3
6600 Noise-All Modes 5
1.5356265E-3

> SELECT TOP 5
> dept,
> SUBSTRING(category + '-' + subcategory,0, 50) AS 'Failure',
> SUM(numfailures) AS 'Failures',
> SUM(numfailures) /
> (SELECT CAST(SUM(numfailures) AS REAL)
> FROM dbo.FTC_TESTRESULTS)
> FROM dbo.FTC_TESTRESULTS
> WHERE dept = '6600'
> AND testdate BETWEEN @.begindate AND @.enddate
> GROUP BY SUBSTRING(category + '-' + subcategory,0, 50),dept
> ORDER BY dept, SUM(numfailures) DESC
> --
> David Portas
> SQL Server MVP
> --
>
Select Top 5
Dept,
SubString(Category + '-' + SubCategory,0, 50) as 'Failure',
Sum(NumFailures) as 'Failures'
From DBO.FTC_TESTRESULTS
Where Dept = '6600'
AND TestDate Between @.BeginDate AND @.EndDate
GROUP BY SubString(Category + '-' + SubCategory,0, 50),
Dept
ORDER BY Dept, Sum(NumFailures) DESC|||On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:

>Dave,
> Will I need to further narrow the divisor... I want to divide by 70...
>the total of the top 5, it is currently dividing by the entire population .
>Bottom of post is the original code that returns top 5 with no divisor.
Hi Paul,
Try this one. I couldn't test it, since you didn't provide CREATE TABLE
and INSERT statements (see www.aspfaq.com/5006).
SELECT TOP 5 Dept,
SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
SUM(NumFailures) AS Failures,
SUM(NumFailures) /
(SELECT SUM(Failures)
FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
FROM dbo.FTC_TestResults
WHERE Dept = '6600'
AND TestDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
ORDER BY SUM(NumFailures) DESC
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Works perfectly... and I spent the last couple of days figuring out how
it works. Now I think I got it. Thanks again.
Paul
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:9mse911404i9c32u9cak1o4e40gl16t6b7@.
4ax.com...
> On Wed, 25 May 2005 20:25:51 -0400, Paul Ilacqua wrote:
>
> Hi Paul,
> Try this one. I couldn't test it, since you didn't provide CREATE TABLE
> and INSERT statements (see www.aspfaq.com/5006).
> SELECT TOP 5 Dept,
> SUBSTRING(Category + '-' + SubCategory, 0, 50) AS Failure,
> SUM(NumFailures) AS Failures,
> SUM(NumFailures) /
> (SELECT SUM(Failures)
> FROM (SELECT TOP 5 SUM(NumFailures) AS Failures
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory, 0, 50)
> ORDER BY SUM(NumFailures) DESC) AS d) AS Pct
> FROM dbo.FTC_TestResults
> WHERE Dept = '6600'
> AND TestDate BETWEEN @.BeginDate AND @.EndDate
> GROUP BY SUBSTRING(Category + '-' + SubCategory,0, 50)
> ORDER BY SUM(NumFailures) DESC
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

No column was specified to allow the component to advance through the file.

Hello,

I apologize in advance if this seems like a relative easy answer, however, I can't find it anywhere, and I can't figure it out personally, I am relatively new to SSIS and C#.

I am attempting to write in C# a simple program, where I am taking a table from an OLE DB Source and transfering it to a comma delimited flat file.

I have been trying to work through samples and other methods of help, but I continue to get stuck, and this is my latest problem.

When trying to excecute I get the error: No column was sepcified to allow the component to advance through the file, for my flat file destination.

I know what this means, I just do not know how to fix it.

Below is my code so far. I highly doubt my code is the best it can get for this type of example, so if you see anything that I do not need in the code for it to perform what I want it to perform, please let me know.

(The code to excecute this package is in a different file).

using System;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

namespace Microsoft.SqlServer.Dts.Samples

{

class Program

{

static void Main(string[] args)

{

// Create a package and add a Data Flow task.

Package package = new Package();

Executable e = package.Executables.Add("DTS.Pipeline.1");

TaskHost thMainPipe = e as TaskHost;

MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

// Create Application

Application app = new Application();

// Add an OLE DB connection manager to the package.

ConnectionManager conMgr = package.Connections.Add("OLEDB");

conMgr.ConnectionString = "Data Source=ROSIE\\ROSIE2005;" +

"Initial Catalog=AdventureWorks;Provider=SQLNCLI;" +

"Integrated Security=SSPI;Auto Translate=false;";

conMgr.Name = "SSIS Connection Manager for OLE DB";

conMgr.Description = "OLE DB connection to the " +

"AdventureWorks database.";

// Create and configure an OLE DB source component.

IDTSComponentMetaData90 source =

dataFlowTask.ComponentMetaDataCollection.New();

source.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Create the design-time instance of the source.

CManagedComponentWrapper srcDesignTime = source.Instantiate();

// The ProvideComponentProperties method creates a default output.

srcDesignTime.ProvideComponentProperties();

// Assign the connection manager.

source.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(conMgr);

// Set the custom properties of the source.

srcDesignTime.SetComponentProperty("AccessMode", 2);

srcDesignTime.SetComponentProperty("SqlCommand",

"Select * from HumanResources.EmployeePayHistory");

srcDesignTime.SetComponentProperty("OpenRowset", "[AdventureWorks].[HumanResources].[EmployeePayHistory");

// Need to set the ConnectionManagerID

if (source.RuntimeConnectionCollection.Count > 0)

{

source.RuntimeConnectionCollection[0].ConnectionManagerID =

conMgr.ID;

source.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(conMgr);

}

// Connect to the data source,

// and then update the metadata for the source.

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Add an flat file source connection manager to the package.

ConnectionManager conMgr2 = package.Connections.Add("FlatFile");

conMgr2.ConnectionString = "C:\\Documents and Settings\\ddoorn" +

"\\My Documents\\Visual Studio 2005\\Projects\\" +

"DennisSampleProgram1\\EmployeePayHistory.txt";

conMgr2.Name = "SSIS Connection Manager for Flat File";

conMgr2.Description = "Flat File Destination Connection";

// Create Destination Component

IDTSComponentMetaData90 destination =

dataFlowTask.ComponentMetaDataCollection.New();

destination.Name = "Flat File Destination";

destination.ComponentClassID =

"DTSAdapter.FlatFileDestination.1";

CManagedComponentWrapper destDesignTime = destination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Assign the connection manager.

destination.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(conMgr2);

// Set Custom Properties

destDesignTime.SetComponentProperty("Overwrite", true);

// Assignment an ID to the ConnectionManager

if (destination.RuntimeConnectionCollection.Count > 0)

{

destination.RuntimeConnectionCollection[0].ConnectionManagerID =

conMgr2.ID;

destination.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(conMgr2);

}

// Create the path from source to destination.

IDTSPath90 path = dataFlowTask.PathCollection.New();

path.AttachPathAndPropagateNotifications(source.OutputCollection[0],

destination.InputCollection[0]);

// Get the destination's default input and virtual input.

IDTSInput90 input = destination.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn

in vInput.VirtualInputColumnCollection)

{

// Call the SetUsageType method of the destination

// to add each available virtual input column as an input column.

destDesignTime.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

}

//map external metadata to the inputcolumn

//int index = 0;

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{

IDTSExternalMetadataColumn90 exMetaColumn =

input.ExternalMetadataColumnCollection.New();

//(IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[index++];

exMetaColumn.CodePage = inputColumn.CodePage;

exMetaColumn.DataType = inputColumn.DataType;

exMetaColumn.Length = inputColumn.Length;

exMetaColumn.Name = inputColumn.Name;

inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;

destDesignTime.MapInputColumn(input.ID, inputColumn.ID, exMetaColumn.ID);

}

// Verify that the columns have been added to the input.

// This is only really required for debugging purposes

Console.WriteLine("Below are the columns that have been added " +

"to the input. Press Enter to Verify");

foreach (IDTSInputColumn90 inputColumn in

destination.InputCollection[0].InputColumnCollection)

{

Console.WriteLine(inputColumn.Name);

}

Console.Read();

// Connect to the data source,

// and then update the metadata for the source.

destDesignTime.AcquireConnections(null);

destDesignTime.ReinitializeMetaData();

destDesignTime.ReleaseConnections();

// Save Package to XML

app.SaveToXml("C:\\Documents and Settings\\ddoorn\\My Documents\\" +

"Visual Studio 2005\\Projects\\DennisSampleProgram1\\" +

"DennisSampleProgram1\\DennisSampleProject1.xml",

package, null);

} // main

} // program

} // namespace

Nevermind, I found an example that comes with SQL Server 2005/Visual Studio and I found my mistake.

No column to map

Hello,

I am using oledb source and running sp_helpdb procedure but when I open the columns windows there are no column available.

your help is appreciated.

Thanks.

got the answer workaround is to use

SET FMTONLY OFF

Next/Prev record

If you have an identity column (i.e. 1-N) and you have 5, what is the most
efficent query to get the next (and prev) row? Gaps could happen so there
may not be a 6, but the next in order may be 8 for example. TIA
William Stacey [MVP]I usually use these:
Prev:
SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
Next:
SELECT TOP 1 key FROM table WHERE key > current ORDER BY key
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>|||Thanks Remus.
William Stacey [MVP]
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:euthl7jTGHA.2156@.tk2msftngp13.phx.gbl...
|I usually use these:
| Prev:
| SELECT TOP 1 key FROM table WHERE key < current ORDER BY key DESC
| Next:
| SELECT TOP 1 key FROM table WHERE key > current ORDER BY key|||William
create table William(c1 int NOT NULL primary key)
go
insert into William values (1)
insert into William values (2)
insert into William values (3)
insert into William values (4)
insert into William values (5)
insert into William values (6)
declare @.d as int
set @.d=4
select top 1 *,(select top 1 * from William where c1 >@.d order by c1
asc)as n
from William where c1 <@.d order by c1 desc
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uw3Tx1iTGHA.424@.TK2MSFTNGP12.phx.gbl...
> If you have an identity column (i.e. 1-N) and you have 5, what is the most
> efficent query to get the next (and prev) row? Gaps could happen so there
> may not be a 6, but the next in order may be 8 for example. TIA
> --
> William Stacey [MVP]
>
>

Next Run Date column not sorting

We've just installed SQL Server 2000 on one of our servers and have
noticed a strange behavior. When clicking the column headings in the
job display in Enterprise Manager, the list is sorted (first click
ascending, then descending) on the column clicked except for "Next Run
Date." When we click on "Next Run Date," the list is reordered, but
randomly. Each time we click it we get a different order but never
ascending or descending. This is true whether we're at the server or
on a remote machine. We never saw this behavior with SQL Server 7.0.

Any ideas? Thanks!"Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
news:e3153db3.0410110535.129c4159@.posting.google.c om...
> We've just installed SQL Server 2000 on one of our servers and have
> noticed a strange behavior. When clicking the column headings in the
> job display in Enterprise Manager, the list is sorted (first click
> ascending, then descending) on the column clicked except for "Next Run
> Date." When we click on "Next Run Date," the list is reordered, but
> randomly. Each time we click it we get a different order but never
> ascending or descending. This is true whether we're at the server or
> on a remote machine. We never saw this behavior with SQL Server 7.0.
> Any ideas? Thanks!

The problem with Enterprise Manager is that you never really know what it's
doing. If you run a SELECT query with an ORDER BY clause in Query Analyzer,
does it return the correct order? QA is a much better and simpler tool for
retreiving and manipulating data than EM, because there's no guesswork
involved and you can control exactly what data you retrieve and how.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<416ad914_2@.news.bluewin.ch>...
> "Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
> news:e3153db3.0410110535.129c4159@.posting.google.c om...
> The problem with Enterprise Manager is that you never really know what it's
> doing. If you run a SELECT query with an ORDER BY clause in Query Analyzer,
> does it return the correct order? QA is a much better and simpler tool for
> retreiving and manipulating data than EM, because there's no guesswork
> involved and you can control exactly what data you retrieve and how.
> Simon

ORDER BY works fine in QA. Ordering also works fine in other columns
of the job list in Ent. Mgr. The "Next Run Date" column is the only
one that is acting weird. If I use SQL 2000 Ent. Mgr. from my laptop
to look at any of our servers (4 SQL 7.0 & 1 SQL 2000) the same
behavior occurs. Ent. Mgr. on our 7.0 servers works fine but, of
course, cannot connect to the 2000 server.

Rich|||"Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
news:e3153db3.0410120501.7ab303bc@.posting.google.c om...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:<416ad914_2@.news.bluewin.ch>...
>> "Rich Hurley" <Rich.Hurley@.mci.com> wrote in message
>> news:e3153db3.0410110535.129c4159@.posting.google.c om...
>>
>> The problem with Enterprise Manager is that you never really know what
>> it's
>> doing. If you run a SELECT query with an ORDER BY clause in Query
>> Analyzer,
>> does it return the correct order? QA is a much better and simpler tool
>> for
>> retreiving and manipulating data than EM, because there's no guesswork
>> involved and you can control exactly what data you retrieve and how.
>>
>> Simon
> ORDER BY works fine in QA. Ordering also works fine in other columns
> of the job list in Ent. Mgr. The "Next Run Date" column is the only
> one that is acting weird. If I use SQL 2000 Ent. Mgr. from my laptop
> to look at any of our servers (4 SQL 7.0 & 1 SQL 2000) the same
> behavior occurs. Ent. Mgr. on our 7.0 servers works fine but, of
> course, cannot connect to the 2000 server.
> Rich

If it's a date column, then perhaps some sort of regional settings issue
might be causing it? Although if it works properly in QA, then I personally
wouldn't worry about it; EM has plenty of other data manipulation issues
anyway, as described in some detail here:

http://www.aspfaq.com/show.asp?id=2455

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<416bd8fc$1_3@.news.bluewin.ch>...
> If it's a date column, then perhaps some sort of regional settings issue
> might be causing it? Although if it works properly in QA, then I personally
> wouldn't worry about it; EM has plenty of other data manipulation issues
> anyway, as described in some detail here:
> http://www.aspfaq.com/show.asp?id=2455
> Simon

If the job list was sorted consistently when I click on the column
heading I'd believe it was a regional setting. But everytime I click
the "Next Run Time" heading, I get a random result. The other columns
sort ascending on the first click and descending on a second click and
continue to flip-flop as many times as they are clicked. Not so with
the "Next Run Time" column. There's no rhyme or reason to the order
that jobs are displayed no matter how many times I click -- it's
different each time -- not just a reverse of the previous display.

QA doesn't help when managing jobs -- as the article points out EM is
the preferred tool for that -- QA queries would simply be too long and
too complex. I'm just puzzled by what could cause this weird behavior
in EM.

Thanks,
Rich

Next Available Identity Value

Hi all,
is there a way in SQL Server for me to get the next available Identity value from an Identity column?
(Idon'twant to perform an insert, and then get the identity through @.@.IDENTITY!)
Thanks
Tryst
SELECT (IDENT_CURRENT('tableName') + 1)
But this is not safe! If 2 processes call this they can get the same ID number.
|||OK - Thanks Darrel.
I may have to give that a miss then, esp is it nots 100% secure.
Thanks
Tryst
|||I was assuming you were reading it and then returning it to yourbusiness object and waiting a while. If you are getting the IDnumber and then immediately updating, then you could wrap it in atransaction and set thetransaction levelto serializable. That would decrease throughput, but if you'renot supporting that many users, maybe it's ok. -- But if that'sall you're doing, why not use autonumber?
|||Hi Darrell,
what I am doing is inserting an entry into the database, where theentry will contain a column that will reference the primary key to thatentry. The idea is that I will be storing XML data in the database, andif the XML data is more than 1024 chars then the XML will be spreadacross multiple rows. Therefore, I need a Column that tells me what rowthese XML rows relate to (which is the first row that contain the XMLdata). I then have an order column which will determine the order I canassemble the XML data from these rows. So, my problem was, for thefirst row that will contain XML data that will span multiple rows (oreven singular rows - XML data < 1024 chars) how do I get to know theprimary for this entry so that I can insert it into the referencecolumn. The way I done this was to do an insert, and then quicklyfollowed by an update after using @.@.IDENTITY.
Thanks
Tryst

NEWBIE: TOP PREDICATE HELP

Hello NG
I am trying to use a declared variable of datatype INT within a SELECT
TOP n * FROM table WHERE clause ORDER BY column Statement. I keep getting an
incorrect syntax near my variable. What am I doing wrong?
IF @.varPRFI > @.varMinimumLabels
SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
[PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
ELSE
<do different Things>
TIAFAH
RandyTOP does not accept a variable in SQL Server 2000.
Try using SET ROWCOUNT, e.g.
SET ROWCOUNT @.varMinimumLabels
SELECT * FROM ... ORDER BY
You should also avoid SELECT * in production code.
A
"Randy" <randywfritz@.s@.nm@.r.com> wrote in message
news:eIWWAGY2FHA.2604@.TK2MSFTNGP12.phx.gbl...
> Hello NG
> I am trying to use a declared variable of datatype INT within a SELECT
> TOP n * FROM table WHERE clause ORDER BY column Statement. I keep getting
> an
> incorrect syntax near my variable. What am I doing wrong?
> IF @.varPRFI > @.varMinimumLabels
> SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
> [PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
> ELSE
> <do different Things>
> TIAFAH
> Randy
>|||OK TY
Now that brings up another dilemma. Let me expand - I need to get 15
Records I am looking for Priority records first if my priority records
exceed 15 then I get only 15 if they are less then 15 I need to get enough
records to make 15 - I had intended to use a UNION Query to get my 15 by the
following
IF @.varPRFI > @.varMinimumLabels
SELECT TOP @.varMinimumLabels * FROM [RestockLabels] WHERE
[PrintedCheck] = 0 AND UPPER([PRFI])='PULL FIRST'
ELSE
SET @.varMinimumLabels = @.varMinimumLabels-@.varPRFI
SELECT * FROM [RestockLabels] WHERE [PrintedCheck] = 0 AND
UPPER([PRFI])='PULL FIRST' UNION SELECT TOP @.varMinimumLabels * FROM (SELECT
* FROM [RestockLabels] WHERE [PrintedCheck] = 0 AND UPPER([PRFI])<>'PULL
FIRST')
Will SET ROWCOUNT Still accomplish this task when I remove the top predicate
form my Union SELECT Statement and will I also be able to insure that I get
my priorities - I do not have an order by clause on the union select - I
haven't gone that far yet but when I do will I lose priorities to non
priorities because my nons will be above in the ordered by result and then
getting the rowcount of the union query. I hope I made this clear enough.
Again TIAFAH
Randy
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OTh1XHY2FHA.4004@.TK2MSFTNGP09.phx.gbl...
> TOP does not accept a variable in SQL Server 2000.
> Try using SET ROWCOUNT, e.g.
> SET ROWCOUNT @.varMinimumLabels
> SELECT * FROM ... ORDER BY
> You should also avoid SELECT * in production code.
> A
>
> "Randy" <randywfritz@.s@.nm@.r.com> wrote in message
> news:eIWWAGY2FHA.2604@.TK2MSFTNGP12.phx.gbl...
SELECT
getting
>