Help has anyone come across a way to automatic db creation.

Hi I've been given a project that requires me to produce a users friendly web base administarion menu that creates a new customer for one of our systems.

The problem is this Project it needs to create a database for each user, in SQL server 2000. This has me stumpted, has anybody ever done this before and could they point me in the correct direction! The majority of our administration menu are written in ASP but any help automating this procedure would be useful.

Thanks a lot, EdYou can create a database simply using the T-SQL command

CREATE DATABASE

that can be invoked as any other command from your client.

You can also use it in a Stored Procedure.|||Originally posted by manowar
You can create a database simply using the T-SQL command

CREATE DATABASE

that can be invoked as any other command from your client.

You can also use it in a Stored Procedure.

Excellent, thanks

Help for Performing Updates Across Multiple Servers

Hi,
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
Jothi
Don't fire triggers over a network. The latency added by doing this tends to
make such a solution unworkable. Plus if one of the servers go down the
trigger and sometimes the entire server will hang for up to 20 seconds
before failing.
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
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:7F1F3EA8-2719-4A0B-AC57-1F6AB44183CF@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
>
|||BTW - your best bet is using distributed transactions.
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
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:7F1F3EA8-2719-4A0B-AC57-1F6AB44183CF@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
>
sql

HELP : Remote query from 2005 to 2000 returns wrong recordset when

Hi there
I came across this bug and could not find a solution, and I'm blocked.
Can anyone help ?
I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
SP4) server.
When I make a remote query from a (linked) SQL 2005 server (SP1 or
SP2), the returned recordset is totally wrong (rows are missing).
The problem does not occur between two SQL 2000, nor between two SQL
2005.Eric,
Can you define the condition of the missing rows better? That might give a
clue.
One suggestion is to compare the collations of the SQL 2000 server and
database with the collations of the SQL 2005 server and database. If that
is a problem, you might be able to resolve it with a COLLATE clause in the
query.
RLF
<EricBello@.eurofins.com> wrote in message
news:9c42bd9d-8d35-4370-bcd5-293830ac27f7@.l1g2000hsa.googlegroups.com...
> Hi there
> I came across this bug and could not find a solution, and I'm blocked.
> Can anyone help ?
> I have a table with TABLE CONSTRAINT, located at a SQL 2000 (SP3 or
> SP4) server.
> When I make a remote query from a (linked) SQL 2005 server (SP1 or
> SP2), the returned recordset is totally wrong (rows are missing).
> The problem does not occur between two SQL 2000, nor between two SQL
> 2005.
>|||Here is the script to reproduce the problem
At SQL 2000 side (SP3 or SP4) :
--cleanup
--DROP TABLE MyPackagesTranslations
--DROP TABLE MyPackages
GO
USE pubs
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
-- Create the table MyPackages,
CREATE TABLE [dbo].[MyPackages](
[packageIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[packageSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackages_packageSqlId] DEFAULT (1121),
[packageName] [nvarchar](60) NULL,
[isDeleted] [bit] NOT NULL CONSTRAINT
[DF_MyPackages_isDeleted_Default] DEFAULT (0x00),
CONSTRAINT [PK_MyPackages] PRIMARY KEY CLUSTERED
(
[packageIncId] ASC,
[packageSqlId] ASC
)
)
GO
-- Create linked table MyPackagesTranslations
CREATE TABLE [dbo].[MyPackagesTranslations](
[packageTranslationIncId] [int] IDENTITY(1,1) NOT FOR REPLICATION
NOT NULL,
[packageTranslationSqlId] [smallint] NOT NULL CONSTRAINT
[DF_MyPackagesTranslations_packageTranslationSqlId] DEFAULT (1121),
[packageIncId] [int] NOT NULL,
[packageSqlId] [smallint] NOT NULL,
[packageTranslationName] [nvarchar](60) NULL,
CONSTRAINT [PK_MyPackagesTranslations] PRIMARY KEY CLUSTERED
(
[packageTranslationIncId] ASC,
[packageTranslationSqlId] ASC
)
)
GO
-- ... with a foreign key between them
ALTER TABLE [dbo].[MyPackagesTranslations] WITH NOCHECK ADD CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
FOREIGN KEY([packageIncId], [packageSqlId])
REFERENCES [dbo].[MyPackages] ([packageIncId], [packageSqlId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[MyPackagesTranslations] CHECK CONSTRAINT
[FK_MyPackagesTranslations_MyPackages]
GO
-- Insert some data with "packageSqlId = 1"
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Foo')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Bar')
INSERT MyPackages (packageSqlId, packageName) VALUES (1, 'Cat')
INSERT MyPackagesTranslations (packageIncId, packageSqlId,
packageTranslationName)
SELECT packageIncId, packageSqlId, 'Translated ' + packageName
FROM dbo.MyPackages
GO
-- Now Add a CHECK CONSTRAINT for MyPackages.packageSqlId = 1121
ALTER TABLE [dbo].[MyPackages] WITH NOCHECK ADD CONSTRAINT
[CK_MyPackages] CHECK NOT FOR REPLICATION
(([packageSqlId] = 1121))
GO
ALTER TABLE [dbo].[MyPackages] CHECK CONSTRAINT [CK_MyPackages]
GO
=> And now at SQL 2005 side (SP1 or SP2)
Execute the query :
-- Now connect to another server with SQL 2005 SP1 or SP2, and launch
the remote query
-- (first verify you have a linked server)
SELECT count(*)
from <remoteserver>.pubs.dbo.MyPackages pack
INNER JOIN <remoteserver>.pubs.dbo.MyPackagesTranslations trans
ON (pack.packageIncId=trans.packageIncId and
pack.packageSqlId=trans.packageSqlId)
where pack.isDeleted=0x0
--=> returns "0" (KO) : should be "3"|||The collations are the same.
I really suspect a bug like the one on the optimizer that considered
some constraints trustworthy whereas they were "not for replication".|||I drilled down to the execution plan, and I can clearly see the
optimizer including the condition "where ...packageSqlId = 1121",
which shows it actually considers the table constraint
"trustworthy" (or sth alike) and restricts the recordset accordingly.|||The missing rows are those that don't comply with the TABLE CONSTRAINT.|||Eric,
Sorry, I really don't know. I used your repro script and get the same
results that you do.
Other things that I tried, on the SQL 2000 server create the RemoteServer
linked server pointing back to itself. Here were my results trying this:
SQLOLEDB connection: Returned 3 as desired.
SQLNCLI connection: I get the following error
OLE DB error trace [Non-interface error: Column 'packageTranslationName'
(compile-time ordinal 5) of object '"pubs"."dbo"."MyPackagesTranslations"'
was reported to have changed. The exact nature of the change is unknown].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.
And, no matter what I try from the SQL 2005 server, I always get 0 returned.
In addition to SQLNCLI connect, I tried to script in a SQLOLEDB connect, but
it converted to SQLNCLI. I tried creating the remote server using the SQL
Server radio button, also. No change in query result.
Is SQLNCLI at the root of the problem? I don't know enough to say.
So, I would say that you have probably found a bug that you could choose to
report to Microsoft. At least a bit of googling did not help me find an
answer.
RLF
<erbellico@.gmail.com> wrote in message
news:06af6c0c-6f36-4e3d-b671-64a27caa12d2@.s12g2000prg.googlegroups.com...
> The missing rows are those that don't comply with the TABLE CONSTRAINT.

Help - Formulas with Functions across Databases

I'm trying to build a table that uses a formula with a function in another database on the same server group. Enterprise Manager validates the formula, then when I save the table, an ODBC error pops up stating that the function is an invalid object name. I'm calling the function in the formula as follows: ([OtherDatabase].[dbo].[FunctionName](parameters)). Any ideas?

-- JakeAre you running at least sp2 ?

-PatP|||Yes, I'm running SP2.

-- Jake|||I know that certain situations do force you to use cross-database DML. But if you're creating a calculated field (is that what you're trying to do?) with a function in another database this means that both databases, AND THEIR NAMES (!!!) must go together, right? Why don't you move the function to the same database as the table? And if the function does reference objects in another database, then it would be transparent for the table. If you want to apply "reusability" and you happen to have a table(-s) in another database that has a calculated field that utilizes the same function, then you can just reference that function within the function wrapper.|||The Database I'm working on is a template that will be replicated for numerous individual projects, so I'm trying to reduce the number of locations the generalized Function code will be stored in. Otherwise, I was curious why I can put ([OtherDatabase].[dbo].[FunctionName](parameters)) in a View (for example) and get the desired result, yet have it not recognize the object when said code is placed in the Formula field (even after it supposedly validates the code). If I'm missing something syntax-wise, please let me know.

Help - Best way to store related data (across or down)?

Good day.
I have a form with approx 90 related components. All 90 components are
associated with an App_ID. An App_ID can occur more than once but with
different data from the 90 fields.
I simply cannot decide whether to store the data from each component in
it's own column or as a separate row.
Any suggestions would be greatly appreciated.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***I think you hit on the key word here "related". To me that automatically
implies that you would set up a separate, but related, table to contain your
90 components. Without knowing more than you said about your app, I would
probably store each related component in its own row in the second table.
My assumption is that not all 90 components would necessarily be populated
for all App_ID's and that if, in the future, you add a 91st component you
don't necessarily want to change your whole schema.
Here's some example DDL. It contains 3 related tables, Apps which contains
your AppID and other App-specific info, AppComponents which contains an
App-specific component list, and LookupComponents which contains
Component-specific information (in this case just ComponentID and Name):
CREATE TABLE [dbo].[AppComponents] (
[AppID] [int] NOT NULL ,
[ComponentID] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Apps] (
[AppID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
CREATE TABLE [dbo].[LookupComponents] (
[ComponentID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
ALTER TABLE [dbo].[AppComponents] WITH NOCHECK ADD
CONSTRAINT [PK_AppComponents] PRIMARY KEY CLUSTERED
(
[AppID],
[ComponentID]
)
GO
ALTER TABLE [dbo].[Apps] WITH NOCHECK ADD
CONSTRAINT [PK_Apps] PRIMARY KEY CLUSTERED
(
[AppID]
)
GO
ALTER TABLE [dbo].[LookupComponents] WITH NOCHECK ADD
CONSTRAINT [PK_LookupComponents] PRIMARY KEY CLUSTERED
(
[ComponentID]
)
GO
ALTER TABLE [dbo].[AppComponents] ADD
CONSTRAINT [FK_AppComponents_Apps] FOREIGN KEY
(
[AppID]
) REFERENCES [dbo].[Apps] (
[AppID]
),
CONSTRAINT [FK_AppComponents_LookupComponents] FOREIGN KEY
(
[ComponentID]
) REFERENCES [dbo].[LookupComponents] (
[ComponentID]
)
GO
"bajan_rick" <bajanrick@.devdex.com> wrote in message
news:eaMBbw1QFHA.4028@.tk2msftngp13.phx.gbl...
> Good day.
> I have a form with approx 90 related components. All 90 components are
> associated with an App_ID. An App_ID can occur more than once but with
> different data from the 90 fields.
>
> I simply cannot decide whether to store the data from each component in
> it's own column or as a separate row.
>
> Any suggestions would be greatly appreciated.
> Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> I have a form with approx 90 related components. All 90 components
are associated with an App_ID. An App_ID can occur more than once but
with different data from the 90 fields. <<
Do not confuse the forms that collect data with the data model and the
tables in the schema. If they are all attributes of the same entity,
then they belong in one row in the table. If a subset of columns is
another entity, then it needs to be in its own table.
Then we have to ask if "app_id" is really a key or not. It sounds like
a sequential form number (remember the "Bates Numbering Stamp" in the
old days?) that has nothing to do with the data model, but is used for
data collection control.
Without any real information, it is imposible to give specific advice.
But a while a 90 column table is possible, it is rare. I would guess
-- I mean guess -- that this is really a 5 to 6 column schema.
The worst thing you can do is build an EAV design or some other variant
that mixes data and metadata in the name of a "flexible" design. They
fall aprt in about a year of production work after running insanely
slow.|||Thanks guys.
I'm not if I can explain the app anymore. I guess it's more like a
spreadsheet where there is the possiblit of multiple rows being
represented by 1 App_ID (PK).
My table is currently:
CREATE TABLE [dbo].[App_Sheet] (
[SheetID] [int] NOT NULL ,
[AppID] [int] NOT NULL ,
[Component_ID] [int],
[Value] [varchar] (50) NULL
)
GO
CELKO:
What do you mean by
"..this is really a 5 to 6 column schema"
*** Sent via Developersdex http://www.examnotes.net ***