Anyone have a script to create a table of US States?
I'd rather not type in all 50 states if possible :)
Thanks!
The forums search page returned no such script :(
Michael
<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
I have this table that I thought I could just do a normal update to because I need to add a state and ID.
How do I add a state to a table that is already created?
Here is the behind the scenes. I have never had to deal with something like this.
This is what it looks like
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RegionStates](
[RegionID] [smallint] NOT NULL,
[code]....
Quick questiom, I need to Import a table with City and State Information
What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?
Or can I just store them in a table with one column?
CityName, State
Thanks for your help/
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.
SP3 has been applied to SQL Server 2000.
Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
Hi all.
First, sorry about my poor english.
I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Thanks.
We have mirroring setup for 5 dbs, 4 of which are synchronized and 1 which is in "synchronizing" state on the principal and "restoring" state on the mirror. Mirroring for all dbs has been working fine for the past several months and we have a witness that has allowed automatic failover in the past without problems.
This database has several bulk inserts performed throughout the day and am sure there is some latency due to the size of these transactions.
Not sure as to why this is happening all of a sudden, but the db in question has been in this state for the past 12 hrs. I checked the mirroring status on the principal and it states that it is "synchronizing: data is being transferred from principal to mirror", but the mirror server states that db is in "restoring" state. Can anyone suggest as to how I can get the database on the mirroring server to get back to "mirror, synchronizing/restoring..." state? Or suggest on how I can troubleshoot this?
Thanks in advance.
I am creating an external application that will be used for hiring applicants and i used MOSS Workflow for this.
I can programmatically start the workflow but the problem is how can i go to next state programmatically? scenario is the next state might trigger after a day or week.
Thanks in advance.
I've got a SQL that registers people passing in and out via their acess cards. Basically passing in means they set a discrete tag to 1 and out tag to 0. SQL logs delta so change is logged. I would now want to Query the database and just want the monthly time they've been in i.e. how long has the tag been 1.
I can pull a monthly list of the tags all statuses (1 and 0) and times but how do I put together a query that just gives me the time it been in 1 status?
following to pull a delta list i.e changes:
SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(wk,-1,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT TagName, DateTime = convert(nvarchar, DateTime, 113), vValue, Quality, QualityDetail = v_History.QualityDetail, QualityString
FROM v_History
LEFT JOIN QualityMap ON QualityMap.QualityDetail = v_History.QualityDetail
WHERE TagName IN ('Pete_Smith')
AND vValue <= 1
AND wwVersion = 'Original'
AND wwRetrievalMode = 'Delta'
AND wwRowCount = 1000
AND DateTime >= @StartDate
AND DateTime <= @EndDate
heson1
Hi All,
I ckecked other posts before posting, and I found one that applies but doesn't quite work for me. I have a database with Address records and want to know how many records are in each state.
Ex,
State Count
AL 26
CA 45
FL 16
NY 75
I tried using this previous post but changing to state if that helps:
Select Count(Name) as [Count], City
From Table
Group by City
Thanks in advance.
Hi
I want to build a class in ASP.Net 2.0 which will lookup if SQL server is up & running & whther connection is getting establish to the Database. if it fails it will try 3 time to reconnect. if no sucess then it will shoot a mail to Admin.
Please help me to achive this.
Thanks
Vishal.
Hi to all, protected void Page_Load(object sender, EventArgs e) { try { Label1.Text = Convert.ToString(DateTime.Now); //string con = System.Configuration.ConfigurationSettings.AppSettings["AJS_CMSConnectionString2"]; ConnectionStringSettings ConstrSettings = ConfigurationManager.ConnectionStrings["AJS_CMSConnectionString2"]; SqlConnection cn = new SqlConnection(ConstrSettings.ConnectionString); if (cn.State == ConnectionState.Open || cn == null || cn.State == ConnectionState.Broken) { cn.Close(); } cn.Open(); SqlDataAdapter sda = new SqlDataAdapter("Select * from user_master", cn); DataSet ds = new DataSet(); sda.Fill(ds,"table1"); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); } catch (Exception ex) { Response.Write(ex.Message); } } protected void Button1_Click(object sender, EventArgs e) { try { Label1.Text = Convert.ToString(DateTime.Now); //string con = System.Configuration.ConfigurationSettings.AppSettings["AJS_CMSConnectionString2"]; ConnectionStringSettings ConstrSettings = ConfigurationManager.ConnectionStrings["AJS_CMSConnectionString2"]; SqlConnection cn = new SqlConnection(ConstrSettings.ConnectionString); if (cn.State == ConnectionState.Open || cn == null || cn.State == ConnectionState.Broken) { cn.Close(); } cn.Open(); SqlDataAdapter sda = new SqlDataAdapter("Select * from user_master", cn); DataSet ds = new DataSet(); sda.Fill(ds, "table1"); GridView1.DataSource = ds.Tables[0]; GridView1.DataBind(); } catch (Exception ex) { Response.Write(ex.Message); } } This is my code. During page load i open the connection and read some records from my database, afterwards i click the button and check the connectionstring state now it is closed, my question is why it was closed, i want to set my connectionstring will always opened how to set it ?
View 2 Replies View RelatedHello all,
I have a question concerning the ridiculous quagmire of SQL Server licensing…
How is SQL Server licensed when used to store session state information? Is MS claiming that this is an end user use of SQL Server functionality? Or, can we be realistic and say that I need a server license plus a device license for each web server?
Personally, I like everything about SQL Server except the licensing. Management at my company has already said that we’ll most likely be moving to either Firebird or Postgres after they found out how much it would cost to move from SQL 2k to SQL 2005. If MS wants us to buy processor licenses for SQL state servers, I guarantee we’ll be doing something else there as well. I wonder if Microsoft has any clue how much business they’re losing. Not just in SQL Server, but in their OS business as well.
while loading the transaction log dump on a 'STANDBY' database the connection got terminated. as a result the database was marked 'LOADING' and inaccesible. i would like to know if there is a way to get the db out of this mode without having to reapply a full database dump.
P.S. please ignore the previous two messages posted with the same subject line. i inadvertently pressed post twice. sorry for the spam.
Server: Msg 229, Level 14, State 5, Procedure sp_monitor, Line 167
EXECUTE permission denied on object 'sp_monitor', database 'master', owner 'dbo'.
How do I fix this? which roles, or permission is that? Is there a
easier way to find out solutions for these problems, as books online
does not seem to provide much help.
Thanks,
Vijay
Does anyone know what this means ??
thanks
I have a big problem with a SQL 6.5 (no SP) running
in a production environment on NT 3.51 SP 4 since
2,5 years.
Suddenly Friday at 99/10/01 14:17:38.93 we started
having problems and we could'nt connect to the
DB from our client server application written in VB4.
We rebooted the server and we had other errors at
99/10/01 16:15:40.96.
The error code is 605-21-1 and it seems like the main
table (FILMS) of our db (VISPO) has phisical allocation
problems.
here is en extract from the SQLServer error log
of the first problem:
99/10/01 14:17:38.93 kernel udread: Operating system error 23(Data error (cyclic redundancy check)) on device 'E:MSSQLDATAvispodat.DAT' (virtpage 0x05000f35).
99/10/01 14:17:43.62 spid13 Buffer 9531a0 from database 'vispo' has page number -1 in the page header and page number 3893 in the buffer header
99/10/01 14:17:43.69 kernel mirrorproc: i/o error on primary device 'E:MSSQLDATAvispodat.DAT'
And here is what we had at 16:15
99/10/01 16:15:40.96 spid11 Getpage: bstat=0x1008/0, sstat=0x80010130, disk99/10/01 16:15:40.96 spid11 pageno is/should be:objid is/should be:99/10/01 16:15:40.96 spid11 0xffffffff(-1)0xffffffff(-1)99/10/01 16:15:40.96 spid11 0xf34(3892)0xe4e2b2e(240003886)99/10/01 16:15:40.96 spid11 ... retry bufget after purging bp 0x943a8099/10/01 16:15:40.98 spid11 Error : 605, Severity: 21, State: 1
99/10/01 16:15:40.98 spid11 Attempt to fetch logical page 3892 in database 'vispo' belongs to object '-1', not to object 'FILMS'.
We are thinking about restoring the BackUp of the previous night that should be OK,
but I would like what we can try to do in order to have the
db working properly.
Even if the restore would be fine, maybe we are starting
to have problems on the disk subsystems?
What can we check, something like scandisk in DOS?
I attach here in ZIP format the 2 log files and plese ignore the MAPI errors
that are "normal", we just don't care.
I don't know if it's possible to attach files to a newsgroup or a discussion
group but I will try
Any help would be appreciated.
Thanks in advance.
Eugenio La Mesa
Publisoft
Rome - Italy
When I change the Identity in a table in EM (undependend on which DB and table) the result is: invalid cursor state.
I have a productive server running SQL Standard version and an play area running SQL Personal.
The funny thing is, when I run the update over QA it works without a problem.
With EM the Profiler tells me an error 16954 after droping a table, and it start to roll back.
I tried hunderts of things to make it work but with no success. Except one thing. I've reinstalled the Personal SQL, restored my DB's and everything works fine now.
This happened first after installing the latest security update from Microsoft on the 10th of February.
I don't want necessarily to reeinstall the SQL server on the productive server, because there are hundreds of Databases on it, and a lot of security settings.
Any help is very appreciated.
Please help me the cause and solution for Error: 602, Severity: 21, State: 4 which is raised while trying to excute a select query on a particular table.
This does not occur every time i excute the query
thanks in advance
Hi,
Running NT 4 Svc Pack 4, SQL Server 6.5 Svc Pack 5a. I have an sp to build indexes and it is no longer running...after a couple moments I get the error(s) "Msg 1509, Lvl20, State 2, Row Compare Error". The "State" is 2 on most of the messages and 1 on 1...I get 5 messages at once. I looked through the support information at Microsoft's site and found 1 article on this but...it was for 6.0, it stated that it was fixed in Svc Pack 3, and provided a workaround of increasing the "sort pages" value in the SQL configuration. I was unable to find the "sort pages" option so I am assuming it is no longer available. I then installed the SQL Svc Pack 5a but still no resolution. At this point I have no indexes on my tables and can't find a way to rebuild them. Any help would be appreciated...The only thing I can think of is that I installed the NT svc pack last week and this might have caused it...the indexes sp runs over the weekend so I did not notice this until this morning.
Thanks in Advance,
Kevin
Attempt to fetch logical page ' ' in database ' '. Object belongs to ' ' not object ' '.
Please someone help me fix this. My table is corrupt and I cannot drop it and restore it because I keep getting an error message that says:
Cannot drop table ' ' there is not enough space in syslogs. blah, blah, blah.
Can anyone help me? It woould be much appreciated.
Christine
I synch 4 databases everyday and on one of them I get the Error 823, Severity 24, State 2 error (Yes I know you have heard this before but I am a new, LOL). I have tried detaching the database and copying it, renaming that database and then copying back to the original name with the same error; therefore, I think the error is somewhere in the database structure. If it is the database structure would it still give the error above or is that simply a hardware error? I have been searching for solution and I am at a loss. Any help would be appreciated.
Thanks.
N. Adkins
Hi All
Ignore the error below, It's got to do with isolation levels (nolock in this case): refer http://support.microsoft.com/?kbid=834290 for details.
Error: 644, Severity: 21, State: 6
Could not find the index entry for RID '1631393920202020202020202020202033392020202020202 020202020202019bcd3113230303037333120202020202020' in index page (1:420134), index ID 0, database 'DBName'..
What I'd like to know is how to get the table name that was affected out of this error message. Do I query sysindexes, sysobjects, use a function or something else?
Thanks in advance.
Graham
Hi,
I have a stored procedure that calls 2 other stored procedures and combines the results into a temporary table. The results of the temporary table is then returned from the stored procedure.
When I execute the stored procedure in Query Analyzer, I get the exact data I want in the correct format - no errors.
When I execute that stored procedure in Omnivex SQL Link 3, I get an "Invalid Cursor State" error.
I did some digging on that error, and found that it could be related to print statements within the stored procedures. I removed all print statements from all 3 stored procedures and the error is still occuring.
Any suggestions?
Hello
(SQL Server 2000)
I have databases that appear as "Suspect" in the Enteprise Manager. What can I do to recover them or put them in a normal state?
Thanks a lot.
Hi, i encounter a problem with my sql.
What my system does is that it restore/dump database from some backup.
it will then send email using the xp_sendmail. After that it will send out sms using a command line script.
The timing as per the attachment i put in here. Pls kindly help. production issue.
Here are some other events that caused the error
Using 'dbghelp.dll' version '4.0.5'
*Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQLlogSQLDump0003.txt
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
Stack Signature for the dump is 0xF107CBA8
Pls let me know if you need any information
I have this query here which displays all duplicates which have the same phone number but i am trying to modify it to display only fields that have the same phone number but have different state
SELECT *
FROM businesses t1
join (SELECT ce_phone FROM businesses GROUP BY ce_phone HAVING COUNT(*)>1) t2
ON t1.ce_phone = t2.ce_phone
WHERE t1.ce_phone is not null
ORDER BY t1.ce_phone
So what I would try to do would be to group state within the ce_phone group and count the state duplicates and if it is less than the count of phone then i would display it but i am just not sure how to write the syntax for it.
Login failed for user ''. Reason: Failed to open the explicitly specified database. [CLIENT:xx.xx.xx.xx]
No missing database & having sysadmin permission.
why this error coming?
Hello guys,
Error: 18456, Severity: 14, State: 8.
Login failed for user 'sa'. [CLIENT: <ip address>]
With what Ive searched.. State: 8 is wrong password. What Ive
encountered I believed is not State: 8 I guess... I have 2 pc..
PCX and PCY.. PCY has SQL Server 2005 so does PCX.. when I run my application that stores data to PCY DB it prompts me an error just
like the one above.. but what seems to be confusing me is both of them have the same DB password. Did I miss something... please help..
-------------------
Real Programmer dont document
If its hard to write Then
its easy to undertand.
End If
HiCan anyone help me with Session Management in ASP.NET. I have a webapplication with a login page and i need to retain the username afterhe logs in. This is what I have in Config FilesGlobal.asax<OBJECT RUNAT="SERVER" SCOPE="SESSION" ID="MyInfo"PROGID="Scripting.Dictionary"></OBJECT>web.config<configuration><!-- This section stores your SQL configuration as an appsettingcalledconn. You can name this setting anything you would like.--><appSettings><add key="conn"value="server=ADVAITH;database=tis;uid=sa;pwd=;" /></appSettings><!-- This section sets the authentication mode to formsauthenticationand routes all traffic to the specified page. It alsospecifies atimeout. The authorization section below denies all users notauthenticated. For testing purposes, custom errors was turnedoff.The section below allows pages to be trace enabled fordebugging--><system.web><sessionStatemode="InProc"cookieless="false"timeout="20" /><authentication mode="Forms"><forms name="MyFormsAuthentication" loginUrl="login.aspx"protection="All" timeout="720" /></authentication><authorization><deny users="?" /></authorization><customErrors mode="Off" /><trace enabled="true" requestLimit="0" pageOutput="true" /></system.web><system.web></system.web></configuration>When i assign the user name in the log in page it workssession("username") = txtUsername.TextBut if I use a new session variable or the username(session variable asdeclared in the login page, in the menu page it gives me error sayingthat Decralation is expected . Is there any files that i need to importb4 using sessions??Please help me out. My email:bramanujam@gmail.com
View 1 Replies View RelatedHi,I am getting the following error from the query below against SQLServer 8.00.2039 (SP4)Error:====Server: Msg 107, Level 16, State 2, Line 1The column prefix 'd' does not match with a table name or alias nameused in the query.Select Statement:=============select ....from trades a,gems_product_groups b,portfolio_nav_mapping c,products d,limit_types eleft outer join gems_prod_trade_mod f on d.product_id =f.product_IDI know this was a known bug in MS-SQL7, but I thought it had been fixedin 2000.Can anyone help?Thanks
View 3 Replies View Relatedhow to check the connection state sqlif (oconn.State.tostring()="Closed")or is there a more approiate waythanksMJ
View 1 Replies View Related
I am writing a user defined function but I am receiving an error message that I can figure out. Any answers out there?
Msg 102, Level 15, State 1, Procedure ufn_GetStreetNumber, Line 25
Incorrect syntax near '@StreetNum'.
-- ---------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Parses out the Street number for Address field
-- =============================================
CREATE FUNCTION dbo.ufn_GetStreetNumber
(
-- Add the parameters for the function here
@StreetAddr varchar(50)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @StreetNum varchar(50);
DECLARE @iIter smallint, @iNoOfChars smallint, @iStreetNo int;
SET @StreetNum=NULL;
SET @iStreetNo=0;
SET @iNoOfChars=1;
SET @iIter=1;
-- Add the T-SQL statements to compute the return value here
WHILE Isnumeric(Substring(@StreetAddr,@iNoOfChars,@iIter))=1
@StreetNum=@StreetNum + Substring(@StreetAddr,@iNoOfChars,@iIter)
@iIter=@iIter+1
BREAK
@iStreetNo=@StreetNum
-- Return the result of the function
RETURN @iStreetNo
END
GO