No login name for database user.

I am using SQL2000. I took a backup of the database on the live server to setup the database on my new development computer. In the tables, there are 2 owners’ dbo and indiankarma. The tables that are owned by indiankarma are the tables that the site runs from.

The problem: The database user was created by the .bak file and does not have a login name. I created a security login named indiankarma and set the password, default database, server roles, BUT when I set the database access and click OK, it says User 'Indiankarma' already exists. I tried to delete the database user and start from scratch but it tells me the selected user cannot be dropped because the user owns objects.

Now I have tried sp_changedbowner 'indiankarma' which does give the security login name indiankarma database access but it makes the user dbo not indiankarma. I need the web application to read indiankarma.[tablename] not dbo.[tablename]

Can anyone help me with this problem??

This is a common issue after restoring a database. Check the following blog

http://weblogs.asp.net/steveschofield/archive/2005/12/31/434280.aspx

Regards

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)

1.1

The problem is that the 'indiankarma' database user has no login name. (See below 1.2).

1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)

1.3

1.4

I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!

1.5

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)

1.1

The problem is that the 'indiankarma' database user has no login name. (See below 1.2).

1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)

1.3

1.4

I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!

1.5

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)

1.1

The problem is that the 'indiankarma' database user has no login name. (See below 1.2).

1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)

1.3

1.4

I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!

1.5

|||

I don't think I explained the problem correctly. When I restored the data base it created 2 sets of tables for each table. One set owned by dbo and the other set by indiankarma. So I have a table that is indiankarma.ikuserinfo and a table right below it that is dbo.ikuserinfo. All the site data is in the indiankarma owned tables. (see below 1.1)1.1
The problem is that the 'indiankarma' database user has no login name. (See below 1.2).1.2

Also, the security login 'indiankarma' does not have access to the database (see below 1.3) and when I try to give it access, I get this error 'User 'Indiankarma' already exists.' (see below 1.4)1.3
1.4
I need to know what I can do to get database user 'indiankara' a login name and how to permit security login 'indiankarma' database access with the user 'indiankarma'. So it looks like the figure below (see below 1.5) but when I click ok, it does not give me the error. Please help!!! Sorry for all the images, I am bad at explaining problems thought they might help!1.5

No Logging

Gurus,
Is there a way to disable database logging while dropping
tables in SQL Server 6.5? In fact, is there a way to
disable logging completely? I realize this is no way to
run a production database, but sometimes extreme problems
call for extreme actions!
Will greatly appreciate any suggestions!
Thanks,
ChetChet
Turn the database option truncate log on checkpoint to on.
(In EM double click on the database you want and you will
find it under the options tab, just tick it)
Regards
John|||No you cannot completely turn off logging. You can set Trunc. log on Chkpt
and SELECT Into/Bulk Copy to true. What are your reasons for wanting to do
this "extreme problems"
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Chet" <chet.hodgins@.verizon.net> wrote in message
news:009801c351f3$71fcedd0$a101280a@.phx.gbl...
> Gurus,
> Is there a way to disable database logging while dropping
> tables in SQL Server 6.5? In fact, is there a way to
> disable logging completely? I realize this is no way to
> run a production database, but sometimes extreme problems
> call for extreme actions!
> Will greatly appreciate any suggestions!
> Thanks,
> Chet|||Thanks for the replies! This is a SAP development
database. We have 4 SAP tables that are have 2541, 2504
and 2543 errors. The data in these 4 tables are not
important. I have copied the tables to new customer
defined tables in SAP. Now want to drop the problem tables
and re-create them. Keep running out of log space (4GB)
before i can get to my first checkoint. Suggestions are
always appreciated!
Thanks,
Chet
>--Original Message--
>No you cannot completely turn off logging. You can set
Trunc. log on Chkpt
>and SELECT Into/Bulk Copy to true. What are your reasons
for wanting to do
>this "extreme problems"
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Chet" <chet.hodgins@.verizon.net> wrote in message
>news:009801c351f3$71fcedd0$a101280a@.phx.gbl...
>> Gurus,
>> Is there a way to disable database logging while
dropping
>> tables in SQL Server 6.5? In fact, is there a way to
>> disable logging completely? I realize this is no way to
>> run a production database, but sometimes extreme
problems
>> call for extreme actions!
>> Will greatly appreciate any suggestions!
>> Thanks,
>> Chet
>
>.
>

No info in diagramed tables

I ran the diagram tool in EM SQL 7.0 and the table appears but nothing else is visible. If you check properties everything is grayed out. Select tables with Joins and all the tables show but again no column names, key or joins visible. I opened the diagram in Northwind and it appears correctly. I must have missed something, hope someone knows what.

Thanks in advance.
KayteeHOriginally posted by Kayteeh
I ran the diagram tool in EM SQL 7.0 and the table appears but nothing else is visible. If you check properties everything is grayed out. Select tables with Joins and all the tables show but again no column names, key or joins visible. I opened the diagram in Northwind and it appears correctly. I must have missed something, hope someone knows what.

Thanks in advance.
KayteeH

Can you right click on the table and get a menu that has an option called table view?|||could it be that you're missing permissions?|||Originally posted by Brett Kaiser
Can you right click on the table and get a menu that has an option called table view?

No - I don't see a menu that says table view.|||Originally posted by ms_sql_dba
could it be that you're missing permissions?

That's what I thought, but I am dbo. I can use the diagram feature in other databases I own. Just not this particular database.

No Import/Export Option in Mgmt Studio Express

Hi,

I just installed SQL Server 2005 Express. I would like to import tables from

another DB, and I read in the help files that I just need to (1) right-click on

a non-system database in Management Studio Express, and (2) select Import/Export

under the Tasks menu.

I tried this, but my Tasks menu only contains these options: Detach, Shrink,

Backup, Restore, Generate Scripts.

I googled for \"sql server 2005 "no import" tasks\", and it appears that a

couple of people have had this problem -- the only solution I read was to

install the "Workstation Components," but I already have, and still no luck.

Any idea what I need to do? Thanks!

Did you try right clicking on the database in the object explorer window. Try that, and then the All Tasks, Generate SQL Scripts option.|||Okay, I right-clicked on my database in the object explorer, then went to Tasks->Generate Scripts. This database is empty, though (I want to import a SQL Server 7 DB into it), so I end up getting a message that says "There are no objects in database of the types scriptable by this wizard."

Ultimately, I just need to import a SQL Server 7 database into SQL Server 2005 Express, but I can't find an "import" option in 2005. Is there some component I need to install?|||Ah, I think I need SQL Server Integration Services (SSIS), and that is apparently not included in SQL Server 2005 Express Edition (see "Unsupported Features" in http://msdn2.microsoft.com/en-us/library/ms365166.aspx). Mystery solved.

No Excel 2007 Table Support in SSIS?

When I connect to an Excel 2007 workbook using the new Microsoft.ACE.Oledb.12.0 provider in SSIS 2005, I notice that any tables that I've created in the worksheet are not recognized in SSIS.

In the OLE DB data source component (using the "Table or View" data access mode) the any table(s) I've created are nowhere to be found in the drop down list. Similarly, when I constuct a simple SQL query on the workbook, such as "SELECT * From MyTable" it returns the error:

Microsoft Office Access Database Engine

Hresult: 0x80004005

Description: "The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly."

I know I have the name right -- I can use structured references to my named table in the worksheet without any problems.

Does the new ACE provider not support Excel 2007 tables? Am I stuck with using "overlapping" cell references to capture data from tables in my worksheet?

I see that you are using Named Ranges. What if you use the worksheet name?

The Office 12 versions of the Jet Provider and Excel Driver are rewritten, so it's possible that behavior has changed, whether intentionally or unintentionally. Previously the driver displayed both sheets and named ranges as "tables".

-Doug

|||

Apologies for not making myself clearer. I'm referring to the improved table objects in Excel 2007, discussed here:

http://blogs.msdn.com/excel/archive/2005/10/25/484915.aspx

When extracting data from an Excel 2007 source, I'm able to access all of the information on the sheet or from my named ranges without any problem -- both appear as what SSIS called "tables" in the OLE DB Source dialog box. This behavior is the exact same as it was using the JET provider.

My problem is that the Excel 2007 table objects that I've created in various sheets simply aren't accessible -- they don't show up at all. Given their structured, unifiorm nature, one would expect an Excel table to be recognized by the new ACE provider just like, say, a named range.

Why named tables instead of simple named ranges?

-- Tables can grow dynamically as you add rows, and named ranges cannot. My thinking was that if you could import an Excel 2007 table, you wouldn't have to worry if the named range was large enough.

-- Excel 2007 tables can be filtered and sorted, giving the user in the workbook a richer experience.

I think it would be very powerful and useful to access data as it is represented in an Excel 2007 table object through SSIS. I haven't been able to determine if this is even possible, or if I need to take some special steps to make it work.

|||

The driver has no knowledge of any new features in Excel 2007. This is merely the legacy driver, updated to connect successfully to Excel 2007 files, which as you know use a different file format.

-Doug

|||

Thanks for your quick reply -- looks like I'll have to go back to named references.

Also, for anyone wanting to do a similar thing with "Lists" in Excel 2003 (the precursor to Excel 2007) a quick test seems to indicate that the Excel driver (for pre-2007 versions) does not support them, either.

No duplicate record from joined table

Hi,
I have 2 tables on DB2 version 6

Tab_A
----
id name
1 shop1
2 shop2

Tab_B
--------
id_tab_a keyword
1 book
1 bookstore
1 handbook
2 restaurant

I need to write a SQL query that find who have 'book' as keyword on Tab_B, but with no duplicated record. If i write:

Select distinct id, name left join Tab_B on id=id_tab_a where
keyword like '%book%'

obtain 3 "duplicated" records.

Any ideas?
Thanks,
JhonOriginally posted by fartman
Select distinct id, name left join Tab_B on id=id_tab_a where
keyword like '%book%'


You need to group this puppy

select id,name from tab_a
left join tab_b on id=id_tab_a
group by id,name

Best of luck
-Chris
Come read my SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html|||HI, the following query will help you

select id,name from tab_a
left join tab_b on id=id_tab_a where keyword like '%book%'
group by id,name;

Originally posted by christodd
You need to group this puppy

select id,name from tab_a
left join tab_b on id=id_tab_a
group by id,name

Best of luck
-Chris
Come read my SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html|||problem solved !!!
Thank you.

Jhonny

Originally posted by neelamchalam
HI, the following query will help you

select id,name from tab_a
left join tab_b on id=id_tab_a where keyword like '%book%'
group by id,name;

No downtime

My goal is to have no downtime while rebuilding indexes. Since SQL
2005 doesn't support online indexing for tables with large object types
(ie NTEXT), this is not an option for me. So I think the only other
option is to use P2P replication..
Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
and the hot-standby server without service interruption? This way, if
the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Also, is it allowed to have a single CPU license for two SQL 2005
server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
Thank you!
MichaelFirst, I'll tackle the larger issues.
A second SQL Server is licensed to be a 'hot standby' server only under the
SQL Server Enterprise Edition license.
Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
in'. The hot standby server must be manually brought on-line after the last
log file is transferred and added.
Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
is down, NOT because a table is inconveniently 'locked'.
I hope this helps you come up with a different strategy.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
> Thank you!
> Michael
>|||I am at loss for another strategy. Is there another common strategy to
workaround timeouts which occur from table locks while reindexing?
I found
http://download.microsoft.com/downl...censingv1.1.doc
which mentions:
"When doing failover support, a server is designated as the passive
server. The purpose of the passive server is to absorb the data and
information held in another server that fails. A passive server does
not need a license, provided that the number of processors in the
passive server is equal or less than those of the active server. The
passive server can take the duties of the active server for 30 days.
Afterward, it must be licensed accordingly."
Are you sure about it can't be a hot-standby server with Standard
edition?
Thank You,
Michael
Arnie Rowland wrote:[vbcol=seagreen]
> First, I'll tackle the larger issues.
> A second SQL Server is licensed to be a 'hot standby' server only under th
e
> SQL Server Enterprise Edition license.
> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'ki
ck
> in'. The hot standby server must be manually brought on-line after the las
t
> log file is transferred and added.
> Even in a Clustered, Fail-Over scenario, FailOver occurs because the serve
r
> is down, NOT because a table is inconveniently 'locked'.
> I hope this helps you come up with a different strategy.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...|||I never assert that I know from beans where licensing is concerned. It's
complex and often changing.
I do believe you are correct about the passive server license issue -I
misunderstood how you proposed to configure the servers. However, I think
that the licensing document goes on to indicate that any client queries are
executing on the passive server while at the same time the active server is
still online and 'working' requires a separate license for the passive
server.
From that same document: "Figure 3. Passive Servers. The passive server does
not require a license given that no queries are being executed against it."
When 'FailOver' occurs, the once active server's license is passed over to
the previous passive server. It's not unlike buying a new car and knowing
that your insurance covers you as you drive home and then you have a 'grace'
period to insure the new car.
But as I read your original post, it seems that your purpose is to have
activity on both servers at the same time -indexing on one server, and query
execution on the other server. I'm reasonably sure that is not a allowed
activity for an Active-Passive single license scenario. Bottom line is, in
my opinion, that you can't use one license for two operating servers. BUT,
the definitive answer can be obtained from the VL folks.
And a locked table STILL does not create a 'FailOver' situation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
>I am at loss for another strategy. Is there another common strategy to
> workaround timeouts which occur from table locks while reindexing?
> I found
> http://download.microsoft.com/downl...censingv1.1.doc
> which mentions:
> "When doing failover support, a server is designated as the passive
> server. The purpose of the passive server is to absorb the data and
> information held in another server that fails. A passive server does
> not need a license, provided that the number of processors in the
> passive server is equal or less than those of the active server. The
> passive server can take the duties of the active server for 30 days.
> Afterward, it must be licensed accordingly."
> Are you sure about it can't be a hot-standby server with Standard
> edition?
> Thank You,
> Michael
>
> Arnie Rowland wrote:
>|||Thank you for the licensing clarification.
I guess the root of my question has nothing to do with licensing and
all to do with high-availablility. EE doesn't not support online
indexing for LOB type tables so I am still stuck without a solution.
My only idea is to: (a) switch traffic to database mirrored server (b)
reindex live server, (c) switch traffic back. Is that even possible?
http://www.sql-server-performance.c...ailability2.asp
gives a good overview of the different options and I am more confused
after reading it.
Arnie Rowland wrote:[vbcol=seagreen]
> I never assert that I know from beans where licensing is concerned. It's
> complex and often changing.
> I do believe you are correct about the passive server license issue -I
> misunderstood how you proposed to configure the servers. However, I think
> that the licensing document goes on to indicate that any client queries ar
e
> executing on the passive server while at the same time the active server i
s
> still online and 'working' requires a separate license for the passive
> server.
> From that same document: "Figure 3. Passive Servers. The passive server do
es
> not require a license given that no queries are being executed against it.
"
> When 'FailOver' occurs, the once active server's license is passed over to
> the previous passive server. It's not unlike buying a new car and knowing
> that your insurance covers you as you drive home and then you have a 'grac
e'
> period to insure the new car.
> But as I read your original post, it seems that your purpose is to have
> activity on both servers at the same time -indexing on one server, and que
ry
> execution on the other server. I'm reasonably sure that is not a allowed
> activity for an Active-Passive single license scenario. Bottom line is, in
> my opinion, that you can't use one license for two operating servers. BUT,
> the definitive answer can be obtained from the VL folks.
> And a locked table STILL does not create a 'FailOver' situation.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...|||On 4 Aug 2006 12:04:32 -0700, mpaine@.htxml.com wrote:
>My goal is to have no downtime while rebuilding indexes. Since SQL
>2005 doesn't support online indexing for tables with large object types
>(ie NTEXT), this is not an option for me. So I think the only other
>option is to use P2P replication..
How about move the ntext into a 1:1 table with just the NTEXT and PK,
and all other indexes in the other table.
It's not free, but it's cheap.
Josh|||<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
I think your worries about the loss in availablility due to reindexing are
grossly exaggerated. The restriction for online indexes and large object
types applies only for clustered indexes on tables with large types. So if
you really must have nothing but online indexes, just make the tables with
large object types heaps, with a non-clustered primary key index. EG
drop table itest
go
create table itest
(
id int not null,
data varchar(max),
data1 text,
constraint pk_itest
primary key nonclustered(id)
)
create unique nonclustered index pk_itest
on itest(id)
with (drop_existing=on, online=on)
Alternatively you can use partitioning to minimize the impace of index
rebuilding as a partitioned index can be rebuilt one partition at a time.
As for P2P replication, it would work fine for this, and would allow you to
take a node down to patch the OS, change hardware or whatever. The
"failover" wouldn't happen automatically since P2P has no built-in notion of
failover, and because nothing failed. You would have to manage the traffic
yourself, through the application, DNS or something like an F5 BigIP switch.
For licensing, my understanding is that an unlicensed passive failover node
can only be used with log shipping, mirroring and failover clustering. So
you would need to license the peer node.
David|||This was more than helpful! Thank you
David Browne wrote:
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> I think your worries about the loss in availablility due to reindexing are
> grossly exaggerated. The restriction for online indexes and large object
> types applies only for clustered indexes on tables with large types. So i
f
> you really must have nothing but online indexes, just make the tables with
> large object types heaps, with a non-clustered primary key index. EG
> drop table itest
> go
> create table itest
> (
> id int not null,
> data varchar(max),
> data1 text,
> constraint pk_itest
> primary key nonclustered(id)
> )
> create unique nonclustered index pk_itest
> on itest(id)
> with (drop_existing=on, online=on)
>
> Alternatively you can use partitioning to minimize the impace of index
> rebuilding as a partitioned index can be rebuilt one partition at a time.
> As for P2P replication, it would work fine for this, and would allow you t
o
> take a node down to patch the OS, change hardware or whatever. The
> "failover" wouldn't happen automatically since P2P has no built-in notion
of
> failover, and because nothing failed. You would have to manage the traffi
c
> yourself, through the application, DNS or something like an F5 BigIP switc
h.
> For licensing, my understanding is that an unlicensed passive failover nod
e
> can only be used with log shipping, mirroring and failover clustering. S
o
> you would need to license the peer node.
> Davidsql

No downtime

My goal is to have no downtime while rebuilding indexes. Since SQL
2005 doesn't support online indexing for tables with large object types
(ie NTEXT), this is not an option for me. So I think the only other
option is to use P2P replication..
Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
and the hot-standby server without service interruption? This way, if
the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Also, is it allowed to have a single CPU license for two SQL 2005
server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
Thank you!
MichaelFirst, I'll tackle the larger issues.
A second SQL Server is licensed to be a 'hot standby' server only under the
SQL Server Enterprise Edition license.
Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
in'. The hot standby server must be manually brought on-line after the last
log file is transferred and added.
Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
is down, NOT because a table is inconveniently 'locked'.
I hope this helps you come up with a different strategy.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
> Thank you!
> Michael
>|||I am at loss for another strategy. Is there another common strategy to
workaround timeouts which occur from table locks while reindexing?
I found
http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
which mentions:
"When doing failover support, a server is designated as the passive
server. The purpose of the passive server is to absorb the data and
information held in another server that fails. A passive server does
not need a license, provided that the number of processors in the
passive server is equal or less than those of the active server. The
passive server can take the duties of the active server for 30 days.
Afterward, it must be licensed accordingly."
Are you sure about it can't be a hot-standby server with Standard
edition?
Thank You,
Michael
Arnie Rowland wrote:
> First, I'll tackle the larger issues.
> A second SQL Server is licensed to be a 'hot standby' server only under the
> SQL Server Enterprise Edition license.
> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT 'kick
> in'. The hot standby server must be manually brought on-line after the last
> log file is transferred and added.
> Even in a Clustered, Fail-Over scenario, FailOver occurs because the server
> is down, NOT because a table is inconveniently 'locked'.
> I hope this helps you come up with a different strategy.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> >
> > Thank you!
> > Michael
> >|||I never assert that I know from beans where licensing is concerned. It's
complex and often changing.
I do believe you are correct about the passive server license issue -I
misunderstood how you proposed to configure the servers. However, I think
that the licensing document goes on to indicate that any client queries are
executing on the passive server while at the same time the active server is
still online and 'working' requires a separate license for the passive
server.
From that same document: "Figure 3. Passive Servers. The passive server does
not require a license given that no queries are being executed against it."
When 'FailOver' occurs, the once active server's license is passed over to
the previous passive server. It's not unlike buying a new car and knowing
that your insurance covers you as you drive home and then you have a 'grace'
period to insure the new car.
But as I read your original post, it seems that your purpose is to have
activity on both servers at the same time -indexing on one server, and query
execution on the other server. I'm reasonably sure that is not a allowed
activity for an Active-Passive single license scenario. Bottom line is, in
my opinion, that you can't use one license for two operating servers. BUT,
the definitive answer can be obtained from the VL folks.
And a locked table STILL does not create a 'FailOver' situation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<mpaine@.htxml.com> wrote in message
news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
>I am at loss for another strategy. Is there another common strategy to
> workaround timeouts which occur from table locks while reindexing?
> I found
> http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> which mentions:
> "When doing failover support, a server is designated as the passive
> server. The purpose of the passive server is to absorb the data and
> information held in another server that fails. A passive server does
> not need a license, provided that the number of processors in the
> passive server is equal or less than those of the active server. The
> passive server can take the duties of the active server for 30 days.
> Afterward, it must be licensed accordingly."
> Are you sure about it can't be a hot-standby server with Standard
> edition?
> Thank You,
> Michael
>
> Arnie Rowland wrote:
>> First, I'll tackle the larger issues.
>> A second SQL Server is licensed to be a 'hot standby' server only under
>> the
>> SQL Server Enterprise Edition license.
>> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
>> 'kick
>> in'. The hot standby server must be manually brought on-line after the
>> last
>> log file is transferred and added.
>> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
>> server
>> is down, NOT because a table is inconveniently 'locked'.
>> I hope this helps you come up with a different strategy.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> <mpaine@.htxml.com> wrote in message
>> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
>> > My goal is to have no downtime while rebuilding indexes. Since SQL
>> > 2005 doesn't support online indexing for tables with large object types
>> > (ie NTEXT), this is not an option for me. So I think the only other
>> > option is to use P2P replication..
>> >
>> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
>> > and the hot-standby server without service interruption? This way, if
>> > the live table is locked
>> > during this operation, the hot-standby server would take over the CRUD
>> > statements.
>> >
>> > Also, is it allowed to have a single CPU license for two SQL 2005
>> > server if
>> > one is a hot-standby-only server and the standby-only server is only
>> > used for failover purposes. Note that when tables are locked during
>> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
>> > possible?
>> >
>> >
>> > Thank you!
>> > Michael
>> >
>|||Thank you for the licensing clarification.
I guess the root of my question has nothing to do with licensing and
all to do with high-availablility. EE doesn't not support online
indexing for LOB type tables so I am still stuck without a solution.
My only idea is to: (a) switch traffic to database mirrored server (b)
reindex live server, (c) switch traffic back. Is that even possible?
http://www.sql-server-performance.com/sql_server_high_availability2.asp
gives a good overview of the different options and I am more confused
after reading it.
Arnie Rowland wrote:
> I never assert that I know from beans where licensing is concerned. It's
> complex and often changing.
> I do believe you are correct about the passive server license issue -I
> misunderstood how you proposed to configure the servers. However, I think
> that the licensing document goes on to indicate that any client queries are
> executing on the passive server while at the same time the active server is
> still online and 'working' requires a separate license for the passive
> server.
> From that same document: "Figure 3. Passive Servers. The passive server does
> not require a license given that no queries are being executed against it."
> When 'FailOver' occurs, the once active server's license is passed over to
> the previous passive server. It's not unlike buying a new car and knowing
> that your insurance covers you as you drive home and then you have a 'grace'
> period to insure the new car.
> But as I read your original post, it seems that your purpose is to have
> activity on both servers at the same time -indexing on one server, and query
> execution on the other server. I'm reasonably sure that is not a allowed
> activity for an Active-Passive single license scenario. Bottom line is, in
> my opinion, that you can't use one license for two operating servers. BUT,
> the definitive answer can be obtained from the VL folks.
> And a locked table STILL does not create a 'FailOver' situation.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <mpaine@.htxml.com> wrote in message
> news:1154723562.606082.185580@.i42g2000cwa.googlegroups.com...
> >I am at loss for another strategy. Is there another common strategy to
> > workaround timeouts which occur from table locks while reindexing?
> >
> > I found
> > http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
> > which mentions:
> >
> > "When doing failover support, a server is designated as the passive
> > server. The purpose of the passive server is to absorb the data and
> > information held in another server that fails. A passive server does
> > not need a license, provided that the number of processors in the
> > passive server is equal or less than those of the active server. The
> > passive server can take the duties of the active server for 30 days.
> > Afterward, it must be licensed accordingly."
> >
> > Are you sure about it can't be a hot-standby server with Standard
> > edition?
> >
> > Thank You,
> > Michael
> >
> >
> >
> > Arnie Rowland wrote:
> >> First, I'll tackle the larger issues.
> >>
> >> A second SQL Server is licensed to be a 'hot standby' server only under
> >> the
> >> SQL Server Enterprise Edition license.
> >>
> >> Hot Standby (with log shipping) is not a FAILOVER cluster. It will NOT
> >> 'kick
> >> in'. The hot standby server must be manually brought on-line after the
> >> last
> >> log file is transferred and added.
> >>
> >> Even in a Clustered, Fail-Over scenario, FailOver occurs because the
> >> server
> >> is down, NOT because a table is inconveniently 'locked'.
> >>
> >> I hope this helps you come up with a different strategy.
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> <mpaine@.htxml.com> wrote in message
> >> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> >> > My goal is to have no downtime while rebuilding indexes. Since SQL
> >> > 2005 doesn't support online indexing for tables with large object types
> >> > (ie NTEXT), this is not an option for me. So I think the only other
> >> > option is to use P2P replication..
> >> >
> >> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> >> > and the hot-standby server without service interruption? This way, if
> >> > the live table is locked
> >> > during this operation, the hot-standby server would take over the CRUD
> >> > statements.
> >> >
> >> > Also, is it allowed to have a single CPU license for two SQL 2005
> >> > server if
> >> > one is a hot-standby-only server and the standby-only server is only
> >> > used for failover purposes. Note that when tables are locked during
> >> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> >> > possible?
> >> >
> >> >
> >> > Thank you!
> >> > Michael
> >> >
> >|||On 4 Aug 2006 12:04:32 -0700, mpaine@.htxml.com wrote:
>My goal is to have no downtime while rebuilding indexes. Since SQL
>2005 doesn't support online indexing for tables with large object types
>(ie NTEXT), this is not an option for me. So I think the only other
>option is to use P2P replication..
How about move the ntext into a 1:1 table with just the NTEXT and PK,
and all other indexes in the other table.
It's not free, but it's cheap.
Josh|||<mpaine@.htxml.com> wrote in message
news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> My goal is to have no downtime while rebuilding indexes. Since SQL
> 2005 doesn't support online indexing for tables with large object types
> (ie NTEXT), this is not an option for me. So I think the only other
> option is to use P2P replication..
> Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> and the hot-standby server without service interruption? This way, if
> the live table is locked
> during this operation, the hot-standby server would take over the CRUD
> statements.
> Also, is it allowed to have a single CPU license for two SQL 2005
> server if
> one is a hot-standby-only server and the standby-only server is only
> used for failover purposes. Note that when tables are locked during
> ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> possible?
>
I think your worries about the loss in availablility due to reindexing are
grossly exaggerated. The restriction for online indexes and large object
types applies only for clustered indexes on tables with large types. So if
you really must have nothing but online indexes, just make the tables with
large object types heaps, with a non-clustered primary key index. EG
drop table itest
go
create table itest
(
id int not null,
data varchar(max),
data1 text,
constraint pk_itest
primary key nonclustered(id)
)
create unique nonclustered index pk_itest
on itest(id)
with (drop_existing=on, online=on)
Alternatively you can use partitioning to minimize the impace of index
rebuilding as a partitioned index can be rebuilt one partition at a time.
As for P2P replication, it would work fine for this, and would allow you to
take a node down to patch the OS, change hardware or whatever. The
"failover" wouldn't happen automatically since P2P has no built-in notion of
failover, and because nothing failed. You would have to manage the traffic
yourself, through the application, DNS or something like an F5 BigIP switch.
For licensing, my understanding is that an unlicensed passive failover node
can only be used with log shipping, mirroring and failover clustering. So
you would need to license the peer node.
David|||This was more than helpful! Thank you
David Browne wrote:
> <mpaine@.htxml.com> wrote in message
> news:1154718272.357172.32830@.i3g2000cwc.googlegroups.com...
> > My goal is to have no downtime while rebuilding indexes. Since SQL
> > 2005 doesn't support online indexing for tables with large object types
> > (ie NTEXT), this is not an option for me. So I think the only other
> > option is to use P2P replication..
> >
> > Can I run ALTER INDEX REBUILD seperately on a live SQL 2005 server
> > and the hot-standby server without service interruption? This way, if
> > the live table is locked
> > during this operation, the hot-standby server would take over the CRUD
> > statements.
> >
> > Also, is it allowed to have a single CPU license for two SQL 2005
> > server if
> > one is a hot-standby-only server and the standby-only server is only
> > used for failover purposes. Note that when tables are locked during
> > ALTER INDEX REBUILD, I assume the failover would kick in. Is that
> > possible?
> >
> I think your worries about the loss in availablility due to reindexing are
> grossly exaggerated. The restriction for online indexes and large object
> types applies only for clustered indexes on tables with large types. So if
> you really must have nothing but online indexes, just make the tables with
> large object types heaps, with a non-clustered primary key index. EG
> drop table itest
> go
> create table itest
> (
> id int not null,
> data varchar(max),
> data1 text,
> constraint pk_itest
> primary key nonclustered(id)
> )
> create unique nonclustered index pk_itest
> on itest(id)
> with (drop_existing=on, online=on)
>
> Alternatively you can use partitioning to minimize the impace of index
> rebuilding as a partitioned index can be rebuilt one partition at a time.
> As for P2P replication, it would work fine for this, and would allow you to
> take a node down to patch the OS, change hardware or whatever. The
> "failover" wouldn't happen automatically since P2P has no built-in notion of
> failover, and because nothing failed. You would have to manage the traffic
> yourself, through the application, DNS or something like an F5 BigIP switch.
> For licensing, my understanding is that an unlicensed passive failover node
> can only be used with log shipping, mirroring and failover clustering. So
> you would need to license the peer node.
> David

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No columns when using temp tables in T-SQL in OLEDB Source

We have a complicated select query that needs to build a couple temporary work tables that are then used in the final select statement (in an OLEDB Source data flow control). We can click preview and see the resultset, but if we click on the Columns view there are no columns. We can save and close the OLEDB Source control but downstream from it there are messages saying that there are no input columns. The T-SQL looks something like this (abbreviated):

SELECT fieldlist INTO #temp1 FROM table

SELECT fieldlist INTO #temp2 FROM table

SELECT fieldlist FROM table INNER JOIN #temp1 INNER JOIN #temp2

DROP TABLE #temp1; DROP TABLE #temp2

Has anyone been able to use temp tables in a source SQL statement in a data flow? Are we doing something wrong or incomplete?

Thanks, Gordy

You might try explicitly managing the creation and dropping of the needed temp table in tempdb. Impact on the server is the same, just a bit more work in the code. Then you should have a table that is indistinguishable from any other.

Ken

No COLLATE definition

Hi,
during the initialization of the subscriber from a mergepublication the
COLLATE definitions were not defined in the created tables of the subscribers.
In the snapshot directory I searched the right schema script and I saw that
the collate definition in the create table command was not defined.
Is this a bug or is it a result of a wrong setting of the publisher or
subscriber ?
How can I solve the problem ?
Best regards
Axel Lanser
On the article properties, snapshot tab there is a checkbox for Collation -
by default it is not taken from the publisher to the subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
my snapshot has more then 200 articles. Is there a way to set the collation
for all articles automaticaly.
Best regards
Axel Lanser
"Paul Ibison" wrote:

> On the article properties, snapshot tab there is a checkbox for Collation -
> by default it is not taken from the publisher to the subscriber.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Nightly Refresh

Please help.
I have Database A (Archived data), Database B (Transactional data).
Both A and B do not have any timestamps in any tables.
I need to bring A (only once because it is archived data) and B with
only differentials into Database C for reporting purposes.

Currently C is cleared before loading B with differentials every night
and A is not loaded since it takes 15 hours to load.

Now I want to bring A once and leave it and then bring B every night
with differentials without clearing C.

Please suggest some solutions.

Thank You(rohini.srigiriraju@.gmail.com) writes:

Quote:

Originally Posted by

I have Database A (Archived data), Database B (Transactional data).
Both A and B do not have any timestamps in any tables.
I need to bring A (only once because it is archived data) and B with
only differentials into Database C for reporting purposes.
>
Currently C is cleared before loading B with differentials every night
and A is not loaded since it takes 15 hours to load.
>
Now I want to bring A once and leave it and then bring B every night
with differentials without clearing C.
>
Please suggest some solutions.

It's very difficult to suggest solutions from this high-level description.
And it's not exactly clear what you mean with differentials. I would
guess new and changed rows since last load. Do you also need to deal
with deletions?

Maybe you could examplify with some tables to give us some more meat?

Please also specify which version of SQL Server you are using.

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

Newby DTS Question

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

Thanks

Mikemike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.
>
I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1
>
How would I go about performing such clauses using DTS?

Hi Mike,

Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:

SELECT my_columns
FROM dbo.My_Table
WHERE id ?

The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.

You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.

HTH,
-Tom.|||Thanks for your help Tom,worked a treat.

Thomas R. Hummel wrote:

Quote:

Originally Posted by

mike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

>
Hi Mike,
>
Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:
>
SELECT my_columns
FROM dbo.My_Table
WHERE id ?
>
The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.
>
You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.
>
HTH,
-Tom.

Newbie: working with expressions on tables

I was reading the help, but I don't know if this is possible.
Dataset
first | last | Id
a | b | 1
c | d | 2
e | f | 3
For 1 cell I want to put:
Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
I just want to put specific fields in different spots. I didn't see this in
the help and looked around a bit. Is there a section in the help that
covers expressions like this?I think you want this:
=iif(Trim(Fields!First.Value) = "c" AND Trim(Fields!Last.Value) = "d",
Fields!ID.Value, Nothing)
Details on the IIF function are available on MSDN:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <dangreece@.hotmail.com> wrote in message
news:eBnL6uYVEHA.1928@.TK2MSFTNGP11.phx.gbl...
> I was reading the help, but I don't know if this is possible.
> Dataset
> first | last | Id
> a | b | 1
> c | d | 2
> e | f | 3
>
> For 1 cell I want to put:
> Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
> I just want to put specific fields in different spots. I didn't see this
in
> the help and looked around a bit. Is there a section in the help that
> covers expressions like this?
>|||That works, but it only takes the 1st value. It doesn't loop through all
the rows if I do it cell by cell.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:u4AUaamVEHA.2716@.tk2msftngp13.phx.gbl...
> I think you want this:
> =iif(Trim(Fields!First.Value) = "c" AND Trim(Fields!Last.Value) = "d",
> Fields!ID.Value, Nothing)
> Details on the IIF function are available on MSDN:
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Cindy Lee" <dangreece@.hotmail.com> wrote in message
> news:eBnL6uYVEHA.1928@.TK2MSFTNGP11.phx.gbl...
> > I was reading the help, but I don't know if this is possible.
> >
> > Dataset
> >
> > first | last | Id
> > a | b | 1
> > c | d | 2
> > e | f | 3
> >
> >
> > For 1 cell I want to put:
> > Fields!ID.Value where Fields!First.Value="c" and Fields!Last.Value="d"
> >
> > I just want to put specific fields in different spots. I didn't see
this
> in
> > the help and looked around a bit. Is there a section in the help that
> > covers expressions like this?
> >
> >
>

Newbie: Transfering databases from different servers

Hi,
I have the following simple situtation:
I created my database (just tables) in Server1, then made a copy to Server2.
There, i changed all the datatypes and created relationships
between the tables.
Now, I want to transfer, not update, completely replace, all the previous
versions of tables from Server1 with the new DB.
I want to ask before i do something stupid.
- Will DTS delete and replace all previous-version tables and add the new
relationships automatically?
- How about all these system tables that are generated automatically
(sysfiles, sysdepends, etc.) ? Do I have to replace them too?
Thanx in advance,
I know its simple but i am very afraid to screw up my DB during the copy.
-steveBackup the database on the "good" server, transfer the backup to the "bad"
server, and restore this
backup over the database on the "bad" server.
Refer to BACKUP DATABASE & RESTORE in BOL
"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
Server2.
> There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>|||"steve" wrote:

> I have the following simple situtation:
By far the easiest way to do this is to "move" the actual physical data
files. Doing this is easy with attach/detatch. See:
http://support.microsoft.com/defaul...kb;en-us;224071
Maury|||"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
> Server2. There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
If you can overwrite the database on Server1, then backup and restore is
probably the easiest solution - I don't really understand from your comments
if that is an option for you or not.
If not, then you could use DTS, or script the objects you want to change
from Enterprise Manager, then run the scripts on the target server. If you
need to keep existing data, and modify tables in place, then you'll need to
write your own ALTER scripts. You shouldn't try to modify or copy system
tables - it isn't necessary.
Whatever you decide, you should probably put some effort into putting your
object scripts in a source control system, setting up a clear release/change
management process etc. You can save a lot of time in the long run if you
have the tools and processes to understand exactly what your code is and
where it goes, especially if you may have to support multiple versions of
your application.
Simon

Newbie: Transfering databases from different servers

Hi,
I have the following simple situtation:
I created my database (just tables) in Server1, then made a copy to Server2.
There, i changed all the datatypes and created relationships
between the tables.
Now, I want to transfer, not update, completely replace, all the previous
versions of tables from Server1 with the new DB.
I want to ask before i do something stupid.
- Will DTS delete and replace all previous-version tables and add the new
relationships automatically?
- How about all these system tables that are generated automatically
(sysfiles, sysdepends, etc.) ? Do I have to replace them too?
Thanx in advance,
I know its simple but i am very afraid to screw up my DB during the copy.
-steveBackup the database on the "good" server, transfer the backup to the "bad"
server, and restore this
backup over the database on the "bad" server.
Refer to BACKUP DATABASE & RESTORE in BOL
"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
Server2.
> There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>|||"steve" wrote:
> I have the following simple situtation:
By far the easiest way to do this is to "move" the actual physical data
files. Doing this is easy with attach/detatch. See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
Maury|||"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
> Server2. There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
If you can overwrite the database on Server1, then backup and restore is
probably the easiest solution - I don't really understand from your comments
if that is an option for you or not.
If not, then you could use DTS, or script the objects you want to change
from Enterprise Manager, then run the scripts on the target server. If you
need to keep existing data, and modify tables in place, then you'll need to
write your own ALTER scripts. You shouldn't try to modify or copy system
tables - it isn't necessary.
Whatever you decide, you should probably put some effort into putting your
object scripts in a source control system, setting up a clear release/change
management process etc. You can save a lot of time in the long run if you
have the tools and processes to understand exactly what your code is and
where it goes, especially if you may have to support multiple versions of
your application.
Simon

Newbie: Transfering databases from different servers

Hi,
I have the following simple situtation:
I created my database (just tables) in Server1, then made a copy to Server2.
There, i changed all the datatypes and created relationships
between the tables.
Now, I want to transfer, not update, completely replace, all the previous
versions of tables from Server1 with the new DB.
I want to ask before i do something stupid.
- Will DTS delete and replace all previous-version tables and add the new
relationships automatically?
- How about all these system tables that are generated automatically
(sysfiles, sysdepends, etc.) ? Do I have to replace them too?
Thanx in advance,
I know its simple but i am very afraid to screw up my DB during the copy.
-steve
Backup the database on the "good" server, transfer the backup to the "bad"
server, and restore this
backup over the database on the "bad" server.
Refer to BACKUP DATABASE & RESTORE in BOL
"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
Server2.
> There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
|||"steve" wrote:

> I have the following simple situtation:
By far the easiest way to do this is to "move" the actual physical data
files. Doing this is easy with attach/detatch. See:
http://support.microsoft.com/default...b;en-us;224071
Maury
|||"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
> Server2. There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve
>
If you can overwrite the database on Server1, then backup and restore is
probably the easiest solution - I don't really understand from your comments
if that is an option for you or not.
If not, then you could use DTS, or script the objects you want to change
from Enterprise Manager, then run the scripts on the target server. If you
need to keep existing data, and modify tables in place, then you'll need to
write your own ALTER scripts. You shouldn't try to modify or copy system
tables - it isn't necessary.
Whatever you decide, you should probably put some effort into putting your
object scripts in a source control system, setting up a clear release/change
management process etc. You can save a lot of time in the long run if you
have the tools and processes to understand exactly what your code is and
where it goes, especially if you may have to support multiple versions of
your application.
Simon

Newbie: Transfering databases from different servers

Hi,
I have the following simple situtation:

I created my database (just tables) in Server1, then made a copy to Server2.
There, i changed all the datatypes and created relationships
between the tables.
Now, I want to transfer, not update, completely replace, all the previous
versions of tables from Server1 with the new DB.

I want to ask before i do something stupid.

- Will DTS delete and replace all previous-version tables and add the new
relationships automatically?
- How about all these system tables that are generated automatically
(sysfiles, sysdepends, etc.) ? Do I have to replace them too?

Thanx in advance,
I know its simple but i am very afraid to screw up my DB during the copy.

-steve"steve" <steve@.here.com> wrote in message
news:tR3je.15721$JU3.69048@.wagner.videotron.net...
> Hi,
> I have the following simple situtation:
> I created my database (just tables) in Server1, then made a copy to
> Server2. There, i changed all the datatypes and created relationships
> between the tables.
> Now, I want to transfer, not update, completely replace, all the previous
> versions of tables from Server1 with the new DB.
> I want to ask before i do something stupid.
> - Will DTS delete and replace all previous-version tables and add the new
> relationships automatically?
> - How about all these system tables that are generated automatically
> (sysfiles, sysdepends, etc.) ? Do I have to replace them too?
> Thanx in advance,
> I know its simple but i am very afraid to screw up my DB during the copy.
> -steve

If you can overwrite the database on Server1, then backup and restore is
probably the easiest solution - I don't really understand from your comments
if that is an option for you or not.

If not, then you could use DTS, or script the objects you want to change
from Enterprise Manager, then run the scripts on the target server. If you
need to keep existing data, and modify tables in place, then you'll need to
write your own ALTER scripts. You shouldn't try to modify or copy system
tables - it isn't necessary.

Whatever you decide, you should probably put some effort into putting your
object scripts in a source control system, setting up a clear release/change
management process etc. You can save a lot of time in the long run if you
have the tools and processes to understand exactly what your code is and
where it goes, especially if you may have to support multiple versions of
your application.

Simon

newbie: to cursor or not to cursor

Hi all, I'd like to ask a general question about some SQL query strategies.
Let's say I have 2 tables, e.g. an authors table (TAuthor), a works table
(TWork), and a work-authors table linking them (TWorkAuthor); each work may
have 1 or more authors:
TAuthor
authorID (PK)
firstName
lastName
TWork
workID (PK)
title
..
TWorkAuthor
wkauID (PK)
authorID
workID
Now I'd want to display a (readonly) view showing all the works with their
authors, but I do not want to make a join as I'd obtain multiple rows for
the same work when the work has more than 1 author, e.g.:
Mickey Mouse | A work by two authors | ...
Donald Duck | A work by two authors | ...
Uncle Scrooge | Another different work | ...
Rather I'd like to have:
Donald Duck, Mickey Mouse | A work by two authors | ...
Uncle Scrooge | Another different work | ...
I was thinking to create a function which uses a cursor to retrieve all the
author names for a specified @.workID, like (I retrieve only the last name
for brevity):
...
DECLARE author_cursor CURSOR FAST_FORWARD
FOR SELECT TAuthor.lastName FROM TAuthor
INNER JOIN TWorkAuthor
ON TAuthor.authID = TWorkAuthor.authID
WHERE TWorkAuthor.workID = @.workID
ORDER BY lastName
OPEN author_cursor
FETCH NEXT FROM author_cursor INTO @.name
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF LEN(@.list) > 0 SET @.list = @.list + ', '
SET @.list = @.list + @.name
FETCH NEXT FROM author_cursor INTO @.name
END
CLOSE author_cursor
DEALLOCATE author_cursor
Then in my view query I could call this function for each work of my SELECT
statement to obtain a single row field with all its author names. Anyway I
fear such an approach might severely degrade the query performance. Is there
a better way of doing this or I'm going the right path?
Thanks to all in advance!Concatenate the author names client-side since this is purely a
presentational matter. In general it should be much easier to do in
languages other than SQL. Alternatively, if you're determined on the SQL
route you can Google in this group on "aggregation" and "concatenation" to
find some TSQL-based solutions.
David Portas
SQL Server MVP
--|||You mean you want all works with their authors but want a list of authors
where there ae multiples?
As to cursors see
http://www.mindsdoor.net/SQLTsql/Cursors.html
But a function could be useful as in
http://www.mindsdoor.net/SQLTsql/CS...bleEntries.html
something like
create function csvtbl
(@.id as int)
returns varchar(1000)
AS
begin
declare @.csv varchar(1000)
select @.csv = coalesce(@.csv+',','') + a.lastName
from TAuthor a
join TWorkAuthor wa
on a.authorID = wa.authorID
where wa.wkauID = @.id
return @.csv
end
go
then
select title, dbo.csvtbl(workID)
from TWork
"Dan" wrote:

> Hi all, I'd like to ask a general question about some SQL query strategies
.
> Let's say I have 2 tables, e.g. an authors table (TAuthor), a works table
> (TWork), and a work-authors table linking them (TWorkAuthor); each work ma
y
> have 1 or more authors:
> TAuthor
> authorID (PK)
> firstName
> lastName
> TWork
> workID (PK)
> title
> ...
> TWorkAuthor
> wkauID (PK)
> authorID
> workID
> Now I'd want to display a (readonly) view showing all the works with their
> authors, but I do not want to make a join as I'd obtain multiple rows for
> the same work when the work has more than 1 author, e.g.:
> Mickey Mouse | A work by two authors | ...
> Donald Duck | A work by two authors | ...
> Uncle Scrooge | Another different work | ...
> Rather I'd like to have:
> Donald Duck, Mickey Mouse | A work by two authors | ...
> Uncle Scrooge | Another different work | ...
> I was thinking to create a function which uses a cursor to retrieve all th
e
> author names for a specified @.workID, like (I retrieve only the last name
> for brevity):
> ...
> DECLARE author_cursor CURSOR FAST_FORWARD
> FOR SELECT TAuthor.lastName FROM TAuthor
> INNER JOIN TWorkAuthor
> ON TAuthor.authID = TWorkAuthor.authID
> WHERE TWorkAuthor.workID = @.workID
> ORDER BY lastName
> OPEN author_cursor
> FETCH NEXT FROM author_cursor INTO @.name
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> IF LEN(@.list) > 0 SET @.list = @.list + ', '
> SET @.list = @.list + @.name
> FETCH NEXT FROM author_cursor INTO @.name
> END
> CLOSE author_cursor
> DEALLOCATE author_cursor
> Then in my view query I could call this function for each work of my SELEC
T
> statement to obtain a single row field with all its author names. Anyway I
> fear such an approach might severely degrade the query performance. Is the
re
> a better way of doing this or I'm going the right path?
> Thanks to all in advance!
>
>|||Thanks to both! So I'll avoid cursors...
have a good day

NEWBIE: Not an Updatable Query

Hello NG,
SQLServer 2000
Background:
I have been working with an Access2K FE with Access2K BE Tables - I have
just moved all the BE tables to the SQLServer database - I have set up full
permissions to Add,Delete,Modify and Read all the records in all the tables.
All of my code is in the FE database.
2 Questions
1. I try to run the following query:
db.Execute ("UPDATE [Stats Daily Table - Temp] INNER JOIN [Production] ON
([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
Number]) AND ([Stats Daily Table - Temp].[Daily Date] = [Production].[Stats
Date]) " & _
"SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats
Daily Table - Temp]![LB Pulling Orders] + ([Production]![Orders] +
[Production]![WPL Orders]), [Stats Daily Table - Temp].[LB Pulling Lines] =
[Stats Daily Table - Temp]![LB Pulling Lines] + ([Production]![Lines] +
[Production]![WPL Lines]) " & _
"WHERE (([Production].[Department Number]=1) AND
([Production].[Area Number]=102) AND (([Production].[Portion]='LL Lower
Portion') Or ([Production].[Portion]='LU Upper Portion') Or
([Production].[Portion]='LE Leather Portion') Or ([Production].[Portion]='LC
Closeout Portion')));")
It gives me an error that this is not an Updatable Query - It did work
Access to Access.
How can I make this query work - do I have to put the query into the
SQLServer database as a function I need to call for? Do I just need to have
it worded differently for the BE to understand.
2. If this needs to be in the BE - How do I call the query from the FE?
Please Help - I am trying to upgrade to SQLServer - I See its benefits - but
if I cannot understand how to use it I guess I'll be stuck with Access2K's
limitations.
TIAFAH
RandyFor one thing get rid of those !'s in your query between the table and
column names. Try running the query in QA (you can copy n paste it from
here):
UPDATE [Stats Daily Table - Temp]
INNER JOIN [Production]
ON ([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
Number])
AND ([Stats Daily Table - Temp].[Daily Date] = [Production].[Stats Date])
SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats Daily Table -
Temp].[LB Pulling Orders] + ([Production].[Orders] + [Production].[WPL
Orders]),
[Stats Daily Table - Temp].[LB Pulling Lines] = [Stats Daily Table -
Temp].[LB Pulling Lines] + ([Production].[Lines] + [Production].[WPL Lines])
WHERE (([Production].[Department Number]=1)
AND ([Production].[Area Number]=102)
AND (([Production].[Portion]='LL Lower Portion')
OR ([Production].[Portion]='LU Upper Portion')
OR ([Production].[Portion]='LE Leather Portion')
OR ([Production].[Portion]='LC Closeout Portion')));
Also, are you running this using ASP.NET as a front end?
Thanks
"Randy Fritz" <Randyfritz@.sbcglobal.net> wrote in message
news:eD0BclsRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hello NG,
> SQLServer 2000
> Background:
> I have been working with an Access2K FE with Access2K BE Tables - I
> have
> just moved all the BE tables to the SQLServer database - I have set up
> full
> permissions to Add,Delete,Modify and Read all the records in all the
> tables.
> All of my code is in the FE database.
> 2 Questions
> 1. I try to run the following query:
> db.Execute ("UPDATE [Stats Daily Table - Temp] INNER JOIN [Production] ON
> ([Stats Daily Table - Temp].[Employee Number] = [Production].[Employee
> Number]) AND ([Stats Daily Table - Temp].[Daily Date] =
> [Production].[Stats
> Date]) " & _
> "SET [Stats Daily Table - Temp].[LB Pulling Orders] = [Stats
> Daily Table - Temp]![LB Pulling Orders] + ([Production]![Orders] +
> [Production]![WPL Orders]), [Stats Daily Table - Temp].[LB Pulling Lines]
> =
> [Stats Daily Table - Temp]![LB Pulling Lines] + ([Production]![Lines] +
> [Production]![WPL Lines]) " & _
> "WHERE (([Production].[Department Number]=1) AND
> ([Production].[Area Number]=102) AND (([Production].[Portion]='LL Lower
> Portion') Or ([Production].[Portion]='LU Upper Portion') Or
> ([Production].[Portion]='LE Leather Portion') Or
> ([Production].[Portion]='LC
> Closeout Portion')));")
> It gives me an error that this is not an Updatable Query - It did work
> Access to Access.
> How can I make this query work - do I have to put the query into the
> SQLServer database as a function I need to call for? Do I just need to
> have
> it worded differently for the BE to understand.
> 2. If this needs to be in the BE - How do I call the query from the FE?
> Please Help - I am trying to upgrade to SQLServer - I See its benefits -
> but
> if I cannot understand how to use it I guess I'll be stuck with Access2K's
> limitations.
> TIAFAH
> Randy
>|||You might want to learn standard SQL syntax and good programming
practices. Never embed blanks in a data element name unless you want to
destroy portability. If you must use reserved words for a data element
name because you do not know ISO-11179 rules then at least do not use
square brackets. I think this is what you were trying to do:
UPDATE DailyStats
SET lb_pull_orders
= DailyStats.lb_pull_orders
+ (SELECT P1.orders + P1.wpl_orders
FROM Production AS P1
WHERE P1.dept_nbr = 1
AND P1.area_nbr = 102
AND DailyStats.emp_nbr = P1.emp_nbr
AND DailyStats.daily_date = P1.stat_date
AND P1.Portion
IN (LL Lower Portion', 'LU Upper Portion',
'LE Leather Portion''LC Closeout Portion'));|||The typo in the IN predicate notwithstanding, Joe is right about your table
naming convention. But you'll get used to that, Joe is always right :)
Even if porting your Database over to another DBMS isn't a priority, those
extra spaces and hyphens in the names could cause you problems down the road
when you try to access the database via other tools and methods. Consider
using underscore characters instead of spaces in identifiers, or just
eliminate spaces altogether and go with the AlternateCAPSLook, which happens
to be "in" this season thanks to a new reality TV show :)
Here's your IN Clause with the correct number of commas and single quotes:
IN ('LL Lower Portion', 'LU Upper Portion',
'LE Leather Portion','LC Closeout Portion'));
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1114129809.103356.5010@.l41g2000cwc.googlegroups.com...
> You might want to learn standard SQL syntax and good programming
> practices. Never embed blanks in a data element name unless you want to
> destroy portability. If you must use reserved words for a data element
> name because you do not know ISO-11179 rules then at least do not use
> square brackets. I think this is what you were trying to do:
>
> UPDATE DailyStats
> SET lb_pull_orders
> = DailyStats.lb_pull_orders
> + (SELECT P1.orders + P1.wpl_orders
> FROM Production AS P1
> WHERE P1.dept_nbr = 1
> AND P1.area_nbr = 102
> AND DailyStats.emp_nbr = P1.emp_nbr
> AND DailyStats.daily_date = P1.stat_date
> AND P1.Portion
> IN (LL Lower Portion', 'LU Upper Portion',
> 'LE Leather Portion''LC Closeout Portion'));
>|||Hi Randy,
When moving over to SQL a load of those update/select queries stop
becomming updatable, the most probable reason is the joins, see if you
can send updates to single tables first. I totally agree with the other
guys, NEVER use blanks in table names/field names use the _ instead for
readablity. Take the time to rename your tables and fields as this will
make your coding easer and more readable.
Good luck
Regards
Alex|||TY Everyone involved,
I Still Have my same problem- First I will work on transferring
everything over to the naming conventions you guys have suggested - I more
than likely will use the underscores in place of spaces as sometimes when
things go wrong others are in here that just do not understand so my naming
of the tables and fields where to make it as simple to understand as
possible for those who do not know how to read code normally.
Second - your queries I am assuming are to be set in the user defined
functions of SQLServer - We use an Access2K FE - How do I call this function
from the front end - The front end is where I have run all of my queries and
the query I gave and the ones you have given still give me the error. what
do I have to do to run the query from an Access FE?.
Again
TIAFAH
Randy
"Michael C#" <xyz@.abcdef.com> wrote in message
news:e4_9e.134$eR1.12@.fe12.lga...
> The typo in the IN predicate notwithstanding, Joe is right about your
table
> naming convention. But you'll get used to that, Joe is always right :)
> Even if porting your Database over to another DBMS isn't a priority, those
> extra spaces and hyphens in the names could cause you problems down the
road
> when you try to access the database via other tools and methods. Consider
> using underscore characters instead of spaces in identifiers, or just
> eliminate spaces altogether and go with the AlternateCAPSLook, which
happens
> to be "in" this season thanks to a new reality TV show :)
> Here's your IN Clause with the correct number of commas and single quotes:
> IN ('LL Lower Portion', 'LU Upper Portion',
> 'LE Leather Portion','LC Closeout Portion'));
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1114129809.103356.5010@.l41g2000cwc.googlegroups.com...
>|||Try pass-thru queries. Tends to be more efficient than relying on Jet as
well. And as far as naming, name your tables for *your* ease of use, not
ours :) The main point about removing spaces is that not all tools deal
equally well with spaces in identifier names, and it could cause you
problems down the road. If you're willing to take that chance though, no
one can force you to rename anything.
"Randy Fritz" <Randyfritz@.sbcglobal.net> wrote in message
news:uSL0Bm1RFHA.1096@.tk2msftngp13.phx.gbl...
> TY Everyone involved,
> I Still Have my same problem- First I will work on transferring
> everything over to the naming conventions you guys have suggested - I more
> than likely will use the underscores in place of spaces as sometimes when
> things go wrong others are in here that just do not understand so my
> naming
> of the tables and fields where to make it as simple to understand as
> possible for those who do not know how to read code normally.
> Second - your queries I am assuming are to be set in the user defined
> functions of SQLServer - We use an Access2K FE - How do I call this
> function
> from the front end - The front end is where I have run all of my queries
> and
> the query I gave and the ones you have given still give me the error. what
> do I have to do to run the query from an Access FE?.
> Again
> TIAFAH
> Randy
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:e4_9e.134$eR1.12@.fe12.lga...
> table
> road
> happens
>|||On Thu, 21 Apr 2005 16:22:21 -0700, Randy Fritz wrote:

>Hello NG,
>SQLServer 2000
>Background:
> I have been working with an Access2K FE with Access2K BE Tables - I hav
e
>just moved all the BE tables to the SQLServer database - I have set up full
>permissions to Add,Delete,Modify and Read all the records in all the tables
.
>All of my code is in the FE database.
(snip)
Hi Randy,
A few points to keep in mind:
1. The UPDATE you posted uses a syntax that only Access understands. The
ANSI standard form of the UPDATE syntax is
UPDATE TableName
SET Column = expression
[ ,Column = expression ] ...
[ WHERE logical-expression ]
SQL Server also supports an alternative UPDATE syntax, much like (but
not the same as!) the special Access syntax you used - simplified:
UPDATE [ TableName | alias ]
SET Column = expression
[ ,Column = expression ] ...
FROM TableName [ [AS] alias ]
[ (jointype) JOIN TableName [ [AS] alias ] ON logical-expression ] ...
[ WHERE logical-expression ]
The SQL Server UPDATE ... FROM syntax has some gotchas that you should
be aware of. The most important is that the end result is inpredictable
when one row in the table to be updated can be joined to more than one
row from the tables in the FROM clause.
2. If you run regular queries on linked tables in Access, then the query
will be completely executed by Access. It will get the data it needs
from SQL Server, combine the data to get the end result, then (if it's
an update query) send the modifications back to SQL Server. This means
that regular queries in Access should still use Access' version of SQL
(Jet SQL), not SQL Server's (Transact-SQL). It also means that things
might get slow, especially if a lot of data has to be fetched from the
server before the end result is generated.
3. If you define a query as a pass-through query, Access will simply
pass the exact SQL code on to SQL Server and SQL Server will execute it.
For these queries to work, you'll have to write your code in Transact
SQL, and you can't use the nice visual drag and drop tools offered by
Access. In most cases, pass-through queries perform faster than "normal"
queries.
(snip)
> 2. If this needs to be in the BE - How do I call the query from the FE?
if you create a pass-thorugh query, then the code will be sent to the BE
for execution. But the recommended best practice is to start writing
stored procedures that perform the tasks that typically need to be done,
then call the stored procedures from the FE.
Check out CREATE PROCEDURE in Books Online for the syntax you need to
create stored procedures.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)