Datatype Problem

Hello
I am new to SQL Server and have migrated an Access DB to SQL Server
I can connect to the database, pull content, etc but a strange thing happen
when I use the "text" datatype. I have a table called "tbl_news
where a field is called "news_story" this is a large text block that my clien
can cut and paste text from MS word. Since it needs to hold large amounts ot tex
I used the MEMO field-type in Access. When I used the TEXT field in SQL serve
some weird stuff happens. When I query the record in a recordset and display
the results on a standard ASP page - one or another fields will not print out
No matter what I try - it seems that one field won't display and there is no rhyme or reason.
When I look in the DB I see the DATA but it wont print on the screen like response.write rs("news_story"
What would cause a field not to print? somewhat hap-hazardly
SHould I use the text datatype? For ex. when I use vchar it doesnt happen. But my tex
gets truncated. Please Help - I am ready to pull my hair ouTo read from TEXT column you should use READTEXT statement,
but you can try to play with SET TEXTSIZE.
Please read "Retrieving ntext, text, or image Values" topics in BOL.
HTH
Igor Raytsin
"Padpros" <anonymous@.discussions.microsoft.com> wrote in message
news:892D6AA1-FCF8-4051-9586-B0FFAA1ED99D@.microsoft.com...
> Hello,
> I am new to SQL Server and have migrated an Access DB to SQL Server.
> I can connect to the database, pull content, etc but a strange thing
happens
> when I use the "text" datatype. I have a table called "tbl_news"
> where a field is called "news_story" this is a large text block that my
client
> can cut and paste text from MS word. Since it needs to hold large amounts
ot text
> I used the MEMO field-type in Access. When I used the TEXT field in SQL
server
> some weird stuff happens. When I query the record in a recordset and
display
> the results on a standard ASP page - one or another fields will not print
out.
> No matter what I try - it seems that one field won't display and there is
no rhyme or reason.
> When I look in the DB I see the DATA but it wont print on the screen like
response.write rs("news_story")
> What would cause a field not to print? somewhat hap-hazardly?
> SHould I use the text datatype? For ex. when I use vchar it doesnt happen.
But my text
> gets truncated. Please Help - I am ready to pull my hair out
>

Datatype Problem

Hello,
I am new to SQL Server and have migrated an Access DB to SQL Server.
I can connect to the database, pull content, etc but a strange thing happens
when I use the "text" datatype. I have a table called "tbl_news"
where a field is called "news_story" this is a large text block that my clie
nt
can cut and paste text from MS word. Since it needs to hold large amounts ot
text
I used the MEMO field-type in Access. When I used the TEXT field in SQL serv
er
some weird stuff happens. When I query the record in a recordset and display
the results on a standard ASP page - one or another fields will not print ou
t.
No matter what I try - it seems that one field won't display and there is n
o rhyme or reason.
When I look in the DB I see the DATA but it wont print on the screen like re
sponse.write rs("news_story")
What would cause a field not to print? somewhat hap-hazardly?
SHould I use the text datatype? For ex. when I use vchar it doesnt happen. B
ut my text
gets truncated. Please Help - I am ready to pull my hair outTo read from TEXT column you should use READTEXT statement,
but you can try to play with SET TEXTSIZE.
Please read "Retrieving ntext, text, or image Values" topics in BOL.
HTH
Igor Raytsin
"Padpros" <anonymous@.discussions.microsoft.com> wrote in message
news:892D6AA1-FCF8-4051-9586-B0FFAA1ED99D@.microsoft.com...
> Hello,
> I am new to SQL Server and have migrated an Access DB to SQL Server.
> I can connect to the database, pull content, etc but a strange thing
happens
> when I use the "text" datatype. I have a table called "tbl_news"
> where a field is called "news_story" this is a large text block that my
client
> can cut and paste text from MS word. Since it needs to hold large amounts
ot text
> I used the MEMO field-type in Access. When I used the TEXT field in SQL
server
> some weird stuff happens. When I query the record in a recordset and
display
> the results on a standard ASP page - one or another fields will not print
out.
> No matter what I try - it seems that one field won't display and there is
no rhyme or reason.
> When I look in the DB I see the DATA but it wont print on the screen like
response.write rs("news_story")
> What would cause a field not to print? somewhat hap-hazardly?
> SHould I use the text datatype? For ex. when I use vchar it doesnt happen.
But my text
> gets truncated. Please Help - I am ready to pull my hair out
>

Datatype mismatch in export

I am exporting my sql table to the access database. My text fileds in SQL are stored as varchar(50) in SQL server 2005. but when I export the table to Access, These fileds get converted to 'memo' fields. I tried converting these memo fields in access to text field but Access does do that, throws an error message saying not enough disk space.

When I try to convert these 'varchar' fields in sql to text fileds, sql throws a 'time out ' message nad does not convert it to text.

What do I do?

Thanks

As per the guidelines of Microsoft, the equivalent of 'VARCHAR' in Sql server is 'TEXT' in MS-Access. How are you exporting Sql server data to Access? Are you using SSIS? On a side note, equivalent of 'MEMO' is 'TEXT' in Sql Server. I wouldn't recommend you to change the data type in Sql Server to change to 'TEXT'.

Text data type in Sql Server is used to store large texts that go beyond 8KB in size. Usually, sql server keeps a 16 byte pointer to the text data in the page. It can contain upto 2 GB size data. In Sql Server 2005, usage of Text data type is not recommended and varchar(max) is recommended.

http://articles.techrepublic.com.com/5100-6345-5033381.html
https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0561.mspx?mfr=true

datasource connection error

have developed a report on a test windows server machine in SSRS 2005 which
I can access from i./e. http://localhost/reports
DataSourcce is accessed using a sql login
My colleague on his machine can not access. His error is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'MarketAnalytics'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errorsCheck the SRS error logs they should have a better error for you. They can be
found in: C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting
Services\LogFiles.
--
SQL Server Developer Support Engineer
"arkiboys2" wrote:
> have developed a report on a test windows server machine in SSRS 2005 which
> I can access from i./e. http://localhost/reports
> DataSourcce is accessed using a sql login
> My colleague on his machine can not access. His error is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'MarketAnalytics'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
>

DataSource and more than one recordset

I've a report that use a stored procedure as datasource.
This s.p. return 2 recordset. How to access the second recordset in
reporting services? (like Recordset.NextRecordset in VB)
--
OSVALDO COLITTIRS does not support more than one recordset being returned.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>|||Bruce is correct in that the native Data Processing Extensions do not
support stored procedures that return more than one recordset - what happens
is that the first recordset is all that your Reporting Services DataSet will
see.
However, you can certainly create a custom Data Processing Extension, and
could then gain access to the other recordsets returned by a stored
procedure in a report. Its not optimal and the best way forward is usually
to re-design the report logic if this is at all possible. Typically what you
need to do in your Data Processing Extension is archive off the recordsets
into a tables that you can then pull them down from seperately for other
Report DataSets in the Report.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>

Datasets in custom code

Hi guys,

is it possible to access my report's datasets from within custom code or pass them as parameters to a custom assembly? I want the dataset itself, not just fields, so i can programmatically look through it for data.

Thanks!

sluggy

This is not directly supported.

There are however some alternative approaches:

* add a list reportitem to the report and inside the list make a call to your custom code/custom assembly function and pass in the fields as parameter values. This will call your function for every row of data of the dataset bound to the list.

* or a more complex but also more powerful option is to use the new CustomReportItem feature in RS 2005 and implement a custom processing control which can examine the processed DataValues inside the CustomReportItem. A starting point with a sample is available here: http://blogs.msdn.com/chrishays/archive/2005/10/04/CustomReportItemSample.aspx

-- Robert

|||Thanks Robert, i will check that solution out, although it is probably overkill for the particular scenario i was dealing with

Datasets access from custom code

Hi to all
Is possible to access to the report datasets or even report items from the
custom code embedded in the report?, If yes, how?,could you send some sample
code?
ThanksNo, you would need to pass in the values you are interested in as parameters
to your function.
For example:
=Code.MyFunction(Fields!CityCode.Value) or
=Code.MyFunction(ReportItems!textbox1.Value)
--
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.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> Is possible to access to the report datasets or even report items from the
> custom code embedded in the report?, If yes, how?,could you send some
sample
> code?
> Thanks
>
>|||Ok... I think this is very restrictive, I need to condition the visibility
of some components in base to some information contained in the dataset,
like if exist some value for one column and if it is the same for others
columns (if exist some) and I was trying to create a general function for
all my components , it seems that I'll have to create other datasets that
makes this kind of comparision with SQL or some SP, If you have a best idea
please let me know...
What about to pass Datasets as parameters? is possible?
Thanks
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> No, you would need to pass in the values you are interested in as
parameters
> to your function.
> For example:
> =Code.MyFunction(Fields!CityCode.Value) or
> =Code.MyFunction(ReportItems!textbox1.Value)
> --
> 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.
> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > Hi to all
> > Is possible to access to the report datasets or even report items from
the
> > custom code embedded in the report?, If yes, how?,could you send some
> sample
> > code?
> > Thanks
> >
> >
> >
> >
>|||No, you cannot pass the entire data set as a parameter.
You could hand in the entire fields collection, however, if that would help
you any.
--
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.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:u1qA$AcdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> Ok... I think this is very restrictive, I need to condition the visibility
> of some components in base to some information contained in the dataset,
> like if exist some value for one column and if it is the same for others
> columns (if exist some) and I was trying to create a general function for
> all my components , it seems that I'll have to create other datasets that
> makes this kind of comparision with SQL or some SP, If you have a best
idea
> please let me know...
> What about to pass Datasets as parameters? is possible?
> Thanks
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> > No, you would need to pass in the values you are interested in as
> parameters
> > to your function.
> > For example:
> >
> > =Code.MyFunction(Fields!CityCode.Value) or
> > =Code.MyFunction(ReportItems!textbox1.Value)
> >
> > --
> > 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.
> > "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> > news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > > Hi to all
> > > Is possible to access to the report datasets or even report items from
> the
> > > custom code embedded in the report?, If yes, how?,could you send some
> > sample
> > > code?
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
>

Dataset row access

I'm trying to get a value from a specific row something like:-
=Fields[2]!Data.Value
where the 2 is the row of data i'm after.
Any ideas? TIA,
DFCould you please elaborate on your scenario so I can sugest a solution?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Fell" <DanFell@.discussions.microsoft.com> wrote in message
news:8F7D8141-5919-4BA3-AF72-DCAD67EE9FEB@.microsoft.com...
> I'm trying to get a value from a specific row something like:-
> =Fields[2]!Data.Value
> where the 2 is the row of data i'm after.
> Any ideas? TIA,
> DFsql

dataset object dispose after first use

I have an Execute SQL Task that returns a dataset to variable DfltValData. A dataflow follows that with a script component that access that dataset (read only variable) (see code below) and everything is fine. Now, after that, there's another dataflow with a script component, with the same code as below, trying to access DfltValData. Here is where the problem is, the DfltValData object does not contains any row. Whats happening and how to solve this?

Thanks!

Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow
olead.Fill(dt, Me.Variables.DfltValData)
For Each row In dt.Rows
.

.

.// read value from row

.HAHAHAHA!! Not to laugh at your problem, but search a bit and you'll find a VERY recent discussion on this very topic.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1406350&SiteID=1|||

Use a MULTICAST if you need to work with same rows again.

There is a code work-around (shown in the forum post Phil referenced), but it's ugly.

|||I don't understand how multicast would work if we have multiple dataflow.|||

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

|||

jwelch wrote:

Sorry, I didn't read the original post closely enough.

If the basic problem is that you want to reuse the data in another data flow, you could use a multicast in your first data flow, and output the data to a raw file. Then use a raw file source to pull it into your second data flow.

Exactly. Or simply use a multicast to populate 2 variables in the first place with exactly the same data.

-Jamie

DataSet Access from a matrix

Data Set Access from a matrix

I am trying to create a schedule by room number report.I am using a matrix.The column group that I am using is room number.There is no row group.When you group by room it is only allowing me access to the first data item for that room.Even though there are other data items for that room I can not access them.I can not access all data items in that grouping expression.Is there a way to get unobstructed access to a dataset while working in a matrix and grouping?

If you are not getting back the correct data... I would use a table format and grouping... you can "trick" the look of the report to appear like a matrix..

Hope this helps..

sql

Datareader and dataset

Hi

I am using a datareader to access data via a stored procedure. The reason for using the datareader is that the stored procedure is multi level depending on the variable sent to it. However I want to do two things with the data being returned.

The first is to poulate a datagrid - which I've done.
The second is to produce an Infragistic Web Graph. However according to the background reading I have done so far, I can only populate the graph from one of the following: datatable,dataview,dataset,Array or Ilist.

I don't want to make another call to the server for the same information, so how can I get the data out of a stored procedure into a dataset or dataview?

regards

JimSorry about that - found and article on the web site which told me that I was going about it the wrong way - should have read it first!

The article was at http://www.dotnetjunkies.com/quickstart/aspplus/doc/webdataaccess.aspx#storedprocs
and showed me how to access data from a stored procedure and put it into a dataset and a dataview.

regards

Jim

DataReader access

Hi,

I am facing a problem to access datareader... actually i want to get data on lables from datareader. actually i am having one table having only one column and i hav accessed all data into datareader but the problem is that i just want to get data row by row...

For example there are four labels Label1, Label2, Label3, Label4

and want to print the data from datareader on to thease labels...

plz do reply... i am in trouble

//Create the DataReader by calling ExecuteReader()SqlDataReader myReader = myCommand.ExecuteReader();//Iterate through the DataReader in a while loopwhile (myReader.Read()){ // each row in DataReader

 Name = myReader.GetString(0);or

 Name = myReader["Name"].ToString();}//Close DataReadermyReader.Close();

HTH

DataItem access in ascx

I am currently building an ecommerce site using aspx, vbscript, & sql.
In my productlist control containing a datalist I am trying to use an ifstatement to determine if products are from a certain supplier... andif they are; display a phone number to call for pricing rather than theprice, since this particular supplier doesnt allow pricing of theirproducts online.
This is what I have now:
<%
Dim objConn
objConn = Server.CreateObject("ADODB.Connection")
Dim strConnect
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
Dim supplier
objConn.Open(strConnect)
supplier = objConn.Execute("Select SupplierID From Product")
If supplier = "C" Then
Response.Write("Call 1-800-POO-STAIN")
Else
Response.Write(" Sug. Retail:")
DataBinder.Eval(Container.DataItem, "Price")
End IF
objConn.Close
objConn = Nothing
%>
This code gives me the following error message:
The component 'ADODB.Connection' cannot be created. Apartment threaded components can only be created on pages with an <%@. Page aspcompat=true %> page directive

Can anybody help me get this working?
I haven't worked with VBScript for acouple years now and I'm a little rusty.
Any help would be greatly appreciated

What you have is Classic ASP code. You should change the extension to ASP, or convert the code to use VB.NET and the ADO.NET components.
Alternately, add this to the top of the page:
<%@. Page aspcompat=true %>
|||

The problem is I am using this in an ascx control. aspcompat is not supported by the 'control' directive.

Any other suggestions?

|||the page that will hold the ascx needs to have the aspcompat attribute.

Dataflow Tab:There is no ODBC Source option in the Toolbox

I need to extract data from tables in a database that I can only access via ODBC.

I have successfully created a connection in Connection Manager (ConnectionManagerType = ODBC) for this database.

However I’m unable to add this connection as a Data Flow Source. There is no ODBC Source option in the Toolbox.

This is a major because we have been using the system dsn Microsoft Visual Foxpro Driver to access free table directory .dbf files under ODBC with DTS for years. To install a new Microsoft OLEDB driver for foxpro is out of the question on a production system as it would cost many thousands of dollars to go through our BAT testing process

How do I extract data from tables in a database via ODBC?

Thanks in advance

Dave

There is too, though it's not marked as such. Use the Data Reader Source.|||

You can use the script component as source.

|||

Thanks for that the data reader souce can actually use a ODBC source. I have it working fine.

I do think there is a peformance overhead and its slower than the ODBC connector in DTS.

|||

well that is interesting How can it connect to a foxpro file .dbf and matching .fpt file the .fpt files are used for memo text fieldsthe foxprpro ODBC driver does this for you behind the scenes

|||

Vijay Thirugnanam wrote:

You can use the script component as source.

True, though it shouldn't be faster than using the prepackaged source connectors.

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

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 V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
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 Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign 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 t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim BuntonJim
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$8G
6.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 V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
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 Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign 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 t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e 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@.TK2MS
FTNGP11.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$8G
6.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 V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
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 Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign 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 t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e 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@.TK2MS
FTNGP11.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$8G
6.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 V
arChar
These transformations were carried out using SQL EnterpriseManager > DesignT
able
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 Compa
nyName VarChar(50) ]
This reports success.
The datatypes are now reported to be as required (SQL EnterpriseManager > De
sign 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 t
ext field BUT the server is still telling the access Front end that the data
type is nVarChar [NOT VarChar - as currently reported by SQL Enterprise
Manager]
NOTE - the Access Front End first deletes ALL the tableDefs then recreates t
hem with an ODBC connection to the Server
All the other tables (apart from Companies) are still updateable as they wer
e before.
Jim Bunton

Databases in SQL Server 2000

I would like to know what is the design of database stored
in the Microsoft SQL Server 2000, Microsoft FoxPro or
Microsoft Access or totally new database?
Can database other than by Microsoft, such as Oracle,
reside in Microsoft SQL Server 2000?You can use DTS in SQL Server to get data from Oracle or FoxPro or Access
into SQL Server. But if you're asking if you can just take an Oracle
database and run it natively in SQL Server, no.
"Max Takaki" <mtakaki@.sjc.org> wrote in message
news:0e7b01c35137$e00cc410$a601280a@.phx.gbl...
> I would like to know what is the design of database stored
> in the Microsoft SQL Server 2000, Microsoft FoxPro or
> Microsoft Access or totally new database?
> Can database other than by Microsoft, such as Oracle,
> reside in Microsoft SQL Server 2000?|||Max,
Reside in SQL Server in their native format itself? No.But you can use
conversion tools like the built-in DTS to convert non-MS databases,FP,
Access etc to SQL Server ones.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Max Takaki" <mtakaki@.sjc.org> wrote in message
news:0e7b01c35137$e00cc410$a601280a@.phx.gbl...
> I would like to know what is the design of database stored
> in the Microsoft SQL Server 2000, Microsoft FoxPro or
> Microsoft Access or totally new database?
> Can database other than by Microsoft, such as Oracle,
> reside in Microsoft SQL Server 2000?

DATABASEPROPERYEX is not a recognised function name

I am trying to write a report in reporting services 2000 which access a sql server 7 database. It all workes fine with the connection and creating a dataset but I cannot seem to get the results displayed in the preview section. I get the following error:

"An error occured during report processing query execution failed for dataset XXXX DATABASEPROPERYEX is not a recognised function name "

Any help greatly appreciated

Your are running RS 2000 RTM - you have two options:

* install RS 2000 SP1 or SP2 on both the report server and the report designer machines. The service packs contain the fix.

* Alternatively, in report designer go to the "Data Options" tab of the "Dataset"
dialog. On the Data Options tab you will see that all settings contain "Auto" (and report server would therefore try to auto-detect the collation settings from the database server). Replace the Auto-settings with the following settings (e.g. if your SQL 7.0
database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true

-- Robert

Databaseaccess

Hi,
how it is possible to make a limition of access to a
database ?
I want that the database is only accessible from only one
other database !
Greetings,
Daniel RakojevicYou limit access by assigning rights to Logins. If you don't want anyone to
have access then don't give them a Login or password and they won't be able
to get in.
Andrew J. Kelly
SQL Server MVP
"Daniel Rakojevic" <anonymous@.discussions.microsoft.com> wrote in message
news:113cb01c3f4a6$9f16dcd0$a601280a@.phx
.gbl...
> Hi,
> how it is possible to make a limition of access to a
> database ?
> I want that the database is only accessible from only one
> other database !
> Greetings,
> Daniel Rakojevic|||Thanks,
i have an internet application and my user is iusr_daniel.
For the hidden database, i need now a new user ?
How to make it, with an new user ?
Greetings,
Daniel

>--Original Message--
>You limit access by assigning rights to Logins. If you
don't want anyone to
>have access then don't give them a Login or password and
they won't be able
>to get in.
>
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Daniel Rakojevic" <anonymous@.discussions.microsoft.com>
wrote in message
> news:113cb01c3f4a6$9f16dcd0$a601280a@.phx
.gbl...
one
>
>.
>|||If that database is on the same server then just make sure the user from the
other database is a valid user in that one. If you don't add any other
users into the second database then that user should be the only one who can
access it other than sa's.
Andrew J. Kelly
SQL Server MVP
"Daniel Rakojevic" <anonymous@.discussions.microsoft.com> wrote in message
news:10e1401c3f52f$6620cec0$a101280a@.phx
.gbl...
> Thanks,
> i have an internet application and my user is iusr_daniel.
> For the hidden database, i need now a new user ?
> How to make it, with an new user ?
> Greetings,
> Daniel
>
> don't want anyone to
> they won't be able
> wrote in message
> one

DATABASE= or INITIAL CATALOG=?

I'm setting up a DSN for a new SQL Server install, and had some problems with
an existing Access app. Every query resulted in an "object not found", and
after a little poking about it became clear that it was because the app was
connecting to "master" instead of our database.
The connection string in Access used "Database=[our database name]". Does
this actually work? Or did the app only work in the past because the default
database for that login was set to ours?
Is there a difference between DATABASE (which I inherited) and INITIAL
CATALOG?
Yes, Database=YourDatabase does actually work.
Initial catalog is generally used when connecting with an
OLE DB provider.
Database is generally used when connecting with an ODBC
driver.
The following is a good resource for connection strings:
http://www.able-consulting.com/ADO_Conn.htm
-Sue
On Wed, 24 Nov 2004 07:37:35 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>I'm setting up a DSN for a new SQL Server install, and had some problems with
>an existing Access app. Every query resulted in an "object not found", and
>after a little poking about it became clear that it was because the app was
>connecting to "master" instead of our database.
>The connection string in Access used "Database=[our database name]". Does
>this actually work? Or did the app only work in the past because the default
>database for that login was set to ours?
>Is there a difference between DATABASE (which I inherited) and INITIAL
>CATALOG?
|||"Sue Hoegemeier" wrote:

> Yes, Database=YourDatabase does actually work.
> Initial catalog is generally used when connecting with an
> OLE DB provider.
Hmmm. That being the case can you suggest any other reason for this problem?
The application logs into the correct server and attaches to master, even
though I say database=mydatabase in the connection string.
|||Using DSNs and connections strings is kind of a waste in my
opinion and can make the issues more convoluted. I have no
idea how the DSN and connection strings are being used but I
would just get rid of the DSN. You introduce maintenance
issues as well as you have your connection string in a DSN
and in code. So I'd eliminate that and just use dsn-less
connections.
Make sure the user exists in the database and can actually
access the database.
-Sue
On Wed, 24 Nov 2004 08:37:04 -0800, Maury Markowitz
<MauryMarkowitz@.discussions.microsoft.com> wrote:

>"Sue Hoegemeier" wrote:
>
>Hmmm. That being the case can you suggest any other reason for this problem?
>The application logs into the correct server and attaches to master, even
>though I say database=mydatabase in the connection string.