Creating DB from scripts: bit

Showing posts with label bit. Show all posts

Thursday, March 29, 2012

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 ADSI Linked Server

I am having quite a bit of difficulty setting up a linked server with ADSI.
I have used the code list in the various MSDN and BOL articles
(sp_addlinkedserver...) but any queries return the following error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare
returned 0x80040e14].
I know I am missing some key piece in my linked server set up here. Any
information would be appreciated.
Chris Whinihan
chrisw@.hma.regence.comHi Chris
There are quite a few posts for this error message!
http://tinyurl.com/b9fcf
Have specified created a mapping for the logins using sp_addlinkedsrvlogin?
John
"Chris Whinihan" wrote:

> I am having quite a bit of difficulty setting up a linked server with ADSI
.
> I have used the code list in the various MSDN and BOL articles
> (sp_addlinkedserver...) but any queries return the following error:
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepar
e
> returned 0x80040e14].
> I know I am missing some key piece in my linked server set up here. Any
> information would be appreciated.
> Chris Whinihan
> chrisw@.hma.regence.com

Thursday, March 22, 2012

Creating a System.Management assembly in order for my own Assembly to work?

I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.

I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.

in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.

namespace inlineCLRsql{

public static class test{
public static void PrintMessage(){
inLineLib.Queue q = new inLineLib.Queue();
int i = q.queueId ;
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());

}
}

}

to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.

so what I did is (and this is where I need to know if I just ruined sql server or not):

1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.

2- CREATE ASSEMBLY SystemManagement

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

3- CREATE ASSEMBLY inLineLibMaster

FROM 'D:\inLine\Server\inLineLib\bin\Debug\inLineLib.dll'

WITH PERMISSION_SET = unsafe

4- and finally

CREATE ASSEMBLY inLineLib

FROM 'D:\inLine\Server\CLRSQL\inlineCLRsql\bin\Debug\inlineCLRsql.dll'

WITH PERMISSION_SET = SAFE

Everything works after those steps (which took some trial and error). I can create a sproc like:

CREATE PROC sp_test AS

EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage

and it returns the Queue ID

Is there anything unadvisable about the steps above?

Thanks for your help

System.Management is not supported by CLR integration in SQL Server 2005. What this means is that it has not been tested for reliability and security to work perfectly under a hosted environment. Some (or all) parts of this assembly might work just fine and some (or none) might not work reliably under conditions like memory pressure, stack overflow, stress etc. You need to test your scenario well (for reliablilty and security) and ensure that it works for you. The list of supported .NET framework assemblies is following:

CustomMarshalers

Microsoft.VisualBasic

Microsoft.VisualC

mscorlib

System

System.Configuration

System.Data

System.Data.OracleClient

System.Data.SqlXml

System.Deployment

System.Security

System.Transactions

System.Web.Services

System.Xml

Wednesday, March 21, 2012

Creating a SSIS package

HI All,

can any body give steps to import data from one sqldb to another through ssis package, i was comfotable with dts but ssis is a lil bit confusing.....

thnx

regards

Start with the Wizard. Use it to build your first package, then save it and open it up in the designer, and you have your first sample package to learn from.

You actually want the Data Flow task for this, but why not let the Wizard show you for now.

You may find it useful to give these a try to help get you familiar with SSIS

Integration Services Tutorials
(http://msdn2.microsoft.com/en-us/library/0fc6e3a7-1c12-444a-b1ef-ead622f805d2.aspx)

Thursday, March 8, 2012

Creating a linked server to DB2 from SQl server 2000 64bit

Hi,
I am trying to create a linked server to DB2 using IBM
64 bit OLEDB provider, as Microsoft doesn't have a DB2
OLEDB driver for 64bit.
When I test the connection to DB2 using the driver, it
works, but when I try to query DB2 using a OPENQUERY it
gives me this message.
"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
Authentication Failed".
The userID I am using in the linked server properties, has
access to DB2 tables.
Is there anyway I could trace, to see where the connection
is failing.
I would really appreciate if someone could help me with
this.
Please let me know if I am posting on the wrong Newsgroup.
Thanks
GeethaYou can turn on a trace flag to try to get additional
information on the 7399 error. Execute
dbcc traceon(7300,3604)
and then run the query. You could also use profiler and
capture the OLEDB Errors event.
-Sue
On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
<gdabbara@.brownshoe.com> wrote:

>Hi,
> I am trying to create a linked server to DB2 using IBM
>64 bit OLEDB provider, as Microsoft doesn't have a DB2
>OLEDB driver for 64bit.
>When I test the connection to DB2 using the driver, it
>works, but when I try to query DB2 using a OPENQUERY it
>gives me this message.
>"Error 7399:OLEDB provider 'IBMDADB2' reported an error.
>Authentication Failed".
>The userID I am using in the linked server properties, has
>access to DB2 tables.
>Is there anyway I could trace, to see where the connection
>is failing.
>I would really appreciate if someone could help me with
>this.
>Please let me know if I am posting on the wrong Newsgroup.
>Thanks
>Geetha|||When I set the trace. I see this error message.
Server: Msg 7403, Level 16, State 1, Line 1
Could not locate registry entry for OLE DB
provider 'IBMDADB2.1'.
OLE DB error trace [Non-interface error: Provider not
registered.].
I can see 'IBMDADB2.1' in the registry. How can I register
the provider? Thanks for your help.
-Geetha.

>--Original Message--
>You can turn on a trace flag to try to get additional
>information on the 7399 error. Execute
>dbcc traceon(7300,3604)
>and then run the query. You could also use profiler and
>capture the OLEDB Errors event.
>-Sue
>On Tue, 6 Jul 2004 14:56:14 -0700, "Geetha Dabbara"
><gdabbara@.brownshoe.com> wrote:
>
IBM[vbcol=seagreen]
has[vbcol=seagreen]
connection[vbcol=seagreen]
Newsgroup.[vbcol=seagreen]
>.
>|||Try reinstalling the DB2 client. You could try just
unregistering and reregistering the dll for the provider
using regsvr32.exe but it might be safer to just reinstall
the client in case other files didn't get registered or
installed correctly.
-Sue
On Thu, 8 Jul 2004 08:47:37 -0700,
<anonymous@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>When I set the trace. I see this error message.
>Server: Msg 7403, Level 16, State 1, Line 1
>Could not locate registry entry for OLE DB
>provider 'IBMDADB2.1'.
>OLE DB error trace [Non-interface error: Provider not
>registered.].
>I can see 'IBMDADB2.1' in the registry. How can I register
>the provider? Thanks for your help.
>-Geetha.
>
>IBM
>has
>connection
>Newsgroup.|||hi,
even i am getting the error like
OLE DB error trace[Non-interface error:Provider not registered.].
Could not locate registry entry for OLE DB provider
'provider=Microsoft.Jet.OLEDB.4.0'.
Deferred prepare could not be completed.
As sue said try to reinstall the DB2 client. is it possible to
reinstall the oledb driver. how can i do it? where can i view the
registry files ? please help me its very urgent?
thans shybi
shybi
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message836063.html|||The provider is installed with MDAC. You can download MDAC
versions for reinstall and also download component checker
to check the MDAC version from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
Using Component Checker is a good way to check your MDAC
installation - easier than going through the registry.
Component Checker will check for mismatches.
-Sue
On Tue, 1 Aug 2006 01:06:46 -0500, shybi
<shybi.2buky9@.mail.mcse.ms> wrote:

>hi,
>even i am getting the error like
>OLE DB error trace[Non-interface error:Provider not registered.].
>Could not locate registry entry for OLE DB provider
>'provider=Microsoft.Jet.OLEDB.4.0'.
>Deferred prepare could not be completed.
>As sue said try to reinstall the DB2 client. is it possible to
>reinstall the oledb driver. how can i do it? where can i view the
>registry files ? please help me its very urgent?
>thans shybi

Friday, February 24, 2012

Creating A Boolean Computed Column

Consider the following columns:

EmpID, Status, TermDate

I want a computed column called OkToDisable as a bit value: True if the TermDate is any day before today.

The logic is if DATEDIFF(day, TermDate, GETDATE()) is greater than 0 then the column should be true, otherwise false.

I cant figure out how to do this... SQL keeps complaining that it can't validate the formula.

I was able to create a computed colum from 'DATEDIFF(day, TermDate, GETDATE())' that shows the number of days past the term date, but if I change it to: 'DATEDIFF(day, TermDate, GETDATE()) > 0' it says it can't validate the formula.

Can't you create boolean computed fields?

Thanks.

There is no boolean type in T-SQL. The [bit] type is an number type. You can do what you want with CASE:

CASE WHEN DATEDIFF(day, TermDate, GETDATE()) > 0 THEN 1 ELSE 0 END

Steve Kass
Drew University
http://www.stevekass.com
|||

Bit is a boolean type, no? Surprise )

I had tried the CASE but I couldn't get it to work properly... It helps when you use the proper syntax. (I had left off the END statement)

Then I had to use a cast, otherwise it was an integer.

Thanks for the reply.

Thursday, March 29, 2012

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 ADSI Linked Server

Thursday, March 22, 2012

Creating a System.Management assembly in order for my own Assembly to work?

Wednesday, March 21, 2012

Creating a SSIS package

Thursday, March 8, 2012

Creating a linked server to DB2 from SQl server 2000 64bit

Creating a linked server to DB2 from SQl server 2000 64bit

Friday, February 24, 2012

Creating A Boolean Computed Column

Creating DB from scripts

Blog Archive

About Me