How can I restrict bandwidth between to SQL servers

Hi
I have two SQL2000 servers in different sites, once a day approximately
1M of data in the form of a large update is required to be transfered
between the 2. We have use of a 2M pipe between the servers but there
is no quality of service, the other users on the pipe are traders so
there must be no interruption in the quality of their bandwidth at any
time.

Is there any way of throttling back the data transfer between the two
servers to restrict its bandwidth use. Obviously we want to retain the
max bandwidth on our local network.
The pipe is administered by a seperate company so we do not have admin
access to their gateways, routers etc.. so a solution which we can
implement on our database servers would be the easiest.
I am not sure if this is the right newsgroup for this but any
information would be great

Thanks
MarkMSSQL itself doesn't have any way to manage this - you would have to
work it out at the OS or network level. From the database side you
could update only a few rows at a time, with pauses in between, but
it's not a great solution and wouldn't be suitable if you need
everything in a transaction. Or perhaps export the data to flat files,
send them in chunks, then reassemble them and do the update on the
server, but again that seems very clumsy.

You might also want to post in a networking group - there may be an
OS-level solution which would do what you need,

Simon

How can I represent checkboxes in my report?

in my input form i have checkboxes and textboxes, if i click on a checkbox the textbox beside it enables, the textbox field is optional so the checkbox alone has a value that needs to be saved to the database. i have a report.rdlc, i want to display the checkboxes and the textboxes, what should i do?

I moved this thread to the SQL Server Reporting Services section, since it appears that you're asking how to put checkboxes and text boxes onto a SRS report (the rdlc file).

|||

icemart525 wrote:

in my input form i have checkboxes and textboxes, if i click on a checkbox the textbox beside it enables, the textbox field is optional so the checkbox alone has a value that needs to be saved to the database. i have a report.rdlc, i want to display the checkboxes and the textboxes, what should i do?

..or maybe I'll use an image, right?

sql

How can I re-install the Northwind database in SQL Server Management Studio Express?Use SqlDataS

Hi all,

In my VB 2005 Express, I created a Windowds Form application "shcDataSet" that used 1 SqlConnection, 1 SqlDataSet and 3 SqlDataAdapters associated with the Northwind Database in my SQL Server Management Studio Express. The SqlConnection had "User Instance" in the following ConnectionString: Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwnd.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True. The 3 SqlDataAdapters were for the Northwind files "Customers", "Orders" and "Order Details" used in the SQLDataSet "AllOrders" with a AllOrders.xsd file. I ran the "shcDataSet" applicatyion and it worked fine. After the execution of "shcDataSet", I checked the Northwind database in my SQL Server Management Studio Express and clicked on "+" in front of Northwind database-I did not see any file showed up and I got the following error message: Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum) Additional information: one or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error:5173). If I executed the "shcDataSet" application again, I got a new error: SqlException was unhandled - Cannot open user default database. Login failed. Login failed for user 'myPC##\myName' ->daCustomers.Fill(AllOrders11, "Customers"). I have 3 questions to ask: (1) How can I re-install the Northwind database in SQL Server Management Studio Express? (2) When I use the .Fill Method, do I have to tell the SQL Server Management Studio Express to load and/or unload the Northwind files "Customers", "Orders" and "Order Details"? (3) After I executed the "shcDataSet", should I Upload the 3 Northwind files back to the SQL Server Management Studio Express? Please help and advise. Thanks, Scott Chang

this is a very common problem in SQL Server Express user intance. YOu are using user instance feature of SQL Server 2005 Express. If you are not intended to do it.. then change the connection string.

Refer this blog :

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Madhu

|||

Hi Madhu, Thanks for your response.

I am new in using the "User Instance" in SQL Server Express and VB 2005 Express. This is the first time I tried to learn it by copying the example from a book. I plan to apply the same technique to my own chemical database and applications for my data management. I do not understand what you said by "If you are not intended to do it.. then change the connection string". Please be specific and give me more details about your statement.

I read the blogs you mentioned and the MS Technical Articles "SQL Server 2005 Express Edition User Instances", "How to: Move Through a DataSet with the Windows Forms BindingNavigator Control", "Connecting to Data with Server Express/Database Express". etc. and I still do not get what you said and the User-Instances Connection, SSEUtil, instance_pipe_name, etc. Please answer my 3 questions stated in my last post in the easy terms for me.

Thanks,

Scott Chang

|||

Hi Madhu, Thanks for your response.

I am new in using "User Instance" and I do not understand what you meant by "If you are not intended to do it, then change the connection string". I want to use it and I just want to see what happened and whether the Northwind Database is in SQL Server Management Studio Express or not. This is the first time I learned the User Instance by doing the example of using User Instance example from a book. I plan to apply the User Instance tecjnique to my chemical database and data management applications in VB 2005 Express-ADO.NET 2.0 programming. If I loose my chemical database during the application of User Instance, it is not possible for me to retrieve the chemical database. I read the blogs you cited and the MS technical articles "SQL Server 2005 Express Edition User Instances", "Connecting to Data with Server Explorer/Database Explorer", "How to: Install and Troubleshoot Database Components for Samples", etc. I do not understand why SSEUtil and Instance_Pipe_Name are used in the connecting to User Instances. Please clarify what you meant by "If you are not intended to do it, then change the connection string" and answer my 3 questions in a easy-to-undersatnd way for me.

Thanks,

Scott Chang

|||

normally, Userinstance of sql server is used unknowingly. that is what i mentioned in my previous post. if you really want to use userinstance of sql server express ... then its ok.... This feature is only available in SQL Server Expess. This one kind of embeded datbase with your application. Handling of Normal and user instance database are totally different. Its already mentioned in above mentioned link. My question is, do you need to use this feature in your application ... this will ofcouse increase the managebility of your Visual studio application (no need to create the database explicitly when you distribute the application) but it has lot of limitations also

Madhu

|||

Hi Madhu,

I am lost completely now and do not want to do the User Instance any more - I just want to repair/restore the Northwind Database in my SQL Server Management Studio Express now. This morning, I downloaded the Northwind and Pubs Sample Databases from http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en/ and saved SQL200SampleDb.msi in my PC. I double-clicked the SQL2000SampleDb.msi file to install the Northwind database - it ran, but the Northwind database in my SQL Server Management Studio Express had no files to show, when I clicked the '+' in front of the Northwind node!!!? What is wrong in my PC? Do I have to delete the User Instance=True of the Connection String of my "shcDataSet" project when executed? Please help me in restoring/repairing the Northwind Database in my SQL Server Management Studio Express?

Many Thanks,

Scott Chang

|||

post the connection string... if you don't want user instance.. yes you need to change the connection string...

For Connection string Refer : http://www.connectionstrings.com/?carrier=sqlserver2005

And also ... sql server 2005 sample datbase is Adventureworks... Northwind still you can use , but many new features like schema and all will not be available in that. you can download Adventureworks database from the link mentioned below

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

Madhu

|||

Hi Madhu, Thanks for your response.

1) I went to the CANtrol Panel => Add or Remove Program => removed the old Microsoft SQL Server 2005 Samples. Then I downloaded the new Microsoft SQL Server 2005 Samples from

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en (as you said).

I checked the SQL Server Management Studio Express and looked at the Northwind Database and I still did not see any files in the Northwind Database!!!? Please tell me how I bring the files of Northwind Database to the SQL Server Management Studio Express (i. e. how to repair it.).

2) I do not understand whay you mean by "if you don't want user instance.. yes you need to change the connection string...

For Connection string Refer : http://www.connectionstrings.com/?carrier=sqlserver2005". Please elaborate this matter if you can. Pardon me for asking this thing.

Thanks again,

Scott Chang

How can I process my cube from a windows form?

I prefer c#.net but if you only know VB.net that would be helpful. Even if you know how to do it from a command prompt, that would be very helpful. I am using SSAS2005.

This is the xmla file that it generates when I go to script the command, I dont know if this helps.

Code Snippet

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
'>http://schemas.microsoft.com/analysisservices/2003/engine">http://schemas.microsoft.com/analysisservices/2003/engine">
; <Process xmlns:xsd="http://www.w3.org/2001/XMLSchemahttp://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema</A< A>>" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instancehttp://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance</A< A>>" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2http://schemas.microsoft.com/analysisservices/2003/engine/2">http://schemas.microsoft.com/analysisservices/2003/engine/2</A< A>>" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
'>http://schemas.microsoft.com/analysisservices/2003/engine/2/2">http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
; <Object>
<DatabaseID>MRSTATSanalysis</DatabaseID>
<CubeID>MRSTATS</CubeID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Batch>

Thank you all.

I found this code in a different thread, but it should work here. This code reads the xmla from a file.

Code Snippet

using System.IO;
using Microsoft.AnalysisServices.AdomdClient;

public class XmlaExecutor
{
public static void Main(string[] args)
{
TextReader tr = File.OpenText(args[0]);
string xmla = tr.ReadToEnd();
tr.Close();
AdomdConnection cn = new AdomdConnection("Data Source=localhost");
cn.Open();
AdomdCommand cmd = cn.CreateCommand();
cmd.CommandText = xmla;
cmd.ExecuteNonQuery();
cn.Close();
}
}

|||Thanks a lot, that worked perfectly. The only thing I had to do was add the .NET reference to Microsoft.AnalysisServices.AdomdClient.|||

You can also use AMO (Microsoft.AnalysisServices.dll), the management object model for Analysis Services 2005. ADOMD.NET (Microsoft.AnalysisServices.AdomdClient.dll) is mostly for querying data, while AMO is for management tasks like process, backup, restore, create, delete objects.

Quick info on AMO: http://adriandu.spaces.live.com/

Sample AMO code to create and process a partition: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516918&SiteID=1

Adrian Dumitrascu

how can I populate data in a one-to-one relationship

Here is what I have and (somewhat understand). I'm using Visual WebDeveloper 2005 Express Edition and have setup my application to use form authentication(which automatically creates the ASPNETDB.MDF file with several default tablesand views). I'm using the CreateUserWizardwhich is fine…but I need to collect additional information like (firstname,lastname, address…and on..). What I'vedone. I've created a tabled namedUserProfile and set UserId as the primary key (uniqueidentifier). I then setup a 1-to-1 relationshipbetween aspnet_Users and UserProfile (which I think is correct). On my UpdateContactInfo.aspx page (whereusers go to update their personal information) I use a hidden label control(UserValue) to receive the UserId during the page_load event as below:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
UserValue.Text = Membership.GetUser().ProviderUserKey().ToString()
End Sub

Now with the UserID available I need to populate theUserProfile table with the UserId, firstname, lastname, address of thecurrently logged in user. How can I do this and am I on theright track..?

Then you can use a SqlCommand to issue the insert command to the UserProfile anywhere you like in your code:

using (SqlConnection conn= new SqlConnection(@."Data Source=(local);Integrated Security=SSPI;Database=mydb;");
{
connection.Open();

SqlCommand cmd = new SqlCommand("insert into UserProfile select @.UserId, @.firstname, @.lastname, @.address", conn);
cmd.Parameters.Add("@.UserId", SqlDbType.UniqueIdentifier);
cmd.Parameters["@.UserID"] = UserValue.Text;
//add all parameters and set their values
cmd.ExecuteNonQuery();
conn.Close();

}

|||

Why aren't you using the built-in profile system?

Why are you setting a hidden form field (Which can be spoofed) to store the userid, instead of a session variable or just looking it up when you want to do the update?

|||How do I go about retrieving the current user's [UserId] information from the session variable? And what is stored inside the session variable by default. Also... I've done some reading on the built-in profile system and have walked through the [ASP.NET HOW DO I Video Series: Profiles and Themes ] . My next question is...what if I wanted to store items in my profile that is not of type: string. So far all the property values that have been stored in the profiles table are all storing as type: string. I would like to store some of these properties as an integer type.|||

The profile can store anything that can be automaticlly converted to and from a string (That includes datetime, integer, float, decimal, and a lot of other obejcts).

You can retrieve the state of the session variable in a sqldatasource by selecting the parameter as type "Session" and the sessionkey of whatever you used.

In code after authentication, for example, put this code:

Session("UserID")=membership.getuser().whatever (Sorry, I forget the exact syntax)

Then in sqldatasource, the parameter type is session, with the key being "UserID".

|||Thanks for all the help! I'm now using the profile to store user information (firstname, lastname, age, address...). However I think I've ran into another problem :( Becuase the profile date is stored in a serialized format blob... I cant easily query the database and perform common sql routines. I would like to display all users and their profile data within a gridview but can't figure how to do that. I've read the TableProfileProvider how-tohttp://www.asp.net/sandbox/samp_profiles.aspx?tabindex=0&tabid=1 but the source code is not in VB.NET. Can someone advise... thanks.|||

I had to write my own T-SQL function to pull out profile properties from the blobified format. Not elegant, and definately not fast. You would probably be better off pulling back usernames in a dataset, then calling the membership object to pull the properties and appending them to the dataset, then binding that to the datagrid.

I wish I had time to finish my profile provider. Unfortunately, I've been to busy to go back to revist it since my deblobify function works ok for what I needed at the time. If you want to use the TableProfileProvider, you should be able to compile it into a dll, then reference it from within your VB.NET web application just fine. But I haven't tried it, as I usually just convert stuff from C# to VB myself, and leave it as source in the project.

How can I make a script out of a table data ?

Hello,

I want to take a table (SQL server) and show all it's data in a form of INSERT... (text file)
so that I can show it on a web-page and just paste the INSERT text into aMyTableData.sql
and then I can just run this script on the Query analyzer and fill my table with data

I need it so I can backup my DATA both in English and other languages...
(a replacement for the DTS packages that gives me hard time with the LOCALE / UNICODE translation)

did anyone already made such program ?
where can I find something like this ?My initial thought is "Don't do it!"!

You are planning on having a textbox that the user will have complete control over, and then you are taking the contents of that textbox and turning it into a SQL script that will be run?

This is extremely insecure and could result in all sorts of malicious script being executed against your database. Even in a trusted intranet environment, I would not write such functionality.

My 2 cents.

Terri|||Here's an app that will do it in its Export feature: http://www.microsoft.com/downloads/details.aspx?FamilyId=F0D03472-5E6C-459E-A6D8-6745A729C3C9&displaylang=en

Terri, your concern is valid, but I'd assume (or at least hope) that the page on the site for doing this would be completely secure.|||

My advice is to use the following softwarehttp://www.denovations.com/products/sqlpopulator/ It's looks handy...

how can I get the list of tables and views together in the database by the dbconnection.ge

I have tried
SqlConnection.getSchema("Tables"). it returns me a list of all tables while getSchema("Views") returns me the views in the form of DataTable.
But can we have a way to get them together in the same DataTable as a return result? because I want to read the rows by DataTable.CreateDataReader() later.

select

Namefromsysobjectswhere typein('v','u')and status>-1

Will return a list of all the user tables and views.

|||

In SQL Server 2005, you should use system view for the same result.

select * from sys.objects where type in ('v','u')