DataDirection output Problems

I have the below code. When the SqlParameter is created the @.UserID is set as input instead of output. anyone have any ideas why this is happining and how I can get it to work. I can create the variable as a single parameter and set the direction to output and it works but when I try it in the code below it won't work. Any help will be appreciated

Endeavor

Dim parametersAs SqlParameter() = {New SqlParameter("@.strSex", Data.SqlDbType.Char, 50), _New SqlParameter("@.intRace", Data.SqlDbType.Int), _New SqlParameter("@.intAge", Data.SqlDbType.Int), _New SqlParameter("@.UserID", Data.SqlDbType.SmallInt, Data.ParameterDirection.Output)}The overload that takes 3 parameters does not have the direction as the 3rd parameter. If you want to do it that way, you'll need the full overload that takes the 10ish parameters.|||

Thanks for your help. OK I don't need all of the rest of the parameters. Is there any way to make the variable an output and still keep it in the array or add it to the array after creating the sqlparameter.

Endeavor

|||cmd.Parameter("@.Name").ParameterDirection=Output

DataDir Property - Is it possible to store files on more than 1 drive?

The "DataDir" property for an AS 2005 instance seems to only allow for one path to be specified. I am working with a very large data set that is being updated frequently and I would like to be able to reference more than on LUN on my disk array.

Yes, you can.

Take a look at the StorageLocation property of your partition.

Edward Melomed (MSFT)

--

This posting is provided "AS IS" with no warranties, and confers no rights.

DataDir empty

Hi,

The DataDir content has been removed on my production server and I lost all my cubes.

Any ideas to explain this issue ? Do i only need to recreate my cubes or this directory contains other files necessary to run SS2005 ?

Cheers, JL

If you restart the SSAS service with an empty DataDir it will add all the required system files and startup with no databases installed. Then you will either need to redeploy your project and reprocess, or restore a backup.|||

furmangg,

Thanks for your reply ; it was due to a disk issue but things are ok now.

Cheers, JL.

DataDir empty

Hi,

The DataDir content has been removed on my production server and I lost all my cubes.

Any ideas to explain this issue ? Do i only need to recreate my cubes or this directory contains other files necessary to run SS2005 ?

Cheers, JL

If you restart the SSAS service with an empty DataDir it will add all the required system files and startup with no databases installed. Then you will either need to redeploy your project and reprocess, or restore a backup.|||

furmangg,

Thanks for your reply ; it was due to a disk issue but things are ok now.

Cheers, JL.

Datadesign - help request

HI There,

Im now working on a assignment where i have hierarchial tree structure arrangement for representing an health care. This is supported by SQL DB 2005.One of the node in the tree is dissolved and all it functionality have to ported to the other tree node which share the similar structure.
Problem in porting is complex due to the following things

1. There are capabilities and permission associated with the each node.

2.each node has a unique identifier across the three (note: though this seems to be of no problem it might become issue when search occurs for eg. a->b->c search now becomes a->b->x->n ->?).

3.Already there are messages left in the DB. Please can anyone help me how can i initiate this migration. Im very new to the design and im just started "ABCD" of it.

Thanks in advance.

Cheers,
Vidhya Raocould you please script the table, i.e. show us the CREATE TABLE statement|||Hi There,

Please can you tell me in detail what u require.

Cheers,
Vidhya|||why don't you start with the CREATE TABLE statement

:)|||Hello,

it is not a data design, it is refactoring a database design which has a hierarchial relationship.

Cheers,
Vidhya|||okay, then

you can start by explaining what you mean by "refactoring"

and i will ask you for the 3rd time, would you please show us your CREATE TABLE statement so that we can see what kind of a hierarchy you have|||Hello,

IF you are available online please do let me know i can send you the DB diagrams. There are many tables and they exhibit strong relationship.
DB structure is not hierarchial but way the directory which im trying to port is the hierarchical.

Please provide me your mail id if possible!!1

Cheers,
Vidhya|||sorry, i don't provide private support for free!!!111|||hi yeh,

Im actually looking for a good mentor to help out i this project.

Im very new to design and started learning now. If you are really an experieced designer please let me know.

if you need contact let me know. Im planning to pay for the service.

Cheers,
Vidhya|||Did someone fib on their resume, and now they have to pay the piper ?

Not a fun situation to be in.:S|||you mentioned medical data and hierarchial tree structure...

are you converting a mumps hierarchial database to a SQL database ?|||are you converting a mumps hierarchial database to a SQL database ?

* Ouch *

Dataconversion: STRING (yyyymmdd) to DATE

Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.

I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?

Any hint for me is very

Thanks in Advance

ulrike

You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

setDATEFORMAT ymd

selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2

|||

You could use CONVERT function with 112 style:

Code Snippet

declare @.dt varchar(20)

set @.dt ='20070507'

selectconvert(datetime,@.dt,112)

|||

Hello again,

Thanks for your advise.

I tried to run the following code:

selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

What’s going wrong? Please give me some mor advise.

Thanks in advance,

ulrike

|||

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB

|||

ulrikeG wrote:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.

|||Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors

DataConversion Problem

SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Thanks for the reply Uri
DownLoaded Service Pack 3
Installed - works
DownLoaded Service Pack 3a (sql2kasp3.exe)
Setup.exe > begins to run - stops on ERROR 145 an error occurred in the move data process
? Help ? !! Have done a reboot. still no joy
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton
|||Creating table first then loading data works fine - ca now edit data
[update to SP 4 - to SP 3 Ok but
SP 3a setup.exe > will not instal - stops wth err 145 'an error occurred in the move data process']
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:eeBjvvB9FHA.1032@.TK2MSFTNGP11.phx.gbl...
Jim
Do you have a last Service Pack installed on the SQL Server?
Looks strange. I just did some testing and it works just fine
Try to create a table first and then run DTS to update the table.
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:KWCif.17904$8G6.12386@.fe1.news.blueyonder.co. uk...
SQL Server 2000, Win 2000 Access 97
I have an Access FrontEnd and BackEnd
The backend tables have been imported (Data Transformation Services) into ab SQL Server Database.
Much of the data was not of the type required - e.g. nVarChar needed to be VarChar
These transformations were carried out using SQL EnterpriseManager > DesignTable
The Access Front End worked Fine.
PROBLEM
For security reasons one tables, Companies, was dropped.
Now after re-importing the table (Data Transformation Services)
and running a script (SQL Query Analyser)
[ example line of script > ALTER TABLE dbo.Companies Alter Column CompanyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > Design Table)
Inspecting the data in the ACCESS 97 front end is fine
BUT - attempts to update the data give an ERROR
The import of the error is that the ACCESS front end is trying to update a text field BUT the server is still telling the access Front end that the datatype is nVarChar [NOT VarChar - as currently reported by SQL Enterprise Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates them with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they were before.
Jim Bunton