Showing posts with label application. Show all posts
Showing posts with label application. Show all posts

Sunday, March 25, 2012

Changing chart type at run-time

Hello,

I'm coding a web application using ASP.NET 2.0 and I'd like to show users some charts allowing them to chose the chart type at run-time.
I'm building a rdlc (local) report source at run-time starting from a template and working in memory.
Users can select wich rows and columns from a table to display in the chart. They can also chose che chart type (Bar, line, pie etc.)

Now the code works fine the first time it's run. Then the report viewer keeps showing the chart with the same chart type and columns data. Only rows change accordingly to the user selection.

I'm using the following code to initialize the reportviewer:

Dim str As System.IO.MemoryStream = New MemoryStream
rptDoc.Save(str) //->that's the xml doc being saved to a memory stream
str.Position = 0
repView.LocalReport.ReportPath = String.Empty 'Just in case...
repView.LocalReport.LoadReportDefinition(str)
repView.LocalReport.DataSources.Clear() 'Just in case again...
repView.LocalReport.DataSources.Add(New ReportDataSource("DataSet1_01E01000", dt2))
repView.LocalReport.Refresh()

Any suggestion?

Thank you all.Can you share with me how you change the Chart Type at Runtime? Thanks!|||

Sorry for the late reply, I haven't checked forums lately.

As I said I'm building the rdlc (local) report source file at run-time starting from some templates.

The rdlc files are nothing more that XML files and the chart type is defined inside a node. All I do is put the desired type in the right node.

You can also build a rdlc right from scratch and pass it to the ReportViewer component via its LoadReportDefinition method using the overloaded version that gets a IO.Stream as source.

Ask me if you need a sample.

Bye.

|||

You need to reset the report by doing a

ReportViewer1.Reset();//Then setting the datasource and all the properties.
|||

Hello Stojilcoviz,

Can you share with me the sample of your code which changes the chart types at run-time in the RDLC? Thanks.

Changing ASP Application to use SQL Native Client

I am attempting to change an ASP application we've got to use SQL Native Client instead of MDAC and running into a problem.

If I use ODBC driver our application runs without problem, but if I try to use the OLEDB provider certain pages are throwing the following error: Item cannot be found in the collection corresponding to the requested name or ordinal.

The pages that are not working are using ADODB.Command object to execute a Stored Procedure and populate ADODB.Recordset object. The error is thrown when I try to access a field (by name or index) in the recordset. I am using recordsetname(0) or recordsetname("fieldname") to access the data in the recordset.

I have other pages that are working that use this same technique, so I must be missing something else that is different about the two ASP's.

Has anyone else run into this problem switching to the OLEDB provider?

Thanks,
Kelsey

Are you accessing columns in any of the new SQL Server 2005 data types? With ADO you must use DataTypeCompatibility=80 in your connection string. For example

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(local);" _
& "Database=AdventureWorks;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open

See http://msdn2.microsoft.com/en-us/library/ms130978.aspx for more details about using SQL Native Client from ADO applications

If this doesn't help please send more details (preferably source code)

|||

Hi Chris -

We are not accessing any of the new SQL Server 2005 data types yet. We do have DataTypeCompatibility=80 in the connection string. Here is an example of the code that produces the error. "oursp" is a stored procedure that has "url" as a field in the resultset. When I run this page I get the "item can not be found in collection" error. If I change this code to use "Driver={SQL Native Client}" instead of the "Provider=SQLNCLI;" it works without any problem.

Any ideas?

Thanks,
Kelsey

--

<%
'Dim some local variables.
Dim lid, llist, rsGen, Cn, p, cmd

'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")

'Set local variables for Stored Procedure Call
lid = 3
llist = "36,37,38"

'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")

'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250

'Open connection with SQL Native Client Provider
Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=ourserver;"_
&"Database=ourdb;"_
&"UID=ouruser;"_
&"PWD=ourpass;"

'Create Command Object
set cmd = server.CreateObject("ADODB.Command")

'Set active connection
cmd.activeconnection = Cn

'Set command text to Stored Procedure name
'and command type to stored procedure
cmd.Commandtext = "oursp"
cmd.Commandtype = 4

'Create Stored Procedure parameters
set p = cmd.Parameters
p.Append cmd.CreateParameter("@.id", 3, 1)
p.Append cmd.CreateParameter("@.list", 200, 1, 255)

cmd("@.id") = lid
cmd("@.list") = llist

'Create the recordset with results of Stored Procedure call
set rsGen = cmd.Execute

'Trap and display any errors thrown when
'trying to access field in recordset
ON ERROR RESUME NEXT

response.write rsGen("url")

If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If

cn.Close
Set cn = Nothing
%>

|||

So this works when you use SQL Native Client ODBC via Kagera (aka MSDASQL the OLE DB to OBC bride which is now deprecated) - which isn't a supported scenario (see http://msdn2.microsoft.com/en-us/library/ms131035.aspx) but not when you use SQL Native Client OLE DB directly (which is supported).

The next step for me is to know if MDAC OLE DB worked as you expected or if this has only ever worked via Kagera.

One possibility is that your stored procedure is generating multiple results and this is handled differently in the two cases. If this is so, then executing

set rsGen = rsGen.NextRecordset

until you settle on the right result set would solve the problem

It might also be that for some reason no rows are being returned, so a check for BOF to detect this would help.

If you call the stored proc from SQL Management Studio you should be able to see if multiple results are being returned.

I don't think I can get much further without knowing if SQLOLEDB works OK, knowing that a row is definitely being returned and knowing more about what the stored procedure does.

|||

You are correct... there was multiple resultsets coming back from the Stored Procedure call. Doing the rsGen.NextRecordset made the error go away. When I execute the Stored Procedure in SMS it looks like a count is being returned first, then the actual result set. If I set NOCOUNT on in SMS it goes away. Is there a way I can set NOCOUNT on in my connection string, or is this a database setting I need to change?

Thank you very much for the help.

Kelsey

|||

Hi Kelsey,

When SQL Server executes a stored procedure it will send a row count to the client for each non-query statement executed. Setting NOCOUNT ON stop this.

Connection string keywords for SQL NAtive Client are here http://msdn2.microsoft.com/en-us/library/ms130822.aspx

You can't set NOCOUNT ON via the connection string, but you can execute the statement 'SET NOCOUNT ON' on the connection before calling the stored procedure. If you execute the SET statement in the stored procedure itself, its effect will be limited to execution of the stored procedure. See http://msdn2.microsoft.com/en-us/library/ms189837.aspx for reference

|||

Hi Chris,

Thanks for the info. The only thing that is hanging me up now is the fact that we have 3000 stored procedures and many more ASP's calling them. The connection string is in an include, but I don't want to call "SET NOCOUNT ON" for each connection open (page load). I could probably justify spending the time required to go through and add it to each of our Stored Procedures if there was going to be performance gain and/or would enable us to use the latest technology available.

Do you know why SQL Native Client OLE DB treats the count as a result set when MDAC and even SQL Native Client ODBC do not? Is it possible that SQL Native Client OLE DB will be updated to function the same way in the future preventing me from needing to go through our Stored Procedures and adding "SET NOCOUNT ON"?

The main reason we are trying to upgrade is that we've noticed through Profiler Traces that using SQL Native Client OLE DB (or ODBC for that matter) eliminates a large number of sp_cursor type calls that are occuring when our application is using MDAC. With SQL Native Client we've noticed that these calls are handled with a single sp_executesql instead of the many sp_cursor calls.

Thanks again for your time and help.

Kelsey

|||

Hi Kelsey,

I'd like to investigate this further, could you send me sample code that is representative of one of your stored procs that is giving problems and how it is called. If you can do this in VB6 that would be even better without being in an ASP page. If not just code fragments will be enough to get me started.

If you're not seeing sp_cursor calls where you were before, then this indicates that a default result set is being returned where previously a server cursor was used. This would be consistent with the stored proc not being eligable for use with a server cursor (to be used with a server cursor a proc has to be a single select statement or a call to another proc that is a single select statement). If a proc can't be used with a server cursor, then it may return one or more counts/default result sets.

I'd expect OLE DB in MDAC and SQL Native Client to behave consistently, and ODBC in MDAC and SQL Native Client to behave consistently with regard to handling of results. ODBC and OLE DB may not necessarily behave the same though. To get a complete picture I'll need to compare each API running the same proc on both SQL Server 2000 and SQL Server 2005. Are you comparing MDAC to SQL Native Client with the same version of SQL Server?

|||

Chris,

It appears like the sp_cursor calls are not happening in places where we are executing Stored Procedures, but rather only in places where we are opening multiple AD-HOC Queries on the ASP. Here is a sample I have created that uses the PUBS DB. If run this page using old MDAC way the second AD-HOC query on the page is opened using a cursor. If I run this page using SQL Native Client (ODBC or OLEDB) it uses sp_executesql for the second AD-HOC instead of sp_cursoropen.

I have also noticed that if I change the first AD-HOC query to open the recordset using "rsGen.Open CMD.Execute" instead of "Set rsGen = cmd.Execute" the second AD-HOC will no longer be opened using sp_cursoropen.

I'm not sure if this behavior is directly related to the problem I am having with some Stored Procedures returning the count as a seperate resultset and others not, or if it is completely seperate.

I've got a large number of ASP's that have multiple AD-HOCs on them so I really would like to switch to SQL Native Client to prevent these cursor calls from happening.

Here is the sample code for recreating the multi AD-HOC problem. I am going to see if I can work up a seperate sample that shows the Stored Procedures that are returning count as a resultset and those that are not. I will post later on today.

Thanks for all the help.

Kelsey

--

<%
'Dim some local variables.
Dim rsGen, Cn, p, cmd

Const adCmdText = &H0001
Const adOpenForwardOnly = 0
Const adUseServer = 2

'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")

'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")

'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250

'--
'New SQL Native Client way of connecting
'--
'Open connection with SQL Native Client Provider
'Driver={SQL Native Client}

Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=SPILLWAY\DEV;"_
&"Database=pubs;"_
&"UID=test_user;"_
&"MARS Connection=False;"_
&"PWD=test;"
'--


'--
'Old MDAC Way of connecting
'using System DSN
'--
'Cn.Open "pubs", "test_user", "test"
'--


DIM lzip, lfname
lzip = 84152
lfname = "Gary"

'Create Command Object
set cmd = server.CreateObject("ADODB.Command")

'Set active connection
cmd.activeconnection = Cn

'
'Test AD HOC Query CAll #1
'
qryGen = "SELECT lname, fname FROM employee WHERE fname = ?"

cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lfname", 202, 1, 20)
cmd("lfname") = lfname
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
Set rsGen = cmd.Execute
'rsGen.Open cmd.Execute
response.write rsGen("lname")

rsGen.Close

'
'Test AD HOC Query Call #2
'
qryGen = "SELECT au_lname, au_fname FROM authors WHERE zip = ?"

set cmd = server.CreateObject("ADODB.Command")

cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lzip", 129, 1, 5)
cmd("lzip") = lzip
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
set rsGen = cmd.Execute
'

ON ERROR RESUME NEXT

if not rsGen.EOF then
response.write rsGen("au_lname")
end if


If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If

cn.Close
Set cn = Nothing
%>

|||Thanks for this Kelsey. Sample code for a stored procedure would really help to understand what's happening. I don't know of a reason why SQL Server 2005 and SQL Server 2000 should behave differently in the way you are seeing, nor why ODBC and OLE DB should behave differently, so i'd really like to reproduce your issue.|||

Hi Kelsey,

In your include, where you configure the conection, you can add this:

oConn.Execute "Set NOCOUNT ON"

You don't need change all stored. In my case it works!

Regards

Jaime

PD: This post help me to be in the right way, thanks Chris and Kelsey

|||

Hi Jamie,

I had tried this as well.

What I didn't like about this solution is that "SET NOCOUNT ON" is good for the entire DB Session, so it seemed like this would create a situation where it was being called more times then nessecary.

I am not sure exactly how the scope of a DB Session is determined, so it is possible that it doesn't last beyond the life of single page load anyway, but I wasn't sure.

Perhaps Chris could shed some more light on this.

I'm still trying to fight my way back to sample stored procedures... just haven't had the time yet.

Thanks,
Kelsey

|||

SET NOCOUNT ON will last until the connection is closed, or you execute a SET NOCOUNT OFF

I tried your earlier sample code with MDAC and SQL Native Client against SQL Server 2000 and SQL Server 2005 using OLE DB and ODBC. MDAC + ODBC uses a server cursor for the second query, in all other cases you get a default result set. A default result set will generally give the best performance, so this is what you want in most cases. I don't see much point in exploring MDAC+ODBC further since OLE DB is the preferred choice for ADO (and ODBC+ADO isn't a supported scenario for SQL Native Client, since MSDASQL is now deprecated).

Changing all VARCHAR to NVARCHAR database-wide?

Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).

This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?

Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR

thanks!

-BretYou should be able to write a script to change your tables - it will take you a while since you probably will need to:

For each table with Varchar columns
- Create a temp table
- Copy all the data to the temp table
- Remove all FK constraints to the table
- Drop the table
- Create the table with NVarchar columns
- Insert the data from the temp table
- Create the IDENTITY column if applicable
- Recreate all the FK constraints

If you have fewer than 100 tables or so it might be quicker to use EM to do this.

For the stored procs: The quickest way to do the change is to allow updates on system tables. You can change this in EM. Then you can find all the stored procedures in sysobjects, with the actual TSQL in syscomments (in the 'text' column) Of course it's not normally a good idea to update system tables directly, and I'm not 100% sure this will work, but it's worth a shot:


update C
set C.Text = replace(C.[Text], 'varchar', 'nvarchar')
from syscomments C
join sysobjects O
on C.[id] = O.[id]
and O.Type = 'P'

Now obviously there's a lot of things that can go wrong here. You string 'varchar' may exist as a part of a variable name, or column name or some other place where it's not supposed to be replaced by 'nvarchar'. It's possible that a stored procedure is long enough to use more than one row in syscomments, and this will be a problem since 'nvarchar' is one character more than 'varchar' so the update will fail with 'string or binary data would be truncated'.

So be careful and be sure to test every SP. Again you can write a script to execute every stored proc in your DB and check for errors.

Have fun!|||You have to change to Unicode Collation, nothing serious in SQL Server 2000 you can change it down to column level but Unicode in SQL Server is Multi byte unlike .NET where you can choose UTF 8 which is equivalent to ASCII. Yes Stored Procs are stored in .SQL text files that you can open with notepad and can be seen under stored procs in the Enterprise Manager in your Database. Run a search in the BOL(books online) for Mixed collation and ALTER PROCEDURE. If you are now using UTF 8 in .NET you may not run into storage problems except disk space which will double. Stored procs in SQL Server 2000 can be up to 128 megs in size. Hope this helps.

Kind regards,
Gift Peddie

Thursday, March 22, 2012

Changing access db to sql db web application.

I am experiencing problems with databases for the web application that i am developing. The problem is that I have transferred the database from access to sql which initially seemed to work ok. I am now however struggling to solve the problems with one of my fields titled 'notes'. The trouble is that in the original database the notes field contained different notes from different dates for each record, and each note is separated by carriage returns. It is the carriage returns that are now proving to be a problem in the sql database but i am not sure what to do. Any advice would be appreciated.
Thanks
Dan
You can use Word through OLE to store the notes in Access then import the Word file into SQL Server Image column. Hope this helps.|||sorry am not too sure how i would go about using word, what is ole?|||OLE is Object linking and embedding it allows one office application to import files to another by dragging and dropping. Try the link below to get started. Hope this helps.
http://office.microsoft.com/en-us/assistance/HA011472041033.aspx#WhyOLE

Tuesday, March 20, 2012

Changes to table not reflected in report

Hi!

I usa a SQL server for my windows application project. I have created a Crystal report, using .NET dataset connections. I made changes to one of the tables used in the report, and I updated the dataset. I then opened the report and used the "Verify database" feature, but the changes are not reflected. This worked before in the same report with changes to the same table. It seems like it does not work in any of my reports (using different datasets).

I have spent a good hour searching knowledge bases and forums, but found nothing. Anyone here that can help me? (I'm a bit pressed for time, close to deadline and all)

Thanks in advanceHave you checked EnableSaveDataWithReport and made sure its set off (either thru the menu option) or thru your code? Hope this helps|||Also Open the report and Do verify database|||Hi!

Thanks for the answers. The problem is solved for the moment. It seems that the "Verify database" functionality always works after I have run the applikation, a build is not enough. But If I start the app and then try to do the changes it seems to work....

So I consider it a bug that its not enough to changes and save the dependant DS... but as long as I get it to work I keep on. =)

Changes to data not replicated

I have an application that uses web-based merge replication.My publisher is SQL 2005 and my subscriber is SQL 2005 Express.I control the replication with RMO code.If I make changes to the data in both databases using SQL Server Management Studio Express, my RMO code correctly syncs the two databases.However if I make changes to the data at the subscription through my application, these changes are not picked up by the replication process, even though the changes are present if you check the tables through Management Studio.What would cause these changes to not be recognized?Any ideas would be appreciated.

Hi there,

If you have changed the data in such a way as to stop the triggers from firing then these changes wouldn't be replicated.

This is normally associated with a bulk insert of data using something like a bulk insert but it doesn't sound like you are doing that.

To check if the triggers are firing you could check for the existence of the rowguids in MSMerge_Contents for the rows that you have changed.

|||

Merge replication uses triggers to track changes. First thing to check is if your updates are causing the triggers to fire and the transactions are commiting after updates. Is there any way for you to profile the updates made by your app? Do you know what DMLs your app uses to make the changes? If you can confirm that the triggers are firing then the next thing to check is if replication is picking up the changes. Are there any errors returned by replication? You can look at the replication log file for detailed errors. Replication log file is sqlmergx.log and its default location is %program files%\microsoft sql server\90\com.

thx

Sudarshan

|||

JR-J

Thanks for your resopnse.

I checked the MSmerge_Contents table after making changes to one of my application's tables. There was a row added when I made the changes from within Management Studio, but not when I made a similar change from within my application. So it looks as though my application doesn't cause the triggers to fire. The changes appear in the application's table in both cases.

I'm just using simple INSERT, UPDATE and DELETE statements in stored procedures in my application. No bulk inserts.

|||

Sudarshan,

Thanks for your resopnse.

I checked the MSmerge_Contents table after making changes to one of my application's data tables. There was a row added when I made the changes from within Management Studio, but not when I made a similar change from within my application. So it looks as though my application doesn't cause the triggers to fire. The changes appear in the application's table in both cases.

I'm just using simple INSERT, UPDATE and DELETE statements in stored procedures in my application. No bulk inserts.

I checked the log file you mentioned and it did not show any errors during merge. The merge picks up the data that is entered through Management Studio, but does not pick up the data I enter through my application.

Everything in the replication process except for this one problem, seems to be working as expected. We created the publication in Management Studio. I then create the subscription on the client using RMO, and also run the synchronization using RMO. This is my last hurdle in getting my application to work.

Are there any special security considerations you must consider for the triggers to work. I just seems to me that the INSERT statement from my stored procedures should cause the trigger to fire, just as entering data into the table in SQL Management Studio. I'm new to using triggers, so I may not be that good at understanding what they are doing behind the scenes.

|||

I am still having the same problem with not being able to sync the data entered through my application. I even created a new publication on a new SQL 2005 Server to ensure that the problem was not with the publication/server/permissions. I reproducibly get the same error mentioned in my original post. As this is the only step left in my development and deployment of this application, I am really stuck until I can solve this problem. Does anyone from Microsoft have and suggestions as to how to troubleshoot this issue. I'll bet that the soultion will be a simple comfiguration setting, or something like that, however, I don't have enough experience to know where to look. We also have several other applications that we hope to use replication on, if we can get this problem solved.

Thanks for any help.

|||

Ok so it's pretty clear that the triggers aren't firing for some reason.

Would it be possible for you to use profiler against your database whilst running the app and any of the logs event viewer/sql logs/ merge agent logs?

It might also be worth while ensuring that the triggers are infact in place. If you script out your tables you should see some triggers that look like this attached to the tables that are participating in replication msmerge_ins_<GUID>, msmerge_upd_<GUID>, msmerge_del_<GUID>.

Cheers, James

|||I tried installing profiler on the computer which runs SQL Express 2005, but the install would not allow me to because Express is installed. The triggers are on all of the tables, and they work, as the data I enter directly into the table through Management Studio Express is correctly merged. Its just that for some reason, the triggers either don't fire, or don't operate correctly when I add data to the same tables from my application. The application is a VB.NET application and uses stored procedures which INSERT, UPDATE and DELETE data in the SQL 2005 Express database. All of the stored procedures are working as expected. I'll see if I can attach profiler to my SQL Express database from another server.|||

yes sorry I forgot you had run the inserts etc manually.

if you have the normal client tools elsewhere hopefully you'll be able to connect across using profiler. I assume there are no error messages in the logs for your issues.

Cheers, James

|||

James,

I was unable to get profiler to connect to my subscriber database.

|||

Just a thought.

Do the row GUIDs need to be named RowGUID in each table. My row GUIDs are named according to the subject of the table, but then marked as a row GUID in the column's properties. This must not make a difference, as the triggers seem to work as expected in Management Studio.

Does anyone know if the Table Adapter's in Visual Studio require any special setup to allow the triggers to fire from within the application. I use DataSets/Table Adapters to design my Queries and Data Access Layer.

|||

No they don't need to be named rowguid in order for everything to work.

What problems are you experiencing connecting profiler to your subscriber?

Sorry I don't know anything about Table adaptors so I'll have to leave that to someone else.

|||

I found the problem.

In Visual Studio you must create a connection string to connect to the databases in your application. One of the advanced features of the connection is a parameter called Replication. It has values of True and False. It was set to True, which seemed logical, since I wanted to do replication of the database. However, True means that the application is recognized as a replication agent. Replication triggers do not fire for replication agents. I set the value to false, and now everything works as expected.

Thanks for your help.

sql

Sunday, March 11, 2012

change user login at runtime

Hi, some one help me with this please
Our application is developed in Dot Net with Sql Server 2000 as a back
end.We have created users in our application (Dot Net) that will be
physically created in the back-end in the DB to which we will be connected
other than SA. These users will be authenticated by an administrator user to
login first time. After logging the application a tree menu appears to which
the forms and reports are attached . We are trying to implement a scenario
that the user will be given privileges of select , update, insert, delete on
the forms when they will double click it and open them. After closing the
form the privileges will be revoked from the user on the certain tables
associated with that form.
Now we cannot understand which user will be giving those privileges to the
connected user as we dont want to use SA. As this could become a problem at
the deployment time as we do not want to hard code the "SA" user. I have
seen "CONNECT To" statement that could change the login at runtime but it is
not running with these commands
EXEC SQL CONNECT TO :svr USER :usr;
Or
EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";
Or
EXEC SQL CONNECT TO gizmo.pubs USER sa;
RegardsHi Shahzad:
I think you can use the connectstring to applied this.
You can change the connect string by the value you get from the asp and
change the connectstring and then connect to the sql server, I think it is
not very complexity.
Best Wishes
Wei Ci Zhou

change user login at runtime

Hi, some one help me with this please
Our application is developed in Dot Net with Sql Server 2000 as a back
end.We have created users in our application (Dot Net) that will be
physically created in the back-end in the DB to which we will be connected
other than SA. These users will be authenticated by an administrator user to
login first time. After logging the application a tree menu appears to which
the forms and reports are attached . We are trying to implement a scenario
that the user will be given privileges of select , update, insert, delete on
the forms when they will double click it and open them. After closing the
form the privileges will be revoked from the user on the certain tables
associated with that form.
Now we cannot understand which user will be giving those privileges to the
connected user as we dont want to use SA. As this could become a problem at
the deployment time as we do not want to hard code the "SA" user. I have
seen "CONNECT To" statement that could change the login at runtime but it is
not running with these commands
EXEC SQL CONNECT TO :svr USER :usr;
Or
EXEC SQL CONNECT TO "gizmo.pubs" USER "sa";
Or
EXEC SQL CONNECT TO gizmo.pubs USER sa;
RegardsHi Shahzad:
I think you can use the connectstring to applied this.
You can change the connect string by the value you get from the asp and
change the connectstring and then connect to the sql server, I think it is
not very complexity.
Best Wishes
Wei Ci Zhou

Friday, February 24, 2012

Change sql agent login user

In keeping with the microsoft suggestion, I changed the
mssql agent login to a user. Now My application does not
run, the error log reports the following
RegOpenKeyEx of "SOFTWARE\Microsoft\MSSQLServer\" failed:
Result Code = 0x00000005
I have tried adding users, and domain users. it only works
if I add the administrators from the built in group.
Please help!Hi!
Do please check if the article at
http://support.microsoft.com/defaul...kb;en-us;283811 is helpful
for you.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"shivi" <infor@.villageideas.com> wrote in message
news:c2b901c3ed05$20714760$a501280a@.phx.gbl...
> In keeping with the microsoft suggestion, I changed the
> mssql agent login to a user. Now My application does not
> run, the error log reports the following
> RegOpenKeyEx of "SOFTWARE\Microsoft\MSSQLServer\" failed:
> Result Code = 0x00000005
> I have tried adding users, and domain users. it only works
> if I add the administrators from the built in group.
> Please help!

Sunday, February 19, 2012

Change servername based on logon server

Hi
We are to use an application on two replicated sites, the application uses a
user dsn.
We propose to use a reg file attached to a logon script to amend the server
name so that users with laptops can move transparently between sites.
Is this the best method or is there another?
Thanks
BA registry file? Sounds dangerous.
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OoEZPKocGHA.1324@.TK2MSFTNGP04.phx.gbl...
> Hi
> We are to use an application on two replicated sites, the application uses
> a
> user dsn.
> We propose to use a reg file attached to a logon script to amend the
> server
> name so that users with laptops can move transparently between sites.
> Is this the best method or is there another?
> Thanks
> B
>|||Hi
Yes the server name is stored in the HKCU.
We use reg files regularly to update other required settings to the HKCU.
But is there a reccomended method?
Thanks
B
"Mike C#" <xxx@.yyy.com> wrote in message news:tAR8g.506$Ut2.358@.fe09.lga...
> A registry file? Sounds dangerous.
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:OoEZPKocGHA.1324@.TK2MSFTNGP04.phx.gbl...
uses[vbcol=seagreen]
>|||That's probably the easiest method, but it would be really easy for some to
alter the reg files and screw up a registry pretty badly. For constantly
changing servers, maybe a File DSN would be a more secure bet? For security
purposes, if you want to mess directly with the registry, maybe adding a
short application to the logon script that only touches the registry keys
you really want to change would be a more secure bet? I'd just be a little
hesitant about installing nondescriminating reg files on user computers.
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:eLjhcPtdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hi
> Yes the server name is stored in the HKCU.
> We use reg files regularly to update other required settings to the HKCU.
> But is there a reccomended method?
> Thanks
> B
> "Mike C#" <xxx@.yyy.com> wrote in message
> news:tAR8g.506$Ut2.358@.fe09.lga...
> uses
>|||Hi Mike,
Thanks for your post, and sorry for the delay in replying, sadly our
Software Vendor only Supports User and System DSNs.
The logon script / reg file is in a secure location on the server where
users do not have write access, it will run on each boot so if the user
messes with the registry: after a boot we are sorted again. Thankfully I
think they all have not even heard of a registry let alone know how to open
regedit :-)
Thanks
B
"Mike C#" <xxx@.yyy.com> wrote in message news:C7t9g.164$yJ6.58@.fe12.lga...
> That's probably the easiest method, but it would be really easy for some
to
> alter the reg files and screw up a registry pretty badly. For constantly
> changing servers, maybe a File DSN would be a more secure bet? For
security
> purposes, if you want to mess directly with the registry, maybe adding a
> short application to the logon script that only touches the registry keys
> you really want to change would be a more secure bet? I'd just be a
little
> hesitant about installing nondescriminating reg files on user computers.
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:eLjhcPtdGHA.3364@.TK2MSFTNGP05.phx.gbl...
HKCU.[vbcol=seagreen]
>|||Hi Ben,
I'd still be concerned that the registry file could be altered at some point
before being installed on the system (a disgruntled IT employee? hacker?
curious employee? who knows...) Of course that's just my initial thoughts,
but I'd be hesitant to send .reg files to be directly imported into the
registry. Personally I prefer the idea of some form of small validating
application called from the script to ensure direct registry updates are
within a very well-defined and narrow scope. In fact, since you're doing
DSNs, I might even go a step further and suggest using the ODBC API to
update them instead of writing the registry directly. Presumably the ODBC
API offers additional validation of the DSN as well (i.e., length of DSN
name, etc.)
Anyway, best of luck!
Mike
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OURbJ2qfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Hi Mike,
> Thanks for your post, and sorry for the delay in replying, sadly our
> Software Vendor only Supports User and System DSNs.
> The logon script / reg file is in a secure location on the server where
> users do not have write access, it will run on each boot so if the user
> messes with the registry: after a boot we are sorted again. Thankfully I
> think they all have not even heard of a registry let alone know how to
> open
> regedit :-)
> Thanks
> B
> "Mike C#" <xxx@.yyy.com> wrote in message news:C7t9g.164$yJ6.58@.fe12.lga...
> to
> security
> little
> HKCU.
>

Change server name in MSSQL cluster

Hi all, I've got PDC and BDC Windows NT 4.0 Servers (Enterprise edition) installed in the MS cluster. The clustered application is MS SQL server 7.0. Now I need to change names of both nodes - PDC and BDC Windows NT 4.0 Servers. Has anyone any experience with such operation ? Is it too risky ? Thanks a lot for any hint MichalChanging the server names will break your SQL servers.

I did this on a standalone (changed the server name) and SQL stopped working. The fix according to Microsoft was to reinstall SQL on the servers. When the installation starts it will see that all the components are already installed and then see the name resolution conflict and fix it.

This worked for the standalone but I dont' know about the cluster.|||Furthermore, you can not change the name of a PDC without reinstalling NT 4 server.

Sunday, February 12, 2012

Change of Execution plan

Hi,

We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.

Need help on this, otherwise it seems we might have to move back to 2000.

Thanks in Advance
Ritesh

Did you update statistics after you upgraded? It seems unlikely to me that SQL 2005 would provide a less efficient execution plan than SQL 2000, given accurate statistics data. I'd update statistics and then recompile all the stored procedures first. I'd also be interested to see what you had to tune in the stored procedure you changed to accommodate SQL 2005.|||

Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.

Change of Execution plan

Hi,

We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.

Need help on this, otherwise it seems we might have to move back to 2000.

Thanks in Advance
Ritesh

Did you update statistics after you upgraded? It seems unlikely to me that SQL 2005 would provide a less efficient execution plan than SQL 2000, given accurate statistics data. I'd update statistics and then recompile all the stored procedures first. I'd also be interested to see what you had to tune in the stored procedure you changed to accommodate SQL 2005.|||

Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.

Friday, February 10, 2012

Change Notification needed in .NET

Hi,
Think I want an application (say in C#) that would say "Hello" when a
particular table in the database get 100 rows. For this I could use a
thread that would check the rowcount in every say 1 minute. But the
problem is 1 minute is too much for an interactive application. I can
use lower time interval but it would increase database activity.
Suppose rowcount cross 100 mark once or twice in a month but need to be
responded immediately. Worst 10000 pepole want "Hello" in their
application. Then all client application would be checking it and
hence increasing network traffic and database activity.
Is there any way in SQL Server 2005 that would enable us to get events
in .NET applications? In older SQL Server versions?
Thanks
MukeshYou might want to look into the "Query Notification" concept in 2005. You'd
get notified when a
modification in the table has been don in a way that the result of a query h
as changed, and it is
without polling. Not sure it would be the best thing here, but worth looking
into.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mukesh" <cmukesh19@.gmail.com> wrote in message
news:1148729766.229925.191570@.j73g2000cwa.googlegroups.com...
> Hi,
> Think I want an application (say in C#) that would say "Hello" when a
> particular table in the database get 100 rows. For this I could use a
> thread that would check the rowcount in every say 1 minute. But the
> problem is 1 minute is too much for an interactive application. I can
> use lower time interval but it would increase database activity.
> Suppose rowcount cross 100 mark once or twice in a month but need to be
> responded immediately. Worst 10000 pepole want "Hello" in their
> application. Then all client application would be checking it and
> hence increasing network traffic and database activity.
> Is there any way in SQL Server 2005 that would enable us to get events
> in .NET applications? In older SQL Server versions?
> Thanks
> Mukesh
>|||In older versions of SQL Server you could have a trigger that calls
XP_LOGEVENT. Then you can use an NT Event Log hook to get your
notification. I know Microsoft has some examples of hooking the Event Log
on their web site. I had some code lying around in java script that did
this, but can't find it at the moment.
"Mukesh" <cmukesh19@.gmail.com> wrote in message
news:1148729766.229925.191570@.j73g2000cwa.googlegroups.com...
> Hi,
> Think I want an application (say in C#) that would say "Hello" when a
> particular table in the database get 100 rows. For this I could use a
> thread that would check the rowcount in every say 1 minute. But the
> problem is 1 minute is too much for an interactive application. I can
> use lower time interval but it would increase database activity.
> Suppose rowcount cross 100 mark once or twice in a month but need to be
> responded immediately. Worst 10000 pepole want "Hello" in their
> application. Then all client application would be checking it and
> hence increasing network traffic and database activity.
> Is there any way in SQL Server 2005 that would enable us to get events
> in .NET applications? In older SQL Server versions?
> Thanks
> Mukesh
>

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu

Change Login-Info for Activity-Monitor and sp_who

Hi

we have one 'application'-user in sysusers that makes the connect to SqlServer for all users, for example:

Application Login-User: Thomas

DB-Connect-User: AppUser

With this solution, in Activity-Monitor or with sp_who I don't know, what is the real name of the connected user.

Any possibility to change the login-information after the connect, so that i can see 'Thomas' in Activity-Monitor or with sp_who?

Thanks for your help.

Thomas

You will see only Thomas and AppUser that are used by web server to connect to server and use objects of db.

I think in your db there is a table that contain users with encrypted passwords and if you want to see who access your application when the user app try to connect with or without succes , using sp that making the verification you can put somehere in a table the information that the user is connected. Querying that table you can see who is connected.

|||

Since Login to User is 1 to M , you will not get the result as you mentioned in either SP_Who or Activity monitor. You can make a customized sp from sp_who and you can use that. Just you need to join with sys.database_principals

(a) SP_Helptext sp_Who -- Will give u the source code of sp_who

(b) Join with sys.database_principals .

the following script is taken from sp_who and then joined with sys.database_principals . sys.database_principals is database specific. So you should run this script in the particular database to get the desired result.

select spid , ecid, status

,loginame=rtrim(loginame)

,hostname ,blk=convert(char(5),blocked)

,dbname = case

when dbid = 0 then null

when dbid <> 0 then db_name(dbid)

end

,cmd

,request_id ,sdp.Name as UserName

from master.dbo.sysprocesses inner join

sys.database_principals sdp on sdp.sid=master.dbo.sysprocesses .sid

Madhu