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.
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
Thursday, March 29, 2012
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
>
>
>
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.-
>
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!!!
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"
--=_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
--
Venkat
sql server admirer
"Jaime Rios" wrote:
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>|||Hi folks,
I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 2000
to another proprietary database server. The other database server will
occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated b
y
the proprietary database account. How do I capture the user or group name in
MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger.
What is the equivalent in MS SQL? Thanks in advance!!!|||just use user_name()
--
Venkat
sql server admirer
"Jaime Rios" wrote:
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||Try the sytem function: system_user.
For example, SELECT system_user will provide the Domain\Loginname of the cur
rently logged in user account.
Look in Books-on-Line for more information on system_user.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Jaime Rios" <JaimeRios@.discussions.microsoft.com> wrote in message news:EB859EAF-3AC8-4CDC-
8ECF-89A3038CC99A@.microsoft.com...
> Hi folks,
> I have to create an INSERT UPDATE Trigger that pushes data from MS SQL 200
0
> to another proprietary database server. The other database server will
> occassionally push data back into MS SQL via JDBC. I don't want the MS SQL
> trigger to execute its SQL code if the INSERT UPDATE Trigger was initiated
by
> the proprietary database account. How do I capture the user or group name
in
> MS SQL? I know in PostgreSQL, you can use the "user" variable in a trigger
.
> What is the equivalent in MS SQL? Thanks in advance!!!|||A problem with using user_name() is that it will return 'dbo' for any user
that is in the dbOwner or SA roles. That may not be as helpful as
system_user. (no parens after system_user).
System_User provides the complete loggin DOMAIN\username. It is the best
option for any form of auditing and perhaps for the purpose you have in
mind.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Venkat" <Venkat@.discussions.microsoft.com> wrote in message
news:3F000F3F-7605-4D1E-BCD1-B9452F532943@.microsoft.com...[vbcol=seagreen]
> just use user_name()
> --
> Venkat
> sql server admirer
>
> "Jaime Rios" wrote:
>sql
Creating an Indexed View
I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!
Here is the Error I am given
"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3
Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."
Here is my code..
CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING
AS
SELECT TEW_D_DT.DT_KEY AS DATE_KEY,
VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,
TEW_D_LOC.LOC_DESC AS LOC_DESC ,
TEW_D_LOC.RGN_DESC AS REGION_DESC,
TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,
ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,
COUNT_BIG(*) AS COUNT
FROM
JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO
INNER JOIN
JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT
ON
VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY
AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER
AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER
AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER
INNER JOIN
JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL
ON
VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY
AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER
AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER
AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER
AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER
AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER
AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'
AND VEW_F_INVC_DTL.GIFT_CARD = 'N'
INNER JOIN
JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER
ON
VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER
AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1
INNER JOIN
JJWHSE.TEW_D_DT TEW_D_DT
ON
VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY
INNER JOIN
JJWHSE.TEW_D_LOC TEW_D_LOC
ON
VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY
WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'
GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,
TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC
GO
CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )
The first error message is as direct as it can be. You cannotcreate a view WITH SCHEMABINDING when its definition mentions
another view that was not itself created WITH SCHEMABINDING.
In your case, you can't create 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'
with schemabinding because it refers to another view,
'JJWHSE.VEW_F_INVC_SHIP_TO', which was not created with
schemabinding.
There does seem to be a bit of confusion. The code you pasted
here tries to do two things:
1. Create a view named 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'
2. Create an index on 'VEW_F_MZT_ORDER_HEADER',
which is a *different* view.
The second error you got (Cannot create index...)
has nothing at all to do with the first error or with
the CREATE VIEW code that generates the first error. It's
also as clear as it can be. The 'VEW_F_MZT_ORDER_HEADER'
exists, but it was not created with schemabinding, which
is a requirement for creating an index on a view.
If you want to index a view, that view and all the views
(and functions) on which it depends must be created with
schemabinding and meet the requirements for that option.
Steve Kass
Drew University
http://www.stevekass.com
topcoder_cc@.discussions.microsoft.com wrote:
> I am trying to create an indexed view, on a date from a date dimension
> table...I am new to SQL, and I am at a loss of ideas on this one. Any
> help would be greatly appreciated!
>
> Here is the Error I am given
>
> "Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line
> 3
>
> Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'.
> 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.
>
> Msg 1939, Level 16, State 1, Line 1
>
> Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is
> not schema bound."
>
> Here is my code..
>
> CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING
>
> AS
>
> SELECT TEW_D_DT.DT_KEY AS DATE_KEY,
>
> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,
>
> TEW_D_LOC.LOC_DESC AS LOC_DESC ,
>
> TEW_D_LOC.RGN_DESC AS REGION_DESC,
>
> TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,
>
> ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,
>
> COUNT_BIG(*) AS COUNT
>
> FROM
>
> JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO
>
> INNER JOIN
>
> JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT
>
> ON
>
> VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY
>
> AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER
>
> AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER =
> VEW_F_INVC_PAY_EXT.INVOICE_NUMBER
>
> AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER =
> VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER
>
> INNER JOIN
>
> JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL
>
> ON
>
> VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY
>
> AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER
>
> AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER
>
> AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER
>
> AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER
>
> AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER
>
> AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'
>
> AND VEW_F_INVC_DTL.GIFT_CARD = 'N'
>
> INNER JOIN
>
> JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER
>
> ON
>
> VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER
>
> AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1
>
> INNER JOIN
>
> JJWHSE.TEW_D_DT TEW_D_DT
>
> ON
>
> VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY
>
> INNER JOIN
>
> JJWHSE.TEW_D_LOC TEW_D_LOC
>
> ON
>
> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY
>
> WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'
>
> GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY ,
> TEW_D_LOC.LOC_DESC ,
>
> TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC
>
> GO
>
> CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON
> JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )
>
>
Creating an Index with a calulated member in MDX ?
Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?
Ex:
Jan07: Actual = 32000 -> index = 114
Feb07: Actual = 34000 -> index = 121
Mar07: Actual = 28000 -> index = 100
Apr07: Actual = 20000 -> index = 71
It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:
Code Snippet
(
([Date].[Month].CurrentMember, [Measures].[Actual])
/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])
) * 100
This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.
Creating an Index with a calulated member in MDX ?
Anyone got a clue on how to create a meassure that shows the Actual meassure as an Index, where current month is index 100 ?
Ex:
Jan07: Actual = 32000 -> index = 114
Feb07: Actual = 34000 -> index = 121
Mar07: Actual = 28000 -> index = 100
Apr07: Actual = 20000 -> index = 71
It must be something with creating af defaultmember in the timedimension, that is dynamic and points to getdate(). Then make a calculation that uses defaultmembers value to calculate the indexnumber|||If you wanted to use the current system date on the server as your definition of the "current date" then you could do something roughly like the following:
Code Snippet
(
([Date].[Month].CurrentMember, [Measures].[Actual])
/ (StrToMember("[Date].[Month].[" + FORMAT(NOW(),"MMMyy") + "]"),[Measures].[Actual])
) * 100
This code is pretty rough, there is no logic in there for handling the All member and it would only work at month granularity, but hopefully it is enough to get you started. You could also set the "current date" as the default member, but it is not strictly necessary to do the calculation.
Creating an Index timing out
I am creating an index on a table wit 35 million records but I get the error
'TT_ObjPerformance' table
- Unable to create index 'IX_TT_ObjPerformance_CACode'.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
How can I get the index created?
Thanks
SQL Server newbie
From where are you creating the index? via query analyzer/job/some front end( hopefully not)?
Creating an 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
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
>
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
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
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
(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
>
>
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
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
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,
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
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
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.
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
Subscribe to:
Posts (Atom)