Addition to the above.

I forgot to add, you'll see that ZONE has less records than STATE, thats
because the current system is storing NAMES not keys, and a ZONE name can be
in more than on STATE, e.g. STATE:"New York" can have a zone called
ZONE:"AGRICULTURE" and STATE:"California" can have a zone called
ZONE:"AGRICULTURE"Sorry, another thing... the record counts in the first post are DISTINCT
counts...

Adding unique tags to associate records

Hello,
Is there anyway to embed tags that use puctuation into a coulmn of an FTS
table and be able to serach for these tags using the Contains predicate? For
example, I would like to add the tag,"CDL=Y", to a column and search for this
value using the Contains predicate. I'm not sure how this tag gets indexed
but there are a couple of problems using the Contains predicate. The First
problem is puncuation is ignored. The next problem is "Y" is noise. This
means that "CDL=Y" is equal to "CDL". I would like to have the entire
word/tag ("CDL=Y") be a unique value so I could identify records with this
specific feature. Thanks in advance.
Dave
You will have to use underscores for this as SQL FTS does not index
properties and their values. So replace this CDL=Y with this CDL_Y in your
content and queries.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DaveS" <DaveS@.discussions.microsoft.com> wrote in message
news:4B924102-F70F-4300-8CFF-7901722B35C0@.microsoft.com...
> Hello,
> Is there anyway to embed tags that use puctuation into a coulmn of an FTS
> table and be able to serach for these tags using the Contains predicate?
For
> example, I would like to add the tag,"CDL=Y", to a column and search for
this
> value using the Contains predicate. I'm not sure how this tag gets
indexed
> but there are a couple of problems using the Contains predicate. The
First
> problem is puncuation is ignored. The next problem is "Y" is noise. This
> means that "CDL=Y" is equal to "CDL". I would like to have the entire
> word/tag ("CDL=Y") be a unique value so I could identify records with this
> specific feature. Thanks in advance.
> Dave
>
|||Another option is to use XQuery in SQL 2005 and store the doc in an XML data
type column. You could do a contains query against the document and then
refine the search to an particular element or attribute having this value.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DaveS" <DaveS@.discussions.microsoft.com> wrote in message
news:4B924102-F70F-4300-8CFF-7901722B35C0@.microsoft.com...
> Hello,
> Is there anyway to embed tags that use puctuation into a coulmn of an FTS
> table and be able to serach for these tags using the Contains predicate?
For
> example, I would like to add the tag,"CDL=Y", to a column and search for
this
> value using the Contains predicate. I'm not sure how this tag gets
indexed
> but there are a couple of problems using the Contains predicate. The
First
> problem is puncuation is ignored. The next problem is "Y" is noise. This
> means that "CDL=Y" is equal to "CDL". I would like to have the entire
> word/tag ("CDL=Y") be a unique value so I could identify records with this
> specific feature. Thanks in advance.
> Dave
>
|||Dave,
To understand your environment and to help troubleshoot this issue, could
you reply with the results of the following T-SQL code?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
The OS platform that you have SQL Server installed on as well as the
OS-supplied wordbreaker is an important aspect of understanding this issue,
for more details see: http://groups.google.com/groups?q=langwrbk+infosoft
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"DaveS" <DaveS@.discussions.microsoft.com> wrote in message
news:4B924102-F70F-4300-8CFF-7901722B35C0@.microsoft.com...
> Hello,
> Is there anyway to embed tags that use puctuation into a coulmn of an FTS
> table and be able to serach for these tags using the Contains predicate?
For
> example, I would like to add the tag,"CDL=Y", to a column and search for
this
> value using the Contains predicate. I'm not sure how this tag gets
indexed
> but there are a couple of problems using the Contains predicate. The
First
> problem is puncuation is ignored. The next problem is "Y" is noise. This
> means that "CDL=Y" is equal to "CDL". I would like to have the entire
> word/tag ("CDL=Y") be a unique value so I could identify records with this
> specific feature. Thanks in advance.
> Dave
>
sql

Adding Records to SQL DB

Here's my dilema. I'm using VB.Net 2003 and SQL DB

I'm having a problen adding a record to a table in my SQL DB

My previous experience has been working with Access databases.

When I try to create an SQLDataAdapter using the wizard from the toolbox the wizard will only create the Select statement. it won't create the UPDATE, DELETE or INSERT statements. I'm not sure why, I even tried using the form wizard and it still wouldn't work...

My tables all have primary keys defined.

So I decided that I would try to create everything programatically.

What I need to know is how to create the INSERT statement and which statements to use to add the record to the table.

I know that when using the OLEDataAdapter with the Acces DB I would do something like the following code.

MyNewRow = MyDatSet.Tables("MyTable").NewRow

MyDataSet.Tables("MyTable").Rows.Add(MyNewRow)
MyDataAdapter.Update(MyDataSet, "MyTable")

I'm pretty sure that for an SQL DB it is different but I don't know how to do it.

Can anyone please help ?

I should also mention that all my textbox fields on my form are databound..

thanks

tattoo

If you define no command on the dataadapter, then no command will be executed. Have a look at the commandbuilder. It will create commands for the apropiate DELETE / INSERT / UPDATE statements based on the underlying schema. If you don′t want to use the (auto)generated command, you can also assign these commands manully. But if you do not give the dataadater any information on how to proceed on the DML action, no action will be done during the udate.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Adding records to an SQL table

I trying to add records from a table in DBF format (created with Visual FoxPro) into an SQL table created with SQL Server 2005.

I first converted the dbf file into an ascii file and then in the Query Editor in the SQL Server I typed:

use [c:\develope\sql\sqldata\psw.listener]

append from 'c:\develope\sql\data\listener.txt' type sdf

go

and then I pressed F5. I get an error that says:

Msg 911, Level 16, State 1, Line 1

Could not locate entry in sysdatabases for database 'c:\develope\sql\sqldata\psw.listener'. No entry found with that name. Make sure that the name is entered correctly.

I checked in the directory c:\develope\sql\sqldata\ and the file psw.mdf is there and when I look in the psw database, the table listener is also there.

Could someone tell me what is going wrong? Also, how do I append the whole dbf table onto an SQL table. there's about 6000 records.

Thanks.

Hi Bob,

the question as asked does not seem to be for the Integration Services forum.

You should be able to do this using SSIS though by using a data flow with a source to read rows from DBF and a destination component to load those rows into your SQL Server table.

HTH.

Adding Records in SQL Server Express 2005

When adding records containing a date field in SQL Server 2005 Express I get an errorInput string was not in a correct format. Do datefields have to be converted when adding or editing into strings?

I usually insert dates into SQL in String format. SQL can convert them by himself. BUT at least in our environment you have to create the date string in "MM.DD.YYYY" format so if you are trying "DD.MM.YYYY" It propably wont work though I know this could be solved with localization somewhere, somehow :)
|||

I do have it in string format. Any Ideas? My code excerpt

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:Web2005ConnectionString1 %>"

ProviderName="<%$ ConnectionStrings:Web2005ConnectionString1.ProviderName %>"

SelectCommand="SELECT * FROM [People] WHERE ([id] = @.id)"

InsertCommand="INSERT INTO [People] ([LastName], [FirstName], [Price], [LogDate]) VALUES (@.LastName, @.FirstName, @.Price, @.LogDate)"UpdateCommand="UPDATE [People] SET [LastName] = @.LastName, [FirstName] = @.FirstName, [Price] = @.Price, [LogDate] = @.LogDate WHERE [id] = @.id"DeleteCommand="DELETE FROM [People] WHERE [id] = @.id">

<SelectParameters>

<asp:ControlParameterControlID="GridView1"Name="id"PropertyName="SelectedValue"

Type="Int64"/>

</SelectParameters>

<UpdateParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

<asp:ParameterName="id"Type="Int64"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="LastName"Type="String"/>

<asp:ParameterName="FirstName"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="LogDate"Type="DateTime"/>

</InsertParameters>

Adding records

Hello guys,

I hope someone can help me!

I have a (probably simple) problem with compact edition, I can't seem to add records using VB 6.0. Here's some sample code I've been testing with:

Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset

Set pConn = New ADODB.Connection
Set pRS = New ADODB.Recordset

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\test.sdf"
'pConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb"
pConn.Open

pConn.Execute "INSERT INTO tblActions ([Branch ID], [Computer ID], [User ID], [Program ID], [Type ID], ID, [Extra 1], [Extra 2]) " & _
"VALUES (1, 1, 1, 1, 1, 1, 'some text', 'more text')"

' Open the recordset
pRS.Open "tblActions", pConn, adOpenForwardOnly, adLockOptimistic

pRS.AddNew
pRS![Branch ID] = 1
pRS![Computer ID] = 1
pRS![User ID] = 1
pRS![Program ID] = 1
pRS![Type ID] = 1
pRS![ID] = 1
pRS![Extra 1] = 1
pRS![Extra 2] = 1
pRS.Update

pRS.Close
pConn.Close

Set pRS = Nothing
Set pConn = Nothing

I can add the records using an SQL statement, but when I try to open the recordset and add a record using the AddNew method it fails with the message:

The command contained one or more errors. [,,,,,]

But, if I connect to an Access database (which uses exactly the same tables, etc.) using the commented out connection string in the above sample, the code works fine. What am I doing wrong?

Many thanks in advance!

I think you will have to rewrite your code to use a SqlCeResultSet instead of a ADODB.RecordSet if you want to use funtionality similar to .AddNew. For a example using using SqlCeResultSet.CreateRecord and using the SqlCeUpdateableRecord, see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.createrecord.aspx

|||

Thanks for the response, but the code you've linked to shows VB.net code, and I need to use VB 6.0. Surely there must be some way to create records in a similar way to using the .AddNew method and an SQL CE database.

My problem is that the code needs to be able to link to either an SQL CE database or an Access database, and must be written in VB 6.0.

|||Sorry, didn't think about the VB 6 issue. I think the only way forward will be to use pConn.Execute with an INSERT statement. Where does the error occur - when calling .AddNew or .Update ?|||

Neither unfortunately, it fails when opening the recordset.

I can open it fine for reading, but as soon as I add the adLockOptimistic parameter, it fails, I've tried all lock types too just in case.

|||

I think the only way forward will be to use pConn.Execute with an INSERT statement.

|||Thanks again Erik, I guess I'll have to struggle through with that for now.

Adding records

Hi
How do i simply add records to my Ms SQL database??
Does anyone have an example?
Best regards
/ErikSure. There are a lot of ways to do it, but the most common is to usethe INSERT INTO statement with data for each field in the table:

INSERT INTO MyTable (ItemID, Description) VALUES (43, 'ASP.NET application builder tool')

There are also variations, such as to insert data that exists in another table.
Don

Adding locationID's to detail records using SQL syntax

SQLserver 2K
Table A (Customer Master)
CustID
CustName
Table B (Customer Location)
CustID
LocationID
LocationOrderID
LocationName
What's the syntax to automatically add LocationOrderID in increment of 1 for
table B where B.CustID = A.CustID ?
For example,
A.CustID = 100
and there are 5 B records having B.CustID = 100
I need to insert LocationOrderID starting with 1 to B.LocationOrderID based
on the order of B.LocationName
Any help is greatly appreciated.
BillTry,
update tableB
set LocationOrderID = (select count(*) from tableB as a where a.CustID =
tableB.CustID and a.LocationName <= tableB.LocationName)
go
AMB
"Bill Nguyen" wrote:

> SQLserver 2K
> Table A (Customer Master)
> CustID
> CustName
> Table B (Customer Location)
> CustID
> LocationID
> LocationOrderID
> LocationName
> What's the syntax to automatically add LocationOrderID in increment of 1 f
or
> table B where B.CustID = A.CustID ?
> For example,
> A.CustID = 100
> and there are 5 B records having B.CustID = 100
> I need to insert LocationOrderID starting with 1 to B.LocationOrderID base
d
> on the order of B.LocationName
>
> Any help is greatly appreciated.
> Bill
>
>|||Alejandro;
This works great!
Thanks
Bill
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9E0D3718-1900-409C-BAFB-885F32212EB9@.microsoft.com...
> Try,
> update tableB
> set LocationOrderID = (select count(*) from tableB as a where a.CustID =
> tableB.CustID and a.LocationName <= tableB.LocationName)
> go
>
> AMB
> "Bill Nguyen" wrote:
>

adding identifier number to records

I have a table already created and need to add an autonumber/ unique identif
ier
field.
If the table already contains records can I just add the new autonumber fiel
d
and run a query to populate this field for all records with unique values?
how do I do this?
are their other ways of doing this ? and how?
thanks
ChrisChris,
When you add and identity column to your table, SQL Server automatically
populates the column. You can not update an identity column.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
drop table t
go
AMB
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||I added the unique identifier column using table design.
However when I queried the database the values were all null.
Also since there are a lot of records in the table and generating the
alpha-numeric values does take processing time, I noticed adding the column
did not require any processing, (i.e. no hour glass).
Also I would prefer to just use a big int as it will take up less space and
be faster.
How can I use @.@.identity to update each record? is this possible?
Gracias Alejandro,
Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
forums.
Perdone mi espanol, estoy apprendiendo.
Por favor, constesta en ingles :)
"Alejandro Mesa" wrote:
> Chris,
> When you add and identity column to your table, SQL Server automatically
> populates the column. You can not update an identity column.
> Example:
> use northwind
> go
> create table t (
> colA char(1) not null unique
> )
> go
> insert into t values('a')
> insert into t values('b')
> insert into t values('c')
> go
> select * from t
> go
> alter table t
> add colB int not null identity
> go
> select * from t
> go
> drop table t
> go
>
> AMB
>
> "Chris" wrote:
>|||As I told you, you can not update an identity column.
AMB
"Chris" wrote:
> I added the unique identifier column using table design.
> However when I queried the database the values were all null.
> Also since there are a lot of records in the table and generating the
> alpha-numeric values does take processing time, I noticed adding the colum
n
> did not require any processing, (i.e. no hour glass).
> Also I would prefer to just use a big int as it will take up less space an
d
> be faster.
> How can I use @.@.identity to update each record? is this possible?
> Gracias Alejandro,
> Tu eres muy asusto. He vido muchas siertos y respuestas de usted en los
> forums.
> Perdone mi espanol, estoy apprendiendo.
> Por favor, constesta en ingles :)
> "Alejandro Mesa" wrote:
>|||okay, so a big int field that has been filled with a value using @.@.identity
can not be edited?
"Alejandro Mesa" wrote:
> As I told you, you can not update an identity column.
>
> AMB
> "Chris" wrote:
>|||I need to create a table with unique primary key on only one field.
How does one create or load a table with unique values in a primary key
field if
you have not been been provided unique values.
I'm happy to use just a big int data type to hold my unique values
"Chris" wrote:

> I have a table already created and need to add an autonumber/ unique ident
ifier
> field.
> If the table already contains records can I just add the new autonumber fi
eld
> and run a query to populate this field for all records with unique values?
> how do I do this?
> are their other ways of doing this ? and how?
> thanks
> Chris|||Correct.
Example:
use northwind
go
create table t (
colA char(1) not null unique
)
go
insert into t values('a')
insert into t values('b')
insert into t values('c')
go
select * from t
go
alter table t
add colB int not null identity
go
select * from t
go
-- this will give an error
update t
set colB = 4
where colB = 2
go
drop table t
go
AMB
"Chris" wrote:
> okay, so a big int field that has been filled with a value using @.@.identit
y
> can not be edited?
>
> "Alejandro Mesa" wrote:
>

Adding Fields To A Table

I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?How are you doing it now?|||Through the Management Studio interface. Modify the table, add the fields, save the table. I get a timeout expired message.|||are you adding them to the end of the table, or inserting between 2 other fields, the latter taking a longer time.

look at Alter Table...|||Did you save the script?

And are you moving the columns into places not that are the last

It will make a temp copy of the table, copy all of the rows, rebuild the new table, then copy the data over, then do an sp_rename, then drop the original

Lot of overhead

just do this

CREATE TABLE myTable99(col1 int IDENTITY(1,1), Col2 datetime DEFAULT(GetDate()), Col3 Char(1))
GO

INSERT INTO myTable99(Col3)
SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Col4 binary
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.|||see the last line in Bretts signature. this violates relational theory. your application should not act like this.|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.|||The problem you are facing is that Management studio is going to do the following to accomplish this:

1) Create a new table with the name tmp_yourtablename with all the columns in the order you want.
2) Transfer all of the data from the old table to the tmp_ table. Yes. all 15 million rows will be doubled up.
3) Drop the old table (usually with no error checking)
4) Rename the tmp_table as the original table name.

Contrast that with the alter table command which would append the two new columns on the end of the table, and takes a few seconds to run.

How can the archival process be simpler by using the field index? i would think that any of the columns "pushed out" by the insertion of new fields in the "middle" of the table to cause much larger problems.|||Some existing processes rely on the order of the fields.

Why would that be?

SELECT * perhaps?|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.

OK, so what does that have to do with not adding them to the end?

In any case, The logging is what's going to kill you

I might bcp the data out
CREATE the new table
bcp the data back in, using a format card, into the new table|||The archival is being done in Visual Basic, using ADO recordsets. When you reference the table's Fields collection using the index, as long as field 1 is ID in one table and 1 is ID in the other table then it doesn't matter the names of the fields, but the order is important.|||The problem with adding them to the end is, the only difference in the table structure is the last field in the archive table. It is the datetime the record was archived.|||The archival is being done in Visual Basic, using ADO recordsets.

Shoot me now|||How many indexes are on this table? You might consider dropping them before you add the new columns, and then recreating them.|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.

I believe SQL need to create a temp table of the table, then insert the data back when you insert, as oppose to add fields to the end of the table. ColIDs change when inserting new fields, as opposed to adding.

adding delta to replica

Can I download a replica on day 1 and then on day 7 download the delta
to that replica?
Delta is the set of new records added to the server (from day 1 to day 7)
after i dowload the replica. Say on day1 I download a replica of 1000
records. Between day1 and day7, the server recieved 10 new records. On day 7,
I want to download
those 10 record only and add them to my replica. Is that possible by
default?
You cam assume there will be no collision bothways.
Thank you
http://groups-beta.google.com/group/...c41adc66b02687
Of course - transactional for one direction and bidirectional transactional
if not, as you are avoiding conflicts. Strictly speaking it is not the
record but a call to an insert sp that is replicated, but the end is the
same.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||My replica is merge replica
"Paul Ibison" wrote:

> Of course - transactional for one direction and bidirectional transactional
> if not, as you are avoiding conflicts. Strictly speaking it is not the
> record but a call to an insert sp that is replicated, but the end is the
> same.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||OK - changed records only are propagated in merge.
Paul
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:6FA34369-67F7-4FD0-96ED-292F1E5E9BCA@.microsoft.com...[vbcol=seagreen]
> My replica is merge replica
> "Paul Ibison" wrote:
|||Can i keep downloading delta after sync my replica with the server.
assume no collision, I submit in 100 records that has changes and everything
goes through fine. The server has now 50 new and changed records (other than
mine) that were submited from another user. Can I download those record only?
Wont my replica expire after i sync and hence i will have to re-initialize
all the replica again?
Sorry for the late reply/comment!
Thanks
|||To be honest I don't entirely follow your scenario, but if you are concerned
about expiry, this is determined by the Publication Property illustrated on
the General Tab of the publication properties dialog box. It is 14 days by
default, so as long as your subscribers synchronize every 14 days, there'll
be no expiry.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dts
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:
> I am using a dts package to move tables from one server to another and will
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.

Adding delete statement in a dts package script to delete records

I am using a dts package to move tables from one server to another and will
like to know how I can add a delete statement that can delete records more
than a year old from a particular table in the source database before the dt
s
package moves the the tables to the destination database. Any help will be
appreciated
Bothe servers are running sql server 2000.Hi
You can add an Execute SQL Task and add workflow so that it completes before
the transformation. You may want to check out www.sqldts.com for more
information on using DTS or check out the content in Books Online
John
"Aboki" wrote:

> I am using a dts package to move tables from one server to another and wil
l
> like to know how I can add a delete statement that can delete records more
> than a year old from a particular table in the source database before the
dts
> package moves the the tables to the destination database. Any help will be
> appreciated
> Bothe servers are running sql server 2000.

Adding dates in SQL

I am trying to pull only records that are greater than 1 month prior to today's date. This is what I have so far...

select *
from MyTable
where eventDateStart > '$Now'
order by eventDateStart

$Now is a variable that pulls in today's date. This sql statement delivers only records that have a eventDateStart greater than today. My problem is I do not know how to make it so it only shows records that are 1 month prior.

Any idea how to do this?Have a look at the DATEDIFF function in SQL Server|||This should do it for you:

SELECT * FROM MyTable
WHERE eventDateStart < DATEADD(m, -1, GETDATE())

Cheers

Gary|||Ok, this is frustrating, I can not get the DATEADD function to work for some reason. This is exactly what I want..

select *
from MyTable
where eventDateStart > DATEADD (m,-1,'2004/12/26')

This "SHOULD" return all the events that have a start date greater than November 26,2004 right? Am I crazy or something? If I get rid of the dateadd function and just have this where clause...

where eventDateStart > '2004/12/26'

it returns exactly what it should...all events that have a start date later than the 12/26/2004. But as soon as I try to DATEADD it returns zero rows. What the heck am I doing wrong|||Hi again,

Sorry I screwed up the first time as I should have said:
eventDateStart > DATEADD(m, -1, GETDATE())
instead of
eventDaytStart < DATEADD(m, -1, GETDATE())

In any case I checked your hardcoded version (SELECT DATEADD (m,-1,'2004/12/26') )and my version (SELECT DATEADD(m, -1, GETDATE())) in query analyzer and both give me the expected results.

I don't know if this is typo on your part but your DATEADD version will produce a comparison date of 2004/11/26 not 2004/12/26, so you are likely to get different results. Try it in query analyzer making sure you are using a dateadd that will be the same date as your hardcoded value and see what happens.

If that failds post the entire query/procedure and I'll look at it again because you're right, there is no reason why this should be difficult.

Cheers

Gary

Adding Columns Dynamically

Dear All,

I have a requirement, in which i have to add columns dynamically depending the records of a master table in sql server 2000.

Pl, do help me

If you mean a query, than there is no way to do that beside dynamic SQL. if you want to use dynamic SQL, make sure you first read the article of Erland to fully understand what you are doing:

http://www.sommarskog.se/dynamic_sql.html

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||

A.K.A Cross Tab query

Research SELECT CASE WHEN THEN END AS []

Adamus

Adding Column Using TSQL With Sequence Specified

I am looking for ways to add new column to a table with records inside. If I add the column using this statement:

ALTER TABLE Table1 ADD NewColumn1 decimal(18, 2) NULL

The column would appear at the end.

Is there a way to set where the column should be placed? (Excluding dropping all columns and add the columns in sequence again). I know this might not be very important, I am interested in knowing how Enterprise Manager done this, since you can move a column up and down to change their sequence eventhough there're records inside.EM creates a temp table, then reorganizes the columns in the base table, then inserts the records into the newly structured table.

Adding and Updating in same query

Hi all,
I am trying to copy records from table 1 where field 1 is equal to "N" to
table 2. I also want to change field1 to "Y" after the copy or delete the
record, so that the record will not get copied again.
Can this be done?
Thank you,
George
One thing I need to mention: I am trying to accomplish this in the
Transformation
Thanks again.
George
|||> One thing I need to mention: I am trying to accomplish this in the
> Transformation
What is "the Transformation"?
|||Of course you can handle this in multiple ways, e.g. one way would be to use
Y on insert
INSERT INTO table2(field1, pk) SELECT 'Y', pk
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.pk = t2.pk
WHERE t2.pk IS NULL
Or not have a "field1" at all. Assuming you can identify any row in either
table uniquely, and properly identify a duplicate, you can easily use a
similar LEFT JOIN without needing a flag.
Or not have two tables at all. What are you trying to accomplish with two
tables that you can't accomplish with one?
If this isn't helpful, then instead of an ambiguous word problem, please
post DDL, sample data and desired results. See http://www.aspfaq.com/5006
for info.
"George" <George@.discussions.microsoft.com> wrote in message
news:CB84E118-4A8C-43BD-9A22-6C58E98D5461@.microsoft.com...
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George
|||Hi George,
INSERT INTO TABLE2 SELECT <FIELDS> FROM TABLE1 WHERE FIELD1='N'
GO
UPDATE TABLE1 SET FIELD1='Y'
GO
Hope this will solve the problem
thanks and regards
Chandra
"George" wrote:

> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George

Adding and Updating in same query

Hi all,
I am trying to copy records from table 1 where field 1 is equal to "N" to
table 2. I also want to change field1 to "Y" after the copy or delete the
record, so that the record will not get copied again.
Can this be done?
Thank you,
GeorgeOne thing I need to mention: I am trying to accomplish this in the
Transformation
Thanks again.
George|||> One thing I need to mention: I am trying to accomplish this in the
> Transformation
What is "the Transformation"?|||Of course you can handle this in multiple ways, e.g. one way would be to use
Y on insert
INSERT INTO table2(field1, pk) SELECT 'Y', pk
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.pk = t2.pk
WHERE t2.pk IS NULL
Or not have a "field1" at all. Assuming you can identify any row in either
table uniquely, and properly identify a duplicate, you can easily use a
similar LEFT JOIN without needing a flag.
Or not have two tables at all. What are you trying to accomplish with two
tables that you can't accomplish with one?
If this isn't helpful, then instead of an ambiguous word problem, please
post DDL, sample data and desired results. See http://www.aspfaq.com/5006
for info.
"George" <George@.discussions.microsoft.com> wrote in message
news:CB84E118-4A8C-43BD-9A22-6C58E98D5461@.microsoft.com...
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George|||Hi George,
INSERT INTO TABLE2 SELECT <FIELDS> FROM TABLE1 WHERE FIELD1='N'
GO
UPDATE TABLE1 SET FIELD1='Y'
GO
Hope this will solve the problem
thanks and regards
Chandra
"George" wrote:
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George

Adding and Updating in same query

Hi all,
I am trying to copy records from table 1 where field 1 is equal to "N" to
table 2. I also want to change field1 to "Y" after the copy or delete the
record, so that the record will not get copied again.
Can this be done?
Thank you,
GeorgeOne thing I need to mention: I am trying to accomplish this in the
Transformation
Thanks again.
George|||> One thing I need to mention: I am trying to accomplish this in the
> Transformation
What is "the Transformation"?|||Of course you can handle this in multiple ways, e.g. one way would be to use
Y on insert
INSERT INTO table2(field1, pk) SELECT 'Y', pk
FROM table1 t1
LEFT OUTER JOIN table2 t2
ON t1.pk = t2.pk
WHERE t2.pk IS NULL
Or not have a "field1" at all. Assuming you can identify any row in either
table uniquely, and properly identify a duplicate, you can easily use a
similar LEFT JOIN without needing a flag.
Or not have two tables at all. What are you trying to accomplish with two
tables that you can't accomplish with one?
If this isn't helpful, then instead of an ambiguous word problem, please
post DDL, sample data and desired results. See http://www.aspfaq.com/5006
for info.
"George" <George@.discussions.microsoft.com> wrote in message
news:CB84E118-4A8C-43BD-9A22-6C58E98D5461@.microsoft.com...
> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George|||Hi George,
INSERT INTO TABLE2 SELECT <FIELDS> FROM TABLE1 WHERE FIELD1='N'
GO
UPDATE TABLE1 SET FIELD1='Y'
GO
Hope this will solve the problem
thanks and regards
Chandra
"George" wrote:

> Hi all,
> I am trying to copy records from table 1 where field 1 is equal to "N" to
> table 2. I also want to change field1 to "Y" after the copy or delete the
> record, so that the record will not get copied again.
> Can this be done?
> Thank you,
> George

Adding a time stamp to the records

I have a shopping cart that will get full from time to time because
customers click out of the site before they confirm their purchase,
therefore leaving a full cart behind. I'd like to have a timestamp on
this table, so that I can delete anything that I find is more than a day
old.

How can I do this?

Thanks,

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

You could use a not null datetime field and specify the default as
getdate(). This will insert the current date/time when the record is
created. and you will not have to specify a value. You can then run a
scheduled task (job) that checks for entries older than a given date.

John

"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:3f4fe28e$0$62083$75868355@.news.frii.net...
> I have a shopping cart that will get full from time to time because
> customers click out of the site before they confirm their purchase,
> therefore leaving a full cart behind. I'd like to have a timestamp on
> this table, so that I can delete anything that I find is more than a day
> old.
> How can I do this?
> Thanks,
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!