|
hello there,
i have a table that have a column, thats values will always unique, that column data will be in numbers, now i wanna know that what variable should i use, bigint or nvarchar ??
this column will be use in checking(i.e. is this ID exist or not?) and that table have already a PK.
currently i'm using bigint and i think that bigint is taking more space than a string, just a think, cant say surely....
so please suggest about it with a good reason 
thanks
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Xmen wrote: currently i'm using bigint and i think that bigint is taking more space than a string, just a think, cant say surely....
Perhaps, if your number is low currently. But, if the number gets big, that will reverse. if you're doing indexing on this id ( which seems the only reason to have it ) a number makes far more sense than a string.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
now i wanna know that what variable should i use, bigint or nvarchar ??
It depends on what's the maximum value you expect in the column. I suppose that you'll never run out of numbers in Bigint unless you handle a billion transactions a day.
currently i'm using bigint and i think that bigint is taking more space than a string
Every character in a nvarchar column occupies 2 bytes. So if you're column is nvarchar(10), it'll occupy 20 bytes of storage(Assuming that the data is 10 characters long). On the other hand, Bigint is 8 bytes for all values in the range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. (thats a lot of numbers)
so please suggest about it with a good reason
One good reason is that a fixed length columns is that indexes will work better on them. Use a bigint type and index it for faster performance of your EXISTS query. If the column stores only numbers, use a suitable numeric datatype. The performance will far offset storage.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
the max lenght of id is 15 and it can be handled by bigint(i.e. 999,999,999,999,999) that also small in size, that mean i'm doing right, i will use "unique index" anyway thanks guys
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87??6?N8?BcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKSQXUFYK[M`UKs*$GwU#(QDXBER@CBN%
Rs0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-i?TV.C\y<p?jxsg-b$f4ia>
--------------------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
hi,
I'm currently using sql server 2000 for my database.
My problem is that this database now hosted on the server gets updated regularly. I mean, we may want to change the structure of some tables and add new fields to them but at the same time we don't want to lose the data entered by users through our website which connects to this database.
I notmally take a copy of the database and update the tables and add stored procedures and then I generate sql scripts for the new tables and new stored procedures but the problem when I try to edit the structure of a table. This will result in losing the data that was inserted in the table. Because using the "generate sql script" will most probably drop the old table and create a new one with the new fields.
so, now how can I update this table and at the same time preserve the data??? I know that the new fields data will be null but that's fine, I can edit them later.
One more thing..when you guys update databases, what exactly do you do if this database is connected to a website? Do you drop the website temporarily in order to update the database? or what?
I mean what if at the exact moment that you dropped you database, some user was adding data? what will happen then??
thanks
|
|
|
|
|
If I need to make changes to a table structure, I generate an 'ALTER TABLE' command. I do NOT allow the system to drop tables.
If multiple changes need to be completed together or bulk rolled back, put them within a transaction.
If possible, ask the users to halt web access during a schedule maintenance period, or, schedule maintenance through the web site. Many large businesses due that on a regular basis.
Hope that helps,
Tim
|
|
|
|
|
Alter table will make the changes without losing the data.
We user Reg-Gate SQL Compare to generate the scripts for deployment.
You MUST always have a development database (take a copy of the production DB to another server)
We normally identify the quietest time for deploying an update and live with the problem of potentially losing some data. This of course depends on your volume and the size of the deployment.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Respected Sir
I have taken backup of my database and format the system now how I restore my database. I am using Sql server 2000.
or
I have backup of database which i taken from another system. now how I restore that database
Signature preview salil_k_singh 11:06 15 Jul '07
|
|
|
|
|
I would copy the backup locally and then, using 'SQL Server Management Studio', use the graphical interfact to restore the database.
Right click on 'Databases', select 'Restore' and fill in the required pieces.
Tim
|
|
|
|
|
sir
what you mention , this option is not in sql server 2000. then how I restore my database
Signature preview salil_k_singh 11:06 15 Jul '07
|
|
|
|
|
hi all
i tried to find the existing sqlservers in the network,
Eammonn Murray's article helped me to find the existing sql servers,
but it didnt deducted the existing databases in the network,
can any body help .
Thanks in adv
kssk
|
|
|
|
|
Eammonn Murray's article helped me to find the existing sql servers,
but it didnt deducted the existing databases in the network,
Databases exist within a SQL Server instance. You ought to enumerate the Servers first, and query the master DB in each server to find the list of databases available in that particular server. Ofcourse, you need valid credentials to log in into each server.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi friends,
In my application (developed in asp.net 1.1), I am going to implement the Cache dependency mechanism. I want to automatically invalidate the Cache object, once any manipulations done on a particular table in SQL Server.
My application and DB is running in different system. I found in one of the site, to create a trigger on the table which in tern calls the sp_makewebtask System stored procedure and write output of the query into a file.
EXEC sp_makewebtask @outputfile = 'c:\testcache.txt'
@query = 'select top 1 Problem_Category_Code from SMD_Problem_Category'
But the 'C:\testcache.txt' is pointing the local system in which SQL is running. I want to write a file which is available in remote system. I shared a folder from the system and made a mapped drive in the SQL system. (as z:\ drive)
I used the statements as :
@outputfile = 'Z:\testcache.txt' also as @outputfile = '\\10.160.1.4\wc\testcache.txt'
But in both the cases I got the error as:
Msg 16821, Level 11, State 1, Procedure sp_makewebtask, Line 131
SQL Web Assistant: Could not open the output file.
But in one site it is mentioned that, we can use the UNC name if we want to write file in remote system. How can I run the SP?
Can anyone please help me to solve this problem?
I need some swift reply on this issue, as it is very urgent for me.
Thankfully,
jm
|
|
|
|
|
This means that SQL Server does not have the required permission to write into the shared folder.
You ought to give write permission to the account under which SQL Server is running to the shared folder. Make sure you give permission under the "Sharing" tab and "Security" tab.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi ,
In my code, if I assign a dataset to the combobox datasource property,it is not getting assigned. The combobox datasource remains null. Because of this the combobox is not getting populated with the data.Can Some one tell me what could be the problem.
B.RAMYA
|
|
|
|
|
You are definitly not trying to do that in SQL / ADO / ADO.NET, so better try the related forum!
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Hi all,
I am developing a window application which can import EXCEL file to dataset/datatabel and display it in datagrid view. I am success to import it and edit or update the data into EXCEL. But i can't delete data in EXCEL(i know this is the limitation of excel if we do excel as a database - error ISAM). So i would like to delete data in dataset/datatable ,and then save entire dataset/datatable into EXCEL file.So, Can anyone tell me how to export entire dataset to excel by using OLEDB?:confused:
Any tips or related resources are welcome.Thanks 
regards
cocoonwls
|
|
|
|
|
|
Read the warning comments about double vs. decimal. (If someone has a solution I'd be happy to know about it!)
Private Function OS(ByVal Word As String) As String<br />
Dim i As Integer = Word.IndexOf(".")<br />
While i > -1<br />
Word = Word.Remove(i, 1)<br />
i = Word.IndexOf(".")<br />
End While<br />
Return Word<br />
End Function<br />
<br />
Public Sub ExportDatasetToExcel(ByVal Location As String, ByVal DS As DataSet)<br />
<br />
If My.Computer.FileSystem.FileExists(Location) Then<br />
Try<br />
My.Computer.FileSystem.DeleteFile(Location)<br />
Catch ex As System.IO.IOException<br />
Dim MsgStr As String = "The file is in use by another process. Close all programs that might use the file and try again."<br />
MsgBox(MsgStr, MsgBoxStyle.Exclamation)<br />
Exit Sub<br />
Catch ex As Exception<br />
Dim MsgStr As String = String.Format("This shouldn't happen, call support{0}Error message:{0}{1}{0}{0}Stacktrace:{0}{2}", Environment.NewLine, ex.Message, ex.StackTrace)<br />
MsgBox(MsgStr, MsgBoxStyle.Exclamation)<br />
Exit Sub<br />
End Try<br />
End If<br />
Dim CreateString As New StringBuilder<br />
Dim Columns As New StringBuilder<br />
Dim Mark As New StringBuilder<br />
Dim ConnectionStr As String<br />
ConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Location & ";Extended Properties=""Excel 8.0;HDR=YES"""<br />
Using Connection As New OleDb.OleDbConnection(ConnectionStr)<br />
Connection.Open()<br />
For Each table As DataTable In DS.Tables<br />
CreateString.AppendFormat("CREATE TABLE [{0}] (", table.TableName)<br />
Columns.Append("(")<br />
Mark.Append("(")<br />
For Each Column As DataColumn In table.Columns<br />
CreateString.Append(OS(Column.ColumnName))<br />
Select Case Column.DataType.Name<br />
Case "SByte", "Byte", "Int16", "Int32", "Int64", "Decimal", "Double", "Single"<br />
CreateString.Append(" Number, ")<br />
Case "Boolean"<br />
CreateString.Append(" Bit, ")<br />
Case "Char", "String"<br />
CreateString.Append(" Memo, ")<br />
Case "DateTime"<br />
CreateString.Append(" DateTime, ")<br />
Case Else<br />
CreateString.Append(" Text, ")<br />
End Select<br />
Columns.AppendFormat("{0}, ", OS(Column.ColumnName))<br />
Mark.Append("?,")<br />
Next<br />
CreateString = CreateString.Remove(CreateString.Length - 2, 2)<br />
CreateString.Append(")")<br />
Columns = Columns.Remove(Columns.Length - 2, 2)<br />
Columns.Append(")")<br />
Mark = Mark.Remove(Mark.Length - 1, 1)<br />
Mark.Append(")")<br />
Using Command As New OleDb.OleDbCommand(CreateString.ToString, Connection)<br />
Command.ExecuteNonQuery()<br />
End Using<br />
Using Adapter As New OleDb.OleDbDataAdapter("SELECT * FROM [" & table.TableName & "$]", Connection)<br />
Using ExcelDataset As New DataSet<br />
Debug.WriteLine("ExcelDataset.Locale.Name = " & ExcelDataset.Locale.Name)<br />
Adapter.Fill(ExcelDataset, table.TableName)<br />
Adapter.InsertCommand = New OleDb.OleDbCommand("INSERT INTO [" & table.TableName & "] " & Columns.ToString & " VALUES " & Mark.ToString, Connection)<br />
For Each Column As DataColumn In table.Columns<br />
Select Case Column.DataType.Name<br />
Case "SByte", "Byte", "Int16", "Int32", "Int64", "Double", "Single" ', "Decimal"<br />
Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Numeric, 100, OS(Column.ColumnName))<br />
Case "Decimal"<br />
'BIG, BIG Warning about this one, Inserting a a double intead of a Decimal is done to fix a bug(?) in OleDB <br />
'when using another Language setting than en-US on the computer (decimal comma vs. decimal dot)<br />
'Changing Cultureinfo on the CurrentThread doesn't help<br />
<br />
Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Double, 100, OS(Column.ColumnName))<br />
<br />
'Here's my original to use with en-US if anyone is interested:<br />
'Dim myParameter As New OleDb.OleDbParameter("@" & OS(Column.ColumnName), OleDb.OleDbType.Decimal, 100, OS(Column.ColumnName))<br />
'myParameter.Precision = 8<br />
'myParameter.Scale = 4<br />
'Adapter.InsertCommand.Parameters.Add(myParameter)<br />
<br />
Case "Boolean"<br />
Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Boolean, 100, OS(Column.ColumnName))<br />
Case "Char", "String"<br />
Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Char, 65536, OS(Column.ColumnName))<br />
Case "DateTime"<br />
Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.DBTimeStamp, 100, OS(Column.ColumnName))<br />
Case Else<br />
Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Char, 65536, OS(Column.ColumnName))<br />
End Select<br />
Next<br />
For Each Row As DataRow In table.Rows<br />
If Row.RowState <> DataRowState.Deleted Then<br />
Dim ExcelRow As DataRow = ExcelDataset.Tables(table.TableName).NewRow<br />
For i As Integer = 0 To table.Columns.Count - 1<br />
ExcelRow.Item(i) = Row.Item(i)<br />
Next<br />
ExcelDataset.Tables(table.TableName).Rows.Add(ExcelRow)<br />
End If<br />
Next<br />
Adapter.Update(ExcelDataset, table.TableName)<br />
End Using<br />
End Using<br />
Next<br />
End Using<br />
System.GC.Collect()<br />
End Sub
|
|
|
|
|
|
Don't double post. It's rude and against forum rules.
You've just asked this question. Why have you asked it again?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
|
Hi
can u please help me to run following code.
___________________________________
declare @tablename varchar(20);
SET @tablename = 'table1';
select * from @tablename;
error: must declare @tablename
Thanks
Jayesh
|
|
|
|
|
Why do you want to do that in the first place?
If your table name is known only at runtime, you've to use dynamic sql
<br />
declare @tablename varchar(20), @sql nvarchar(256);<br />
SET @tablename = 'table1';<br />
set @sql = 'select * from ' + @tablename<br />
exec sp_executesql @sql<br />
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
actually i need query with dynamic table name for cursor where ur this code will not help me dear.
my problem is here..
declare @TABLENAME as varchar(50);
SET @TableAuto = 'temp_' + @LINE; (where @LINE is parameter)
DECLARE C1 CURSOR READ_ONLY
FOR SELECT *
FROM @TABLENAME
modified on Wednesday, April 9, 2008 4:18 PM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
