This Is Driving Me Nuts - Solicit Help From The Gurus

Jul 20, 2005

Hi -
I hope some one can help me with this.

I am using sql server 2000
[Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003
16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)]

I have a stored procedure that is using cursors and a few joins, and
writes to a few tables. (I can post the code if that will help) The
stored procedure takes approximately 27 seconds to complete when
executed inside query analyser. However, if I run the stored procedure
source directly inside query analyser (like a long sql script), it
takes only 3 seconds!! These results are consistent and reproducible.

I would think a stored procedure stores the plan, and I would expect
better optimization. Why am I witnessing the opposite behaviour? Any
one has any experience?

The server is manned by DBAs (I work at a large corporation), so I
believe it is well configured. We have noticed similar behaviour on
data restores on a different physical server.

Thanks in advance,
-praty77

View 6 Replies


ADVERTISEMENT

Sp_processmail - Driving Me Nuts!

Apr 2, 2004

I have two different SQL server installations (7 and 2000) that I have configured for using the SP_processmail procedure. Both servers can properly execute xp_startmail, xp_stopmail, xp_readmail, and xp_findnextmsg. HOWEVER, both have different problems with SP_processmail.

Using this command (and a host of many other variations):
sp_processmail @filetype = 'CSV', @separator = ',', @dbuse = 'ryantest', @set_user = 'guest'

SQL 7.0 says: Server: Msg 18007, Level 16, State 1, Line 0
Supplied datatype for @set_user is not allowed, expecting 'varchar'

I have tried every variation possible, including not specifying the yser which should result in it defaulting to the "guest" account. The message is always the same.

SQL 2000 says: Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80004005
Queries processed: 0.

I have checked all my configurations and read every KB article - I can't find anything wrong.

Can someone PLEASE respond if you have any experience with SP_PROCESSMAIL on your servers.

Thanks in advance.

Ryan Hunt

View 1 Replies View Related

Math Is Driving Me Nuts

Jul 20, 2005

The business rule is, the sales manager is commissioned on the avg. numberof appointments set up per salesrep per day during the month.I have 2 tables: The UserLog table records only 1 entry per day per user(salesrep). This will log how many salesreps worked a particular day. Thesecond table logs any appointments set up.UserLog: ID, UserName, EnteredTimeAppointment: ApptID, EnteredTime, ApptDateI figured that, for a given date ranged, I could1. sum the number of appointments2. sum the number of days worked2. sum the salesreps / number of days = avg number of salesreps per day3. number of appointments / avg number of salesreps per day = avg numberof appointments per sales repBut this logic is flawed. If I average out every day and then take anaverage of this daily average, I get a different result. Any ideas on howto best solve this problem?Thanks.

View 7 Replies View Related

Simple IIF Statement Driving Me Nuts

Oct 17, 2007



I have an IIF statement in a text box. Its very basic

What I want is to check if the value of something is greater than 1. If it is, then make it 1. If it isn't then just use what the value is.

(loose example)

=IIF((Field!Var1.Value * Field!Var2.Value)>1, 1,Nothing)

Basically I can't have a pecentage greater than 100. If it is then make it 100. If its not then just use the result of the calculation.

View 7 Replies View Related

Driving Me Nuts: Error: 18456, Severity: 14, State: 16.

Oct 16, 2007

Hello,

I'm currently evaluating Visual Studio Team System using SQL Server 2005 (9.00.3042.00, SP2, Enterprise Evaluation Edition) and from time to time, I get this error:


Error: 18456, Severity: 14, State: 16.

If I try to connect to the database using SQL Server Management Studio, I get the error:

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)".

If I restart the SQL Server services, everything works fine! This is driving me nuts!

Additional information:
- The user accounts and security are OK - they have been double checked, and once the services are restarted everything works fine.
- SQL Server is configured to accept remote connections - again, once the services are restarted eveything works fine.
- I have disabled meanwhile the following protocols: VIA, Named Pipes, Shared Memory both at the Server and Client - and the problem still remains.

Any help?

Thanks!
JoĆ£o

View 2 Replies View Related

Silly Question - For SQL Gurus And SWYNK Gurus

Nov 3, 2000

Probably this will be a silly question?

1. How can I Put all the Q documents from Microsoft that are related to SQL server? So that I can search fast.

2. How can I search SWYNK for all the ANSWERS that MAK/CRAIG/RAY replied to the threads. Its so weird that I answered someone and now I forgot the answer what I posted. Now I am having the same problem. I cant search.

3. I wanna put all SWYNK's Questions responses in one database say SQLSERVER, so that I can search all the threads.

I had sent many emails to SWYNK. No reply so far.

Any inputs r well appreciated!!!!!!

-MAK

View 2 Replies View Related

Going Nuts Trying To Do This

Jan 12, 2004

Im going completly nuts trying to do this. In ASP it would have taken me seconds.

Ok I think this is right but correct me if im wrong

I have a class that for example has a function that gets all the states from the DB:

File called Class.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Configuration


Public Class Getalldata
Public Function GetPicdata() As SqlDataReader

Dim cnn As New SqlConnection(ConfigurationSettings.AppSettings("CONNECTION_STRING_USER"))
Dim cmd As New SqlCommand("Get_states", cnn)


cnn.Open()
Dim result As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
cnn.Close()

End Function
End Class

Ok How in the hell do I call this just to LOOP and print them out????


Sort of like this

Call class/object/whatever. I dont know how

Then

While Whaever

Loops thru the states response.writting them

Loop

Please hel;p before I completly go nuts

View 1 Replies View Related

Going Nuts Here!

Aug 30, 2004

Alright- I plugged this code in to tell my form to write to SQLServer when I click the Submit button. However I error out when I load the form:


dim Conn as new OleDbConnection("DSN=MyDB")

Sub On_Click(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'")

ExecuteStatement(strSQL)
end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, Conn)

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as Exception
end try
objCmd.Connection.Close()
end function

I get this error:
C:InetpubwwwrootSOSCommCommWeb4.aspx(14) : error BC30471: Expression is not an array or a method, and cannot have an argument list.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:InetpubwwwrootSOSCommCommWeb4.aspx(14) : error BC30205: End of statement expected.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~
C:InetpubwwwrootSOSCommCommWeb4.aspx(15) : error BC30035: Syntax error.

"'" & params(0) & "'," & _


Any ideas??

View 5 Replies View Related

Need Help With Aspnetdb.mdf As Im Going Nuts

Aug 1, 2006

I been all over this forum trying to find out how to get the aspnetdb.mdf running on my remote site and its driving me nuts.
i been using VWD 2005 express Edition and SQL Express 
Error Server Error in '/' Application.
An attempt to attach an auto-named database for file I:DataWebqsh.eumarker_93618920-db3b-4a04-a274-3fb613afc5dfwwwApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
over the last week it has been a nightmare trying to get anything working on the site but i have got all the other databases working but i can get the aspnetdb.mdf working. i am very new to ASP.net so please use the he kiss method  "Keep It Simple, Stupid!" as im a newbe to this and it will just go over my head.
Thanks 

View 3 Replies View Related

Simple Login, I'm Going Nuts (new Guy, Please Help)

Apr 17, 2006

Hi all, quick, hopefully simple question that has been driving me nuts for 3 days.  Using Visual Studio 2005, doing a web app, connecting to an SQL server with already existing login names and passwords...
I have a login name and password text box and a login button.  Upon clicking, sqldatasource1 checks to see if the items in the text box match a login and password in the DB.  If so, it sets Profile.login and Profile.password to the values in that text box so I can use it to key off the DB for application IDs associated with that login/pass later.
It displays just the login name in a details view upon a match.  If nothing is there that matches, it shows a hidden label saying "login failed".
My problem is, that I really don't want the detailsview to display if there is a match.  I want it to automatically go to page2.aspx...but I can't figure this out.  Response.Redirect("page2.aspx"); doesn't work.  It's like the check is happening before detailsview1 shows any data and therefore it doesn't load page2....and I can't figure out how to get it to check detailsview1 for data AFTER it has been displayed.
I guess that I don't know how to get this thing to check if there is something in the detailsview, to go to page 2.  It's just not working.  Any help would be great!  (BTW, not using the builtin login control because I can't figure out how to get that to connect to our sql database's login table.)
Thanks all!

View 7 Replies View Related

Going Nuts - The ConnectionString Property Has Not Been Initialized - Help!

May 27, 2006

I'm struggling with the different methodologies for using DataSets in 2.0. I can knock this out in no time flat in 1.0, but am bogged down in 2.0. Can anyone guide me on the right track? My code is below. I'm forming a DataSet from a SQL Server Express database from the user's choice from a drop down. My connection string "ASOCTCOConnectionString" is declared in my web.config and used successfully throughout in the WYSIWYG configurations of my databound controls.  But I want to control this in code. This si getting frustrating and right now I'm longing for the clean simplicity I had with Web Matrix :(
In any case, any suggestions? It crashes with this error when opening the connection with MyConn.Open(). I just want to be able to configure a DataSet and apply it to my controls and results.
<code>
Dim connectionString As String = System.Configuration.ConfigurationManager.AppSettings("ASOCTCOConnectionString")
Dim MyConn As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim MyQuery, SearchString As String

Dim myAdapter As New System.Data.SqlClient.SqlDataAdapter
Dim myDataSet As New System.Data.DataSet
Dim mySelectCommand As New System.Data.SqlClient.SqlCommand
mySelectCommand.CommandText = "SELECT * from Device_Type where Category LIKE " & DDLDevType.SelectedItem.Text
mySelectCommand.Connection = MyConn
MyConn.Open()
Grid1.DataSource = myDataSet
Grid1.Databind()
MyConn.Close()
</code>

View 11 Replies View Related

Xp_sendmail Driving Me Mad !

Jan 11, 2002

Has anyone else noticed that if you create a non-existing file as an attachment using xp_sendmail in SQL Server 2000, it does not create a copy of that file on the Hard Drive, nor does it format the attached file sensibly if you attach it as a .csv file ?

I am using the procedure below :-

CREATE PROCEDURE mailtest AS

declare @sql varchar (255)

SELECT PERSONID, FORENAME, SURNAME INTO ##TEMP FROM PERSON

SELECT @sql='SELECT * FROM ##TEMP'

IF @@ROWCOUNT > 0

begin

exec master.dbo.xp_sendmail
@recipients = 'itsmarkdavies@hotmail.com',
@Message = 'Test file',
@Query = @sql,
@attachments = 'C:MARKTEST.CSV',
@Attach_Results = 'True',
@Message = '',
@Subject = 'test',
@No_Header = 'True',
@Width = 500,
@Separator = ','

end
DROP TABLE ##TEMP
GO

In the example above, the file MARKTEST.CSV does not currently exist, but the procedure should create it, put it in the root of C: and e:mail it, as it did when it ran under SQL Server 6.5. However, under 2000 it now doesn`t put a copy on the Hard Drive and it formats the .csv file in a very odd manner.

View 1 Replies View Related

CURSOR Is Driving Me Insane!!

Jan 13, 2007

We have a tree structure containing section names.  Each node is a
section name and each section can have subsections.  I have to copy the
tree structure but need to maintain the parent-child relationship
established within the id / parent_id fields.  How do i acheive this?For example i have the treeSection 1   |-Section 1.1Section 2   |-Section 2.1The
"Section" table contains 3 fields: id, parent_id, and caption.  ID is
the identity of the section record and parent_id contains NULL or the
ID of this record's parent to create a child.  So "Section 1" (id=1,
parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1"
(id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).I would
like to copy this sucture to create 4 new sections but they need to
maintain their id/parent_id relationships BUT with new IDs.  For this i
created the following stored procedure:----------------CREATE PROCEDURE [dbo].[CopySection] AS     -- Declare a temporary variable table for storing the sections     DECLARE @tblSection TABLE     (          id int,          parent_id int,          caption varchar(max),     )     DECLARE @newAgendaID int, @newSectionID int;     DECLARE     @tid int, @tparent_id int, @tcaption varchar(max);BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;     -- Copy the desired sections into the local temp variable table     INSERT INTO @tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id;     -- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID     DECLARE c1 CURSOR FOR SELECT * FROM @tblSection ORDER BY parent_id FOR UPDATE OF parent_id;     OPEN c1;     FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption;     WHILE @@FETCH_STATUS = 0     BEGIN                   -- Insert the new Section and record the identity          INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@tparent_id, @tcaption);           SET @newSectionID = SCOPE_IDENTITY();          -- Update the temp variable table with the new identity from the newly created real section in tblSection                -- Update all temp variable records to point to the new parent_id          UPDATE @tblSection SET parent_id = @newSectionID WHERE parent_id = @tid;          FETCH NEXT FROM c1 INTO @tid, @tparent_id, @tcaption;     END     CLOSE c1     DEALLOCATE c1     END----------------The
critical "UPDATE @tblSection" part doesnt seem to update the temp
variable table with the @newSectionID (the actual section identity
obtained after inserting a real record into the tblSection table).  So
in the end the inserted records into tblSection still point to the
incorrect parent_id instead of the copied record's parent_id.Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset?  I've tried using both table variables and temp tables but no luck.

View 2 Replies View Related

DTS Error Problems Driving Me Mad!!!!

Sep 8, 2004

I have a DTS scheduled to run daily. It has run OK for the past few months, but now always fails, the following error appears:

dtsrun.exe - application error

the instruction at 'xxxxxxx' referenced memory at 'yyyyyyy'. The memory could not be 'read'. click on ok to terminate the program.


The dts itself runs fine if started manually, but always fails as a scheduled job, even if its started manually.

Help!!!!!

View 2 Replies View Related

Syntax Error - Driving Me Crazy

Nov 29, 2007

Hi Guys,    I have a syntax error in my SQL SELECT Query that is driving me crazy. All I am doing is adding a conditional with a literal value and that is breaking the query.This Query Works: SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
 This One is Broken:SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
ON ORDER_TANK_LINK.OrderID = 1
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
 Obviously I haven't explained the design of the tables, but because of the fact that I have another query that is so similar working perfectly, I didn't think you'd need it.
The exact error is "Incorrect Syntax Near the Keyword 'ON'". OrderID is definately a value of the ORDER_TANK_LINK Table and is of type int.
Thanks in advance!

View 4 Replies View Related

Parameterized Query Driving Me Crazy

Apr 16, 2008

I'm trying to do a basic update query which is working on other pages but not on this page.  Dim uid As Integer = CInt(Session("uid"))
Dim cmd As New SqlCommand("UPDATE [cvdata] SET [jobCompanyName] = @inputJobCompanyName WHERE [user_id] = @uid", strConn)
With cmd.Parameters
cmd.Parameters.AddWithValue("@inputJobCompanyName", inputJobCompanyName.Text)
cmd.Parameters.AddWithValue("@uid", uid)
End With
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
The funny thing is that if i remove inputJobCompanyName.Text and add a custom value (for example "test") it works.So it doesn't seem to read my updated textfield or something im clueless.Kind regards,
Mark

View 2 Replies View Related

Ugh...timeout Error Driving Me Insane

Mar 28, 2006

ok here's the deal...

I've got 2 identical DB's on the same server, one production, one test. No we don't have a test env, but at least I'm not testing on a prod DB (some people here do, trust me).

I've got a prod VB6 app that used SQL sp's. I've pointed the ADO connection string to the test DB while I make the changes I need to make, and I'll obviously change it back before I'm done etc.

I made some VERY minor changes to one of the sp's (added a variable, changed some stuff), tested thoroughly in query analyzer (runs with no errors in <1 sec), altered my ADO command accordingly and when I executed I get this:

run time error '-2147217871 (80040e31):
[Microsoft][ODBC SQL Server Driver] timeout expired

The connection to the server is fine, I've tested that - it trips on the execution of the command:

Set rstCalls = conHelpArchiveConnection.Execute(SQLQ)

so here's my question: The prod version of this app works like a charm, and the test app times out. I'd rather not toy with the connection timeout setting on the server. Any thoughts on what could be causing this?

Any help would be appreciated, I'm ready to throw in the towel. Well at least until tomorrow morning ;)

View 7 Replies View Related

DTS Export Wizard - Simple Question Driving Me Mad!

Dec 6, 2000

Where does sql server store the server names that show up in the drop down list for choosing a source or destination server?

View 3 Replies View Related

Driving SSIS Packages From ASP.NET Web Services...is It Thread-safe?

Jun 10, 2007

Our application drives SSIS packages from ASP.NET web services. To alleviate some of the package load time overhead application caches SSIS Application object and several instances of "pre-loaded" packages in ASP.NET Application context. As needed the code uses cached SSIS Application instance to execute "pre-loaded" packages. Is this thread-safe?

View 10 Replies View Related

SQL Server 2005 And Management Studio Driving Me CRAZY -Im UPGRADING To 2000

Feb 2, 2006

Is it just me, or are features missing in Management Studio? I mean, where is the "Export Objects" option when exporting data? Why is it so difficult to register a remote server? Why do the object views suck? And most important, why is the documentation  the worst software documentation I have EVER SEEN?!
Things that were easy in Enterprise Manager with Client Network Utility and Query Analyzer seem impossible, difficult, or maybe just hidden in Management Studio. If it is there, the documentation sure doesnt tell you how to find it.
Please someone tell me that it's just me. Tell me that the features are there but Im not doing something right. I feel like putting my head in between my door opening and banging the door against it.

View 2 Replies View Related

TRIGGER: Help With 2 IFTHEN Statements Driving Multiple Inserts Into B_items Table...

Jul 30, 2007

Assuming I should be using values from temp inserted to insure correct record...
Need help coding IF...THEN INSERT statements in following After TRIGGER:

Create TRIGGER trg_insertItemRows

ON dbo.a_form
AFTER INSERT

AS
SET NOCOUNT ON
-- Checkbox Driven:
IF a_form.missingCheckbox = -1 THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')

-- Textbox Driven:
IF a_form.incorrectTxtbox <> 'na' THEN
Insert into b_items (form_ID, parent_ID, ItemTitle)
Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)


Sample code below:

-- Source table the Trigger acts on
Create Table a_form (
form_ID int Not Null,
parent_ID int,
missingCheckbox bit,
missingNote varchar(100),
incorrectTxtbox varchar(50),
replaceWithTxtbox varchar(50)
)

--Target table Trigger inserts into
Create Table b_items (
items_ID int Not Null,
form_ID int Not Null,
parent_ID int,
ItemTitle varchar(150)
)

View 5 Replies View Related

SQL Gurus Help!!!

Oct 16, 2006

My boss has a crazy idea that he want to implement using SQL 2005 and ASP.NET 2. Its something like this... If a user retrieve a record and someone is updating that same record a control on a asp.net page would warn that user that its being updated. Just like a stop light, green for go, yellow for warning, and red for stop. Were implementing optimistic here not pessimistic. Is this possible? I know pessimistic can lock that certain record. But what about in optimistic? Is it possible to monitor or know if a certain record or row in SQL 2005 is being access, updated, and deleted? How?

View 3 Replies View Related

Sql Gurus

Feb 14, 2001

Hi,

How to hide my database with the users, I am deploying my application, i want to hide my database to client. Please help me regarding this.

Rajesh.

View 2 Replies View Related

Gurus

Sep 22, 2000

Our company is looking for fulltime Java/ASP/SQL gurus immediately.Big pay.If interested please send resume to polasp@hotmail.com.
Thanks!

View 1 Replies View Related

SQL Gurus Please Help

May 14, 2002

I have 2 tables :
tab1 col1 char(10), col2 char(10), col3 char(10)....Primary key on col1+col2
tab2 col4 char(10),col5 char(10),col6 char(10)......Primary key on col4+col5


I want to insert a row from tab2 if its not there in tab1.
And I want to update col3 of tab1 from col6 of tab2 if col1+col2 matches col4+col5.

Any thoughts on doing these 2 tasks. I am sure these will be 2 differnet commads.

SQL Gurus please help....

jfk

View 4 Replies View Related

SQL Gurus, Please Help

Jul 23, 2005

Hi,I have a table in which two fields(FirstID, SecondID) together make theprimary key for the table. Let's look at the following example:FirstID SecondID******* ********2 22 32 55 55 78 79 1011 12Here is what I am trying to do. For a given FirstID (say, 2) find allSecondID with this FirstID (2,3,5). Now for these second ID's is therea FirstID other than 2? (Yes, 5). I would say FirstID=2 is related toFirstID = 5. Extending the logic FirstID = 5 is related to FirstID = 8.I am trying to write a SQL (some kind of self join, I think) which willgive me all related FirstID for a given First ID. For First ID = 2 theSQL will give me 5 and 8 in the above example. For First ID = 9 the SQLwill give me 11.Any help will be greatly appreciated. Thanks!!-Raj

View 6 Replies View Related

For All MS SQL Gurus :( Please Advice

Aug 30, 2005

Hi guys,we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again  :( and i m very sad abt it all )..please adviceThanx in advance    ashish

View 2 Replies View Related

SQL Question...for Gurus!!

Oct 24, 2000

We need to return the results of a query as a single string.For example if the query returns 10 rows,all the 10 rows should be concatenated and put it in an output @returnstring. Is that possible in SQL stored procedures ??
Thanks!

View 1 Replies View Related

Calling All GURUS ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! ! !

May 19, 2002

SQL Server Load Balancing -- Any ideas?

There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.

Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?

Will this work? If not, why not?
What will work? Will federated servers work for an entire database??

View 1 Replies View Related

Attn: Sql Gurus

Jul 15, 2002

I need to determine the differences between two tables with the same structure. The primary key for these tables would be a combination of all the columnhs. They use the ID field to join all their records. (i know ID is a reserved word - the client made the tables) Below is the structure of the tables.


TableA
and
TableB
----------

ID int
ComBank int
inboth int
EqorMA int
TranType int


I need to know the rows that Are different in TableA from TableB.

Any help would be appreciated.

Thanks

James

View 2 Replies View Related

TSQL Gurus - Need Your Help

Oct 9, 2001

Hi there!

I'm trying to insert a MSWord/Bitmap/Powerpoint file into a table in SQL 2000. Unfortunately, I haven't had the luck to get it work. Here is my table structure :

Create table tbl_blob (
doc_id varchar(20) not null,
doc_blob image null )

My question is, do you have any script on how I can insert a MSWord/Bitmap file in my blob table. I will be running the script using Query Analyser.

thanks!
100NA

View 3 Replies View Related

Any Cursor Gurus Out There?

Jun 28, 2006

i realize i am starting to ask a lot of questions here so, thanks to all who tolerate me.

In my previous post, i figured out (with the help of r937) how to create a non-identity column using a cursor. I now have some resulting questions.

1. where do cursors live? there is no folder like triggers or stored procedures. is a cursor its own file or is it embedded in a stored procedure?

2. how do i get my cursor to "act" upon my stored procedure? I want the values in my cursor's "relativeposition" column to replace the values in my stored procedure's photo_number column. how do i do this?

BELOW IS MY STORED PROCEDURE:

Code:


ALTER PROCEDURE dbo.sp_NewPersonalPic

(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)

AS

BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END


--SET ANSI_WARNINGS OFF
INSERT INTO PersonalPhotos
(photo_name, photo_location, photo_size, user_name, photo_caption, photo_default, photo_private, photo_number, photo_date)

VALUES (@photo_name, @photo_location, @photo_size, @user_name, @photo_caption, @photo_default, @photo_private, @photo_number, @photo_date)
--SET ANSI_WARNINGS ON

END

RETURN



AND HERE IS THE CURSOR:

Code:


DECLARE RelativePositionCursor

CURSOR LOCAL FAST_FORWARD FOR
SELECT photoId, photo_date
FROM PersonalPhotos
ORDER BY photo_date ASC

OPEN RelativePositionCursor

DECLARE @curs_id INT
DECLARE @curs_date DATETIME
DECLARE @totalRows INT
DECLARE @firstRow INT

SET @firstRow = 0

DECLARE @Results TABLE
(
curs_id INT NOT NULL PRIMARY KEY,
curs_date DATETIME,
RelativePosition INT
)

FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @totalRows = (SELECT COUNT(*) FROM PersonalPhotos)


WHILE(@firstRow <= @totalRows)
BEGIN
SET @firstRow = @firstRow + 1
BREAK
END

INSERT @Results
VALUES (@curs_id, @curs_date, @firstRow)

FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
END

CLOSE RelativePositionCursor

DEALLOCATE RelativePositionCursor

SELECT *
FROM @Results
ORDER BY curs_date

View 1 Replies View Related

Q: Calling All Gurus

Jul 20, 2005

The Scenario:We have some data that can be in three states. One is a saved state,a temporary state, and a complete state.The complete state is the bulk of the data. This data will berequested 1% of the time, maybe even less.The saved state and temporary state will only number 10-15 records.This data will be used 99% of the time, very frequently.So to summarize there will be 10-15 records that will be used ALL thetime…. And 50,000-100,000 that will be used very infrequently.The programmer that I work with wants to combine the tables. I wantto keep the tables sepperate.My logic is that it will be much faster to work with 10-15 recordsthan sift threw 50-100k most of which are unnecessary to 99% of therequests. Also there are frequent deletions/modifications in thetemporary table(10-15 records). The complete table can NOT havemodifications or deletions (50-100k records). The optimization forthese tables is also very different.He says that since the structures are almost identical they should bein the same table, that we should just use a field to denote thedifferent types of records. He insists that my view is wrong and thatI know nothing about databases.I may be new to SQL, but am I really that out to lunch? Would it notmake sense to keep these tables separate?(Thank you for taking the time to read this)Sat

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved