Creating DB from scripts: 2012

Thursday, March 29, 2012

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

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

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

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

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

VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

INNER JOIN

JJWHSE.TEW_D_LOC TEW_D_LOC

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

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

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

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 index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book a
nd
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530
Is there a question here? You certainly can create an index on a Bit column
but the question is do you really want to? Most of the time the selectivity
is too low to be of use for an index. But under certain conditions it makes
sense.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:3339C46D-605A-4522-852D-E67DDA36B8DF@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>
|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

Creating an index on a BIT column

I had an issue with indexing a BIT column in SQL Server 2000. Every book and
newsgroup I read said that you cannot do this. But you actually can!
Refer to this website:
http://www.aspfaq.com/show.asp?id=2530Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:F472E5D4-2406-4455-BD40-BCFF8B469507@.microsoft.com...
>I had an issue with indexing a BIT column in SQL Server 2000. Every book
>and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530
>
>|||It is well known, that a BIT column cannot be indexed in SQL Server 7.0
or earlier. As of SQL Server 2000 this was changed, and you can now also
index BIT column(s).
Note that in many situations, indexing a BIT column is not useful. Only
if the data distribution is very skewed (many 0's, few 1's or vice
versa) will the optimizer consider using the index.
HTH,
Gert-Jan
Anonymous wrote:
> I had an issue with indexing a BIT column in SQL Server 2000. Every book and
> newsgroup I read said that you cannot do this. But you actually can!
> Refer to this website:
> http://www.aspfaq.com/show.asp?id=2530

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 to a SELECT statement

Hi all,
I am pretty much going insane. I have tried all sorts of things and gotten
nowhere, and I'm fairly sure there exists a simple solution to my problem.
If you help me, I will be eternally in your debt.
Here is the scenario. I have a stored procedure which conains a fairly heavy
UNION query, which I'm not going to repeat here. The data comes from all
sorts of places, and the data that comes back has no unique record
identifier. I want to add one, that is, effectively add a IDENTITY column to
the query result.
I have put the data from the UNION query into a table variable, called
@.myResults (I could put it into a temp table #myResult instead, if you
care). This kind of makes my problem simpler to see, but be aware that the
source tables have no unique identifier I can use. The column "myID" I have
just made up with zero value, in case it can be used.
SELECT myID, Color FROM @.myResults -- Simplified example, this is
what I get
0 Blue
0 Red
0 Green
I want to add a column or update the myID column so that it looks like this,
counting each row
-- This is what I want. How?!?!
1 Blue
2 Red
3 Green
Simple, eh? That's what I thought.
I have tried this --
SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
work
and
SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has number
3 on each row
what I want is something like this --
SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt as
far as I can tell
As a general thing, I'm not sure how to add an identity column to a table
that already has data in it. That's kind of what I am trying to do, but to a
select statement result.
I have even considered looping through each record in a cursor and manually
updating the int. Seems like a lot of work, and this stored procedure is
going to get hit a lot and needs to be fairly fast.
Mostly I'm just burning up because I *know* there is a simple answer to
this - I just can't see it!
Thanks in advance,
SaulLook at the IDENTITY function in Books Online; you basically want to do
something like:
SELECT IdentColumn = IDENTITY(int, 1,1),
OTHERColumns
INTO TargetTable --must be a table or temp table
FROM @.myResults
HTH
Stu|||I suggest you to insert data into a #TempTable like
Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From TableNam
e
-- That should generate record numbers for you
HTH
Ed
"Saul" wrote:

> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly hea
vy
> UNION query, which I'm not going to repeat here. The data comes from all
> sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
to
> the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I hav
e
> just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like thi
s,
> counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has numbe
r
> 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt a
s
> far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
a
> select statement result.
> I have even considered looping through each record in a cursor and manuall
y
> updating the int. Seems like a lot of work, and this stored procedure is
> going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>
>|||Guys,
Thank you for your responses!!
Yes, what you suggested works, and works quite well. What I guess I don't
like about it is that this means creating a temp table to solve the problem.
But it does work, so I'm not complaining! So, thanks again!!
On the way down to lunch, I thought of another solution though, which I like
better and also works. Here's the idea - when I intially declare the table
variable (@.myResults) I define an indentity column there. Then, when I do
the insert into.., I don't insert into that ID column, and it takes care of
creating the identity. What I preffer about this solution is that the
identity is built the first time when the data is being inserted.
eg
declare @.myResults table (my_ID int identity(1,1) , colour varchar(20))
insert into @.myResults
SELECT colour
FROM table1
UNION
SELECT colour
FROM table2
Of course, my real world query is vastly more complicated, but it's for
illustration purposes.
- Saul
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:7088713E-1340-4D8D-9328-C8F0DEAF251B@.microsoft.com...
>I suggest you to insert data into a #TempTable like
> Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From
> TableName
> -- That should generate record numbers for you
> HTH
> Ed
>
> "Saul" wrote:
>|||There is no "simple" way of doing this, and there may not every be. The
issue here is that you need to something to order the data on. If you have
some unique value, and you want to add a sequence number, you can do
something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
To do a non-sortable order, you will need to build the data first (don't
expect the order you see from a select to always be the order of the
results. There are no guarantees with row order in a relational database.)
(note, in 2005 there will be an easier way to do this, but the same
limitations do exist.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Saul" <sbryan@.nsw.counterpoint.com.au> wrote in message
news:4355a839$0$1360$c30e37c6@.ken-reader.news.telstra.net...
> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly
> heavy UNION query, which I'm not going to repeat here. The data comes from
> all sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
> to the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I
> have just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like
> this, counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has
> number 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt
> as far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
> a select statement result.
> I have even considered looping through each record in a cursor and
> manually updating the int. Seems like a lot of work, and this stored
> procedure is going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>

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 Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an expression - how to concatinate

I'm creating an expression in a textbox in a table.
I would like to concatinate two expressions together.
What function can do this?Jim, you can use the ampersand (&) to concatinate items. For example,
fieldname & "/" & fieldname
"Jim" wrote:
> I'm creating an expression in a textbox in a table.
> I would like to concatinate two expressions together.
> What function can do this?sql

creating an existing db schema baseline

What is the best method of creating schema creation scripts that can be
stored into a version control system. The process of using em to
generate a script is not an appealing option. I am still learning the
MS Sql sys tables and have not found a useful list of all the codes &
types to join the tables etc.

mike

--
Posted via http://dbforums.comwukie <member30544@.dbforums.com> wrote in message news:<3242331.1060980041@.dbforums.com>...
> What is the best method of creating schema creation scripts that can be
> stored into a version control system. The process of using em to
> generate a script is not an appealing option. I am still learning the
> MS Sql sys tables and have not found a useful list of all the codes &
> types to join the tables etc.
>
> mike

I don't like the fact that all source code versioning systems are
using proprietary files instead of proven relational databases
(SourceSafe is not exception from this). The reasons for this are
probably RDBMS licensing costs in the past.

Database schema can be exported also as XML file, which can be further
manipulated. If you and your team have serious schema versioning needs
I suggest you to evaluate Meta Data Services in SQL Server 2000 and
XML. One article about this has been published in the MSDN Magazine:
http://msdn.microsoft.com/msdnmag/i...es/default.aspx

Metadata Repository can be created not only through Enterprise Manager
but also programmatically using Meta Data API. Further information
with examples can be found in Meta Data Services SDK 3.0, which can be
downloaded for free.

Sinisa Catic|||found what I was looking for...

in EM > Tools > Generate SQL Scripts. THis will create the total schema
of the existing database.

mike

any known issues with this tool??

--
Posted via http://dbforums.com

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 custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
Nimesh
Upgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegr oups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>
|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegr oups.com...
>
>
> - Show quoted text -

Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
NimeshUpgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
>
> > Hi,
> > I have an requirement for doing somecustomaction in my
> >applicationwhen anySQLservertable is modified. One of the ways,
> > which I think it is achievable is to write ancustomapplicationwhich
> > listens toSQLqueries fired on the particular Database. This will
> > enable me to do thecustomaction, when any DML statement is trapped
> > through mycustomSQLprofiler. So, more or less, it boils down to
> > writing/using something similar to theSQLProfilertool. Any help
> > would be appreciated.
> > Thanks in Advance,
> > Nimesh- Hide quoted text -
> - Show quoted text -

Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
NimeshUpgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
>
>
>
> - Show quoted text -sql

Creating an Audit trail on a table using a trigger

Hi,
This is kind of following on from my last couple of posts regarding Identity
columns and so on.
Basically, I want to ensure that for a particular table, every row has a
numeric reference. This reference must be unique and gapless. Ideally it
should order in the sequence of the records being inserted however this
isn't an absolute requirement.
From my (limited) understanding of SQLS, I think I can achieve this with a
FOR INSERT Trigger - in that the trigger is fired every time a row is
inserted and the trigger is the same transaction as the initial insert hence
I avoid any concurrency issues.
However I'm not completely sure how to achieve this. I think that my trigger
should be along the lines of this...
CREATE TRIGGER AssignAuditReference ON tblBooking
FOR INSERT
AS
DECLARE @.Ref int
--Get the highest reference and add one.
SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
--Update the inserted row to have a booking_referecen of the new reference
obtained above.
Update tblBooking
SET Booking_Reference = @.ref
WHERE Booking_ID = INSERTED.Booking_ID
However I'm getting problems with the INSERTED table not being recognised.
I understood that the INSERTED table contained the row that the insert that
started the trigger inserted.
Two questions:
1. Where am I going wrong with my trigger. Have I misunderstood some key
point of using triggers.
2. Is this the right approach to achieve what I am after? Are there any
better approaches...Hi Chris,
You have to mention the Inserted Table in your Update Query
Update tblBooking
SET tblBooking.Booking_Reference = @.Ref
FROM tblBooking
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
Because of the lack between getting the @.Ref-Value and writing it in the
table i would prefer an inline Query and Update
Update tblBooking
SET tblBooking.Booking_Reference = NewJobIdTable.NewJobId
FROM tblBooking,
(
Select ISNULL(MAX(job_id),0)+1 AS NewJobId from tblBooking
) NewJobIdTable
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Chris Strug" <hotmail@.solace1884.com> schrieb im Newsbeitrag
news:eBcd9GOQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is kind of following on from my last couple of posts regarding
> Identity
> columns and so on.
> Basically, I want to ensure that for a particular table, every row has a
> numeric reference. This reference must be unique and gapless. Ideally it
> should order in the sequence of the records being inserted however this
> isn't an absolute requirement.
> From my (limited) understanding of SQLS, I think I can achieve this with a
> FOR INSERT Trigger - in that the trigger is fired every time a row is
> inserted and the trigger is the same transaction as the initial insert
> hence
> I avoid any concurrency issues.
> However I'm not completely sure how to achieve this. I think that my
> trigger
> should be along the lines of this...
> CREATE TRIGGER AssignAuditReference ON tblBooking
> FOR INSERT
> AS
> DECLARE @.Ref int
> --Get the highest reference and add one.
> SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
> --Update the inserted row to have a booking_referecen of the new
> reference
> obtained above.
> Update tblBooking
> SET Booking_Reference = @.ref
> WHERE Booking_ID = INSERTED.Booking_ID
> However I'm getting problems with the INSERTED table not being recognised.
> I understood that the INSERTED table contained the row that the insert
> that
> started the trigger inserted.
> Two questions:
> 1. Where am I going wrong with my trigger. Have I misunderstood some key
> point of using triggers.
> 2. Is this the right approach to achieve what I am after? Are there any
> better approaches...
>|||Syntactically, your UPDATE statement is missing the FROM clasue:
UPDATE tblBooking
SET Booking_Reference = @.ref
FROM tblBooking , INSERTED
WHERE Booking_ID = INSERTED.Booking_ID
1) This will FAIL if more than one row is inserted - not a good idea
for maintaining an audit trail. 2) I don't see what advantage this has
over the more concise and reliable solution(s) already discussed in
your earlier threads. For example:
INSERT INTO tblBooking (booking_reference, x, y, z, ...)
SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
FROM tblBooking
IMO an incrementing counter is a poor way to maintain an audit trail
anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
preserve the history of changes to rows? This is easy to do in triggers
or in your data access code and doesn't suffer the inevitable and
serious blocking problems that your approach implies.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113478283.991829.222620@.g14g2000cwa.googlegroups.com...
> Syntactically, your UPDATE statement is missing the FROM clasue:
> UPDATE tblBooking
> SET Booking_Reference = @.ref
> FROM tblBooking , INSERTED
> WHERE Booking_ID = INSERTED.Booking_ID
> 1) This will FAIL if more than one row is inserted - not a good idea
> for maintaining an audit trail. 2) I don't see what advantage this has
> over the more concise and reliable solution(s) already discussed in
> your earlier threads. For example:
> INSERT INTO tblBooking (booking_reference, x, y, z, ...)
> SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
> FROM tblBooking
> IMO an incrementing counter is a poor way to maintain an audit trail
> anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
> preserve the history of changes to rows? This is easy to do in triggers
> or in your data access code and doesn't suffer the inevitable and
> serious blocking problems that your approach implies.
> --
> David Portas
> SQL Server MVP
> --
>
First of all thanks to both David and Jens for their replies.
Apologies for repeating myself, I just want to make sure that I understand
what I'm doing rather than repeating it parrot fashion into my database.
Regarding the trigger, I was under the impression that the trigger would
occur for every new row, I gather that it in fact applies to every INSERT.
Ahh... That makes things clearer.
Regards the actual implementation (TIMESTAMP vs. numeric reference),
unfortunately this is out of my hands. I've been informed that this a is a
non negotiatable requirement. What can you do?
if I may ask one more question, assuming that I did attempt to implement my
apprioach using triggers, could you expand on the blocking problems that you
would expect me to face?
Anyway, thank you once again for taking the time to help me, I do appreciate
it.
Regards
Chris.

Creating an asynchrous output

Are there any good examples creating an asychronous output. I need to be able to output the values from the input as well as adding 7 new columns to each row.

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

The answers are available, you just need to search for them. I found the link above by typing "asynchronous component code ssis" into Google.

-Jamie

|||

Also, be aware that just because you need to add columns to a row does not mean that you need to use an async output. You need to use an async output only if you need to hold onto data longer that the ProcessInput call or if you need to generate more rows going out than coming in. Generally, if one of those 2 conditions don't apply then you can use a sync output, which I would highly recommend because it is significantly simpler.

HTH,

Matt

|||

Matt David wrote:

Also, be aware that just because you need to add columns to a row does not mean that you need to use an async output. You need to use an async output only if you need to hold onto data longer that the ProcessInput call or if you need to generate more rows going out than coming in. Generally, if one of those 2 conditions don't apply then you can use a sync output, which I would highly recommend because it is significantly simpler.

HTH,

Matt

And it executes quicker!

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/
>
>

Thursday, March 29, 2012

Creating an MS SQL data table programatically

creating an INSTANCE from an existing INSTANCE

Creating an installer for SQL-side components of an app

Creating an INSERT UPDATE Trigger that works on certain account

Creating an INSERT UPDATE Trigger that works on certain account

Creating an Indexed View

Creating an Index with a calulated member in MDX ?

Creating an Index with a calulated member in MDX ?

Creating an Index timing out

Creating an index on a BIT column

Creating an index on a BIT column

Creating an index on a BIT column

Creating an index on a BIT column

Creating an Image gallery??

Creating an Identity column to a SELECT statement

Creating an IDENTITY column in a view

Creating an Expression to Modify a Date Field

Creating an Expression to Modify a Date Field

Creating an expression - how to concatinate

creating an existing db schema baseline

Creating an Excel linked server

Creating an Excel linked server

Creating an Excel linked server

Creating an exact replica of the db

Creating an exact replica of the db

Creating an exact replica of the db

Creating an custom application simulating an SQL Server Profiler.

Creating an custom application simulating an SQL Server Profiler.

Creating an custom application simulating an SQL Server Profiler.

Creating an Audit trail on a table using a trigger

Creating an asynchrous output

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

Creating an Assembly that calls a COM object

Creating DB from scripts

Blog Archive

About Me