Creating an MS SQL data table programatically

I use Visual Studio and VB.
If I want to create an SQL data table on the server, I go to ServerExplorer and make a small table with the corerct columns. Then I addrows programatically as needed from the Application I'm working with.
It would suit me, for a current job, to be able to create the table itself programatically.
I don't know how to do this. Can it be done ? If so, could someone giveme a starter, please ? Four cols with a key in the first.
David Morley
SQL understands both Data Manipulation Language (DML) and Data Definition Language (DDL). The DML is the stuff you use to change the values in the table whereas the DDL allows you to change the structure of your database. Look up 'CREATE TABLE' and start there. PS, I'd avoid ADOX and the like unless you're making a cross database product...even then I'd probably stay clear.

creating an INSTANCE from an existing INSTANCE

Is it possible , without using the installation CD , to create a new
INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
--
___________________________________
Need an IT job? http://www.ITjobfeed.comNo. (Sorry.)
RLF
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:7d-dnT4Z_5Cy-0DbnZ2dnUVZ8v6dnZ2d@.bt.com...
> Is it possible , without using the installation CD , to create a new
> INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
>
> --
>
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com
>
>
>|||No Jack. You must start setup from CD\DVD again and choose another name for
your new instance. It's totally a new installation except for some common
services (SSIS etc.)
--
Ekrem Önsoy
"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:7d-dnT4Z_5Cy-0DbnZ2dnUVZ8v6dnZ2d@.bt.com...
> Is it possible , without using the installation CD , to create a new
> INSTANCE of SQL Server 2005 from an existing INSTANCE on the same server?
>
> --
>
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com
>
>
>

Creating an installer for SQL-side components of an app

Hello.
I'm researching about the ways I can create an installer of the SQL (2000)
objects for an application.
At the present time we're using a set of calls to osql utility from a VB
application. But the maintenance of the scripts is becoming cumbersome.
Does MS provides utilities for this? Where can I look up for further info?
Thanks.-
| Thread-Topic: Creating an installer for SQL-side components of an app
| thread-index: AcThIuI4MycuAx2CSYGbOG6c0kGPaQ==
| X-WBNR-Posting-Host: 200.44.173.82
| From: =?Utf-8?B?cnBhbGxhcmVz?= <rpallares@.discussions.microsoft.com>
| Subject: Creating an installer for SQL-side components of an app
| Date: Mon, 13 Dec 2004 06:49:01 -0800
| Lines: 12
| Message-ID: <CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clients
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.clients:29256
| X-Tomcat-NG: microsoft.public.sqlserver.clients
|
| Hello.
|
| I'm researching about the ways I can create an installer of the SQL
(2000)
| objects for an application.
|
| At the present time we're using a set of calls to osql utility from a VB
| application. But the maintenance of the scripts is becoming cumbersome.
|
| Does MS provides utilities for this? Where can I look up for further info?
|
| Thanks.-
|
|
<><><><><><><><><><><><><><><><><><><><><><><><><> <><><>
Hi,
If you still need assistance and you're using MSDE then I believe you will
find this link useful:
http://msdn.microsoft.com/library/de...us/dnmsde/html
/msdedepl.asp
Regards,
Yasemin Gunduz
Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What u mean with the SQL server Objects ?
"rpallares" <rpallares@.discussions.microsoft.com> wrote in message
news:CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com...
> Hello.
> I'm researching about the ways I can create an installer of the SQL (2000)
> objects for an application.
> At the present time we're using a set of calls to osql utility from a VB
> application. But the maintenance of the scripts is becoming cumbersome.
> Does MS provides utilities for this? Where can I look up for further info?
> Thanks.-
>

Creating an INSERT UPDATE Trigger that works on certain account

Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> the proprietary database account. How do I capture the user or group name in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> What is the equivalent in MS SQL? Thanks in advance!!!|||This is a multi-part message in MIME format.
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the =currently logged in user account.
Look in Books-on-Line for more information on system_user.
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message =news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database server will =
> occassionally push data back into MS SQL via JDBC. I don't want the MS =SQL > trigger to execute its SQL code if the INSERT UPDATE Trigger was =initiated by > the proprietary database account. How do I capture the user or group =name in > MS SQL? I know in PostgreSQL, you can use the "user" variable in a =trigger. > What is the equivalent in MS SQL? Thanks in advance!!!
--=_NextPart_000_0EFB_01C6952D.3CD86780
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try the sytem function: system_user. =
For example, SELECT system_user will provide the =Domain\Loginname of the currently logged in user account.
Look in Books-on-Line for more =information on system_user.
-- Arnie Rowland, YACE* "To be =successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Jaime Rios" wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...> =Hi folks,> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL =2000 > to another proprietary database server. The other database =server will > occassionally push data back into MS SQL via JDBC. I don't want =the MS SQL > trigger to execute its SQL code if the INSERT UPDATE =Trigger was initiated by > the proprietary database account. How do I capture =the user or group name in > MS SQL? I know in PostgreSQL, you can use =the "user" variable in a trigger. > What is the equivalent in MS SQL? =Thanks in advance!!!

---

--=_NextPart_000_0EFB_01C6952D.3CD86780--|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>> Hi folks,
>> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL
>> 2000
>> to another proprietary database server. The other database server will
>> occassionally push data back into MS SQL via JDBC. I don't want the MS
>> SQL
>> trigger to execute its SQL code if the INSERT UPDATE Trigger was
>> initiated by
>> the proprietary database account. How do I capture the user or group name
>> in
>> MS SQL? I know in PostgreSQL, you can use the "user" variable in a
>> trigger.
>> What is the equivalent in MS SQL? Thanks in advance!!!|||I concur with Arnie.
--
Venkat
sql server admirer
"Arnie Rowland" wrote:
> Try the sytem function: system_user.
> For example, SELECT system_user will provide the Domain\Loginname of the currently logged in user account.
> Look in Books-on-Line for more information on system_user.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-8ECF-89A3038CC99A@.microsoft.com...
> > Hi folks,
> > I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
> > to another proprietary database server. The other database server will
> > occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> > trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated by
> > the proprietary database account. How do I capture the user or group name in
> > MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
> > What is the equivalent in MS SQL? Thanks in advance!!!

Creating an INSERT UPDATE Trigger that works on certain account

just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>|||Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated b
y
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!|||just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:

> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>sql

Creating an Indexed View

I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!

Here is the Error I am given

"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3

Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."

Here is my code..

CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

AS

SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

TEW_D_LOC.LOC_DESC AS LOC_DESC ,

TEW_D_LOC.RGN_DESC AS REGION_DESC,

TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

COUNT_BIG(*) AS COUNT

FROM

JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

INNER JOIN

JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

ON

VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

INNER JOIN

JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

ON

VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

INNER JOIN

JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

ON

VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

INNER JOIN

JJWHSE.TEW_D_DT TEW_D_DT

ON

VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

INNER JOIN

JJWHSE.TEW_D_LOC TEW_D_LOC

ON

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,

TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

GO

CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

The first error message is as direct as it can be. You cannot

create a view WITH SCHEMABINDING when its definition mentions

another view that was not itself created WITH SCHEMABINDING.

In your case, you can't create 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

with schemabinding because it refers to another view,

'JJWHSE.VEW_F_INVC_SHIP_TO', which was not created with

schemabinding.

There does seem to be a bit of confusion. The code you pasted

here tries to do two things:

1. Create a view named 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

2. Create an index on 'VEW_F_MZT_ORDER_HEADER',

which is a *different* view.

The second error you got (Cannot create index...)

has nothing at all to do with the first error or with

the CREATE VIEW code that generates the first error. It's

also as clear as it can be. The 'VEW_F_MZT_ORDER_HEADER'

exists, but it was not created with schemabinding, which

is a requirement for creating an index on a view.

If you want to index a view, that view and all the views

(and functions) on which it depends must be created with

schemabinding and meet the requirements for that option.

Steve Kass

Drew University

http://www.stevekass.com
topcoder_cc@.discussions.microsoft.com wrote:

> I am trying to create an indexed view, on a date from a date dimension

> table...I am new to SQL, and I am at a loss of ideas on this one. Any

> help would be greatly appreciated!

>

> Here is the Error I am given

>

> "Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line

> 3

>

> Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'.

> 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

>

> Msg 1939, Level 16, State 1, Line 1

>

> Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is

> not schema bound."

>

> Here is my code..

>

> CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

>

> AS

>

> SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

>

> TEW_D_LOC.LOC_DESC AS LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC AS REGION_DESC,

>

> TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

>

> ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

>

> COUNT_BIG(*) AS COUNT

>

> FROM

>

> JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

>

> ON

>

> VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

>

> AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER =

> VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER =

> VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

>

> AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

>

> AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

>

> AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

>

> AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

>

> AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

>

> AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

>

> AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

>

> INNER JOIN

>

> JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

>

> ON

>

> VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

>

> AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

>

> INNER JOIN

>

> JJWHSE.TEW_D_DT TEW_D_DT

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

>

> INNER JOIN

>

> JJWHSE.TEW_D_LOC TEW_D_LOC

>

> ON

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

>

> WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

>

> GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY ,

> TEW_D_LOC.LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

>

> GO

>

> CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON

> JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

>

>

Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.

Creating an Index with a calulated member in MDX ?

Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?

Ex:

Jan07: Actual = 32000 -> index = 114

Feb07: Actual = 34000 -> index = 121

Mar07: Actual = 28000 -> index = 100

Apr07: Actual = 20000 -> index = 71

It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||

If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:

Code Snippet

(

([Date].[Month].CurrentMember, [Measures].[Actual])

/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])

) * 100

This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.

Creating an Index timing out

I am creating an index on a table wit 35 million records but I get the error

'TT_ObjPerformance' table
- Unable to create index 'IX_TT_ObjPerformance_CACode'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How can I get the index created?

Thanks
SQL Server newbie

From where are you creating the index? via query analyzer/job/some front end( hopefully not)?

Creating an Image gallery??

I'm using SRS 2005 and have been requested to create an image gallery
(containing employee images and information). The one catch is - is it
possible to display dataset rows in a dataregion where the dataset rows
are display horizontally?
The client would like to have the images/information displayed in
tablular format of 3 columns by N rows.
I've been trying various out-of-the-box solutions but can't seem to
find any way to get the data into any other format that a single
vertical column.
Any suggestions?
GlennYou need to use a table or a matrix, not a list.
Your data set should also return the data in 3 columns, so you can place
each field in a new column. The rows will be dynamic.
Kaisa M. Lindahl Lervik
<gowens@.nixonpeabody.com> wrote in message
news:1160138092.717876.194350@.h48g2000cwc.googlegroups.com...
> I'm using SRS 2005 and have been requested to create an image gallery
> (containing employee images and information). The one catch is - is it
> possible to display dataset rows in a dataregion where the dataset rows
> are display horizontally?
> The client would like to have the images/information displayed in
> tablular format of 3 columns by N rows.
> I've been trying various out-of-the-box solutions but can't seem to
> find any way to get the data into any other format that a single
> vertical column.
> Any suggestions?
> Glenn
>|||Hi,
> I've been trying various out-of-the-box solutions but can't seem to
> find any way to get the data into any other format that a single
> vertical column.
This worked for me:
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx|||Hi,
> I've been trying various out-of-the-box solutions but can't seem to
> find any way to get the data into any other format that a single
> vertical column.
This worked for me:
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspxsql

Creating an IDENTITY column in a view

Is it possible to create an IDENTITY column in a view? I would like to have a column which will always start at 1 and increment 1 for every record in the view.
Thanks!
CSThere might be another way to get what you want. It depends on your data. For example if you have a table that has unique rows you could write something like this:

SELECT
COUNT(*) AS ID,
A.Activity_Type_Ky
FROM
Activity_Type AS A
JOIN Activity_Type AS B
ON A.Activity_Type_Ky > B.Activity_Type_Ky
GROUP BY
A.Activity_Type_Ky

You could also use a function or stored procedure with a temporary table to get what you want if you are not limited to a view.|||I suggest you use a stored procedure to:

1. create a temporary table that includes the identity column
2. insert all the records of your view into the temporary table using single T-SQL statement
3. select * from the temprary table

in sql server 2000 you don't need to drop the temp table

Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error
.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just fo
r
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:

> Hello, I am trying to create a linked server to an Excel file (SQL 2005).
I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the err
or.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!
Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:

> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:
> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an exact replica of the db

Hi,
I need to create a mirror image of one our database. So that whatever data
is there in our db... show up in the other db at each second.
Is there a way this can be done..? And how.
Thanks
pmud
Hi,
You could implement Transactional Replication. See Transactional Replication
topic in Books online.
Thanks
Hari
SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud
|||one second latency? Not sure if that can be accomplished, but you will need
to use replication to get you close.
What is the purpose here? Disaster recovery? Reporting server?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud
|||Just to add, replication will only copy data, but not other structural
changes and new proceures, new tables etc. What's the requirement? If you
want a 'mirror' copy as you said, may be logshipping is what you are after
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud
|||Hi ,
I want a mirror image for everything not only data but all stored
procedures, views ( and everythjing else) that are added in one db should be
automatically reflected in the mirror image too . 1 second latency is not
necessary; a few minutes will do too.
The mirror image is needed for testing purposes. We develop our sites on a
Dev db. Then port all the data to live. But we are now planning to introduce
another db layer to testing. So that all our sites are develpoed in the dev
db. We have a mirror image of the LIVE db running. Then we migrate everything
from DEV db to the MIRROR image db. Then from there we move to the live db .
This is because once everything is working on the mirror image, it will work
on live too since both are the exact same.
What is the best thing for this... replication? or Logshipping or something
else.? And where can I find some information about it?
Thanks for your help.
pmud
"Narayana Vyas Kondreddi" wrote:

> Just to add, replication will only copy data, but not other structural
> changes and new proceures, new tables etc. What's the requirement? If you
> want a 'mirror' copy as you said, may be logshipping is what you are after
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
>
>
|||In your case, you need to look into log-shipping.
Check this out,
http://www.microsoft.com/technet/pro...y/hasog02.mspx
FYI, in SQL Server 2005 there is a new concept called database mirroring,
which comes close to your requirement. Google it to get more info.
- - - - - - - - -
Thanks
Yogish
"pmud" wrote:
[vbcol=seagreen]
> Hi ,
> I want a mirror image for everything not only data but all stored
> procedures, views ( and everythjing else) that are added in one db should be
> automatically reflected in the mirror image too . 1 second latency is not
> necessary; a few minutes will do too.
> The mirror image is needed for testing purposes. We develop our sites on a
> Dev db. Then port all the data to live. But we are now planning to introduce
> another db layer to testing. So that all our sites are develpoed in the dev
> db. We have a mirror image of the LIVE db running. Then we migrate everything
> from DEV db to the MIRROR image db. Then from there we move to the live db .
> This is because once everything is working on the mirror image, it will work
> on live too since both are the exact same.
> What is the best thing for this... replication? or Logshipping or something
> else.? And where can I find some information about it?
> Thanks for your help.
> --
> pmud
>
> "Narayana Vyas Kondreddi" wrote:
sql

Creating an exact replica of the db

Hi,
I need to create a mirror image of one our database. So that whatever data
is there in our db... show up in the other db at each second.
Is there a way this can be done..? And how.
Thanks
--
pmudHi,
You could implement Transactional Replication. See Transactional Replication
topic in Books online.
Thanks
Hari
SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||one second latency? Not sure if that can be accomplished, but you will need
to use replication to get you close.
What is the purpose here? Disaster recovery? Reporting server?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Just to add, replication will only copy data, but not other structural
changes and new proceures, new tables etc. What's the requirement? If you
want a 'mirror' copy as you said, may be logshipping is what you are after
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Hi ,
I want a mirror image for everything not only data but all stored
procedures, views ( and everythjing else) that are added in one db should be
automatically reflected in the mirror image too . 1 second latency is not
necessary; a few minutes will do too.
The mirror image is needed for testing purposes. We develop our sites on a
Dev db. Then port all the data to live. But we are now planning to introduce
another db layer to testing. So that all our sites are develpoed in the dev
db. We have a mirror image of the LIVE db running. Then we migrate everything
from DEV db to the MIRROR image db. Then from there we move to the live db .
This is because once everything is working on the mirror image, it will work
on live too since both are the exact same.
What is the best thing for this... replication? or Logshipping or something
else.? And where can I find some information about it?
Thanks for your help.
--
pmud
"Narayana Vyas Kondreddi" wrote:
> Just to add, replication will only copy data, but not other structural
> changes and new proceures, new tables etc. What's the requirement? If you
> want a 'mirror' copy as you said, may be logshipping is what you are after
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> > Hi,
> >
> > I need to create a mirror image of one our database. So that whatever data
> > is there in our db... show up in the other db at each second.
> >
> > Is there a way this can be done..? And how.
> >
> > Thanks
> > --
> > pmud
>
>|||In your case, you need to look into log-shipping.
Check this out,
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog02.mspx
FYI, in SQL Server 2005 there is a new concept called database mirroring,
which comes close to your requirement. Google it to get more info.
--
- - - - - - - - -
Thanks
Yogish
"pmud" wrote:
> Hi ,
> I want a mirror image for everything not only data but all stored
> procedures, views ( and everythjing else) that are added in one db should be
> automatically reflected in the mirror image too . 1 second latency is not
> necessary; a few minutes will do too.
> The mirror image is needed for testing purposes. We develop our sites on a
> Dev db. Then port all the data to live. But we are now planning to introduce
> another db layer to testing. So that all our sites are develpoed in the dev
> db. We have a mirror image of the LIVE db running. Then we migrate everything
> from DEV db to the MIRROR image db. Then from there we move to the live db .
> This is because once everything is working on the mirror image, it will work
> on live too since both are the exact same.
> What is the best thing for this... replication? or Logshipping or something
> else.? And where can I find some information about it?
> Thanks for your help.
> --
> pmud
>
> "Narayana Vyas Kondreddi" wrote:
> > Just to add, replication will only copy data, but not other structural
> > changes and new proceures, new tables etc. What's the requirement? If you
> > want a 'mirror' copy as you said, may be logshipping is what you are after
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> > > Hi,
> > >
> > > I need to create a mirror image of one our database. So that whatever data
> > > is there in our db... show up in the other db at each second.
> > >
> > > Is there a way this can be done..? And how.
> > >
> > > Thanks
> > > --
> > > pmud
> >
> >
> >

Creating an exact replica of the db

Hi,
I need to create a mirror image of one our database. So that whatever data
is there in our db... show up in the other db at each second.
Is there a way this can be done..? And how.
Thanks
--
pmudHi,
You could implement Transactional Replication. See Transactional Replication
topic in Books online.
Thanks
Hari
SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||one second latency? Not sure if that can be accomplished, but you will need
to use replication to get you close.
What is the purpose here? Disaster recovery? Reporting server?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Just to add, replication will only copy data, but not other structural
changes and new proceures, new tables etc. What's the requirement? If you
want a 'mirror' copy as you said, may be logshipping is what you are after
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
> Hi,
> I need to create a mirror image of one our database. So that whatever data
> is there in our db... show up in the other db at each second.
> Is there a way this can be done..? And how.
> Thanks
> --
> pmud|||Hi ,
I want a mirror image for everything not only data but all stored
procedures, views ( and everythjing else) that are added in one db should b
e
automatically reflected in the mirror image too . 1 second latency is not
necessary; a few minutes will do too.
The mirror image is needed for testing purposes. We develop our sites on a
Dev db. Then port all the data to live. But we are now planning to introduce
another db layer to testing. So that all our sites are develpoed in the dev
db. We have a mirror image of the LIVE db running. Then we migrate everythin
g
from DEV db to the MIRROR image db. Then from there we move to the live db
.
This is because once everything is working on the mirror image, it will work
on live too since both are the exact same.
What is the best thing for this... replication? or Logshipping or something
else.? And where can I find some information about it?
Thanks for your help.
pmud
"Narayana Vyas Kondreddi" wrote:

> Just to add, replication will only copy data, but not other structural
> changes and new proceures, new tables etc. What's the requirement? If you
> want a 'mirror' copy as you said, may be logshipping is what you are after
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:A44EB9E3-ABCF-435F-B22B-47BC81AE02E5@.microsoft.com...
>
>|||In your case, you need to look into log-shipping.
Check this out,
http://www.microsoft.com/technet/pr...oy/hasog02.mspx
FYI, in SQL Server 2005 there is a new concept called database mirroring,
which comes close to your requirement. Google it to get more info.
--
- - - - - - - - -
Thanks
Yogish
"pmud" wrote:
[vbcol=seagreen]
> Hi ,
> I want a mirror image for everything not only data but all stored
> procedures, views ( and everythjing else) that are added in one db should
be
> automatically reflected in the mirror image too . 1 second latency is not
> necessary; a few minutes will do too.
> The mirror image is needed for testing purposes. We develop our sites on a
> Dev db. Then port all the data to live. But we are now planning to introdu
ce
> another db layer to testing. So that all our sites are develpoed in the de
v
> db. We have a mirror image of the LIVE db running. Then we migrate everyth
ing
> from DEV db to the MIRROR image db. Then from there we move to the live d
b .
> This is because once everything is working on the mirror image, it will wo
rk
> on live too since both are the exact same.
> What is the best thing for this... replication? or Logshipping or somethi
ng
> else.? And where can I find some information about it?
> Thanks for your help.
> --
> pmud
>
> "Narayana Vyas Kondreddi" wrote:
>

Creating an associative table using SQL as values change ....

I have a table in a database however there are no pk-fk relationships in the
schema.
So i need to create a view/temp table which relates the Sublevels to the Top
Level values.
For example, i have the following table:
Name LV_Level LV_ID
Admin 1 1
HR 1 8
Ops 1 11
Issuer 2 12
Acquirer 2 13
. . .
. . .
Shared Serv 1 19
Finance 2 20
Facilities 2 21
Legal 2 22
The level 2s and greater indicate sublevels to the level 1.
What i need to be able to do is create a hierarchy so that Issuer (2) and
Acquirer (2) belong to Ops (Level 1, ID 11)
and that Finance (2), Facilities (2) and Legal (2) belong to Shared Serv
(Level 1, ID 19).
The LV_ID gets renumbered as new values in the Application are added to the
database. There is another column not shown that acts a pk/uid, but there is
no relationship in this table other than a sequential renumbering of LV_ID.
So if i add anouther value under Ops, Shared Serv may get renumbered to 20
and all the items below it are renumbered as well.
I need to be able account for growth in the tables are new values are added.
I was thinking of something along the results of:
LV_ID Level_Reports_to
1 1
8 8
11 11
12 11
13 11
19 19
20 19
21 19
22 19
I've tried various ways and am not accomplishing the results above.
Any hints on syntax in SQL would be appreciated.On Tue, 13 Sep 2005 08:01:56 -0600, TroyS wrote:

>I have a table in a database however there are no pk-fk relationships in th
e
>schema.
Hi TroyS,
Fix that first, please. Every table should have a primary key. Every
relationship should be enforced by a FK constraint. Omitting that basic
rule of relational databases is asking for garbage in your data.

>So i need to create a view/temp table which relates the Sublevels to the To
p
>Level values.
>For example, i have the following table:
(snip)
>I need to be able account for growth in the tables are new values are added
.
>I was thinking of something along the results of:
>LV_ID Level_Reports_to
>1 1
> 8 8
>11 11
>12 11
>13 11
>19 19
>20 19
>21 19
>22 19
>I've tried various ways and am not accomplishing the results above.
>Any hints on syntax in SQL would be appreciated.
I'm not sure if this is the best way to store your data, though I'm very
sure that it's a whole lot better than your current way!
Order a copy of Joe Celko's Trees and Hierarchies in SQL For Smarties
now, and read it when you have it to find out all you want to know (and
more) about good and bad ways to model this kind of data.
Anyway, for now I'll give you a query that will hopefully convert your
current mess in the somewhat better version you're asking for:
INSERT INTO BetterTable (LV_ID, Level_Reports_to)
SELECT a.LV_ID,
CASE
WHEN a.LV_Level = 1
THEN a.LV_ID
ELSE (SELECT MAX(b.LV_ID)
FROM BadTable AS b
WHERE b.LV_Level = 1
AND b.LV_ID < a.LV_ID)
END
FROM BadTable AS a
(Untested - see www.aspfaq.com/5006 if you prefer a tested version).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
thx for the info
I realize the schema is flawed, however, this isn't an appl i'm writing.
it's a 3rd party,commercial appl and therefore i can't change the
schema.
If the schema had pk-fk relationships, then i wouldn't need some
help...
i will take a look at the query...thx for the help.
*** Sent via Developersdex http://www.examnotes.net ***

Creating an Assembly that calls a COM object

Hi,
I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
which in turn calls some COM stuff. I get an error message in SQL Server 200
5
related to COM interoperability (does not exist in SQL catalog). I can give
you the exact error message if needed. Any ideas on how this can be handled?
Appreciate any help. Thanks.Can anyone help me with my question? Thanks.
"KMP" wrote:

> Hi,
> I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
> which in turn calls some COM stuff. I get an error message in SQL Server 2
005
> related to COM interoperability (does not exist in SQL catalog). I can giv
e
> you the exact error message if needed. Any ideas on how this can be handle
d?
> Appreciate any help. Thanks.|||Hello KMP,
Providing the error and code in question is always a good idea. Did you cata
log
the assembly as unsafe?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Here is the error message (below) that I get when I try to create an
assembly in SQL Server2005 pointing to Test.dll (just an example name).
Test.dll (which is C# dll) refers to some COM objects inside it. I thinking
that is what is causing the Assembly to fail. I hope this info is good
enough. Please let me know if I need to pass on any more info. Thanks.
========================================
=============
Assembly "interop.testlib,
version=0.0.0.0,culture=neutral,publickeytoken=null" was not found in SQL
catalog
========================================
=============
"Kent Tegels" wrote:

> Hello KMP,
> Providing the error and code in question is always a good idea. Did you ca
talog
> the assembly as unsafe?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello KMP,

> Here is the error message (below) that I get when I try to create an
> assembly in SQL Server2005 pointing to Test.dll (just an example
> name). Test.dll (which is C# dll) refers to some COM objects inside
> it. I thinking that is what is causing the Assembly to fail. I hope
> this info is good enough. Please let me know if I need to pass on any
> more info. Thanks.
Its been ages since I need to do something like this from even regular .NET.
My first thought is that you probably need to run TLBIMP and get a PIA into
the directory with your DLL so that it also catalogs it. You may also have
to manually deploy it.
I'm pushing this over to the CLR newsgroup and we'll see if Niels chimes
in with additional thoughts.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||I was wondering if anyone could help me with this issue. Thanks in
anticipation...
"Kent Tegels" wrote:

> Hello KMP,
>
> Its been ages since I need to do something like this from even regular .NE
T.
> My first thought is that you probably need to run TLBIMP and get a PIA int
o
> the directory with your DLL so that it also catalogs it. You may also have
> to manually deploy it.
> I'm pushing this over to the CLR newsgroup and we'll see if Niels chimes
> in with additional thoughts.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||examnotes <KMP@.discussions.microsoft.com> wrote in news:88F5ED5D-
B63B-436D-AB35-B5B90AA66C75@.microsoft.com:

> I was wondering if anyone could help me with this issue. Thanks in
> anticipation...
>
Sorry, I didn't see this post until now. Can you please re-post the issue
you have.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
which in turn calls some COM stuff. I get an error message in SQL Server 200
5
related to COM interoperability (does not exist in SQL catalog). I can give
you the exact error message if needed. Thanks.
"KMP" wrote:

> Hi,
> I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
> which in turn calls some COM stuff. I get an error message in SQL Server 2
005
> related to COM interoperability (does not exist in SQL catalog). I can giv
e
> you the exact error message if needed. Any ideas on how this can be handle
d?
> Appreciate any help. Thanks.|||Hello KMP,

> I am trying to create an Assembly in SQL Server 2005 that calls a C#
> DLL which in turn calls some COM stuff. I get an error message in SQL
> Server 2005 related to COM interoperability (does not exist in SQL
> catalog). I can give you the exact error message if needed. Thanks.
Reposting to m.p.ss.clr, please continue thread there.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||What is "m.p.ss.clr"?
"Kent Tegels" wrote:

> Hello KMP,
>
> Reposting to m.p.ss.clr, please continue thread there.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>

Creating an Assembly that calls a COM object

Hi,
I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
which in turn calls some COM stuff. I get an error message in SQL Server 2005
related to COM interoperability (does not exist in SQL catalog). I can give
you the exact error message if needed. Any ideas on how this can be handled?
Appreciate any help. Thanks.
Can anyone help me with my question? Thanks.
"KMP" wrote:

> Hi,
> I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
> which in turn calls some COM stuff. I get an error message in SQL Server 2005
> related to COM interoperability (does not exist in SQL catalog). I can give
> you the exact error message if needed. Any ideas on how this can be handled?
> Appreciate any help. Thanks.
|||Hello KMP,
Providing the error and code in question is always a good idea. Did you catalog
the assembly as unsafe?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Here is the error message (below) that I get when I try to create an
assembly in SQL Server2005 pointing to Test.dll (just an example name).
Test.dll (which is C# dll) refers to some COM objects inside it. I thinking
that is what is causing the Assembly to fail. I hope this info is good
enough. Please let me know if I need to pass on any more info. Thanks.
================================================== ===
Assembly "interop.testlib,
version=0.0.0.0,culture=neutral,publickeytoken=nul l" was not found in SQL
catalog
================================================== ===
"Kent Tegels" wrote:

> Hello KMP,
> Providing the error and code in question is always a good idea. Did you catalog
> the assembly as unsafe?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||Hello KMP,

> Here is the error message (below) that I get when I try to create an
> assembly in SQL Server2005 pointing to Test.dll (just an example
> name). Test.dll (which is C# dll) refers to some COM objects inside
> it. I thinking that is what is causing the Assembly to fail. I hope
> this info is good enough. Please let me know if I need to pass on any
> more info. Thanks.
Its been ages since I need to do something like this from even regular .NET.
My first thought is that you probably need to run TLBIMP and get a PIA into
the directory with your DLL so that it also catalogs it. You may also have
to manually deploy it.
I'm pushing this over to the CLR newsgroup and we'll see if Niels chimes
in with additional thoughts.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||I was wondering if anyone could help me with this issue. Thanks in
anticipation...
"Kent Tegels" wrote:

> Hello KMP,
>
> Its been ages since I need to do something like this from even regular .NET.
> My first thought is that you probably need to run TLBIMP and get a PIA into
> the directory with your DLL so that it also catalogs it. You may also have
> to manually deploy it.
> I'm pushing this over to the CLR newsgroup and we'll see if Niels chimes
> in with additional thoughts.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
|||=?Utf-8?B?S01Q?= <KMP@.discussions.microsoft.com> wrote in news:88F5ED5D-
B63B-436D-AB35-B5B90AA66C75@.microsoft.com:

> I was wondering if anyone could help me with this issue. Thanks in
> anticipation...
>
Sorry, I didn't see this post until now. Can you please re-post the issue
you have.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
which in turn calls some COM stuff. I get an error message in SQL Server 2005
related to COM interoperability (does not exist in SQL catalog). I can give
you the exact error message if needed. Thanks.
"KMP" wrote:

> Hi,
> I am trying to create an Assembly in SQL Server 2005 that calls a C# DLL
> which in turn calls some COM stuff. I get an error message in SQL Server 2005
> related to COM interoperability (does not exist in SQL catalog). I can give
> you the exact error message if needed. Any ideas on how this can be handled?
> Appreciate any help. Thanks.
|||Hello KMP,

> I am trying to create an Assembly in SQL Server 2005 that calls a C#
> DLL which in turn calls some COM stuff. I get an error message in SQL
> Server 2005 related to COM interoperability (does not exist in SQL
> catalog). I can give you the exact error message if needed. Thanks.
Reposting to m.p.ss.clr, please continue thread there.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||What is "m.p.ss.clr"?
"Kent Tegels" wrote:

> Hello KMP,
>
> Reposting to m.p.ss.clr, please continue thread there.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
sql