I have a table that has a int field that contains unique nubers.
if I need to insert 4000 or so new records into this table using a SQL
command how would I be able to fill this field with the next available
number?
This will be a manual procedure done maybe once a year so the next
number will be known ahaed of time. but the insert command need to
increment this number by one everytime it puts a new row in.
Hi
I am currently working on an application that uses a stored procedure to retrieve data from a database and then display it in a web page. My problem is that some of the data in the database will be images, I am currently putting in test data to test my code/procedures my problem is how do I put in test data for images, when I am finished I am going to add an admin section that will allow me to add images that way but how do I go about adding them to the database until then? I have set the field to the image data type but have no idea how to relate this to an image on my server?
Thanks, Adam
Hi,
I have a SSN field that I need to encrypt. Only persons who wants to get information about a user should be able to decrypt that field. What is the best way to encrypt and decrypt the ssn field? Thanks
Does anyone know how to use Formula Field in SQL Server 2000. I have googled a lot, but haven't been able to find out how to use it..
Thx
I am a little embarrased to be asking this question because I know that the answer must be simple but I am still a nube at SQL. I am stuck with a simple query that copies one field of data into another. Below is the script that I am trying to execute.
UPDATE table
SET field2 = field1
I get an error from SQL Server that says that the String or binary data would be truncated. I am assuming that the SQL server is trying to concatenate all the value of field1 into field2. All I am trying to do is copy the values of one field into another for each and every row in the table. Thanks in advance.
See sample data below. I want hourly breakdown for the last X years, the month and day will be the same for each year.
SELECT '2013-12-10 04:00:00.000' as dt, 220.50 as amt UNION ALL
SELECT '2013-12-10 06:00:00.000' as dt, 24.50 as amt UNION ALL
SELECT '2013-12-10 07:00:00.000' as dt, 527.50 as amt UNION ALL
SELECT '2013-12-10 08:00:00.000' as dt, 28.50 as amt UNION ALL
SELECT '2013-12-10 11:00:00.000' as dt, 25.50 as amt UNION ALL
[Code] .....
-- expected result
SELECT '2013-12-10 00:00:00.000' AS dt, NULL AS Amt UNION ALL
SELECT '2013-12-10 01:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 02:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 03:00:00.000', NULL AS Amt UNION ALL
SELECT '2013-12-10 04:00:00.000', 220.50 AS Amt UNION ALL
[code].....
I have a database that is part of AlwaysOn that is filling up the transaction log drive even though I have a daily full backup and transaction logs set for every 2 hours. The backups are going from both the primary and secondary replica backuping up to the shared disk and I have the backup preferences set to the primary.
When I try to shrink the log I get 'The transaction log for database 'DB' is full due to 'LOG_BACKUP''. I have to manually backup the trans log and then shrink, why the maintenance plan backups aren't doing this even though they are "working".
Hello all. I have a nvarchar field in the format dd:hh:mm:ss I need to get a sum of these fields in the same format. So for example if the values are 01:23:00:00 and 00:01:00:00 then the sum of these fields should be 02:00:00:00. Can someone help me? Thanks.
View 12 Replies View RelatedEdited by SomeNewKid. Please post code between <code> and </code> tags.
This is probaly the easiest question you've ever read but here goes.
I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.
Here is my code:
Sub AddSection_Click(Sender As Object, e As EventArgs)
Dim myCommand As SqlCommand
Dim insertCmd As String
' Build a SQL INSERT statement string for all the input-form
' field values.
insertCmd = "insert into Customers values (@SectionName, @SectionLink, @Title, @NewWindow, @LatestNews, @Partners, @Support);"
' Initialize the SqlCommand with the new SQL string.
myCommand = New SqlCommand(insertCmd, myConnection)
' Create new parameters for the SqlCommand object and
' initialize them to the input-form field values.
myCommand.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.nVarChar, 50))
myCommand.Parameters("@SectionName").Value = Section_name.Value
myCommand.Parameters.Add(New SqlParameter("@SectionLink", SqlDbType.nVarChar, 80))
myCommand.Parameters("@SectionLink").Value = Section_link.Value
myCommand.Parameters.Add(New SqlParameter("@Title", SqlDbType.nVarChar, 50))
myCommand.Parameters("@Title").Value = Section_title.Value
If New_window.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1))
myCommand.Parameters("@NewWindow").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1))
myCommand.Parameters("@NewWindow").Value = 1
End If
If Latest_news.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1))
myCommand.Parameters("@LatestNews").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1))
myCommand.Parameters("@LatestNews").Value = 1
End If
If Partners.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1))
myCommand.Parameters("@Partners").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1))
myCommand.Parameters("@Partners").Value = 1
End If
If Support.Checked = false Then
myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1))
myCommand.Parameters("@Support").Value = 0
else
myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1))
myCommand.Parameters("@Support").Value = 1
End If
myCommand.Connection.Open()
' Test whether the new row can be added and display the
' appropriate message box to the user.
Try
myCommand.ExecuteNonQuery()
Message.InnerHtml = "Record Added<br>" & insertCmd
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not add record, please " _
& "ensure the fields are correctly filled out"
Message.Style("color") = "red"
End If
End Try
myCommand.Connection.Close()
BindGrid()
End Sub
Any response would be appreciated
I would like to update a varchar field with an update statement that appends information to what is currently stored in the field, for example in the Statement field I may currently have a name (Mark) and I want to add a unique identifier to the end of the name so that it may look like, Markq1572, is there a way to do this in an update statement?
View 1 Replies View RelatedWhy is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.
Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?
What is best way to encrypt password field in sql server 2000?.
Is there any system stored procedure to encrypt password?.
What kind of algorithm password encryption method uses?.
cant connect to sql server 2000 from c# 2003
View 1 Replies View RelatedI live in Brazil, and use SQL Server 2000 SP4 with Visual Basic 5.0 SP3, with connectivity to ODBC through RDO/ADO.
I have an example table with the following structure:
Table Name: Autioneer's (translated from Portuguese)
'Field 1/3
Name: Code
DataType: int
Description: Auctioneer's Code
'Field 2/3
Name: Name
DataType: nvarchar(50)
Description: Auctioneer's Name
'Field 3/3
Name: RegNum
DataType: nvarchar(20)
Description: Auctioneer's Registration Number
I need a way to programatically extract the Fields Description Property from the table
example sintaxe:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='AUCTIONEERS'
Thanks for any help that can be offered here
Neil Ramkeerat.
Replies can be sent to neilramkeerat@hotmail.com
Hi
this is my problem.
mshflexgrid converts date value from dd.mm.yyyy to yyyy-mm-dd or mm.dd.yyyy
When i use JetOLEDB & .mdb file
I can show correctly date type field in mshflexgrid with str() function.
For example
rst.open"Select str(MyDateField) from table
set mshflexgrid.datasource = rst
i can see dd.mm.yyyy ' namely good
when i dont use str() function i see mm.dd.yyyy ' bad version
Now, i'm using SQL server 2000 & mdf file.
mshflexgrid don't show correctly date field ( i want it to be like dd.mm.yyyy ) because i cant use str() function with SQL sever 2000 & mdf file
For example
rst.open"Select str(MyDateField) from table... occur error
When i want to use Convert function in SQL server 2000 & mdf file
it automaticly converts it like Jun 18 2004 12:00AM but i dont want this
I want see this 18.06.2004 in Mshflexgrid.
What can i do. help me pls.
I have used DTS in SQL Server 2000 to import an MDB filed (MS ACCESS) of a table. When the table is imported the primary key is lost and the memo field data is completely gone.
I use the tranformation option in the DTS wizard to add the primary key and make sure the data type for the memo field is varchar and has a size of 8000. I need that large size since I am storing lots of html code.
When I preview the data I see the html code that is supposed to get imported. However, when I return all rows from the table in Enterprise Manager the field is empty.
So I tried to manually copy the data from the MS Access Database into SQL Server. Could not figure out if SQL Server has an interface like MS Access to simply copy data into a table. So I linked to the tables from MS Access to the SQL Server table.
When I opened the linked table I see the data in the description field. However, if I return the rows from within SQL Server no data is present.
I have some ASP code trying to read the data in the SQL Server table. However, nothing is returned and when I run the SQL Statement, nothing gets returned. The SQL statement returns all rows. All the other data is present but nothing in the description field.
What am I doing wrong? Any suggestions anyone, please!
TIA
hi all,
i have created a table with image field in it. Now i just want to store a jpeg file in it but not finding any way to do so.
how can i store any image ? what are the steps???????
thanx in advance
Hello All
I am wanting to fill a drop down list in ASP.NET using C# from a SQL database table using a stored procedure. I have my Sproc. But using ASP.NET C# I have no idea how to do this. Can someone give me a good example, and if not too much trouble, place comments in the code, and give an explanation. I am just learning ASP.NET after moving from Classic. Things are alot different.
Thank You in advnace for all your help
Andrew
I have a ton of data to load into a SQL 2005 database.
I just loaded a bunch of data for a number of tables using bcp, and the last table that my script loaded was an 8 million row table. The next table was a 12 million row table, and about 1 million rows into the bcp'ing a log full error was incurred. I have the batch size set to 10000 for all bcp commnads.
Here is the bcp command that failed:
"C:Program FilesMicrosoft SQL Server80ToolsBinncp" billing_data_repository..mtr_rdng_hrly_arc_t in mtr_rdng_hrly_arc_t.dat -c
-b10000 -Sxxxx -T
Here is the last part of the output from the bcp command:
...
10000 rows sent to SQL Server. Total sent: 970000
10000 rows sent to SQL Server. Total sent: 980000
10000 rows sent to SQL Server. Total sent: 990000
SQLState = 37000, NativeError = 9002
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'billing_data_repository' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
BCP copy in failed
I thought that a commit was issued after every 10000 rows and that this would keep the log from filling up.
The log_reuse_wait_desc column in sys.databases is set to 'LOG_BACKUP' for the database being used.
Does a checkpoint need to be done more often?
Besides breaking up the 12 million row data file into something more manageable, does anyone have a solution?
How can I continue to use my same loading script, and keep the log from filling up?
Thank you.
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.
This is the code for the Method to return a datareader
publicSqlDataReader GetOrgID()
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
SqlConnection con = new SqlConnection(conString);
string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
con.Open();
SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dtr;
This is the code trying to accomplish the same thing with a Dataset instead.
public DataSet organID(DataSet dataset)
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand(queryString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataset);
return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.
So assume that the Guid is a valid entry..I should return a valid dataset but its null.
Additionally if I change the sql query to just be Select * From aspnet_OrgNames I still get a null value...I am assuming I am doing something wrong trying to fill the dataset.
Any help would be appreciated.
MS SQL Enterprise Server, SP5, running under version 6.5.
I have recently been having a problem with the TempDb database
filling up. I originally started the database at 250 Mb but
recently expanded it to 500 Mb.
My last check of the activity on the server during an event
such as this produced the following information.
- Approx. 300 connections to primarily 2 databases.
- 4 active connections:
Connection 1 -SELECT on database 1 with 13,000 records
and a record size of approx. 300 bytes.
Connection 2 -SELECT on database 1 with 13,000 records
and a record size of approx. 300 bytes.
Connection 3 -SELECT on database 2 with 550 records
and a record size of approx. 100 bytes.
Connection 4 -Replication subscriber set at 100 transactions.
My questions are:
1. What processes may cause the TempDb database to fill up?
2. What processes prevent the database from purging?
Any information would be greatly appreciated.
Jim Story
We are having continual problems with our transaction log filling up on one of our major applications.
Does anyone know of a way or tool to read the transaction log? We want to determine what is causing this problem.
Thanks
Recently, we converted an Access database to SQL server 6.5. One of the processes that runs against the server is
missing a commit causing temporary stored procedures to fill up TEMPDB in the sysobjects table. The only way to
clear up TEMPDB is to stop and start SQL server when the database fills up. I wrote a quick and dirty stored
procedure to delete the affending rows out of the tempdb..sysobjects table, however, the database still registers as
full after the deletes.
Question:
does anyone know of a process/DBCC I can run against the tempdb..sysobjects table to regain the space in TEMPDB
without having to stop and restart SQL Server? I need a temporary solution while the programmer is debugging the
affending code.
Thanks!
TC
hello,
I've got replication set up as a publisher subscriber, to basically sync a primary server with a
backup server. My distribution log keeps filling up, I've got a perf alert for now to truncate it
at 75% full for now, but why does it fill up? it's size is about 1.5 gig
My tran log for my database will also not remove about 500mb of data as well, is there any way to
see what is going on?
Thanks
hello, i'd need a little help with filling GridViewsi browsed over like 10 search pages, but couldnt find any which would solve my problem.so in my ajax project i made a testing page pulled a gridview (GridView1) on it with a fhew buttons and textboxes.i need to fill the gv from code so my websie.asp.cs looks like this 1 protected void Page_Load(object sender, EventArgs e)2 {3 4 5 string connstr = "Data Source=.;database=teszt;user id=user;password=pass";6 SqlConnection conn = new SqlConnection(connstr);7 SqlCommand comm = new SqlCommand("select * from users", conn);8 conn.Open();9 SqlDataReader reader;10 reader = comm.ExecuteReader();11 if (reader.HasRows)12 {13 GridView1.DataSource = reader;14 GridView1.DataBind();15 }16 reader.Close();17 conn.Close();18 comm.Dispose();19 } so i load the page and there's no gridview on the page at all, nor an error msg, the connection and the database/table is fine.any suggestions on what am i doing wrong? and i also like to know if there would be any problem with using this on a tabcontrol/tabthankyou
View 3 Replies View Relatedhello everyone
i have created a table in sqlserver2005 named "Departments" - in this table different departments of a telephone ( landline ) company are to be stored,which deals with complaints registered to them by there users.
i want to know the name of these different departments which deals with complaints assigned to them like if i do have complaint from a user who has problem with his handset then that complaint will be assigned to "Maintance dept."
as i was never in indusrty , i need the help in filling the table.
just do write me name of departments and the nature of complaints wh they deal with!!!
thanks for the consideration
I have a table that keeps track of click statistics for each one of my dealers.. I am creating graphs based on number of clicks that they received in a month, but if they didn't receive any in a certain month then it is left out..I know i have to do some outer join, but having trouble figuring exactly how..here is what i have:
select d.name, right(convert(varchar(25),s.stamp,105),7), isnull(count(1),0)
from tblstats s(nolock)
join tblDealer d(nolock)
on s.dealerid=d.id
where d.id=31
group by right(convert(varchar(25),s.stamp,105),7),d.name
order by 2 desc,3,1
this dealer had no clicks in april so this is what shows up:
joe blow 10-2004 567
joe blow 09-2004 269
joe blow 08-2004 66
joe blow 07-2004 30
joe blow 06-2004 8
joe blow 05-2004 5
joe blow 03-2004 9
I have complex VB code that loads millions of records into a SQL 7 database. I see my tempdb slowly growing until system runs out of disk space.
What are typical programming bugs that would cause tempdb to fill up,
and how can I identify the offending statements through profiler?
Any help greatly appreciated!
What is the best way to fix issues with your log files in TempDB when you start to see them causing error msgs?
Thansk for your time.
We have a stored procedure that uses temp tables and must gather a lot of data. When we stress test this stored procedure, the tempdb transaction log fills up.
We tried using "Select Into" for our tables. That caused us not to write to the transaction log but it caused problems because it locked up sysobjects.
Is there some other way not to write to the transaction log?
I have a database that I am splitting the data using odd account numbers and even account numbers. The odd acct numbers in one database and the even in the other database.
This database is very large. The problem is when I run the delete statements
it is going to fill up the log files. Can I turn on the "Simple" mode on the database while I am deleteing the data. Will this cause a problem? Then can I turn back on the 'Full' mode when I have finished?
Has anyone ever done this and so how did it work. Or better yet is it possible?
Thanks,
Dianne
Hello,
I am new to SQL Server and learning lots very quickly! I am experienced at building databases in Access and using VBA in Access and Excel.
I have a time series of 1440 records that may have some gaps in it. I need to check the time series for gaps and then fill these or reject the time series.
The criteria for accepting and rejecting is a user defined number of time steps from 1 to 10. For example, if the user sets the maximum gap as 5 time steps and a gap has 5 or less then I simply want to lineraly interpolate betwen the two timesteps bounding the gap. If the gap is 6 time steps then I will reject the timeseries.
I have searched the BOL and MSDN for SQL Server and think there must be a solution using the PredictTimeSeries in DMX, but not quite sure if I can do this. I may be better off simply passing through the time series as a recordset and processing as I would have done in Access...(I am reluctant to do this as I have of the order 100 * 5 * 365 time series and growng by 100 each day and fear it will take quite some time...)
Can anyone help me by pointing me in the right direction please?
Unless there is a way of using PredictTimeSeries on its own, I think the solution is:
Identify if a record is the a valid one or part of a gap (ie missing values).
Identify the longest gap and reject or process data on this value.
Identify if a record preceedes or succeeds a gap.
For each gap fill it using a linear interpolation.
Thanks,
Alan.
Hello,
In my application I am using Identity columns. When some rows are deleted from table, This identity values are not filling the gap. I mean My current identity is 5. That means 1 to 5 rows sequentially i inserted. If I am deleting 3rd and 4th rows, next identity will still continue with 6. So is there any method to fill the gap between rows