Consulting

Results 1 to 5 of 5

Thread: SQLServer Nvarchar unicode test [for Tommy et al]

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    SQLServer Nvarchar unicode test [for Tommy et al]

    I'm doing this on SQLServer Express 2005, but don't think it makes that much difference:

    I used a catalog called NCS, the file I want to insert [Russian Unicode] is called unitest.txt, and I create a table named N_MaxText... so I leave it up to you to insure the path/filename and that the table does not already exist. Note: for future posts in this forum... very difficult to provide working code for 'servers'... too many differences

    Step 1: using OLEDB, set up connection and create table

    [vba]
    adExecuteNoRecords = 128
    cConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=,\SQLEXPRESS;Initial Catalog=NCS"
    Set oConn = CreateObject("ADODB.Connection")
    oConn.CommandTimeOut=0
    oConn.Open(cConn)
    cSQL = "CREATE TABLE [dbo].[N_MaxTest] ( [NMaxVar] NVARCHAR(MAX) NOT NULL );"
    oConn.Execute cSQL, ,adExecuteNoRecords
    [/vba]


    now insert file into table using an ADODB Stream Object (and if you have a better way to insert it as unicode w/out a stream let me know)

    [vba]
    cFile = "[yourpath]\unitest.txt"
    Set oRS=CreateObject("ADODB.Recordset")
    Set oS=CreateObject("ADODB.Stream")
    oS.Type=2
    oS.Open()
    oS.LoadFromFile(cFile)
    oS.Position=0
    oRS.Open "[dbo].[N_MaxTest]",cConn,1,3,2
    oRS.Addnew
    oRS.collect("NMaxVar")=oS.ReadText()
    oRS.Update
    oRS.Close
    Set oRS=Nothing
    oS.Close
    Set oS=Nothing
    oConn.Close
    Set oConn=Nothing
    [/vba]

    and there you have it, your have inserted a paragraph of Pushkin poetry into a Unicode field.

    So, the test.... hmmmm ... you give me code I can use to extract that field, and place it into Excel as both russian and english [via Google's weak Beta translation... that code in the workbook I posted in the previous thread that got us here]. I am re-posting the pushkin unicode... just unzip.

    Stan

  2. #2
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    I'm having trouble connecting to the Server:






    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    That would be the major issue which distinguishing testing server code versus local code. I can post what works for me, but others have to ensure that they are connecting to a valid catalog on their own server, and as I said modify any file/paths to their own configuration. Please post your connection string, it's a lot easier to work with than an error message. Stan

  4. #4
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    At first, I tried it the way it was. Then I tried adding my name to the "\SQLExpress" since that's how it's stated in my object explorer in SQL Server Management Studio express:
    [vba]cConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;Data Source=,JOSEPH\SQLEXPRESS;Initial Catalog=NCS"[/vba]
    What is a catalog? What does it do?




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  5. #5
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by malik641
    What is a catalog? What does it do?
    A catalog is similar to a database. See my remarks on my post under your regex thread.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •