I have an application created with VS 2005 and VB 2005 using a Compact Edition Database. I created some tables that I no longer use or want. How can I permanently delete them from the database?
Connect to the database a run .ExecuteNonQuery:
Code Snippet
DROP TABLE tablename
You may have to run
Code Snippet
ALTER TABLE tablename DROP CONSTRAINT constraint_name
first, if you have foreign key constraints.
Hello,
I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?
Any idia?
Thread moved to the appropriate forum|||hi,
SQLExpress setup boostrapper accept "parameters" to define the "final" settings of the installed instance at install time..
the parameter you are looking for is SECURITYMODE=SQL to enable standard SQL Server authenticated connections..
please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx for all supported parameters..
regards
|||Thank for sujession, but still it is not work.
i have entered following line for change SQL Server Security Mode Windows Auth. To SQL Auth.
Start /wait setup.exe /qb INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345
i have also tried this :
Start /wait setup.exe /qb UPGRADE=Client_Components INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345
but still it is not work. i got the same error when i run my program "Login fail for user sa. The user is not associated with a trusted SQL Server connection.
|||hi,
the exception is correctly related to not enabled SQL Server authenticated connections..
but the SECURITYMODE=SQL parameter is there for the case, and it "must" work as expected, as it really does since MSDE 2000 installer...
please pardon me, but are you sure you are later connecting to the correct SQLExpress instance?
regards
|||Yes i connecting to correct SQLExpress instance.
but don't warry, it's solve now bcoz i directly change parameter in package.xml(Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en) file.
Hello,
I have created one application in visual studio 2005 and also created setup project of that application. now i want to install SQL Server Express edition with my application. so i have checked SQL Server Express 2005 in Setup Project Properities(Prerequisites...). now i want to change SQL Secirty Mode during setup. and i don't know how can we do this?
Any idia?
Thread moved to the appropriate forum|||hi,
SQLExpress setup boostrapper accept "parameters" to define the "final" settings of the installed instance at install time..
the parameter you are looking for is SECURITYMODE=SQL to enable standard SQL Server authenticated connections..
please have a look at http://msdn2.microsoft.com/en-us/library/ms144259.aspx for all supported parameters..
regards
|||Thank for sujession, but still it is not work.
i have entered following line for change SQL Server Security Mode Windows Auth. To SQL Auth.
Start /wait setup.exe /qb INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345
i have also tried this :
Start /wait setup.exe /qb UPGRADE=Client_Components INSTANCENAME=SQLEXPRESS SECURITYMODE=SQL SAPWD=pwd12345
but still it is not work. i got the same error when i run my program "Login fail for user sa. The user is not associated with a trusted SQL Server connection.
|||hi,
the exception is correctly related to not enabled SQL Server authenticated connections..
but the SECURITYMODE=SQL parameter is there for the case, and it "must" work as expected, as it really does since MSDE 2000 installer...
please pardon me, but are you sure you are later connecting to the correct SQLExpress instance?
regards
|||Yes i connecting to correct SQLExpress instance.
but don't warry, it's solve now bcoz i directly change parameter in package.xml(Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\SqlExpress\en) file.
Starting with my existing, working page which has one DataSourceControl and one Gridview bound to the control.
1. Add a ReportViewer to the page.
2. Select "Design a new report" from the viewers smart-tag
3. Add a Table to the report
4. Add a DataSet to the project
5. Add a DataTable to the DataSet
6. Add 2 columns to the DataTable (assuming the case where my DataSourceControl will be returning 2 columns)
7. Now that I've added the DataSet and DataTable, it appears in the ReportViewer's "Website Data Sources" toolbox, so I drag "Column1" and Column2" out onto the table. This gives me 2 headers and the data cells look like "=Fields!Column1.Value" etc.
8. Only after I have added the columns can I go back to the ReportViewer's smart tag and select "Choose Data Sources". This brings up a dialog with a 2 column table with the headings "Report Data Source" and "Data Source Instance". The ReportDataSource is already set to "DataSet1_DataTable1" and the "DataSourceInstance is defaulted to "(None)" but has a drop-down with which I can either select the instance of my DataSourceControl which is on the page (DataSourceControl1) or else select "<New Data Source...> which brings up a Data Source Configuration Wizard. I choose to select my Data Source Control instance.
9. I select "Rebind Data Sources" on the Report Viewer's smart-tag. This adds a new "Object Data Source" to the page, but I don't see how this has any relation to my DataSourceControl. It's SelectMethod is set to "GetData" and its TypeName is set to "DataSet1TableAdapters." which doesn't make much sense to me.
What am I missing? Is there a simpler way to do this? Can I bind a ReportViewer to my DataSource control directly? or do I need to add methods to my DataSourceControl which return the tabular data as a DataTable inside of a DataSet?
Thanks for any help. This is way too confusing.no replys? is my question too complex? i would think this would not be too hard. any help would be greatly appreciated.
I have a table with a primary key titled as 'ID NUmber' which needs to be created automatically, however every time i add a new record the ID is not added and i have to write it manually i.e. 1, 2, 3.., could you please advice me how i can format this; i know you can do this with microsoft Access but with VS 2005 + VB language this option is not available under data type
*i am using VS 2005 and VB language
Go into design view for the table
toward the bottom, you'll see Identity Specification - open that up and set IS IDentity to True
|||Thank you, i have found the 'IS identity' but mine is set to 'No' and is blocked (i can't click on it). should i be using an specific data type; currenly i am using 'Real' is that right? I had to use 'Real' as with other data types the option for 'primary key' was not available.|||
Generally (though it might be possible some other way that I'm not familiar with), the way to do this is with an INT datatype, so it can automatically increment.
Maybe someone else can chime in here, how to do it some other way
|||To set a column as an Identity column the datatype has to be one of the INT types - tinyint, smallint, int, bigint etc.
|||i have used the tinyint to set the IS identity as 'yes', however when i run the database and add a new row to the database the row id is for example 9 instead of 2, even though i have deleted all the previous records and doesn't realise that i have now only 1 row and that the next id should be 2. is there any way to correct this?
|||You need to read up books online about Identity columns. Once a number is assigned to a row, its gone. Even if the row is deleted the number is gone. Any new rows will get the next number. If you were doing this as a test and want to empty the table and reset the identity you can TRUNCATE the table instead of delete. Again, before doing the truncate, read up books online about the command. You can also use DBCC CHEKCIDENT to reset the seed value.
|||Remember, also - tinyint is defined like this:tinyint
0 to 255
So - I'd recommend at least using INT
One last thing also - you said that your column is titled "ID Number"
My (and others) recommend that you remove the space (IDNumber) and that you never put spaces in your field or table names. Believe me, this will cause you less grief in the future.
Hi,all here,
I got a problem that I can only attach database files created on my local server, while database files from outside the local server cant be attached. So what is the problem then?
Thanks a lot in advance for any guidance and help.
Which error do you get ?HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||
Hi, Jens, the error I always got is as below:
could not find row in sysindexes for database ID 21, object ID 1, index ID 1, Run DBCC CHECKTABLE on sysindexes.
Thanks a lot.
|||Any help? Thank a lot.|||it happened to me that I was not able to re-attach database when logged in to the server under login B, different from login A used to detach the database. Login A also happened to be a dbo for this database.
try this:
1) re-attach the files to their original server,
2) create a new login that log in using SQL authentication,
3) set this login a dbo for the database you are transferring
4) on the new PC, create the same login (with the same pwd), assign it a sysadmin role, and log into the server with this login
5) try again attaching the file(s).
|||Hi, Lolik, thanks a lot.Short answer: you can't :-(
Once you have attached it to SQL Server 2005 it is no longer compatible with the SQL Server 2000 file format.
Hi, all,
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
Thanks a lot in advance for any guidance.
Here is an example of how to add an identity column
create table TestID (SomeField varchar(49))
insert into TestID values ('1.1.9')
insert into TestID values ('2.2.2.10')
insert into TestID values ('2.2.2.8')
insert into TestID values ('2.2.2.9')
insert into TestID values ('1.1.7')
insert into TestID values ('1.1.8')
insert into TestID values ('1.2.1')
insert into TestID values ('1.1.1')
insert into TestID values ('1.1.10')
insert into TestID values ('1.1.10')
GO
select * from TestID
GO
alter table TestID add IdField int identity not null
Go
select * from TestID
GO
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Hi, thanks a lot.
Got it done, but the problem is: the new added identity column is not in order? like its values from 1 to 82 is actually not in the order, it is sorted out based on other columns values in the existing table. So how can I modify its values from 1 to 83 in the oder?
Thanks a lot.
|||Order is never guaranteed for a table, use ORDER BY to get the rows back in the order that you want|||Hi, thanks a lot.In an sql authentication environment with an automatic failover in database mirroring how to you manage new logins which have been created on the principle since the start of mirroring? Since the master cannot be mirrored, and the mirror database cannot be read during mirroring (except as a snapshot) in order to find the missing logins, I assume that only after failover a script should run to create the new logins and then run sp_change_users_login . The qestions are:
1) should the script create a new login first and then run sp_change_users_login with option update_one , or should sp_change_users_login using option
Auto_Fix create the missing logins?
2) But what is the password of these users? is it initially NULL , as a consequence of sp_change_users_login? What about the SIDs?
3) Or should we bypass sp_change_users_login altogether and use
CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>,...as described in http://blogs.msdn.com/chadboyd/archive/2007/01/05/login-failures-connecting-to-new-principal-after-failover-using-database-mirroring.aspx
4) What is the event that would trigger this script to run after the aitomatic failover ?
Is there a definitive MIcrosoft agreed apon and recommended method to tackle this?
If you want your application users to connect after failover you better create those logins beforehand @. both the principal and @. mirror server @. the time of configuring mirroring so that they will be available to you but will be mismatched and after failover you can correct them using sp_change_users_login sp.
and for the less important ones you can make use of SSIS to transfer logins frequently so that after failover you can just restore the master syslogins table or master db and fix the mismatch by change users login sp......
|||
Thank you for your anwer. My question was about the new logins created after mirroring had begun. Of course before mirroring started the existing logins were recreated on the mirrorserver beforehand . The question was whether sp_change_users_login should be used with update_one or Auto_Fix or not used at all . The auto fix option creates a login if there isn't one. The update_one option does not .
Assuming rhat SSIS would transfer the new logins from the princiopal master to the mirror master while database mirroring would transfer the users from the principal to the mirror. Will the SIDs be in order? If a script has to be run it would have to be run automatically so what is the trigger that this script will be triggered by?
|||Actually there are a few issues here:
Firstly SSIS will not help you because as of 2005 the Transfer Logins task does not transfer the passwords, it resets the passwords which to be honest wouldn't help especially if you are using the High Availability option.
Next, you need to take care of the SID's otherwise you will have to sync them. See this KB article
Lastly, In 2005 you cannot create a login which has a default database that is offline. So if an app needs to use Automatic Client Redirect in a High Availability setup and the account is created on the Principal after mirroring has been configured, you will not be able to add the login on the Mirror. The only workaround is to bring the mirror online periodically and sync the logins. I heard it may be fixed in 2008 however; I have not tried yet, maybe someone can clarify for me.
This question is related to VSTS and above parameters in the request. Question is:
A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.
You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)
You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.
|||Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.
Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!
|||I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)
|||It won't. The expiration time is computed when the session is created.
Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?
|||Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like
GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007
Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).
With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.
<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>
Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.
Thanks for your input.
|||can some one reply to this please|||The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1
This question is related to VSTS and above parameters in the request. Question is:
A script created with VSTS for testers has hard coded values for ReportSession and ControlID in the requests, These IDs are expired after some time and scripts fails to run after few hours. Can some one tell me how to set the session expiration time on these ID's in the Report server so I can increase the value to 60*24*365 (1 year) this would solve my problem with hard coded values in the scripts for above mentioned IDs.
You cannot set this stuff for only some sessions -- it's a global property (<SessionTimeOut> in rsreportserver.config) that will apply to all sessions. Setting this to 1 year would be a very bad idea as we store data for each session in the reportservertempdb database until the session expires: You'd be holding onto data for every single connection ever made to SSRS for a year at a time :)
You can keep the session alive by simply pinging the SSRS web service now and then (maybe return a list of folders or something with the ListChildren method call) using the SessionID. If you do every 10 minutes (the default timeout), the session will stay up.
|||Thanks for the comment Russell. After making the change to 525600 (1 year) if I set it back to defalt would that clean up the Temp DB? for any sessions that were kept.
Reason I need this value big is because these ID's are hard coded in the Visual Studio Team Tester, since they are dynamic in nature I can create the script (which would have hard coded dynamic IDs) but the script would fail as soon ID's expire. So for now solution is keep it alive as I run though my load test and reset it back to 10 min once I am done with the load test :) what do you think!!!
|||I honestly don't know - I assume it would, but since I've never tried it myself, who knows :)
|||It won't. The expiration time is computed when the session is created.
Can you provide an example of the URL which VSTS generates and the steps you went through in VSTS to generate it?
|||Hi John, Here is the request that VSTS records when you are creating the script. Performance requirements for this particular report are that 450 users should be able to run this report for 4 hours, to fulfil this requirement I created a .webtest that goes to reports URL and once script is recorded one of the request looks like
GET /Reports/Reserved.ReportViewerWebControl.axd?ReportSession=o2k22t45jovswpqfkyswbn45&ControlID=d78e217e-d7f3-4952-ad32-cc9f6bb483f6&Culture=1033&UICulture=9&ReportStack=1&OpType=SessionKeepAlive&TimerMethod=KeepAliveMethodctl160TouchSession0&CacheSeed=Thu%20Feb%2022%2010%3A20%3A18%202007
Report Session and Control ID in this request are dynamic and change every few mins. once I try running this script after few hours these request (with session ID hard coded start failing). So I asked the question on the forum on how to keep the session alive for long time so I can get through my Performance testing cycle (next couple of months).
With respect to load testing with 450 users VSTS will simulate same user 450 times so only one session ID will live for 1 year but you would probably know better what would the Temp DB look like after the script is done running for few hours. One thing I did notice from the rsreport.config file it did not had any SessionTimeout key, it has the following though.
<Add Key="CleanupCycleMinutes" Value="10"/>
<Add Key="SQLCommandTimeoutSeconds" Value="60"/>
<Add Key="MaxActiveReqForOneUser" Value="20"/>
<Add Key="DatabaseQueryTimeout" Value="120"/>
<Add Key="RunningRequestsScavengerCycle" Value="60"/>
<Add Key="RunningRequestsDbCycle" Value="60"/>
<Add Key="RunningRequestsAge" Value="30"/>
<Add Key="MaxScheduleWait" Value="5"/>
Does this mean I have to add SessionTimeout key or first key above "cleanupcycleminutes" is the same as SessionTimeOut key.
Thanks for your input.
|||can some one reply to this please|||The "SessionTimeout" is a system property. You have to set it via SOAP API or rs.exe
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=525110&SiteId=1
