Populate a Table with Stored Proc.

I am looking to populate a Schedule table with information from two
other tables. I am able to populate it row by row, but I have created
tables that should provide all necessary information for me to be
able
to automatically populate a "generic" schedule for a few weeks or
more
at a time.

The schedule table contains:
(pk) schedule_id, start_datetime, end_datetime, shift_employee,
shift_position

A DaysOff table contains:
(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
of
week (1-7) form

A CalendarDays table contains:
(pk) date, calendar_dow <-- dow contains the day of week number (as
above) for each day until 2010.

My main question is how to put all of this information together and
have SQL populate the rows with data based on days off. Any
suggestions?Nate (nate.borland@.westecnow.com) writes:

Quote:

Originally Posted by

I am looking to populate a Schedule table with information from two
other tables. I am able to populate it row by row, but I have created
tables that should provide all necessary information for me to be able
to automatically populate a "generic" schedule for a few weeks or more
at a time.
>
The schedule table contains:
(pk) schedule_id, start_datetime, end_datetime, shift_employee,
shift_position
>
>
A DaysOff table contains:
(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
of
week (1-7) form
>
>
A CalendarDays table contains:
(pk) date, calendar_dow <-- dow contains the day of week number (as
above) for each day until 2010.
>
>
My main question is how to put all of this information together and
have SQL populate the rows with data based on days off. Any
suggestions?

Just as a reminder, in case you are getting old and don't remember
what you did yesterday, you posted this question yesterday as well,
and I replied by asking some questions, and Plamen Ratchev suggested
some queries. I suggest that you go Google news and find the old
thread and review our replies.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Nate (nate.borland@.westecnow.com) writes:

Quote:

Originally Posted by

>I am looking to populate a Schedule table with information from two
>other tables. I am able to populate it row by row, but I have created
>tables that should provide all necessary information for me to be able
>to automatically populate a "generic" schedule for a few weeks or more
>at a time.
>>
>The schedule table contains:
>(pk) schedule_id, start_datetime, end_datetime, shift_employee,
>shift_position
>>
>>
>A DaysOff table contains:
>(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
>of
>week (1-7) form
>>
>>
>A CalendarDays table contains:
>(pk) date, calendar_dow <-- dow contains the day of week number (as
>above) for each day until 2010.
>>
>>
>My main question is how to put all of this information together and
>have SQL populate the rows with data based on days off. Any
>suggestions?

>
Just as a reminder, in case you are getting old and don't remember
what you did yesterday, you posted this question yesterday as well,
and I replied by asking some questions, and Plamen Ratchev suggested
some queries. I suggest that you go Google news and find the old
thread and review our replies.
>
>

Hmm. I'm getting old and I don't do daft things like that!
Now, what was I doing before I read this?...

Popuate field with value from last row

Does anyone know how
-- during an insert --
to automatically populate a field with the same data as the field in the
last inserted row?
Thanks.rmg66 wrote:
> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in
> the last inserted row?
>
First, you need a way to identify the last inserted row. is there an
InsertionDate column?
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:%23ozT1swBGHA.208@.tk2msftngp13.phx.gbl...
> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in the
> last inserted row?
> Thanks.
SQL server has no concept of Last Inserted Row.
If you have a column on the table that is populated with the date and time
the row was inserted, you could query that row. But it's not impossible that
2 or more rows can be inserted at exactly the same time.
Also, in your case, what happens if the value later changes in that Last
Inserted Row.
If you tell us what the logic is behind what you're trying to do, someone
here could perhaps suggest a better solution.|||> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in the
> last inserted row?
Since a table is an unordered set of rows, how do you define "last"?
And why not keep this "last" value in another table?|||Does anyone know why
-- during an insert --
you would want to do this.
I mean if you are doing a select into or and insert select then you can
choose the data you want, if you are doing simple insert stmts. then you
already know what the data is.
post some ddl and maybe we can help
"rmg66" wrote:

> Does anyone know how
> -- during an insert --
> to automatically populate a field with the same data as the field in the
> last inserted row?
> Thanks.
>
>|||You could use a insert trigger on the table.
If value is specified add it as an extended property to the table.
If the value is not specified lookup the extended property on the table.
John
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OOr62uwBGHA.412@.TK2MSFTNGP15.phx.gbl...
> rmg66 wrote:
> First, you need a way to identify the last inserted row. is there an
> InsertionDate column?
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||John Kendrick wrote:
> You could use a insert trigger on the table.
>
Why are you replying to me?
I'm not the OP ;-)

> If value is specified add it as an extended property to the table.
> If the value is not specified lookup the extended property on the
> table.
Which gets us back to:
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> If value is specified add it as an extended property to the table.
> If the value is not specified lookup the extended property on the table.
What does an extended property have to do with data in the table?|||You could create an extended property for each columns that is use the last
inserted record.
When the insert trigger fires it will lookup and use that last column value.
The extended property is an alternative way to handle getting the last
record value without querying the table. Otherwise you would need some way
to find the latest record. Like max(datetime) as Bob mentioned.
John
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23S$7lDxBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> What does an extended property have to do with data in the table?
>|||>> You could create an extended property for each columns that is use the
Can you please elaborate on this approach a bit? Perhaps an example would be
helpful.
Anith

Poor performance import text file

Hi,

I'm very new at SSIS. I've got a package to import a 1,000,000 row tab delimited text file into my SQL2005 database. Using SSIS with a Text File source and OLE DB PROVIDER target it crawls. I do have it skipping error rows but that is the only logic I am using. No indexes on the table either. This is pretty basic and it takes SSIS 1.5 hours, DTS blows this away... Assuming this is not something outside of SSIS(like the network, or a user locking issue), what I am I doing wrong?

BTW -- I'm searching for some batch commit sizes, like in DTS, but I cannot find them.

Any ideas.

From the information that you've given, it's hard to know what the problem is, but here are a few things you can check.

Try to isolate the problem. Change the destination to a rowcount or trash destination from SQLIS.com, see if it improves the throughput.

Is the flatfile on the same machine as the executing package?

Are you minimally logged on the destination?

Is the destination DB on the same machine?

Have you tried the SQL Server Destination?

How many errors are you getting from the flat file?

Rows per batch is on the OLEDB Destination Adapter. Did you try changing that?

K

Pls Help-2000 Replication

How do I know whether the record in a row was insert,update and delete that replicated to a subscriber?
Is there a flag or an indicator in msmerge_contents,msmerge_tombstone ,msmerge_genhistory tables or any other msdb/replication system tables?You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks

Originally posted by joejcheng
You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Sory I left out a sentence. Let say, just to know whether the records in MSMERGE_CONTENTS and MSMERGE_TOMBSTONE has been send over to replicate on Publisher or Subscriber.

Thanks again!

Originally posted by eshl
Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks|||msrepl_commnads and msrepl_transactions.|||I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Originally posted by joejcheng
msrepl_commnads and msrepl_transactions.|||back to this issue, I could find the history record that stored the latest record that was merged in th publisher and subscriber. By using this, I will be able to know which record was generated and which wasnt. This is helpful in determining the records that was replicated or not yet.
I m looking into modifying the replication engine,MSSQL replication engine is flexible.

Originally posted by eshl
I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Please Help: Cannot Sort row of size 8104: any advice.

I have a database linked too a website that, we've just put the live data into and hay presto the thing has fallen over. The data has gone in fine but When we search on it it coming up with this error:

Microsoft OLE DB Provider for SQL Server (0x80040E14) Cannot sort a row of size 8104, which is greater than the allowable maximum of 8094.

Has anyone come across this kinda of problem before and is their any nice work arounds. Thanks EdObviously, you managed to get a row size which is 10 more than the maximum. Consider to shorten one of your VARCHAR(x) fields by 10.|||Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.

So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?

If it any help the SQL look like this:

SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName

And the field causing the problems is:
terms.TermShortDesc

Thanks Again|||Originally posted by Nixies
Okay I think I understand the problem now, But I'm still slightly stuck;
I've got the huge amount of data that I've imported and it gone in fine it just this search which throws up problems.

So as far as I can see the only two solution I can look at are some way to make the search accept these size of data or come up with some kind of way of identifing just those singler rows which are causing the problem and deal with them indevidually. Is their some kind of SQL statment that would just return the row where data is above a certain size?

If it any help the SQL look like this:

SELECT DISTINCT
terms.TermName, terms.TermAltName, terms.TermShortDesc, linkSectionSub.SectionID, linkSectionSub.SubID, terms.AutoId,
subSections.SUBName, sections.SCTName
FROM terms INNER JOIN
linkSectorSection INNER JOIN
linkSectionSub ON linkSectorSection.SectionID = linkSectionSub.SectionID INNER JOIN
linkSubTerm ON linkSectionSub.SubID = linkSubTerm.SubID ON terms.AutoId = linkSubTerm.TermID INNER JOIN
sections ON linkSectionSub.SectionID = sections.AutoId INNER JOIN
subSections ON linkSectionSub.SubID = subSections.AutoID
WHERE (linkSectorSection.SectorID = 1)
AND (terms.TermName LIKE '%mortgage%')
OR (linkSectorSection.SectorID = 1)
AND (terms.TermShortDesc LIKE '%mortgage%')
ORDER BY terms.TermName

And the field causing the problems is:
terms.TermShortDesc

Thanks Again

I'm assuming that TermShortDesc is a text field not a varchar.

You have two possible solutions:

Fix the problem short term:
SELECT terms.AutoId, LEN(terms.TermShortDesc) AS CHAR_LEN, terms.TermShortDesc
FROM TERMS
WHERE LEN(terms.TermShortDesc) >8093.
and then edit that data down in length.

The permanent solution is:
Before you get to deep into this, ask yourself these questions:
Do you have to search the entire field? Can you limit data to 8093 characters? In your search do you really have to go beyond the first 500 characters? 1000?

For a permanent solution if you only need the first 500 characters, then make an in your import that TermShortDescSort field and just take the LEFT(TermShortDesc,500) and insert them into TermShortDescSort on import. If you need to go beyond and have all 24,282, then make 3 fields TermShortDesc1, TermShortDesc2, TermShortDesc3 and on import insert into them as Substr(TermShortDesc,1,8094), Substr(TermShortDesc,8095,8094), Substr(TermShortDesc,16188,8094).

Just throwing in my 2 centavos....|||This is fantasic, we've found the offending rows and everything is up and working, Thanks for your Help, Ed