Question VB/SQL Select Query syntax issue!

[rgouette]

I can get this to work fine in SSMS...
in VB? not so much(VS2013)

        dim com as string = "select spid, substring(nt_username,1,30) nt_login," & _
            "substring(loginame,1,30) db_login," & _
            "substring(name,1,30) db," & _
            "count(*) over() as total_users" & _
            "from master..sysprocesses" & _
            "inner join master..sysdatabases on master..sysdatabases.dbid = master..sysprocesses.dbid" & _
            "where master..sysprocesses.program_name like 'pxxi/sqlca/%'" & _
            "and master..sysprocesses.ecid = 0" & _
            "group by spid,nt_username,loginame,name" & _
            "order by nt_login"

error I'm trapping is:

 'Incorrect syntax near 'master'.

Thoughts?
thanks lads,
Rich

 

[jmcilhinney]

Are you specifying a database in your connection string? I would think that you should omit that and then omit all the "master.." qualifiers. I've never actually tried to query the master database but I believe that it is the default database if none is specified in the connection string.

 

[rgouette]

I did have the database specified.
Tried removing the name, to use this: Dim str As String = "Server=SQL1;Trusted_Connection=True;"
I removed "master.." references, and get syntax errors so not sure if I'm going to make it work with my feeble skills..
I tried using explicit references to table names...I dunno..

R