Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.sqlmonster.com
Checkout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com
|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.c om...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
Message posted via http://www.droptable.comCheckout sp_spaceused in BOL.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
Keith
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQ
droptable.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.droptable.com

Data File Remaining Space

I am running SQL 2000. I have a data file set up with restricted growth. Is
there a stored procedure or script available that I can schedule or run
when I want manually that will give me the space remaining in the data file?
--
Message posted via http://www.sqlmonster.comCheckout sp_spaceused in BOL.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.com|||sp_helpdb YourDBNameHere
gives some information regarding database and file size.
--
Keith
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be0b5b63225d4993937c2075fbb4510f@.SQLMonster.com...
> I am running SQL 2000. I have a data file set up with restricted growth.
Is
> there a stored procedure or script available that I can schedule or run
> when I want manually that will give me the space remaining in the data
file?
> --
> Message posted via http://www.sqlmonster.comsql

Data file inlezen via Stored Procedure

Hello
I want to import a datafile into an sql database with stored procedure. But i have now idee how i must do that.
Also the procedure must be run automatily each morning at 10 a clock.
Greetz TomCreate stored procedure for importing data and setup job for running this procedure.

Data extension question?

Hi,
when you use sql server data extension and set the query to a stored
proceduere, Reporting services automatically creates report parameters based
on the stored proc parameters. I'm creating a custom data processing
extension and i would like to create parameters based on a config file. How
do i do this?
thanks
shankarIn your custom data processing extension, you should implement
IDbCommandAnalysis. In GetParameters, you can access your config file.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Hi,
> when you use sql server data extension and set the query to a stored
> proceduere, Reporting services automatically creates report parameters
based
> on the stored proc parameters. I'm creating a custom data processing
> extension and i would like to create parameters based on a config file.
How
> do i do this?
> thanks
> shankar
>|||Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
interface and i got all the parameters from my config file. In the config
file , i also stored designer default values for all the parameter. I would
like the Reportdesigner to use the designer value when it executes the query
command. You could view the designer value as dummy value. For Example, if
you are using sql server stored proc, you would pass all the required
parameter as a part of command text.
When i implemented, IDbCommandAnalysis interface, i passed this
parameter,designer value pair. I think reporting services uses this
interface only to get the list of parameters and it simply ignores default
value.Is there any way to do this.
thanks
shankar
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> In your custom data processing extension, you should implement
> IDbCommandAnalysis. In GetParameters, you can access your config file.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "shankar" <sramasubramanian@.ozcap.com> wrote in message
> news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > when you use sql server data extension and set the query to a stored
> > proceduere, Reporting services automatically creates report parameters
> based
> > on the stored proc parameters. I'm creating a custom data processing
> > extension and i would like to create parameters based on a config file.
> How
> > do i do this?
> >
> > thanks
> >
> > shankar
> >
> >
>|||That is correct. The design tool uses the interface only to obtain the
parameter names, not to retrieve default values.
Using it for default values as well is a good idea. I'll forward it on to
the designer team. Thanks.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:OjXL2YLeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
> interface and i got all the parameters from my config file. In the config
> file , i also stored designer default values for all the parameter. I
would
> like the Reportdesigner to use the designer value when it executes the
query
> command. You could view the designer value as dummy value. For Example, if
> you are using sql server stored proc, you would pass all the required
> parameter as a part of command text.
> When i implemented, IDbCommandAnalysis interface, i passed this
> parameter,designer value pair. I think reporting services uses this
> interface only to get the list of parameters and it simply ignores default
> value.Is there any way to do this.
> thanks
> shankar
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > In your custom data processing extension, you should implement
> > IDbCommandAnalysis. In GetParameters, you can access your config file.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "shankar" <sramasubramanian@.ozcap.com> wrote in message
> > news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > when you use sql server data extension and set the query to a
stored
> > > proceduere, Reporting services automatically creates report parameters
> > based
> > > on the stored proc parameters. I'm creating a custom data processing
> > > extension and i would like to create parameters based on a config
file.
> > How
> > > do i do this?
> > >
> > > thanks
> > >
> > > shankar
> > >
> > >
> >
> >
>

Data export to individual files

Hello,
I am looking to T-SQL extract records from a table and have each record
stored in individual .eml files. A query based DTS doesn't let me
specify what extension my files will have AND I'm unsure how to have
each record written to a separate file? Any ideas on how I can pull
this off?
Much appreciated,
Prpryan75 wrote:
> Hello,
> I am looking to T-SQL extract records from a table and have each record
> stored in individual .eml files. A query based DTS doesn't let me
> specify what extension my files will have AND I'm unsure how to have
> each record written to a separate file? Any ideas on how I can pull
> this off?
> Much appreciated,
> Pr
>
A few possibilities:
1. Use OSQL to run the query and pipe the output to the desired file
2. Use xp_cmdshell to issue DOS "ECHO" commands to write the desired file
3. Use a combination of xp_cmdshell and OPENROWSET to create and then
write the desired file
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Data Encryption: Searching Encrypted Data

Hey there,
I am beginning a new project that will require data to be encrypted and
that data is obviously going to be stored within a Sql 2000 database.
The majority of the data is basically OCR text and will be required to
be searched via a web application via calling a Stored Procedure, with
the rest being Customer Order information. From what I can gather, I
will not be able to store my data encrypted if I want to search it.
Therefore, does anyone have a suggestion on possibly how to go about
this differently, if there even is an option. Anyone have a good
suggested read on how to stored, say credit card information encrypted,
as well as how to search that encrypted data?
Thanks in advance for any suggestions you may have.
XVYou can start by reviewing the encryption section in this
security FAQ. It lists some third party providers that offer
encryption solutions. Some of the sites have white papers on
encryption as well consulting services:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On 26 Jan 2005 10:31:04 -0800, xanthviper@.xanthviper.com
wrote:

>Hey there,
>I am beginning a new project that will require data to be encrypted and
>that data is obviously going to be stored within a Sql 2000 database.
>The majority of the data is basically OCR text and will be required to
>be searched via a web application via calling a Stored Procedure, with
>the rest being Customer Order information. From what I can gather, I
>will not be able to store my data encrypted if I want to search it.
>Therefore, does anyone have a suggestion on possibly how to go about
>this differently, if there even is an option. Anyone have a good
>suggested read on how to stored, say credit card information encrypted,
>as well as how to search that encrypted data?
>Thanks in advance for any suggestions you may have.
>XV|||Thank you Sue. I appreciate your input.

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is written
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is know
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive...encryption.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is writt
en
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is kn
ow
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

Data Encryption

Is there anyone out there practicing data encryption in
their database ? Column data or table data (Not Stored
P.). If so, how can I apply it to my database too ?
Management wants even the data in the tables encrypted to
the DBA.
If there are any third party utilities out there, can
someone direct me to them '.
T.I.AYou can find some third party tools listed in the encryption
section of this FAQ:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
-Sue
On Fri, 11 Feb 2005 08:34:10 -0800, "Chris"
<anonymous@.discussions.microsoft.com> wrote:
>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them '.
>T.I.A|||In message <210201c51057$83d37640$a601280a@.phx.gbl>, Chris
<anonymous@.discussions.microsoft.com> writes
>Is there anyone out there practicing data encryption in
>their database ? Column data or table data (Not Stored
>P.). If so, how can I apply it to my database too ?
>Management wants even the data in the tables encrypted to
>the DBA.
>If there are any third party utilities out there, can
>someone direct me to them '.
>T.I.A
Personally, I have embedded AES within one of our Utility DLL's and when
I need to protect columns (ie: passwords or pin numbers - anything
really) it is called prior to updating SQL Server (ie: via INSERT or
UPDATE).
Secondly, it is very rare that this data needs decrypting (or else
what's the point) so only ever compare encrypted values in SELECT's.
There are some third party utilities that can help with this issue (try
Google) but from experience I would tell Management that you could
provide encryption to individual columns but its not practical to
implement this for all data (after all there is a big overhead,
specially on large databases).
The best protection for the database from users is for the DBA to set it
up right in the first place by using SQL Logins to restrict access to
only the tables, views and stored procedures they require. This then
means of course that the applications accessing the data use the right
credentials for the each operation, feature and user (ie: by design).
As for protecting the data from DBA's I would suggest this is done by
employing the right people in the first place; adding various clauses to
employment contracts; signing NDA's; and of course threatening with very
serious law suits and of course don't forget the base ball bats <g>.
The ultimate protection for the database from everyone (specially those
in Management) would be to turn the Computer OFF; Lock it in a Nuclear
bunker; destroy the key and then burn the maps to its location. <g>
Alternatively, don't store the data in the first place. <bg>
I think Yukon might have some new features in this area as well, however
someone else will need to answer that one. Besides, Yukon is not hear
yet !
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

data encryption

i have to encrypt my data in sql2005 database using assymetric key encryption which i have done properly.But i have a doubt that while writing stored procedure i have to provide key information in it , that will be visible to everyone then whats the use of taht encryption. Can anyone tell me how can i write stored procedure without providing key information in it.

Divya

Well, you can have SQL Server manage the keys for you, then you never have to expose them to client applications.

But I assume from your question that you are using passwords (passphrases) to protect the asymmetric keys? If so, is there a reason you are doing that? You could instead protect the key using the database master key and then not have to worry about providing the key in your stored procedure.

Don

|||

A very good tutorial for similar situation is

http://aspnet.4guysfromrolla.com/articles/022807-1.aspx

|||

It all depends on who and what you are trying to protect from.

By the web server passing the key, the loss of just the physical database server itself would be meaningless. You couldn't decrypt the data on it since they does not exist on that machine. It also can simplify a number of other issues as well since the key is stored with the application that is accessing the data. And it does make for a stronger link between application and data. if you were store the key on the database (and not pass it), then you most likely wouldn't gain very much. The stored procedure could look it up, but then you are subject to the problem that if the physical machine is compomised (Stolen), the data can be decrypted. It also causes a problem because then you have to make a second security layer so that the stored procedure is only then able to be executed by connections it trusts. And if you write that layer, what is the point of a stored procedure that encrypts data since you are now dependant on the security of the connection? If you can trust the connection, the just put permissions on your tables and you've got the same level of security. If somehow the stored procedure is compromised, again, the encryption is useless. It's encrypted, but anyone can decrypt it just by asking, so what is the point in that?

Now, that's not to say that the key being stored/sent from the application doesn't have it's own set of problems, but there is a lot more to encryption and security that must be thought through. And your security scheme must reflect what types of attacks you want to protect yourself from. All-in-all, having the application send the key is more secure in most cases. Just make sure your connection is encrypted as well.

|||

hello don,

i tried d solution suggested by u by creating the database master key, but i am not able to take backup of this key in ntfs directory as it involves editing registry information. Can u suggest me any feasible solution for it.

Thanks

Divya

|||Hello don i tried this solution from this linkhttp://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp and trying to encrypt and decrypt using database master key , creating certificate without password but here i am not able to decrypt the column with this method, can u plz help me on this.|||

Why don't you post the code you're using at this point? That will make it easier to see what you're trying to do.

And is a certificate the best type of encryption to use? Why did you select that? (I don't mean to imply there is anything wrong with that choice. I just want to understand what you are trying to do.)

Don

|||

thanx don, earlier i was trying the encryption using password. But when i tried using master key it worked fine.

thanx

data driven subscription not available

Hi,
I am trying to create a data driven subscription. I have stored the
credentials and custom data source in the report. My account has site
administrator and content manager permissions, yet the "New Data Driven
Subscription" button is not available. I have multiple reports like this.
Any ideas?
MarcusAre you on Enterprise Edition of Reporting Services, it's an EE only
feature.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> Hi,
> I am trying to create a data driven subscription. I have stored the
> credentials and custom data source in the report. My account has site
> administrator and content manager permissions, yet the "New Data Driven
> Subscription" button is not available. I have multiple reports like this.
> Any ideas?
> Marcus|||That explains it... Thanks!!
"Jasper Smith" wrote:
> Are you on Enterprise Edition of Reporting Services, it's an EE only
> feature.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > Hi,
> >
> > I am trying to create a data driven subscription. I have stored the
> > credentials and custom data source in the report. My account has site
> > administrator and content manager permissions, yet the "New Data Driven
> > Subscription" button is not available. I have multiple reports like this.
> >
> > Any ideas?
> >
> > Marcus
>
>|||That might explain my problem...where does it state that it's an EE only
option? The developer edition supports it as well (obviously I suppose) but I
hadn't realised it wouldn't work in production!!
"Jasper Smith" wrote:
> Are you on Enterprise Edition of Reporting Services, it's an EE only
> feature.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > Hi,
> >
> > I am trying to create a data driven subscription. I have stored the
> > credentials and custom data source in the report. My account has site
> > administrator and content manager permissions, yet the "New Data Driven
> > Subscription" button is not available. I have multiple reports like this.
> >
> > Any ideas?
> >
> > Marcus
>
>|||If you go to the SQL Server site then navigate to the reporting services page
and then find the feature compairison it is listed there.
For us the only thing we need EE for is the data driven subscriptions and I
can't get the boss to come off the hip with 10x the money for that one
feature.
I am going to try and write some code to run reports from the reporting
server using .net and sql statements.
"Paul Hasell" wrote:
> That might explain my problem...where does it state that it's an EE only
> option? The developer edition supports it as well (obviously I suppose) but I
> hadn't realised it wouldn't work in production!!
> "Jasper Smith" wrote:
> > Are you on Enterprise Edition of Reporting Services, it's an EE only
> > feature.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> > http://www.sqldbatips.com
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> > news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > > Hi,
> > >
> > > I am trying to create a data driven subscription. I have stored the
> > > credentials and custom data source in the report. My account has site
> > > administrator and content manager permissions, yet the "New Data Driven
> > > Subscription" button is not available. I have multiple reports like this.
> > >
> > > Any ideas?
> > >
> > > Marcus
> >
> >
> >

Data Driven

Hi,
I understand I can create a stored procedure to return all the parameters
for data driven. The last statement of the Stored Procedure is Select *
DataDrivenTableName. However, after I put Exec SPName, and when i go to the
next page to fill out all the To, CC, Subject..., there is nothing in each
drop down box and I can't continue to finish the setup.
Any idea what's wrong.
Thanks
EdAFAIK RS uses first resultset to get data, not last.
Stjepan
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2F0D6FE4-2FA8-49EB-A965-D0A57D47362C@.microsoft.com...
> Hi,
> I understand I can create a stored procedure to return all the parameters
> for data driven. The last statement of the Stored Procedure is Select *
> DataDrivenTableName. However, after I put Exec SPName, and when i go to
> the
> next page to fill out all the To, CC, Subject..., there is nothing in each
> drop down box and I can't continue to finish the setup.
> Any idea what's wrong.
> Thanks
> Ed

Data Convertion Error

I am using SQL server 2005, Visual Web Developer 2005 express (for right now). Can get the stored procedure to run fine if I do not return the CityID.

Stored Procedure

ALTER Procedure [dbo].[WDR_CityAdd1]

(

@.CountryID int,

@.CityName nvarchar(50),

@.InternetAvail bit,

@.FlowersAvail bit,

@.CityID int OUTPUT

)

AS

IF EXISTS(SELECT 'True' FROM city WHERE CityName = @.CityName AND CountryID = @.CountryID)

BEGIN

SELECT

@.CityID = 0

END

ELSE

BEGIN

INSERT INTO City

(

CountryID,

CityName,

InternetAvail,

FlowersAvail

)

VALUES

(

@.CountryID,

@.CityName,

@.InternetAvail,

@.FlowersAvail

)

SELECT

@.CityID = 'CityID' ( I have also tried = @.@.Identity but that never returned anything it is an identity column 1,1)

END

Here is the code on the other end. I have not included all the parameters, but should get a sense of what I am doing wrong.

Dim myCommand As New SqlCommand("WDR_CityAdd1", dbConn)

myCommand.CommandType = CommandType.StoredProcedure

Dim parameterCityName As New SqlParameter("@.CityName", SqlDbType.NVarChar, 50)

parameterCityName.Value = CityName

myCommand.Parameters.Add(parameterCityName)

Dim parameterCityID As New SqlParameter("@.CityID", SqlDbType.Int, 4)

parameterCityID.Direction = ParameterDirection.Output

myCommand.Parameters.Add(parameterCityID)

Try

dbConn.Open()

myCommand.ExecuteNonQuery()

dbConn.Close()

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

'AlertMessage = "City Added"

Catch ex As Exception

AlertMessage = ex.ToString

End Try

Here is the error I get. So what am I doing wrong? I figured maybe in the stored procedure. CityID is difined in the table as an int So why is it telling me that it is a varchar when it is defined in the stored procedure, table and code as an int? Am I missing something?

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'CityID' to data type int. at System.Data.SqlClient.SqlConnection.OnError

Thanks

Jerry

There are a couple of issues:

grbourque wrote:

...

SELECT @.CityID = 'CityID'

...

Return (parameterCityID.Value).ToString (have played with this using the .tostring and not using it)

...

--> Conversion failed when converting the varchar value 'CityID' to data type int.

In this location,

SELECT @.CityID = 'CityID'

You are attempting to assign the @.CityID (an integer) the string value 'CityID'. That is what caused the error you reported.

I suggest using SCOPE_IDENTITY to capture the IDENTITY value of the last row entered by the current user/session.

SET @.CityID = SCOPE_IDENTITY()

Also, the RETURN value 'should' be an integer, and you are attempting to return a string value.

('Normally', one would use the RETURN value for success/failure reporting.)

Somewhere prior to the RETURN statement, you 'should' assign the parameterCityID value to a previously declared variable, then use that variable in your application.

|||

Arnie

I had tried playing around with the 'cityID' fieldname and was getting different error messages. I have re-written the code with your help and it works. Sometimes this just amazes me. I do appreciate the help. Maybe with time I will be able to help others as I get a handle on this.

Jerry

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!

Does anybody know?

Thanks in advance,

Jerome Smith

The data connections list is stored in the registry under HKCU\Software\Microsoft\VisualStudio\8.0\Packages\{4A0C6509-BF90-43DA-ABEE-0ABA3A8527F1}\Settings\Data\Connections. Its a BIDS specific setting, not specific to SSIS, but nevertheless used for GUI based package development under BIDS.

If one were to migrate to a different machine for package development, I could see where it would be useful to copy those registry entries over, true enough, in the same sense that you can export/import favorites from IE, or server listings from management studio.

One note though, the data connections are a measure of convenience (a memory bank of previous connections) to individuals, and are not a deployment/migration artifact. Connection managers are persisted in the IS packages (which you probably already knew), and when migrated to different environments, configurations are used to mesh IS packages into the new environment.

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database)? I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages.

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

However, once the connection manager is made, that information has been copied into the package itself, and that registry entry might as well have never existed and does not need to exist in the future.

Now, if you want to see the last point demonstrated rather than just asserted (that is, that a connection manager's connectivity information is persisted to the package), create a IS package in BIDS with an OLEDB connection manager used in an execute sql task and execute the package sucessfully from BIDS.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

Now, run the package . What happens? Runs as before. Which database is hit? The one the connection manager was changed too. Is the registry updated to point to the connection manager's current database? No, it is not.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

BIDS is an acryonym for Business Intelligence development studio, which is the design-time environment hosted by Visual Studio 2005 for building BI projects ( Integration Services, Analysis Services, Reporting Services).

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith

Data Connections: Where the hell are they stored?!!

Hello all,

Does anybody know where SSIS Data Connections are stored? Whenever one creates a Connection Manager, a list of all created Data Connections appears. It's very quick and easy to create a Connection Manager from an existing Data Connection, so really the latter are in essence the Connection Managers and are thus part of the application. It is therefore important to back them up if for example one wants to migrate the application to another computer. I have looked everywhere in Documents and Settings and Program Files and I can't find any folder or file where these Data Connections are stored! It's annoying to have this mysterious black-box behaviour!

Does anybody know?

Thanks in advance,

Jerome Smith

The data connections list is stored in the registry under HKCU\Software\Microsoft\VisualStudio\8.0\Packages\{4A0C6509-BF90-43DA-ABEE-0ABA3A8527F1}\Settings\Data\Connections. Its a BIDS specific setting, not specific to SSIS, but nevertheless used for GUI based package development under BIDS.

If one were to migrate to a different machine for package development, I could see where it would be useful to copy those registry entries over, true enough, in the same sense that you can export/import favorites from IE, or server listings from management studio.

One note though, the data connections are a measure of convenience (a memory bank of previous connections) to individuals, and are not a deployment/migration artifact. Connection managers are persisted in the IS packages (which you probably already knew), and when migrated to different environments, configurations are used to mesh IS packages into the new environment.

|||

Hi,

Thanks for your reply.

Forgive my ignorance, but what is BIDS?

Now OK, connection managers are persisted in the IS packages, but what use are they if they don't store connection information (Server, Authentication, Database)? I thought that's what they were for but it now appears that this connection information is stored in the Windows registry, which is not persisted in the IS packages.

Is there any way to retain the connection information in the connection managers?

Cheers,

Jerome

|||

Connection managers do store connection information.

You thought that's what they were for and that is exactly correct.

Now the confusing part is that the connection information is stored in "both" places.

However, once the connection manager is made, that information has been copied into the package itself, and that registry entry might as well have never existed and does not need to exist in the future.

Now, if you want to see the last point demonstrated rather than just asserted (that is, that a connection manager's connectivity information is persisted to the package), create a IS package in BIDS with an OLEDB connection manager used in an execute sql task and execute the package sucessfully from BIDS.

Then,export those those registry entries and delete them (you'll reimport them later), using a tool like regedit.exe.

If you don't want to to mess with the registry, the following will demonstrate the point as well; double-click on the connection manager and point it to a different database.

Now, run the package . What happens? Runs as before. Which database is hit? The one the connection manager was changed too. Is the registry updated to point to the connection manager's current database? No, it is not.

The connection information is persisted to the package.

Now, if you deleted the registry entries, re-import them.

BIDS is an acryonym for Business Intelligence development studio, which is the design-time environment hosted by Visual Studio 2005 for building BI projects ( Integration Services, Analysis Services, Reporting Services).

|||

Thank you very much. That was very useful.

Best regards,

Jerome Smith

Data binding to a stored procedure that returns two result sets

Hi there everyone. I have a stored procedure called "PagingTable" that I use for performing searches and specifying how many results to show per 'page' and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:

strSQL = "EXECUTE PagingTable " & _

"@.ItemsPerPage = 10, " & _

"@.CurrentPage = " &CStr(intCurrentPage) &", " & _

"@.TableName = 'Products', " & _

"@.UniqueColumn = 'ItemNumber', " & _

"@.Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _

"@.WhereClause = '" & strSQLWhere &"'"

The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In 'classic' ASP I did this like this.

'Open the recordset

rsItems.Open strSQL, conn, 0, 1

'Get the values required for drawing the paging table

intCurrentPage = rsItems.Fields("CurrentPage").Value

intTotalPages = rsItems.Fields("TotalPages").Value

intTotalRows = rsItems.Fields("TotalRows").Value

'Advance to the next recordset

Set rsItems = rsItems.NextRecordset

I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:

A) Bind the repeater control to the second resultset

B) Build a "pager" of some sort using the values from the first resultset

Hi,

have you tried setting the DataMember property of the repeater ?

|||

Thanks for the quick reply.

I was thinking about that but I don't know what the second result set would be "called". The results come back as if you executed two SELECT statements one after another without a UNION statement in between.

So what would I set the repeater's DataMember property to? Can I use an integer like

repItems.DataMember = 1 (assuming 0 is the first result set)

|||

If you don't specify a name...:

Table1

Table2

...

TableN

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryUse whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:

>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:

>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryThis is a multi-part message in MIME format.
--020803060805070804090702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
--020803060805070804090702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Use whatever convention is logical and meets your needs; the main
thing is to be consistent.<br>
<br>
Personally, I tend to stick with the GUI default
<dbname>_data.mdf & <dbname>_log.ldf for everything I
can (using default values for things, if they're sensible &
reasonable, tends to avoid the pain of having to memorise different
conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer
(1, 2, 3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf,
mydb2_Data.ndf, etc.<br>
<br>
CREATE DATABASE <dbname>, with no extra parameters, will create
the database with <dbname>.mdf & <dbname>_log.ldf but
I've always thought that to be inconsistent (with "_log" but not
"_data") so I've always gone with the recommendation of the GUI (with
the _data & _log postfixes).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
HarrySmith wrote:
<blockquote cite="midONj5bFAtFHA.3604@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
</pre>
</blockquote>
</body>
</html>
--020803060805070804090702--