Having trouble Creating Linked Server to Access database

I been having an issue trying to connect an Access database through SQL
Server as a Linked Server.
The crux of the problem is the Access Database (mdb file) has a Database
Password and this cannot be removed due to the database still being used by a
VB application. SO can someone Assist in a detailed outline that works, for
configuring a Linked Server connecting to a mdb with a Database Password set.
If the password is temperarily removed from the database I am able to create
a Linked Server using the "MS Jet 4.0 OLD DB Provider" However to complete
my objective I cannot remove the Database Password on our clients DB.
When the password is put back in, then once again I am unable to access the
DB. I have tried different configuration options and recieved different
errors. for instance on one try SQL Server was erroring and saying it needed
a mdw file that we do not have becaue that is for User Level security which
out MDB is not using.
I tried using a OpenROWSET command and providing a Provider Connection
String setting the Database Password in the String, However this Also errors
with a Could not find Installable ISAM.
Thanks
Tony
Hi Tony,
I understand that you would like to know how to open your
password-protected access database (.mdb) in SQL Server via linked server
or OPENROWSET/OPENDATASOURCE.
If I have misunderstood, please let me know.
I reproduced your issue at my side. It seems not possible to use linked
server or OPENROWSET to connect to a password-protected mdb file. However
per my test, you can use OPENDATASOURCE to query your MDB file with
specifying the parameter "Jet OLEDB:Database Password=******". For example:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="C:\Documents and Settings\charles\My Documents\db1.mdb";User
ID=Admin;Password=;Jet OLEDB:Database Password=myPassword!')...tblDT;
I am not sure why the parameter does not work for OPENROWSET and linked
server and I will try to consult the product team regarding the issue. If
there is any response, I will let you know. Anyway as a temporary
workaround, I recommend that you use OPENDATASOURCE to see if it helps.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Having trouble Creating Linked Server to Access database

I been having an issue trying to connect an Access database through SQL
Server as a Linked Server.
The crux of the problem is the Access Database (mdb file) has a Database
Password and this cannot be removed due to the database still being used by
a
VB application. SO can someone Assist in a detailed outline that works, fo
r
configuring a Linked Server connecting to a mdb with a Database Password set
.
If the password is temperarily removed from the database I am able to create
a Linked Server using the "MS Jet 4.0 OLD DB Provider" However to complete
my objective I cannot remove the Database Password on our clients DB.
When the password is put back in, then once again I am unable to access the
DB. I have tried different configuration options and recieved different
errors. for instance on one try SQL Server was erroring and saying it neede
d
a mdw file that we do not have becaue that is for User Level security which
out MDB is not using.
I tried using a OpenROWSET command and providing a Provider Connection
String setting the Database Password in the String, However this Also errors
with a Could not find Installable ISAM.
Thanks
TonyHi Tony,
I understand that you would like to know how to open your
password-protected access database (.mdb) in SQL Server via linked server
or OPENROWSET/OPENDATASOURCE.
If I have misunderstood, please let me know.
I reproduced your issue at my side. It seems not possible to use linked
server or OPENROWSET to connect to a password-protected mdb file. However
per my test, you can use OPENDATASOURCE to query your MDB file with
specifying the parameter "Jet OLEDB:Database Password=******". For example:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="C:\Documents and Settings\charles\My Documents\db1.mdb";User
ID=Admin;Password=;Jet OLEDB:Database Password=myPassword!')...tblDT;
I am not sure why the parameter does not work for OPENROWSET and linked
server and I will try to consult the product team regarding the issue. If
there is any response, I will let you know. Anyway as a temporary
workaround, I recommend that you use OPENDATASOURCE to see if it helps.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Tony,
Just notify that I have not got the product team's confirmation now. I will
try to follow up them. Since the process may need a long time, could you
please just leave me (changliw_at_microsoft_dot_com) an email response so
that I can timely update you when I got the confirmation?
Also could you please let me know whether or not the OPENDATASOURCE method
worked for you?
If you have any questions or concerns, please feel free to let me know. It
is my pleasure to be of your assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!
Hi,
When a SQL Server 2005 db runs on Windows 2003, there is a way to enforce
password policy.
I believe this is on per login basis. So you should be able to switch this
off while creating a login.
Look for the keyword CHECK_POLICY in BOL 2005.
Thank you.
Regards,
Karthik
"bcap" wrote:

> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!
Hi
"bcap" wrote:

> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>
I assume this is SQL Server 2005!!
You can create your login using the CREATE LOGIN statement and the option
CHECK_POLICY = OFF which will ignore password policy checking, but you should
really use policy checking on both systems and strong passwords on all
accounts.
John

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!Hi
"bcap" wrote:

> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>
I assume this is SQL Server 2005!!
You can create your login using the CREATE LOGIN statement and the option
CHECK_POLICY = OFF which will ignore password policy checking, but you shoul
d
really use policy checking on both systems and strong passwords on all
accounts.
John

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!Hi,
When a SQL Server 2005 db runs on Windows 2003, there is a way to enforce
password policy.
I believe this is on per login basis. So you should be able to switch this
off while creating a login.
Look for the keyword CHECK_POLICY in BOL 2005.
Thank you.
Regards,
Karthik
"bcap" wrote:
> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>sql

Having trouble creating a login account.

Hello,
I am trying to create a TEST environment by replicating everything on
the PRODUCTION environment.
When creating a login under Sercurity, I am using the same user name
and passowrd. However, I am getting the following error message:
Password validation failed. The password does not meet Windows policy
requirements becuase it is not complex enough. ( Microsoft SQL Server,
Error: 15118 )
Is there some kind of setting I need to change to have lower
requirements?
Thank you!Hi
"bcap" wrote:
> Hello,
> I am trying to create a TEST environment by replicating everything on
> the PRODUCTION environment.
> When creating a login under Sercurity, I am using the same user name
> and passowrd. However, I am getting the following error message:
> Password validation failed. The password does not meet Windows policy
> requirements becuase it is not complex enough. ( Microsoft SQL Server,
> Error: 15118 )
> Is there some kind of setting I need to change to have lower
> requirements?
> Thank you!
>
I assume this is SQL Server 2005!!
You can create your login using the CREATE LOGIN statement and the option
CHECK_POLICY = OFF which will ignore password policy checking, but you should
really use policy checking on both systems and strong passwords on all
accounts.
John

Having some trouble connecting to a database....

I am doing one of the Microsoft virtual labs "Creating ASP.NET Web Applications with C# - Part 2" using Visual Web Developer Express. I am trying to fill a gridview with database information, but it gives me the error

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)".

This is on the local machine that I am executing the code.

public void BindGrid(string sortfield) {//Create DataAdapter to fetch data from Prodcuts table SqlDataAdapter myCommand =new SqlDataAdapter("select * from Products", myConnection);//Create dataset and fill it with Product data DataSet ds =new DataSet(); myCommand.Fill(ds,"Products");//Bind Product data to Datagrid DataView Source = ds.Tables["Products"].DefaultView; Source.Sort = sortfield; dgProducts.DataSource = Source; dgProducts.DataBind(); }

It stops on the line "myCommand.Fill(ds, "Products"); and points out the error mentioned earlier. I am not sure what to do...any help would be greatly appreciated, thanks in advance, and I apologize as well if I have not pointed out enough information.

What does your connection string look like?|||

check this link for help:

http://support.microsoft.com/kb/914277/en-us

Thanks

|||

First of all, thank you both greatly for responding.

Smalltalk, here is the connection string:

myConnection = new SqlConnection("server=localhost;database=northwind;Trusted_Connection=yes");

Jpazgier, my connections are set at local and remote connections, plus the server is on the same machine, I have no idea why it mentions that error for me.

Thanks again, guys.

|||

Hi GWShane,

Please make sure that the server is the default instance on your machine. If you're using SQL Express, the default name for this instance might be SQLExpress. In this case, you might need to add the instance name to the DataSource like:

myConnection = new SqlConnection("server=localhost\\SQLExpress;database=northwind;Trusted_Connection=yes");

Having problems creating an SQL statement

I am having trouble getting the SQL statement to return stats from a survey the way I want them. The table is set up as:

ID Q1 Q2 Q3 Q4

Responses for each question (Columns Q1 – Q4) will be a numerical value between 1-5. I want to count how many 1s, 2s, 3s, etc. I have tried different joins, self joins, unions and sub selections but cannot get the correct output.

I would like to get the output for each question as a single record, and if possible have a final column with an average for the question. But I can do that in the data binding if needed.

Qs Ones Twos Threes Fours Fives

Q1 #of 1s #of 2s #of 3s #of 4s #of 5s

Q2 #of 1s #of 2s #of 3s #of 4s #of 5s

Q3 #of 1s #of 2s #of 3s #of 4s #of 5s

Any tips or SQL sample statements would be greatly appreciated.

It looks like 2 pivots will be needed to to transpose the data from your table layout to your desired output layout. If you're using Sql 2005, there is a Pivot feature but here i'll show you how you could do this using syntax that will work for Sql2000 or Sql2005.

I'll break each step down into it's own View. Each view will build off of the previous view(s).

The 1st View is named: VIEW_SurveyRotation1
In this View, we turn all the column headings (Q1, Q2 etc..) into data values and move all the answers into a single Answer column. This is actually the opposite of what we normally consider a pivot, but i still tend to think of it as a [reverse] pivot.

SELECT'Q1'AS Qs, Q1AS answerFROM dbo.SurveyUNIONALLSELECT'Q2'AS Qs, Q2AS answerFROM dbo.SurveyUNIONALLSELECT'Q3'AS Qs, Q3AS answerFROM dbo.SurveyUNIONALLSELECT'Q4'AS Qs, Q4AS answerFROM dbo.Survey

The 2nd View is named: VIEW_SurveyRotation2
In this View, we pivot the answer values back into column headings by querying against the results of our first View. As mentioned, there's more than one way to create a Pivot in Sql.

SELECT Qs,CASEWHEN answer = 1THEN 1ELSE 0END AS One,CASEWHEN answer = 2THEN 1ELSE 0END AS Two,CASEWHEN answer = 3THEN 1ELSE 0END AS Three,CASEWHEN answer = 4THEN 1ELSE 0END AS Four,CASEWHEN answer = 5THEN 1ELSE 0END AS FiveFROM dbo.VIEW_SurveyRotation1

The 3rd View is named: VIEW_SurveyAverages
Here we can create a simple set of Averages by querying against our first View

SELECT Qs,AVG(CAST(answerAS decimal))AS [Avg]FROM dbo.VIEW_SurveyRotation1GROUP BY Qs

The 4th and final View is named: VIEW_SurveyResult
In this View, we summarize the answer counts for each column and also join in the Averages

SELECT dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg],SUM(dbo.VIEW_SurveyRotation2.One)AS Ones,SUM(dbo.VIEW_SurveyRotation2.Two)AS Twos,SUM(dbo.VIEW_SurveyRotation2.Three)AS Threes,SUM(dbo.VIEW_SurveyRotation2.Four)AS FoursFROM dbo.VIEW_SurveyRotation2INNERJOIN dbo.VIEW_SurveyAveragesON dbo.VIEW_SurveyRotation2.Qs = dbo.VIEW_SurveyAverages.QsGROUP BY dbo.VIEW_SurveyRotation2.Qs, dbo.VIEW_SurveyAverages.[Avg]

I tend to work with complex sql queries by breaking it down into steps like this. It helps me to achieve the desired result. Then, once you've got it working, you can review it and see if you can eliminate any of the steps by consolidating them into fewer queries.

|||

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

--Table and test data

CREATETABLE [dbo].[pivotQuestions](

[ID] [int]NotNULL,

[Q1] [int]NULL,

[Q2] [int]NULL,

[Q3] [int]NULL,

[Q4] [int]NULL,

[Q5] [int]NULL,

[Q6] [int]NULL

)

GO

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(1, 3, 4, 5, 5, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(2, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(3, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(4, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(5, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(6, 3, 4, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(7, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(8, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(9, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(10, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(11, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(12, 2, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(13, 3, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(14, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(15, 5, 5, 5, 5, 5, 5)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(16, 5, 1, 1, 1, 1, 1)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(17, 3, 2, 2, 2, 2, 2)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(18, 5, 3, 3, 3, 3, 3)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(19, 4, 4, 4, 4, 4, 4)

INSERT [dbo].[pivotQuestions]([ID], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6])VALUES(20, 5, 5, 5, 5, 5, 5)

|||

Thanks for the tips, I will give them a try.

|||

Do not have SQL 2005 so I did what you suggested mbanavige. I was able to combine it all into a single query and not use Views, just couldnt add in the averages that. But that was easy enough to do on the databind. Working like a charm. thanks.

|||

limno:

Here is the code sample for UNPIVOT and PIVOT solution with SQL Server 2005:

SELECT Questionas Qs, [1]as Ones, [2]as Twos, [3]as Threes, [4]as Fours, [5]as FivesFROM

(SELECT Question, [Value]FROM pivotQuestions

UNPIVOT([Value]FOR [Question]in([Q1], [Q2], [Q3], [Q4], [Q5], [Q6])

)as unpvt) t

PIVOT(COUNT([Value])FOR [Value]IN([1], [2], [3], [4], [5])

)as pvt

Hi,

I'm having a really similar problem, also with surveys.

The only 2 differences are that (a) I don't want to summarise my results at all (b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.

So far I have come up with...

TABLE

=====

SurveyID RespondantID QuestionID Answer

PIVOT QUERY

===========

SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (
COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD

But it doesn't work and I can't figure out why.

What am I doing wrong?

Having problems creating a function or procedure

When I try to create functions or procedures sqlplus just hangs.
I have tried creating several function and procedures written in the
developing pl/sql manual. The 1st procedure I created worked. All the others will not compile. Any ideas on the problem?
ReneeHello,

could it be, that the procedures is still in creation status by another process - or it is running ?

Just an idea

Regards
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Having Indexed tbl will speed up DTS job ?

Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/d...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage spac
e
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieva
l
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:

> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieva
l
> of data but in the case where my DTS job needs to populates the data acros
s,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thkssql

Having Indexed tbl will speed up DTS job ?

Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thks
Hi
From:
http://msdn.microsoft.com/library/de...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:

> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks

Having Indexed tbl will speed up DTS job ?

Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_30s5.asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks

Having difficulty creating a stored procedure

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am having
difficulty though. Whenever I press the execute button, here is the error message I get:

Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21
Incorrect syntax near 'MarketName'.

Here is the stored procedure. Note that the very first column in named "MarketId" but I did not
include it in the stored procedure since it should be auto generated.

USE [StockWatch]
GO
/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
@.MarketDescription nvarchar(100)
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
MarketDescription
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateUser
@.MarketDescription
)

You need to use comma's to separate the column names.

USE[StockWatch]

GO

/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[MarketCreate]

(

@.MarketCodenvarchar(20),

@.MarketNamenvarchar(100),

@.LastUpdateDatenvarchar(2),

@.MarketDescriptionnvarchar(100)

)

AS

INSERTINTO Market

(

MarketCode,

MarketName,

LastUpdateDate,

MarketDescription

)

VALUES

(

@.MarketCode,

@.MarketName,

@.LastUpdateUser,

@.MarketDescription

)

|||

Thanks !

Have:DateTime, Need: string, use in EXEC sp_executesql parm list

Hello,
Below is the extract of code I am stuck on. My goal is to take the DateTime
value in the variable @.End and convert it for creating the sp_executesql
params.
The problem:
======================
declare @.End datetime
declare @.E varchar
select @.End=comm_end from CommPeriod
where period_id = (select min(period_id) from CommPeriod
where period_closed = 0 and CountryCode='US')
and CountryCode = 'US'
print @.End
SET @.E = CONVERT(VARCHAR, @.End , 112)
print @.E
The Output:
==================
Dec 31 2004 11:59PM
2
I humbly ask for a solution, clues, tips and...
Thanks,
Bob
> declare @.E varchar
No length specified, implies varchar(1). Make it varchar(8).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob B via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:dbba533561de44c18d098a9ec4fb6472@.droptable.co m...
> Hello,
> Below is the extract of code I am stuck on. My goal is to take the DateTime
> value in the variable @.End and convert it for creating the sp_executesql
> params.
> The problem:
> ======================
> declare @.End datetime
> declare @.E varchar
> select @.End=comm_end from CommPeriod
> where period_id = (select min(period_id) from CommPeriod
> where period_closed = 0 and CountryCode='US')
> and CountryCode = 'US'
> print @.End
> SET @.E = CONVERT(VARCHAR, @.End , 112)
> print @.E
> The Output:
> ==================
> Dec 31 2004 11:59PM
> 2
>
> I humbly ask for a solution, clues, tips and...
> Thanks,
> Bob
sql

Have:DateTime, Need: string, use in EXEC sp_executesql parm list

Hello,
Below is the extract of code I am stuck on. My goal is to take the DateTime
value in the variable @.End and convert it for creating the sp_executesql
params.
The problem:
======================
declare @.End datetime
declare @.E varchar
select @.End=comm_end from CommPeriod
where period_id = (select min(period_id) from CommPeriod
where period_closed = 0 and CountryCode='US')
and CountryCode = 'US'
print @.End
SET @.E = CONVERT(VARCHAR, @.End , 112)
print @.E
The Output:
==================
Dec 31 2004 11:59PM
2
I humbly ask for a solution, clues, tips and...
Thanks,
Bob> declare @.E varchar
No length specified, implies varchar(1). Make it varchar(8).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob B via droptable.com" <forum@.nospam.droptable.com> wrote in message
news:dbba533561de44c18d098a9ec4fb6472@.SQ
droptable.com...
> Hello,
> Below is the extract of code I am stuck on. My goal is to take the DateTim
e
> value in the variable @.End and convert it for creating the sp_executesql
> params.
> The problem:
> ======================
> declare @.End datetime
> declare @.E varchar
> select @.End=comm_end from CommPeriod
> where period_id = (select min(period_id) from CommPeriod
> where period_closed = 0 and CountryCode='US')
> and CountryCode = 'US'
> print @.End
> SET @.E = CONVERT(VARCHAR, @.End , 112)
> print @.E
> The Output:
> ==================
> Dec 31 2004 11:59PM
> 2
>
> I humbly ask for a solution, clues, tips and...
> Thanks,
> Bob

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
Michael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com
|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:

> Michael Tissington wrote:
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
Michael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com
|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:

> Michael Tissington wrote:
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comMichael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:
> Michael Tissington wrote:
> > I'm looking at creating an association between a database and the
> > hardware that it is running on - for example by creating a license
> > key that includes a hash of the mac address.
> >
> > Obviously clients will be able to query the database directly. What
> > I'm trying to do is to tie my client application to a specific
> > database/hardware combination.
> >
> > What pieces of hardware information can SQL query ?
> >
> > How would I get the mac address that SQL is listening on?
> >
> > Any other ideas that I can use to tie the database to the hardware.
> >
> > Thanks
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Hardware information

I'm looking at creating an association between a database and the hardware
that it is running on - for example by creating a license key that includes
a hash of the mac address.
Obviously clients will be able to query the database directly. What I'm
trying to do is to tie my client application to a specific database/hardware
combination.
What pieces of hardware information can SQL query ?
How would I get the mac address that SQL is listening on?
Any other ideas that I can use to tie the database to the hardware.
Thanks
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comMichael Tissington wrote:
> I'm looking at creating an association between a database and the
> hardware that it is running on - for example by creating a license
> key that includes a hash of the mac address.
> Obviously clients will be able to query the database directly. What
> I'm trying to do is to tie my client application to a specific
> database/hardware combination.
> What pieces of hardware information can SQL query ?
> How would I get the mac address that SQL is listening on?
> Any other ideas that I can use to tie the database to the hardware.
> Thanks
There could be more than one MAC address because many servers have two
network cards. You can query the MAC address from the IPCONFIG
command-line tool. From SQL Server, you can execute:
exec master..xp_cmdshell N'IPCONFIG /all'
You'll have to parse the results, however.
You might be better off sending a key to the customer based on the
server-name or MAC address and have that value stored in a table. The
application queries the value and decrypts it to make sure it's valid.
If it includes the MAC address, you can compare it against the string
returned from IPCONFIG.
In the end, however, the best way to control your software is to hire a
good licensing attorney and create a solid license agreement. Most
legitimate companies are very proactive abount licensing. Those that
aren't are going to steal it anyway.
David Gugick
Imceda Software
www.imceda.com|||Hi
And if it runs on a cluster, what happens then? There are a minimum of 4
cards per cluster, with SQL Server only seeing 2 at any one time.
Any form of hardware enforcement is actually counter-productive to disaster
recovery.
Regards
Mike
"David Gugick" wrote:

> Michael Tissington wrote:
> There could be more than one MAC address because many servers have two
> network cards. You can query the MAC address from the IPCONFIG
> command-line tool. From SQL Server, you can execute:
> exec master..xp_cmdshell N'IPCONFIG /all'
> You'll have to parse the results, however.
> You might be better off sending a key to the customer based on the
> server-name or MAC address and have that value stored in a table. The
> application queries the value and decrypts it to make sure it's valid.
> If it includes the MAC address, you can compare it against the string
> returned from IPCONFIG.
> In the end, however, the best way to control your software is to hire a
> good licensing attorney and create a solid license agreement. Most
> legitimate companies are very proactive abount licensing. Those that
> aren't are going to steal it anyway.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>