Database Discussion Boards

Re: Problems with SQL

Colin Angus Mackay11-Oct-06 2:45

Colin Angus Mackay

11-Oct-06 2:45

Britney S. Morales wrote:
i want a query who get the rows by packages

I don't understand what you mean by "packages"

Do you mean you only want the SQL to return 10 rows at a time?

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: Problems with SQL

Frank Kerrigan11-Oct-06 2:47

Frank Kerrigan

11-Oct-06 2:47

Britney S. Morales wrote:
i want a query who get the rows by packages ( 10 by 10 users).

PD: Only sql statements, not using paging gridview property

What's a package ? : (stupid answers on a postcard please )

Blog Have I http:\\www.frankkerrigan.com

Re: Problems with SQL

Eric Dahlvang12-Oct-06 4:57

Eric Dahlvang

12-Oct-06 4:57

I am guessing that you want something like this:

select <code>TOP 10</code> * from users where codgroup = 1  <code>AND </code><code>usersortfield > 'last groups greatest value' ORDER BY </code><code></code><code>usersortfield</code>

EXAMPLE:
If you were ordering by LastName, you would do this to get the first 10:

select <code>TOP 10</code> * from users where codgroup = 1  </code><code><code>ORDER BY </code></code><code><code><code></code></code>LastName</code><code>

Then, when you want the next 10, you would take the lastname from the last record in the previous 10 and:

select <code>TOP 10</code> * from users where codgroup = 1  </code><code>AND </code><code><code></code><code><code></code></code>LastName <code></code></code><code>> </code><code><code>'</code><font class="messagetitle">Morales</font></code><code>' </code><code><code>ORDER BY </code></code><code><code><code></code></code>LastName

(Of course, you wouldn't use LastName alone, because it isn't unique enough...but you can see what I mean by this example.)

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Problems with SQL

Britney S. Morales13-Oct-06 2:19

Britney S. Morales

13-Oct-06 2:19

Eric thats the point, it isnt the answer, but your post given to me a light in the dark,
(its just a sentence) Laugh | :laugh:

keep Learning and you never will be out of date...

Analysis Services 2000 - Member.FetchAllProperties Error

Kevin McFarlane10-Oct-06 3:26

Kevin McFarlane

10-Oct-06 3:26

I get

"System.NotSupportedException: The method specified is not supported by the current provider."

AdomdClient v8, VS 2003, SQL Server 2000

What's the cause of this and is there any way around it?

Kevin

HIPAA Advice

jajanes10-Oct-06 2:46

jajanes

10-Oct-06 2:46

I've been retained by a company to create a HIPAA compliant billing file from their current data. The useless government information guide (A useless government manual, how could that be?) they were provided with is about 2 phone books thick and would make an excellent campfire.

Does anyone have any experience working with HIPAA billing files? Any advice would be appreciated - excellent advice would be compensated! Smile | :)

Thanks

John

stored-proc question

flippydeflippydebop9-Oct-06 22:51

flippydeflippydebop

9-Oct-06 22:51

just a quickie...

Is there a feature in ms sql server that allows you to setup a stored-proc to be run at predefined times.. Im thinking along
the lines of the "scheduled tasks" in windows where you can choose to run a process every so often.

Re: stored-proc question

yahao9-Oct-06 23:37

yahao

9-Oct-06 23:37

you can use sql server agent. Smile | :)

Re: stored-proc question

flippydeflippydebop10-Oct-06 0:15

flippydeflippydebop

10-Oct-06 0:15

cheers mate Smile | :)

Re: stored-proc question

Frank Kerrigan11-Oct-06 2:48

Frank Kerrigan

11-Oct-06 2:48

You can also use a DTS package (which uses SQL Server Agent)

Blog Have I http:\\www.frankkerrigan.com

Accessing SQL via VPN firewall

hairy_hats9-Oct-06 22:47

hairy_hats

9-Oct-06 22:47

Hi all,

I'm trying to get a couple of remote computers running a UI to be able to access an SQL database behind a VPN firewall. They can get through the VPN successfully but cannot connect to the database, which is set up to do validation via trusted connections.

Any ideas as to how I can set this up please?

Re: Accessing SQL via VPN firewall

Colin Angus Mackay10-Oct-06 0:53

Colin Angus Mackay

10-Oct-06 0:53

Ensure that the TCP/IP port SQL Server is using is exposed through the firewall.

If you are using SQL Server 2000 you can find this out with the Enterprise Manager:
Right click the server icon for the server you wish to expose. Click Network Configuration. Ensure that TCP/IP is in the Enabled list (if not, seek advice from your Network Admin). Click TCP/IP in the Enabled list and click the "Properties" button below it. It will tell you the default port. You need to open that port on the firewall to see the SQL Server.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: Accessing SQL via VPN firewall

Frank Kerrigan11-Oct-06 3:08

Frank Kerrigan

11-Oct-06 3:08

Keberos does not travel well across VPN links. Try using SQL user connections rather than trusted.

Blog Have I http:\\www.frankkerrigan.com

MS Linked Servers and Triggers [modified]

flippydeflippydebop9-Oct-06 3:47

flippydeflippydebop

9-Oct-06 3:47

Hello.. I wonder if someone here could help me.

I have 2 MSSQL Server 2000 databases installed on 2 different machines. These are named Master & Servant. The master db is a Windows 2003 machine. The servant DB is Windows XP machine.

- I have successfully setup a linked server on the master that references the Servant DB.
- I then written a stored-proc on the Servant which adds a record to a table.
- i have successfully called this stored-proc from master db which adds a new row to the table.

When, however i try to call this stored-proc from a triger on the master db i recieve the following error.

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

My trigger currently looks like this:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_testCopyDataAcrossDb' AND type = 'TR')<br />
   DROP TRIGGER trig_testCopyDataAcrossDb<br />
GO<br />
CREATE trigger trig_testCopyDataAcrossDb on testbed FOR update, insert AS<br />
<br />
BEGIN<br />
	SET XACT_ABORT ON -- this is required for distributed transactions.<br />
	SET IMPLICIT_TRANSACTIONS OFF <br />
	BEGIN DISTRIBUTED TRANSACTION distTrans<br />
<br />
	--insert into servant.testbed.dbo.mytable ( DateTimeRaised ) VALUES ( getdate() )<br />
	exec servant.testbed..sp_AddRecord<br />
<br />
	COMMIT TRANSACTION<br />
END

What i must stress here and what i find wierd is that am able to call the stored-proc from Query Analyzer with success. I am only recieving this error when trying to call this stored-proc from my trigger.

-- modified at 12:17 Monday 9th October, 2006

MS Linked Servers and Triggers Solution/Hack!

flippydeflippydebop10-Oct-06 0:15

flippydeflippydebop

10-Oct-06 0:15

Okay guys this one definatly tripped me up but ive managed to find a way to sort it. Firstly this table syncronisation is only a tempory job which i want for a couple of weeks so i didnt want to go through the whole replication bag. That is just to heavy-duty for what i need, and something that want be around in a month or so.

So, after reading a post over at MSDN forums i found that the reason why the call to the stored-proc works inside Query Analyzer. Query Analyzer only requires the oledb to support ITransactionLocal. - But triggers require ITransactionJoin.

I looked into distributed transactions but but couldnt come up with any work-arounds. It looks like there is a problem with our security settings and distributed transactions across the two servers.

In the end i stumbled upon this hack and added a "COMMIT TRANSACTION" as the first line of the trigger and this worked. - I guess this is commiting the distributed transaction first. Then i added the call to my stored-proc which now works.

the trigger now looks like this:

<br />
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_testCopyDataAcrossDb' AND type = 'TR')<br />
DROP TRIGGER trig_testCopyDataAcrossDb<br />
GO<br />
CREATE trigger trig_testCopyDataAcrossDb on testbed FOR update, insert AS<br />
<br />
BEGIN<br />
   COMMIT TRANSACTION<br />
   EXEC servant.testbed..sp_AddRecord<br />
END

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

steve_rm9-Oct-06 1:06

steve_rm

9-Oct-06 1:06

Hello,

I have a problem trying to connect a remote sql server. The error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

DEVSQL computer name
RealitySolutions instance name

Using a windows application on a LAN.

my connection string is:
connectionString="server=DEVSQL\REALITYSOLUTIONS; database=ServiceMaster; Integrated Security=True

This is what I have done:
Enabled TCP/IP and named pipes and share memory protocals on my instance in configuratoin management.
surface area configuration I have enabled enabled remote connection using TCP and Named pipes.

I still get the above error message.

Any help will be most grateful, and I think i have tried everything.

Thanks

Steve

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

yahao9-Oct-06 5:30

yahao

9-Oct-06 5:30

if connect to server on otder machine,use machine-IP\instance-id,otherwise use "(local)".

btw,maybe need config the client-network information with SQL Server Client Network Utility,the server-ip and port,etc.

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

steve_rm9-Oct-06 7:57

steve_rm

9-Oct-06 7:57

Hello,

thanks for your reply.

But i think the ip address would be used for connecting to a remote server on the internet. Local would be the local server on my machine not a remote one.

How do i config client-network information with sql server? Not sure about this.

Thanks in advance,

Steve

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Private_Void9-Oct-06 8:46

Private_Void

9-Oct-06 8:46

Data Source="SQLServerIP\InstanceName";Initial Catalog="DataBase";Persist Security Info=True;User ID="UserID";Password="Password"

Obviously the " are just to separate the required values and what you will modifiy.

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

steve_rm9-Oct-06 22:17

steve_rm

9-Oct-06 22:17

Hello private_void,

Thanks for your reply.

I have noticed you are using that connection string with a sql security. Currently I have my sql server setup for Windows authenication. That is why I never had the pwd or username.

Just wondering, can you connect remotely with windows authentication? If so, have I missed something about windows user account? security login?

Please if you have any idea about windows authentication.

thanks very much,

Steve

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

yahao9-Oct-06 23:44

yahao

9-Oct-06 23:44

try to config an alias for "DEVSQL" with sql server client network utility.

ps: DEVSQL <=> IP on Lan

i have only use SSPI in same machine,not lan or internet yet.:p

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

steve_rm10-Oct-06 0:09

steve_rm

10-Oct-06 0:09

Thanks for your reply yahao,

But I am sure what you mean by:

yahao wrote:
config an alias for "DEVSQL" with sql server client network utility

How will this help solve the current problem?

I have connected to sql server many times. This the first time remotely.

The server I am using is 2005 developer edition.

Many thanks again,

Steve

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

yahao10-Oct-06 0:30

yahao

10-Oct-06 0:30

or try to directly connect remote server use IP rather than use machine name.

this is the fun of resolve problem. Smile | :)

Re: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Private_Void10-Oct-06 3:04

Private_Void

10-Oct-06 3:04

Well I think you should use the IP address. This should be a static IP. If it is not that could be why you are having problems. When you enable the connections via Pipes you should reboot the Server. I don't see anything wrong with your connection string and yes SQL 2005 does allow Windows Authentication, but you still have to set up a Login under Security\Logins. The user name should be Domain\WindowsUserAccount. If you have any questions regarding connections strings there is a great website; http://www.connectionstrings.com.

Hope this helps.

adding row to database

ranro20068-Oct-06 19:12

ranro2006

8-Oct-06 19:12

I have a question that I was hoping someone could help me with. I am using VB.net 2005 express edition with SQL Server 2005 Express edition.

I have a database that has a table in it called Folder. It consists of two fields. One is called folderId and folderName.

I want to add a new row to this folder by calling a second form from my main form. For example, say I open up a second form from my main form. This second form consists of a text box and an button called OK. The user will enter the new folder name into the text box. How do I make it so the name the user enters into the text box is added to the folder table.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.