Newbie Question on SQL DB

Hello All!
I know I can do this, but not sure of the best way. I have a users table,
with a userID as key. I also have a profile table. When a user logs in,
they can add to thier profile if they want. My question is, What is the bes
t
way to link that user ID so the user ID is filled in on the userID col in th
e
profile table, and the user profile sticks to that ID. Even if the user
comes back to add to it later.
I think I would have to return the value of the user ID, pass that forward
to the profile table, but I'm not sure.
TIA!!!
RudyRudy wrote:
> Hello All!
> I know I can do this, but not sure of the best way. I have a users
> table, with a userID as key. I also have a profile table. When a
> user logs in, they can add to thier profile if they want. My
> question is, What is the best way to link that user ID so the user ID
> is filled in on the userID col in the profile table, and the user
> profile sticks to that ID. Even if the user comes back to add to it
> later.
> I think I would have to return the value of the user ID, pass that
> forward to the profile table, but I'm not sure.
> TIA!!!
>
> Rudy
Create Table MyUsers (
UserID INT IDENTITY NOT NULL PRIMARY KEY,
UserName NVARCHAR(50))
Create Table UserProfiler (
UserID INT NOT NULL REFERENCES MyUsers(UserID),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
Not sure of your design, but assuming you had a relationship like the
above, you need to physically insert the UserID into the UserProfile
table. There is not way for SQL Server to know what UserID you want
interted, unless you're talking about a login name (are you?).
For a login name you could use suser_sname() and have it as the default
on the table:
Create Table UserProfile (
UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
OptionValue NVARCHAR(30) NOT NULL,
PRIMARY KEY CLUSTERED (UserID, OptionID) )
and then use:
Insert UserProfile (
OptionID, OptionValue)
Values (
50, N'Profiler Data')
if the user id is something your database stores separately from the
login name, you would need to send the value to SQL Server. So you might
grab the UserID when the user logs into the application and pass it to
the insert statement or pass it to a stored procedure to be inserted
into the profile table.
David Gugick
Imceda Software
www.imceda.com|||Hi David!
Thank you for the quick reply. So I am talking about a login name, and the
user ID is on the same table as the user name.
For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
I don't understand what you mean by the suser_sname as the default. It's
been awhile since I had to work with SQL, and was just learning at that time
.
Now that I need to use SQL a little bit more indepth than just making simple
tables, and passing values back and forth, I'm kinda in the weeds if you kno
w
what I mean. LOL
So now that I have set you up for my ignorance, how does one refrence? I
know about relationships and stuff, sorta. And I know I can use views to hav
e
data update automaticly from other tables. And views can be used just like
tables, right? Would I create a FK between the two tables using the userID?
But that doesn't update or keep the information of the userID the same, does
it?
I though if I could just return a value to what user was logged on, and then
that userID would link with the profile table, andthen the info can be
update. Maybe it would be easier if I had a table for just users who are
logged on?
Am I way off base or what?
Thank you for your time David!
Rudy
"David Gugick" wrote:

> Rudy wrote:
> Create Table MyUsers (
> UserID INT IDENTITY NOT NULL PRIMARY KEY,
> UserName NVARCHAR(50))
> Create Table UserProfiler (
> UserID INT NOT NULL REFERENCES MyUsers(UserID),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
>
> Not sure of your design, but assuming you had a relationship like the
> above, you need to physically insert the UserID into the UserProfile
> table. There is not way for SQL Server to know what UserID you want
> interted, unless you're talking about a login name (are you?).
> For a login name you could use suser_sname() and have it as the default
> on the table:
> Create Table UserProfile (
> UserID NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
> OptionID INT NOT NULL REFERENCES ProfileOptions(OptionID),
> OptionValue NVARCHAR(30) NOT NULL,
> PRIMARY KEY CLUSTERED (UserID, OptionID) )
> and then use:
> Insert UserProfile (
> OptionID, OptionValue)
> Values (
> 50, N'Profiler Data')
>
> if the user id is something your database stores separately from the
> login name, you would need to send the value to SQL Server. So you might
> grab the UserID when the user logs into the application and pass it to
> the insert statement or pass it to a stored procedure to be inserted
> into the profile table.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Rudy wrote:
> I don't understand what you mean by the suser_sname as the default.
suser_sname() is a function that returns the logged in user name.
My example showed a PK/FK reference. And as I mentioned, the FK value
does not update automatically, it just enforces values based on the
available PK values in the referenced table.
I think you need to spell out in a clear and concise way exactly what
you are trying to do, what all the data means, etc.
David Gugick
Imceda Software
www.imceda.com

Newbie question on BULK INSERT of text file

A non-SQL application appends messages to a plain text file.
I want to read the rows of this text file into a table.
The text is in a general format and should be considered one column.
The lines are terminated with a typical CR/LF.
I'd like to do more analysis on the text after getting it into a table.
My Problem: BULK INSERT will skip every other line
with the code below. It seems to take the CR/LF as a column
terminator and then skip over the next row to the new CR/LF
to consider the row to be complete.
How do I get rows of plain text from a text file to a table?
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'MyTextFile.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r',
ROWTERMINATOR = '\n'
)try taking out the field terminator parameter and make the row teminator the
combined "\r\n" instead.
If that fails, It's possible you might have to use "\n\r".
"Don Anthony" wrote:

> A non-SQL application appends messages to a plain text file.
> I want to read the rows of this text file into a table.
> The text is in a general format and should be considered one column.
> The lines are terminated with a typical CR/LF.
> I'd like to do more analysis on the text after getting it into a table.
> My Problem: BULK INSERT will skip every other line
> with the code below. It seems to take the CR/LF as a column
> terminator and then skip over the next row to the new CR/LF
> to consider the row to be complete.
> How do I get rows of plain text from a text file to a table?
> Create Table #MyTempFile ( FileLine varchar(250) )
> BULK INSERT #MyTempFile
> FROM 'MyTextFile.txt'
> WITH
> (
> BATCHSIZE = 50,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r',
> ROWTERMINATOR = '\n'
> )
>|||When I take out the FIELDTERMINATOR line I get the error show below
(tried various combinations of ROWTERMINATOR but get the same error).
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1.
Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
0x80004005: The provider did not give any information about the error.].
The statement has been terminated.|||You're right. My mistake. But I did get two different versions of bulk
insert ot work
including your original code. Are you sure there are no other stray
caharacters at the end of the lines other than Cr/LF? If you have a text
editor, check the hex display to make sure. Also, is it possible there are
data lines that are more than 250 bytes? Your table definition allows for
varchar(250).
This worked for me:
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r',
ROWTERMINATOR = '\n'
)
Textest.txt contains for records each with cr/lf line terminator:
1234567890
0987654321
abcdefghij
wxyzabcdef
This version worked too:
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r\n'
)
"Don Anthony" wrote:

> When I take out the FIELDTERMINATOR line I get the error show below
> (tried various combinations of ROWTERMINATOR but get the same error).
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1, column 1
.
> Make sure the field terminator and row terminator are specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any
> information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned
> 0x80004005: The provider did not give any information about the error.].
> The statement has been terminated.
>|||Your code works perfectly.
My code wasn't working because it wasn't quite what I originally indicated
(I did say I was a newbie...)
The real table definition was
CREATE TABLE #MyTempTable ( FileLine varchar(250), RowID int IDENTITY(1, 1)
)
not
CREATE TABLE #MyTempTable ( FileLine varchar(250) )
The bulk insert apparently threw away every other line after
failing to fit it into the identity column.
Everything works fine after I take out the extra column.
Thanks for your help.
"tthrone" wrote:sql

newbie question describing tables

How can I print the structure of a table from Enterprise Manager?
Or is there another app that has a print option?"MacKenzieMouse" <no-spamkeyconc@.yahoo.com> wrote in message
news:JrmdnX6w396rZ2XcRVn-1w@.comcast.com...
> How can I print the structure of a table from Enterprise Manager?
> Or is there another app that has a print option?

You can create and print database diagrams in EM (right-click Diagrams, New
Diagram), or if you use a third-party data modelling tool (Erwin,
Embarcadero etc.) then it would be able to do the same thing. If the CREATE
TABLE script is good enough, then you can generate it in Query Analyzer
(right-click a table in the Object Browser), then print the resulting
script.

Simon|||To print the structure of a table from Query Analyzer just print the
ouput of EXEC sp_help 'table_name' or a query from the
information_schema.columns table.
--
David Portas
SQL Server MVP
--|||David Portas wrote:
> To print the structure of a table from Query Analyzer just print the
> ouput of EXEC sp_help 'table_name' or a query from the
> information_schema.columns table.
> --
> David Portas
> SQL Server MVP
> --

How do I print it ?
Is there any way to do that using the Query Analayzer ?|||Select the Text ouput option (CTRL+T), run the query, select the
Results tab then File/Print or CTRL+P.
--
David Portas
SQL Server MVP
--|||Works great! Thanks !|||MacKenzieMouse wrote:
> How can I print the structure of a table from Enterprise Manager?
> Or is there another app that has a print option?
Thanks to all that helped!sql

Newbie question about initial table size

Hi all,

I've worked with informix for a very long time and this is my first aproach to sql server. I have an extremely simple design for a "small" database and at this moment I'm creating the tables, in informix I can assign a first extent and next extent size to the creation of the table so if your volume and growth analisys is good you can basically be sure that you will allways have contigous space on disk for your table. I'm readin BOL to see if I have that feature here but can't seem to find anything similar. Does that mean that my table data will be "fragmented" all over the primary and secondary files every time I load into them? Would it be a good practice to simulate the extents by creating a secondary file for each table with the size I require?

Any coments will be greatly appreciated :)

Luis TorresSpecifying that the db grow in reletively large chunks (so that the file doesn't grow very often) can reduce it's fragmentation. A seperate file for very large tables or for tables that get updated a lot is a good idea. Also you can set up amaintenance plan to run (daily, weekly, etc..) that can reorganize (optimize) the data & indexes.|||The size allocation for tables is done on the extent level. It means that if the last page of the initial extent is filled, a logically contiguous set of 8x8K pages is allocated for the new data. There may be fragmentation between the extents, and depending on your RAID level and array architecture the physical continuity of pages, but "logically" each extent is comprised of 8 continuous pages sitting in a row ;)|||Thanks pshisbey and rdjabarov for your comments, they are greatly appreciated :)

Luis Torres

Newbie question about indexes

Hi ,

I have following statement :

SELECT * FROM Table WHERE Col1=@.Var1 AND Col2=@.Var2 ... AND ColN=@.VarN

How should I design indexes for best performance ?
(
Add one index on columns Col1 till ColN
or add N indexes, first for column Col1, second for Col2, ...
)

Thanks, for your suggestions

How many rows do you expect it to return?
Do you prefer retrieval speed over update speed?
Do you have other queries that might benefit from individual indexes?

|||Strictly from your query perspective, you will only need one index with the key (col1...colN). However, this index will be useless if say col1 is missing from your where clause. So you need to evaluate full set of queries that you plan to run on this table. Also, as Eric points out, you will need to evaluate the cost of updating indexes if you have lots of updates/inserts/deletes
Thanks|||

Hi,

I expect to return c. 100 rows. (Would indexing strategy differs if I have return whole table ?)
Retrieval speed is priority.
I have no other queries for this table.

Thank you

|||Here's what the optimizer guys have to say:

One index should be fine if he always has every column in the WHERE condition and he is doing equality matching.

This will give two main plan options:

1. index lookup + fetch
2. table scan

It will be a cost-based decision.

Newbie question about employee counts

Hello,

I built a table with columns to count the employees. The columns are either Active or Term and if they are Active it has a 1, if not, 0. Same goes for Terms – 1 is they termed and 0 if not. It also has the current effective date.

Then I run a loop that builds a fact table with all the employees in each of the months for a year. So the table looks (something) like this:

RowKeyEmpKeyDeptKeyActiveTermDate

110751020061001

2111001020061001

31392 1 020061001

416921020061001

Then the next month:

RowKeyEmpKeyDeptKeyActiveTermDate

510751020061101

611100 1 020061101

713920120061101

816921020061101

This way I can keep track of who is the active employees each month as well as who terminated that month.and do year to date totals on the terminations, which I need for turnover calculations.

The issue is, when I view the data in a Reporting Services report, and I drill-down to a Department and look who is active, I also see the termed employees. My assumption is because they are part of the count – that part being zero. Is there a better way to approach this?

My guess was not having Active and Terms columns. Instead I thought of a single column with a StatusKey. But if I did that I wouldn’t know how to do the calculations of Year-to-Date terminations.

Any suggestion is greatly appreciated. As well as (constructive) criticism on this technique.

Thank you.

-Gumbatman

I don't see how using YTD can return correct results, because you would end up calculating the same employee as many times as many days he terminated. If you have Active and Term as measures, I'd replays 0 with NULL (there is a property of measure or measure group that says preserve null, I don't remember it's name from the top of my head). Again, if I remember correctly Reporting Services applyes Non Empty to the query, therefore in this case you wan't see employees that have NULL as active, if you use both Employess and Active in the query and you are not drilling down the time. As for calculating number of terminated employes I would use something like this:

count(filter(NonEmpty (employee.members, Term * Time.<today>), IsEmpty ((employe.currentmember, Term, Time.<CurrentYear>.firstchild.firstchild.firstchild)))) //calculates the number of employees that are marked as terminated today, but where still working on january first. Of course if you have laxuary to delete employees from the system that are gone more then a year, you can simplify this formula.

|||

Irina,

Thank you for the information.

What is strange (and I have to look at more closely) is that the YTD terms are calculating correctly even though I am probably double-counting them. I think I only added those who terminated in that year and I added them only to the last month of the year. I have to check that.

What I am still not too clear on is if I drill-down to a Product, will I see the terms and actives who are in that Product when all I want to see is the terms? Will the null, in the terms column, help me with that?

What about combining the Actives and Terms into a single dimension, it is sort of no longer a measure I guess?

Thanks for the help

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.
Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:

> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.
|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/Transacti...leGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?
|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:

> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/Transacti...leGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?
|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.

> How can I check which recovery mode is it in.
sp_helpdb
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...[vbcol=seagreen]
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:

newbie question :Truncate Table side effect

We have 23 tables in the database, for each table we have a auditlog table
having similar columns and few others like date created and date updated and
AuditLogId.
When the application runs for the first time we are allocating 300MB for MDF
file and 99MB for LDF file.
Audit Logs are eating up all the space, so when we get Primary File Group
Full error, we used BCP command to transfer the content to text files and
then run the truncate table command on each AuditTable.
We are noticing a unusual behaviour after running the BCP followed by
truncate table command. The database free space suddenly is being used up at
less slower pace. We are not having any data loss or audit table data loss.
For example, before running the BCP+truncate table, we can import 30
libraries in our application, but after running the BCP+truncate table we can
import way too many around 90 libraries.
I am not able to solve this mystery because I am not familiar with SQL
Server internals how it behaves. Any help will be greatly appreciated.Hi
Check the following:
That if you databse is set to "Full Recovery" mode, backup your transaction
log on a regular basis.
You can set the data and logs to grow automatically so that you do not run
out of space.
Regards
Mike
"Help_Me_Please" wrote:
> We have 23 tables in the database, for each table we have a auditlog table
> having similar columns and few others like date created and date updated and
> AuditLogId.
> When the application runs for the first time we are allocating 300MB for MDF
> file and 99MB for LDF file.
> Audit Logs are eating up all the space, so when we get Primary File Group
> Full error, we used BCP command to transfer the content to text files and
> then run the truncate table command on each AuditTable.
> We are noticing a unusual behaviour after running the BCP followed by
> truncate table command. The database free space suddenly is being used up at
> less slower pace. We are not having any data loss or audit table data loss.
> For example, before running the BCP+truncate table, we can import 30
> libraries in our application, but after running the BCP+truncate table we can
> import way too many around 90 libraries.
> I am not able to solve this mystery because I am not familiar with SQL
> Server internals how it behaves. Any help will be greatly appreciated.|||If you are talking about the database free space in enterprise manager then
that is data and log so it would probably be the logs that are causing the
problem.
If the database is in full recovery mode then transaction logs will fill up
until truncated or baced up. This does not happen until after the first
backup though (log is automatically truncated until then as the backups are
useless without a full backup). Maybe your truncate is causing the same
effect and you would start using more space after the next full backup.
Have a look at
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
If it's to do with just data then it's probably fragmentation or the way you
are checking the file space.
30 - 90 sounds a lot though.
How is the audit trail taken? Do you have something that depends on the
previous data?|||The tables I am truncating do not have any foreign key constraints hence
truncation did not have any problem.
After the BCP + truncation, I tried to run the automated test to fill up the
database, the behaviour I noticed is the LDF file is still 101MB, while the
MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
the transaction logs are not being written?
How can I check which recovery mode is it in. I want to check before we run
into the problem and after we run the truncate cmd.
Thank you so much for the explaination.
Regards.
"Nigel Rivett" wrote:
> If you are talking about the database free space in enterprise manager then
> that is data and log so it would probably be the logs that are causing the
> problem.
> If the database is in full recovery mode then transaction logs will fill up
> until truncated or baced up. This does not happen until after the first
> backup though (log is automatically truncated until then as the backups are
> useless without a full backup). Maybe your truncate is causing the same
> effect and you would start using more space after the next full backup.
> Have a look at
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> If it's to do with just data then it's probably fragmentation or the way you
> are checking the file space.
> 30 - 90 sounds a lot though.
> How is the audit trail taken? Do you have something that depends on the
> previous data?|||> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
No. It just means that the LDF file were large enough to hold the log records produced by your
modifications.
> How can I check which recovery mode is it in.
sp_helpdb
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Help_Me_Please" <HelpMePlease@.discussions.microsoft.com> wrote in message
news:90ADC3D5-3E79-4D6F-B0A7-9A754F876A09@.microsoft.com...
> The tables I am truncating do not have any foreign key constraints hence
> truncation did not have any problem.
> After the BCP + truncation, I tried to run the automated test to fill up the
> database, the behaviour I noticed is the LDF file is still 101MB, while the
> MDF file is 1.2 GB. i.e., now LDF file is not growing as fast. Does that mean
> the transaction logs are not being written?
> How can I check which recovery mode is it in. I want to check before we run
> into the problem and after we run the truncate cmd.
> Thank you so much for the explaination.
> Regards.
> "Nigel Rivett" wrote:
> > If you are talking about the database free space in enterprise manager then
> > that is data and log so it would probably be the logs that are causing the
> > problem.
> > If the database is in full recovery mode then transaction logs will fill up
> > until truncated or baced up. This does not happen until after the first
> > backup though (log is automatically truncated until then as the backups are
> > useless without a full backup). Maybe your truncate is causing the same
> > effect and you would start using more space after the next full backup.
> >
> > Have a look at
> > http://www.nigelrivett.net/TransactionLogFileGrows_1.html
> >
> > If it's to do with just data then it's probably fragmentation or the way you
> > are checking the file space.
> > 30 - 90 sounds a lot though.
> > How is the audit trail taken? Do you have something that depends on the
> > previous data?

Newbie Question - Table only Backup

I may be missing something... but I simply want to backup a single table from my DB inorder to export it to another server (via FTP)... is there a way to do this specifically in MS SQL 7 ' I want to save the backup on my desktop, for instance... and not into another DB on the server... am I clear or missing something..
Thanks !!Put the table on a different filegroup, since you can back up filegroups
separately. You can't back up tables separately.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Joe Mizrahi" <mizrahij@.finance.nyc.gov> wrote in message
news:00470D0D-6DC6-4A59-AEF9-80FAE5EA26A2@.microsoft.com...
> I may be missing something... but I simply want to backup a single table
from my DB inorder to export it to another server (via FTP)... is there a
way to do this specifically in MS SQL 7 ' I want to save the backup on my
desktop, for instance... and not into another DB on the server... am I clear
or missing something...
> Thanks !!|||Note that such a backup is possibly unusable for Joe's scenario. When
restoring an FG backup you have to do that into the same database from where
you took the backup and also apply all log backups taken since, up to
current point in time.
Joe,
another option is to export he data in the table, using such tools as BCP,
DTS etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eIhT5OpvDHA.2408@.tk2msftngp13.phx.gbl...
> Put the table on a different filegroup, since you can back up filegroups
> separately. You can't back up tables separately.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Joe Mizrahi" <mizrahij@.finance.nyc.gov> wrote in message
> news:00470D0D-6DC6-4A59-AEF9-80FAE5EA26A2@.microsoft.com...
> > I may be missing something... but I simply want to backup a single table
> from my DB inorder to export it to another server (via FTP)... is there a
> way to do this specifically in MS SQL 7 ' I want to save the backup on my
> desktop, for instance... and not into another DB on the server... am I
clear
> or missing something...
> > Thanks !!
>|||Ah, that's a good point. Yes, you could DTS the table to another database,
and backup from there. Or keep the table in its own separate database
permanently, I suppose.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eCdyUSpvDHA.1512@.TK2MSFTNGP10.phx.gbl...
> Note that such a backup is possibly unusable for Joe's scenario. When
> restoring an FG backup you have to do that into the same database from
where
> you took the backup and also apply all log backups taken since, up to
> current point in time.
> Joe,
> another option is to export he data in the table, using such tools as BCP,
> DTS etc.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:eIhT5OpvDHA.2408@.tk2msftngp13.phx.gbl...
> > Put the table on a different filegroup, since you can back up filegroups
> > separately. You can't back up tables separately.
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.aspfaq.com/
> >
> >
> >
> >
> > "Joe Mizrahi" <mizrahij@.finance.nyc.gov> wrote in message
> > news:00470D0D-6DC6-4A59-AEF9-80FAE5EA26A2@.microsoft.com...
> > > I may be missing something... but I simply want to backup a single
table
> > from my DB inorder to export it to another server (via FTP)... is there
a
> > way to do this specifically in MS SQL 7 ' I want to save the backup on
my
> > desktop, for instance... and not into another DB on the server... am I
> clear
> > or missing something...
> > > Thanks !!
> >
> >
>

newbie question - sp help

hi all,

i need some helpful advise from you.
i need to write a sp that you may be able to help me with.

i have a table that only contains a numeric field. i need to write a
sp that, when executed, the numberic field increments by one and then
returns the new numeric. i have looked on the net for this, but have
not had any look...

can you please help in any way? i know nothing about writing sp's!

- hollis..Try something like:
/***********START SP

CREATE PROCEDURE numberTest @.numberIn int

AS

declare @.numberOut int

set @.numberOut = @.numberIn + 1

print @.numberOut

/***********END SP

You will need to manupulate to fit in excatly with what you need

JV
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector -
www.ciquery.com/tools/sqldirector/

"Hollis" <hollis_uk@.lycos.co.uk> wrote in message
news:98c2c468.0309091201.50cd813d@.posting.google.c om...
> hi all,
> i need some helpful advise from you.
> i need to write a sp that you may be able to help me with.
> i have a table that only contains a numeric field. i need to write a
> sp that, when executed, the numberic field increments by one and then
> returns the new numeric. i have looked on the net for this, but have
> not had any look...
> can you please help in any way? i know nothing about writing sp's!
> - hollis..|||hollis_uk@.lycos.co.uk (Hollis) wrote in message news:<98c2c468.0309091201.50cd813d@.posting.google.com>...
> hi all,
> i need some helpful advise from you.
> i need to write a sp that you may be able to help me with.
> i have a table that only contains a numeric field. i need to write a
> sp that, when executed, the numberic field increments by one and then
> returns the new numeric. i have looked on the net for this, but have
> not had any look...
> can you please help in any way? i know nothing about writing sp's!
> - hollis..

This is one possible way:

create proc dbo.GetNextID
@.NextID int output
as
update dbo.MyTable
set @.NextID = NumericColumn = NumericColumn + 1
go

declare @.id int
exec dbo.GetNextID @.NextID = @.id output
select @.id
go

Simon

Newbie Question - Please be gentle!

Can I run a script as an automated process?
i.e. I have a simple script that deletes the content of a table so I can
import fresh data, I know how to set up the DTS to import the data
automatically, but I have to run the script every day manually before the DTS
runs & I want the delete script to run at a predefined time rather than
having to remember to do it. Does that make sense?
Tia
Jonathan
Sure. It's hard to give precise directions without know what you're scripts
look like, but... if you already have a DTS job you can easily create a
step in that package (that's what the DTS container is called) that will cun
a TSQL script. Then you can easily schedule that from SQLAgent. You can
right click on the job name from DTS and select 'schedule job' which will
walk you throught the process of setting up the DTS package to run from SQL
agent.
Hope that helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:F7E842B8-BFD6-4922-8ED2-4AD0BCE55CA6@.microsoft.com...
> Can I run a script as an automated process?
> i.e. I have a simple script that deletes the content of a table so I can
> import fresh data, I know how to set up the DTS to import the data
> automatically, but I have to run the script every day manually before the
DTS
> runs & I want the delete script to run at a predefined time rather than
> having to remember to do it. Does that make sense?
> Tia
> Jonathan

Newbie Question - Please be gentle!

Can I run a script as an automated process?
i.e. I have a simple script that deletes the content of a table so I can
import fresh data, I know how to set up the DTS to import the data
automatically, but I have to run the script every day manually before the DT
S
runs & I want the delete script to run at a predefined time rather than
having to remember to do it. Does that make sense?
Tia
JonathanSure. It's hard to give precise directions without know what you're scripts
look like, but... if you already have a DTS job you can easily create a
step in that package (that's what the DTS container is called) that will cun
a TSQL script. Then you can easily schedule that from SQLAgent. You can
right click on the job name from DTS and select 'schedule job' which will
walk you throught the process of setting up the DTS package to run from SQL
agent.
Hope that helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:F7E842B8-BFD6-4922-8ED2-4AD0BCE55CA6@.microsoft.com...
> Can I run a script as an automated process?
> i.e. I have a simple script that deletes the content of a table so I can
> import fresh data, I know how to set up the DTS to import the data
> automatically, but I have to run the script every day manually before the
DTS
> runs & I want the delete script to run at a predefined time rather than
> having to remember to do it. Does that make sense?
> Tia
> Jonathan

Newbie Question - Please be gentle!

Can I run a script as an automated process?
i.e. I have a simple script that deletes the content of a table so I can
import fresh data, I know how to set up the DTS to import the data
automatically, but I have to run the script every day manually before the DTS
runs & I want the delete script to run at a predefined time rather than
having to remember to do it. Does that make sense?
Tia
JonathanSure. It's hard to give precise directions without know what you're scripts
look like, but... if you already have a DTS job you can easily create a
step in that package (that's what the DTS container is called) that will cun
a TSQL script. Then you can easily schedule that from SQLAgent. You can
right click on the job name from DTS and select 'schedule job' which will
walk you throught the process of setting up the DTS package to run from SQL
agent.
Hope that helps,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:F7E842B8-BFD6-4922-8ED2-4AD0BCE55CA6@.microsoft.com...
> Can I run a script as an automated process?
> i.e. I have a simple script that deletes the content of a table so I can
> import fresh data, I know how to set up the DTS to import the data
> automatically, but I have to run the script every day manually before the
DTS
> runs & I want the delete script to run at a predefined time rather than
> having to remember to do it. Does that make sense?
> Tia
> Jonathan

Newbie Question - Automatically update

Maybe my question is not at the right place but I'm totally new to this.

So I have the following scenario:

I have a DB with one table with five records. I create a cube on this. Based on the cube I have to show some KPIs. Everyday I have one more record in my table. If someone clicks on this site it has to show the current uptodate KPIs every day. How can I do this?

If I understand right I have to process the cubes manually to show the actual datas from the DB. Is there a way to automate this somehow?

thanks for any your help

Create an integration services package that processes the cube and then create a job with the Sql Server Agent that consists of running this package. Schedule the job to run once nightly. If you need more flexibility then integration services offers you you'll have to write your own .Net program with AMO and then schedule it similarly but normally this shouldn't be needed.|||

First, thanks for the quick answer it helped me a lot.

The only what I don't really know how to process the cube in the Integration services package. Could you please help me with this?

|||

SSIS has a Processing task - from memory it looks like a little cube - which you can drag onto your control flow. You then set which objects you want to process.

Another option may be proactive caching - it depends on your structures and what sort of transformations you are doing to your data. In a way it allows you to configure your cubes to process themselves.

see: http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx

|||

Thanks Guys it helped me a lot.

Is there a way to run this from the site? I mean If the user would like to refresh the datas click on a button and it processes the cube and refreshes the data.

|||

I'm assuming you'll have to write some code yourself - either to run the package or agent job programmatically which seems to be described here (though I haven't tested it myself):

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

Alternatively you bypass the package entirely and writes a program in AMO that works directly at Analysis Services and processes the cube. AMO is described here:

http://msdn2.microsoft.com/en-us/library/ms345092.aspx

Newbie Question

Table 1
Order Number
1234
4321
5601
Table 2
Order Number
1234
4321
How do I Get SQL to Return that the Order number 5601 does not match any Order number in Table 2:Try:

SELECT Table1.OrderNumber
FROM Table1 LEFT JOIN Table2 ON Table1.OrderNumber = Table2.OrderNumber
WHERE Table2.OrderNumber Is Null

Newbie question

Is it necessary for the table structure on the publisher from whom an article will be updated on the subscriber, have the same structure as the subscriber table?
Wilma,
replication can be set up like this (nosync), but it is not necessary. The
simplest method is to have a snapshot applied on the subscriber - a process
referred to as initialization. In this case nothing needs to be on the
subscriber prior to setting off the publication.
Regards,
Paul Ibison
|||Thankyou Paul

Newbie Question

I am trying to sum some data in a table, and I can't figure it out. Below
is some sample data
Date,Customer,Order_Amt,Paid_Amt
2004-10-02 00:00:00,101,1418.82,-1400.00
2004-10-02 00:00:00,101,265.21,-200.00,
2004-10-02 00:00:00,101,648.74,-648.74
2004-10-02 00:00:00,102,95.95,-95.95
2004-10-02 00:00:00,102,457.42,-450.00
I trying to sum all of the orders for each customer from yesterday, without
showing each order, just the date, customer number, total orders, total
paid. I also want to add a 5th column stating order_amt-Paid_amt. Although
pathetic, this is the furthest I got :
select date, cust, order_amt, paid_amt from day_sales
where date > getdate()-2
group by date, cust, order_amt, paid_amt
I would these results :
date,cust,order_total,paid_total,amt_owe
d
2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
Any help would be greatly appreciated.
ThanksTry this:
select date, cust,
Count(*) OrderCount,
Sum(order_amt) TotalAmt,
Sum(paid_amt) TotalPaid,
Sum(order_amt-Paid_Amt) Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||Hi
Check out GROUP BY and SUM in books online.
Try (untested);
Select date, cust, SUM(order_amt), SUM(paid_amt), SUM(order_amt)
-SUM(paid_amt) AS Outstanding, COUNT(*) AS No_Orders from day_sales
where date > getdate()-2
group by date, cust
Your getdate()-2 may not give you the exact information required if you
have times with the orders to round to day one way is to use convert e.g.
Select CONVERT(CHAR(8),date,112) AS Date, cust, SUM(order_amt),
SUM(paid_amt), SUM(order_amt) -SUM(paid_amt) AS Outstanding, COUNT(*) AS
No_Orders from day_sales
where CONVERT(CHAR(8),date,112) >= CONVERT(CHAR(8),getdate()-2,112)
group by CONVERT(CHAR(8),date,112), cust
John
"J Abrams" wrote:

> I am trying to sum some data in a table, and I can't figure it out. Below
> is some sample data
> Date,Customer,Order_Amt,Paid_Amt
> 2004-10-02 00:00:00,101,1418.82,-1400.00
> 2004-10-02 00:00:00,101,265.21,-200.00,
> 2004-10-02 00:00:00,101,648.74,-648.74
> 2004-10-02 00:00:00,102,95.95,-95.95
> 2004-10-02 00:00:00,102,457.42,-450.00
> I trying to sum all of the orders for each customer from yesterday, withou
t
> showing each order, just the date, customer number, total orders, total
> paid. I also want to add a 5th column stating order_amt-Paid_amt. Althou
gh
> pathetic, this is the furthest I got :
> select date, cust, order_amt, paid_amt from day_sales
> where date > getdate()-2
> group by date, cust, order_amt, paid_amt
> I would these results :
> date,cust,order_total,paid_total,amt_owe
d
> 2004-10-02 00:00:00,101,2332.77,-2248.74,84.03
> Any help would be greatly appreciated.
> Thanks
>
>|||thanks for your help, that did the trick. I have one quick question. How
does sql know how to count the total orders per customer ? What if I wanted
a count of all of the orders that equal 1418.82 ? I don't see anything in
the script below that points the count command to the customer number.
Thanks again for your help, I really appreciate it !!
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
> Try this:
> select date, cust,
> Count(*) OrderCount,
> Sum(order_amt) TotalAmt,
> Sum(paid_amt) TotalPaid,
> Sum(order_amt-Paid_Amt) Balance
> from day_sales
> where date > DateAdd(day, -2, getdate())
> group by date, cust
> "J Abrams" wrote:
>|||The group By statement says to SQL:
1) Collect all the records which match the criteria, and group them into one
groups, based on the values of the columns Cust, and Date, Then
2) Output ONE ROW for each of those GROUPS...
3) Any expression in the Select Clause, which has an aggregate function
(Sum, Count, Min, Max, etc.) IS then evaluated for ALL The records in each o
f
those constructed groups...
"J Abrams" wrote:

> thanks for your help, that did the trick. I have one quick question. How
> does sql know how to count the total orders per customer ? What if I want
ed
> a count of all of the orders that equal 1418.82 ? I don't see anything in
> the script below that points the count command to the customer number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
>
>|||Hi
If you wanted to restrict the whole query to add up certain rows i.e an
order_amt value of 1418.82 then you would need to add this to the where
clause.
i.e.
where date > DateAdd(day, -2, getdate())
and order_amt = 1418.82
You should notice that the OrderCount column is less than without the
additional clause (unless the customer only ever orders the one
amount!).
If you wanted to have an additional count that summed everything but
counted the number of times they ordered for an amout of 1418.82
select date, cust,
Count(*) AS OrderCount,
SUM(CASE WHEN order_amt = 1418.82 THEN 1 ELSE 0 END) AS
OrderedSpecificAmtCount,
Sum(order_amt) AS TotalAmt,
Sum(paid_amt) AS TotalPaid,
Sum(order_amt-Paid_Amt) AS Balance
from day_sales
where date > DateAdd(day, -2, getdate())
group by date, cust
The rest is in books online. Please spend some time reading it as is a
very rich source of information.
John
J Abrams wrote:
> thanks for your help, that did the trick. I have one quick question.
How
> does sql know how to count the total orders per customer ? What if I
wanted
> a count of all of the orders that equal 1418.82 ? I don't see
anything in
> the script below that points the count command to the customer
number.
> Thanks again for your help, I really appreciate it !!
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:18FE707F-8A17-48C5-BEFE-DAE19FADF953@.microsoft.com...
out.
yesterday,
total

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type, first_name, last_name. I need help creating a query that will pull from an excel file that have the same colum names. This excel file is automaticaly generated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if there are new records in that xls file it will add those to the table or if the records have changed it will update the records within the table based on the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
Thanks
Eric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks
|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or if
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>
sql

Newbie Question

I have a table called cr. Within that table I have the colums c_id, c_type,
first_name, last_name. I need help creating a query that will pull from an e
xcel file that have the same colum names. This excel file is automaticaly ge
nerated from one of our sys
tems and the c_id will never change for the record.
So this is what im wanting to do. I need it to look at the xls file and if t
here are new records in that xls file it will add those to the table or if t
he records have changed it will update the records within the table based on
the c_id. I think this is
feasable but I can't seem to get it to work. And example would be great.
ThanksEric
1) DTS package
2) SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> I have a table called cr. Within that table I have the colums c_id,
c_type, first_name, last_name. I need help creating a query that will pull
from an excel file that have the same colum names. This excel file is
automaticaly generated from one of our systems and the c_id will never
change for the record.
> So this is what im wanting to do. I need it to look at the xls file and if
there are new records in that xls file it will add those to the table or if
the records have changed it will update the records within the table based
on the c_id. I think this is feasable but I can't seem to get it to work.
And example would be great.
> Thanks|||and that will insert or update any records?
THanks
"Uri Dimant" wrote:

> Eric
> 1) DTS package
> 2) SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:254E3DCB-06A0-4D59-9BAB-120B33A59F0B@.microsoft.com...
> c_type, first_name, last_name. I need help creating a query that will pull
> from an excel file that have the same colum names. This excel file is
> automaticaly generated from one of our systems and the c_id will never
> change for the record.
> there are new records in that xls file it will add those to the table or i
f
> the records have changed it will update the records within the table based
> on the c_id. I think this is feasable but I can't seem to get it to work.
> And example would be great.
>
>

Newbie question

Hi all,
I need an autonumber field in a table.
I set the following parameters for the field:
data type - int,
identity - yes (not for replication)
identity seed - 1
identity increment - 1
Is this the right thing to do?
TIA
CSharpHi,
You are right.
Thanks
Hri
MCDBA
"CSharp" <smitha@.asianetindia.com> wrote in message
news:OMXoyYuFEHA.3180@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I need an autonumber field in a table.
> I set the following parameters for the field:
> data type - int,
> identity - yes (not for replication)
> identity seed - 1
> identity increment - 1
> Is this the right thing to do?
> TIA
> CSharp
>