PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
What is the problem w/ my Connection String?-VBForums
Results 1 to 13 of 13

Thread: What is the problem w/ my Connection String?

  1. #1

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    753

    Question What is the problem w/ my Connection String?

    I have a problem in connecting my application to a remote SQL Server.
    I always get a timeout expire while executing a query w/ some functions like Convert() and RTRIM().

    I don't get a problem if I'm using MS SQL Server Management Tool (although it is a little bit slow 'coz the result is more 9,000 rows/records).

    This is my Connection String:
    Data Source=[RemoteServer];Initial Catalog=[DatabaseName];User ID=[Username];PWD=[Password];Connect Timeout=300

    Thanks for any help.

  2. #2

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    753

    Re: What is the problem w/ my Connection String?

    Please help me!

  3. #3
    Frenzied Member moinkhan's Avatar
    Join Date
    Jun 2000
    Location
    Karachi, Pakistan
    Posts
    2,011

    Re: What is the problem w/ my Connection String?

    sorry if this sounds stupid...

    but are you sure that you r connected with sql server? is that server up and running?

  4. #4
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,708

    Re: What is the problem w/ my Connection String?

    Sounds like basic select statement and the timeout is set to 5 mins but the timeout should be set in the connection querytimeout property not the connection timeout. Two different things.

    Try this cn.

    "Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=myUsername;Password=myPassword;"

    Then once connected you can try adjusting the querytimeout of the connection object.

    What is this in? Classic ADO or ADO.NET?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    753

    Re: What is the problem w/ my Connection String?

    I'm sorry for being stupid.
    The remote SQL Server runs 24/7, it is in Canada and we are in Philippines.

    I'm using ADO.NET, how to change querytimeout?

  6. #6
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,708

    Re: What is the problem w/ my Connection String?

    Set the CommandTimeout property of the SqlCommand object
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    753

    Re: What is the problem w/ my Connection String?

    Thanks Rob, I'll try it.
    Should I set it to a high value like 300?

  8. #8
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,708

    Re: What is the problem w/ my Connection String?

    I still just wonder why it takes so long when those functions are not intense. Are you retrieving all 50K records? Did you set "SET NO COUNT ON" in your sp?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    753

    Re: What is the problem w/ my Connection String?

    Hey, I'm using a DataAdapter to fill-up my Dataset.
    This is my code:
    VB Code:
    1. Dim myDA As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter([SelectQuery],[ConnectionObject])
    2. Dim myDS As New Data.DataSet
    3. myDA.Fill(myDS)
    Is it alright to insert this code myDA.SelectCommand.CommandTimeout = 300 before myDA.Fill(myDS)?

    I need the dataset to write an XML file for later use.

  10. #10
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,708

    Re: What is the problem w/ my Connection String?

    You should create a stored procedure on the sql server as it will greatly increase the execution time due to sp's being precompiled and stored on the sql server.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  11. #11

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    753

    Re: What is the problem w/ my Connection String?

    Yeah! There are many stored procedures before but the first programmer of the WEB Application that I'm working on removed some of those were used in reporting.
    I don't know what is in his mind why he removed it. Maybe he is jealous, I do really hate that attitude.

    I can't insert stored procedures now on the remote database, maybe because I'm using only SQL Server Management Express. I have an old copy of the database w/ all the stored procedures he is using.
    Now I'm using almost same algorithm to download all the data I need, then save it as an XML file.
    I'll use the XML file for generating MS Excel reports.

    BTW, there should be 3 XML files needed to be created; the first one is around 70kbs w/ 1 table and around 200 records, the second one is around 250kbs w/ 1 table and around 400 records, and the last one is more than 7MB w/ 3 tables w/ a total of 22,000+ rows. I already started posting some upcoming problems about it in Office Development, if you remember. I hope you may help me.

    Thanks Rob,

  12. #12
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,708

    Re: What is the problem w/ my Connection String?

    Yes, sounds like a competitive insecurity issue with the other programmer not wanting to share the sp's. Even though your running Express you can still creat the sp's on the remote db by executing the CREATE STORED PROCEDURE .... sql code. Generate a script from your backed up version of the db where the missing sp's are located. Then you can execute it on your other remote db to re-add the sp's.

    Yes, I remember a bit of it but I'll look again if you still have some threads outstanding.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  13. #13

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    753

    Re: What is the problem w/ my Connection String?

    OMG! Now I know how noob I am in database development.
    How to create that script or how to create that stored procedure on the remote database?

    I found the thread, as of now the application that I'm developing can generate reports on MS Excel very fast (2-3 sec for 2 sheets w/ around 300 rows, also /w cell formating and borders), I'm not sure of my 1GB Ram or improvement in VSTO. but I'm still happy to the improvement of the speed.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width