Right-Click Table -> Import data GONE?

Hi all
Apologies if this should be a tools question!

In the 'good old days' you could right-click a table, select Import data and have the target pre-selected....not only does that no longer exist for SQL 2005 you now always have to explicitly select the source and target....

Is there a work-around or is this a security cleanup gone mad and is thus a 'live with it :( ?Right-click on the database-->Tasks-->Import Data...

-Jamie|||Hi Jamie
You are of course correct, however this does mean that you have to explicitly select the source and target. My point is that beforehand you could implicitly select the target by right-clicking the table and select import data thereafter typically performing a text file import.

Surely we should not have to open the BI Studio or have to run an import at the database not table level?|||

Charl wrote:

Hi Jamie
You are of course correct, however this does mean that you have to explicitly select the source and target. My point is that beforehand you could implicitly select the target by right-clicking the table and select import data thereafter typically performing a text file import.

Surely we should not have to open the BI Studio or have to run an import at the database not table level?

It does seem an oversight I agree. However, is it really that much of a hardship to have to explicitly select the table? In my opinion its not - its only my opinion however!

I dare say MS will have had some UE type people on this (if they didn't - they should have done) and so will be able to explain the decision.

-Jamie|||Hi Jamie
It's not so much that it is a hardship to explicitly select the table rather that you have to jump through more hoops.

One of the beauties of the right-click-->import data on a table was that you were left with a visual clue as to the table to be imported into. Now you first select the target, set the text qualifier (and silently lose information if you don't - my .csv file imported successufully but lost everything after the first comma),set the source column types (and if these are too short if you have done the basic suggest types it will error with truncation warnings, but not where you expect?!) then select the target Server & DB.

Finally the summary screen shows your "quick import" - you now have to expand the window and the source / destination columns to see what you are trying to do - not something you want to go through again if it fails!

All I am doing here is to populate a set of static tables, nothing fancy required as these are simple .csv files that will be imported infrequently.

Thanks for your replies

ATB

Charl

Returning recordset as xml via DomDocument in ASP results in error

Hi all,
I'm trying to return a recordset obtained from a sql server 2000 stored
procedure, as a text/XML content type via old asp.
The problem is the sql data contains foreign characters and when returning
the xml page to the IE6, the default xsl shows the error "invalid character"
for the foreign characters.
The code for the sp is the following:
Create Procedure webTest( @.nDummy Int )
As
Begin
Select Id = 1, Name = 'PARAGRAPH SYMBOL '
End
The code for the asp is:
<%@. Language=JavaScript %>
<%
Response.ContentType = "text/xml";
var cn=Server.CreateObject("ADODB.Connection");
var rs=Server.CreateObject("ADODB.Recordset");
var cmd=Server.CreateObject("ADODB.Command");
cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
Catalog=MATDESA;Data Source=sqlrespaldo01");
rs.CursorLocation = 3;
cmd.ActiveConnection = cn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "webTest";
var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
cmd.Parameters.Append(prm);
var rs = cmd.Execute();
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
rs.Close();
cn.Close();
%>
Looking at the return on IE6 it returns "invalid character" on the ""
symbol
But if I modify the code to save the recordset directly to the Response
stream, then it works. I mean if substituting the lines
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
by
rs.Save(Response, adPersistXML);
the browser shows ok the foreign symbols.
Where is the error on the first case? It looks like the created doc assumes
ascii character encoding or something alike...
Any hint is welcomed
Thanks in advance
Sammy
"SammyBar" <sammybar@.gmail.com> wrote in message
news:e%2393NI9QHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm trying to return a recordset obtained from a sql server 2000 stored
> procedure, as a text/XML content type via old asp.
> The problem is the sql data contains foreign characters and when returning
> the xml page to the IE6, the default xsl shows the error "invalid
character"
> for the foreign characters.
> The code for the sp is the following:
> Create Procedure webTest( @.nDummy Int )
> As
> Begin
> Select Id = 1, Name = 'PARAGRAPH SYMBOL '
> End
> The code for the asp is:
> <%@. Language=JavaScript %>
> <%
> Response.ContentType = "text/xml";
> var cn=Server.CreateObject("ADODB.Connection");
> var rs=Server.CreateObject("ADODB.Recordset");
> var cmd=Server.CreateObject("ADODB.Command");
> cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
> Catalog=MATDESA;Data Source=sqlrespaldo01");
> rs.CursorLocation = 3;
> cmd.ActiveConnection = cn;
> cmd.CommandType = adCmdStoredProc;
> cmd.CommandText = "webTest";
> var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
> cmd.Parameters.Append(prm);
> var rs = cmd.Execute();
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> rs.Close();
> cn.Close();
> %>
> Looking at the return on IE6 it returns "invalid character" on the ""
> symbol
> But if I modify the code to save the recordset directly to the Response
> stream, then it works. I mean if substituting the lines
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> by
> rs.Save(Response, adPersistXML);
> the browser shows ok the foreign symbols.
> Where is the error on the first case? It looks like the created doc
assumes
> ascii character encoding or something alike...
>
All strings in script are unicode.
Hence doc.xml returns a unicode encoding of the XML document content.
Response.Write takes such a string and sends it to the client encoding it
using the the current Response.CodePage setting (Session.Codepage on
IIS5[.1])
The codepage defaults to the system codepage, typically in the West 1252.
Hence the character is encoded as a standard ANSI 1252 single byte
character.
However the receiving client XML DOM is expecting UTF-8 which is the default
encoding used by XML. Hence the character causes an error.
rs.Save(Response, adPersistXML)
OTH, will send the XML generated by the recordset in it's 'native' UTF-8
format directly to the client without being converted to another codepage.
Hence all is well.
As Martin has said this is the recommended approach.
BTW, personnally I prefer to tell the client explicitly it is receiving
UTF-8 using:-
Response.CharSet = "UTF-8"
Immediately after setting the content type.
Anthony.

Returning recordset as xml via DomDocument in ASP results in error

Hi all,
I'm trying to return a recordset obtained from a sql server 2000 stored
procedure, as a text/XML content type via old asp.
The problem is the sql data contains foreign characters and when returning
the xml page to the IE6, the default xsl shows the error "invalid character"
for the foreign characters.
The code for the sp is the following:
Create Procedure webTest( @.nDummy Int )
As
Begin
Select Id = 1, Name = 'PARAGRAPH SYMBOL '
End
The code for the asp is:
<%@. Language=JavaScript %>
<%
Response.ContentType = "text/xml";
var cn=Server.CreateObject("ADODB.Connection");
var rs=Server.CreateObject("ADODB.Recordset");
var cmd=Server.CreateObject("ADODB.Command");
cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
Catalog=MATDESA;Data Source=sqlrespaldo01");
rs.CursorLocation = 3;
cmd.ActiveConnection = cn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "webTest";
var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
cmd.Parameters.Append(prm);
var rs = cmd.Execute();
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
rs.Close();
cn.Close();
%>
Looking at the return on IE6 it returns "invalid character" on the ""
symbol
But if I modify the code to save the recordset directly to the Response
stream, then it works. I mean if substituting the lines
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
by
rs.Save(Response, adPersistXML);
the browser shows ok the foreign symbols.
Where is the error on the first case? It looks like the created doc assumes
ascii character encoding or something alike...
Any hint is welcomed
Thanks in advance
Sammy"SammyBar" <sammybar@.gmail.com> wrote in message
news:e%2393NI9QHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm trying to return a recordset obtained from a sql server 2000 stored
> procedure, as a text/XML content type via old asp.
> The problem is the sql data contains foreign characters and when returning
> the xml page to the IE6, the default xsl shows the error "invalid
character"
> for the foreign characters.
> The code for the sp is the following:
> Create Procedure webTest( @.nDummy Int )
> As
> Begin
> Select Id = 1, Name = 'PARAGRAPH SYMBOL '
> End
> The code for the asp is:
> <%@. Language=JavaScript %>
> <%
> Response.ContentType = "text/xml";
> var cn=Server.CreateObject("ADODB.Connection");
> var rs=Server.CreateObject("ADODB.Recordset");
> var cmd=Server.CreateObject("ADODB.Command");
> cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
> Catalog=MATDESA;Data Source=sqlrespaldo01");
> rs.CursorLocation = 3;
> cmd.ActiveConnection = cn;
> cmd.CommandType = adCmdStoredProc;
> cmd.CommandText = "webTest";
> var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
> cmd.Parameters.Append(prm);
> var rs = cmd.Execute();
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> rs.Close();
> cn.Close();
> %>
> Looking at the return on IE6 it returns "invalid character" on the ""
> symbol
> But if I modify the code to save the recordset directly to the Response
> stream, then it works. I mean if substituting the lines
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> by
> rs.Save(Response, adPersistXML);
> the browser shows ok the foreign symbols.
> Where is the error on the first case? It looks like the created doc
assumes
> ascii character encoding or something alike...
>
All strings in script are unicode.
Hence doc.xml returns a unicode encoding of the XML document content.
Response.Write takes such a string and sends it to the client encoding it
using the the current Response.CodePage setting (Session.Codepage on
IIS5[.1])
The codepage defaults to the system codepage, typically in the West 1252.
Hence the character is encoded as a standard ANSI 1252 single byte
character.
However the receiving client XML DOM is expecting UTF-8 which is the default
encoding used by XML. Hence the character causes an error.
rs.Save(Response, adPersistXML)
OTH, will send the XML generated by the recordset in it's 'native' UTF-8
format directly to the client without being converted to another codepage.
Hence all is well.
As Martin has said this is the recommended approach.
BTW, personnally I prefer to tell the client explicitly it is receiving
UTF-8 using:-
Response.CharSet = "UTF-8"
Immediately after setting the content type.
Anthony.

Returning Range of Records from SQL Server 7 - Old issue revisited

Hi all,

I am using the following stored procedure in SQL 7 to return a range of records.

CREATE PROCEDURE spRetMyTable
@.maxRows int, @.lastRecord int, @.SortPhrase varchar(122)
AS
begin
DECLARE @.stSql VARCHAR(255)
SET ROWCOUNT @.maxRows
SET @.stSql = 'SELECT * FROM (SELECT TOP 10 colPrimary, col1, col2, col3 FROM (SELECT TOP ' + CONVERT(VARCHAR, @.lastRecord) +
'colPrimary, col1, col2, col3 FROM [MyTable] ORDER BY [colPrimary] ASC) AS tbl1 ORDER BY [colPrimary] DESC) AS tbl2 ORDER by [colPrimary] ASC'
exec(@.stSql)
set rowcount 0
end

To execute the above procedure I am issuing the command:

execute spRetMyTable @.maxRows 10, @.lastRecord 1500

The column [colPrimary] is the primary field in the table.

I will be using the selected records in a gridView with paging (3 records per page). So if the next page is selected I would require to take out record 1491 to record 1494 and Add record 1501 to record 1503 in the datatable subject to the availability of records in the table MyTable.

How do I provide sorting by col1, col2 or col3? It could be single column or multiple column sorting as chosen by the user during runtime.

Is there any performance overhead in the above method in the first place?
Could it be improved by any alternative process?

I need YOUR ASSISTANCE to cater to the above issues in a feasible way.

Thanks in advance.

You may take a look atSorting Data with Data Source Controls

|||

HiIori_Jay,

Thanks. I will try on the advised line.