|
hi
i went create new data base from my computer to server
when i creat in local is ok
this my code to create data
Private Sub ButCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButCreate.Click
ButCreate.Enabled = False
btnExit.Enabled = False
Dim dmoServer As New SQLDMO.SQLServer
Dim filSqlScript As IO.StreamReader
Dim strPathToFile As String
Dim I As Integer
Dim StrDataScript0 As String
Dim StrDataScript1 As String
Dim StrDataScript2 As String
Dim strDrive As String
If TxtDBName.Text <> "" Then
If txtDBChar.Text <> "" Then
'If IsNumeric(txtDBChar.Text) = False Then
dmoServer.LoginSecure = True
Try
dmoServer.Start(True, MainMod.serverName)
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.UnauthorizedAccessException
Dim objDbLogin As New frmDbLogin
If objDbLogin.ShowDialog(Me) = DialogResult.OK Then
Try
dmoServer.Start(True, MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exc As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exce As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exce As System.UnauthorizedAccessException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
End Try
End If
End Try
filSqlScript = IO.File.OpenText(Application.StartupPath & "\DataScript.sql")
StrDataScript0 = Replace(filSqlScript.ReadToEnd, "Amazon", TxtDBName.Text)
strDrive = dmoServer.Properties.Application.FullName.Substring(0, 3)
StrDataScript1 = Replace(StrDataScript0, "C:\", strDrive)
StrDataScript2 = Replace(StrDataScript1, "rplc", txtDBChar.Text)
'Try
dmoServer.ExecuteImmediate(StrDataScript2)
filSqlScript.Close()
22222222222
|
|
|
|
|
hi
i went create new data base from my computer to server
when i creat in local is ok
this my code to create data
Private Sub ButCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButCreate.Click
ButCreate.Enabled = False
btnExit.Enabled = False
Dim dmoServer As New SQLDMO.SQLServer
Dim filSqlScript As IO.StreamReader
Dim strPathToFile As String
Dim I As Integer
Dim StrDataScript0 As String
Dim StrDataScript1 As String
Dim StrDataScript2 As String
Dim strDrive As String
If TxtDBName.Text <> "" Then
If txtDBChar.Text <> "" Then
'If IsNumeric(txtDBChar.Text) = False Then
dmoServer.LoginSecure = True
Try
dmoServer.Start(True, MainMod.serverName)
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch ex As System.UnauthorizedAccessException
Dim objDbLogin As New frmDbLogin
If objDbLogin.ShowDialog(Me) = DialogResult.OK Then
Try
dmoServer.Start(True, MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exc As System.Runtime.InteropServices.COMException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exce As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
Catch exce As System.UnauthorizedAccessException
Try
dmoServer.Connect(MainMod.serverName, objDbLogin.txtUser.Text, objDbLogin.txtPassword.Text)
Catch exc As System.Runtime.InteropServices.COMException
MessageBox.Show("لاتوجد قاعدة بيانات أو لم يتم السماح بدخولها", "خـطـأ", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RtlReading)
End Try
End Try
End If
End Try
PrgBarCreate.Value = 10
Label3.Text = PrgBarCreate.Value & "%"
filSqlScript = IO.File.OpenText(Application.StartupPath & "\DataScript.sql")
PrgBarCreate.Value = 20
Label3.Text = PrgBarCreate.Value & "%"
StrDataScript0 = Replace(filSqlScript.ReadToEnd, "Amazon", TxtDBName.Text)
strDrive = dmoServer.Properties.Application.FullName.Substring(0, 3)
StrDataScript1 = Replace(StrDataScript0, "C:\", strDrive)
StrDataScript2 = Replace(StrDataScript1, "rplc", txtDBChar.Text)
PrgBarCreate.Value = 30
Label3.Text = PrgBarCreate.Value & "%"
'Try
dmoServer.ExecuteImmediate(StrDataScript2)
PrgBarCreate.Value = 40
Label3.Text = PrgBarCreate.Value & "%"
filSqlScript.Close()
PrgBarCreate.Value = 50
Label3.Text = PrgBarCreate.Value & "%"
For I = 51 To 100
Thread.Sleep(100)
PrgBarCreate.Value = I
Label3.Text = PrgBarCreate.Value & "%"
Next
22222222222
|
|
|
|
|
And the problem/question is.....?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi , I have a problem ! I have interviewers working on cases and I need to calculate the following:
Calculation per case :
1. wait time between two cases for each interviewer ( I am clueless on this one )
Summary Calculation by group:
2. Average wait time for each interviewer
3. Total cases worked by each interviewer
My table has this information : ID,CaseId,CaseStarttime,CaseEndTime,InterviewerId
Primary Key is ID, only that is Unique in table.
For each InterviewerId I want to calculate the wait time.
Wait time= CaseEndTime(of last case) - CaseStartTime(of next case)
Minoo S
|
|
|
|
|
Minoo,
The answer is:
1) Convert (CaseEndTime-CaseStartTime) to get the seconds/minutes as integer.
2) Then you can simply get the average(minutes/seconds) by grouping the interviewers.
If you still need further help I will send the query, do let me know.
Best Regards,
Darpan Gogia
|
|
|
|
|
Hi Darpan,
I got the others thank you. Just the wait time I need more information on.
So if I group cases by interviewid, how do I calculate wait time, if wait time is (CaseEndTime of first case/first row in group by Interviewer)-(CaseStarttime of next case/second row in group by Interviewer)
If you could give me the full query that will certainly help.
thx
Minoo S
|
|
|
|
|
Help anyone ?? I still need to calculate the wait time.
Minoo S
|
|
|
|
|
Hi
how to make one varable local for some store procedure pleas give me example??
good bye
best regards
|
|
|
|
|
To make a local variable inside a stored procedure use DECLARE , e.g.
DECLARE @myLocalVar INT
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Hi!!
How do return the error code and description from a stored procedure to the .cs code? Lets say an INSERT statement was not successful then I want to display the error code, and error description to the user on the.aspx page.
Hope some one can help me.
Regards,
ma se
|
|
|
|
|
The SqlException that is thrown will contain this information. The Message will, naturally, contain the error message and the Number will contain the number of the error. However, it is possible for the SQL Server data provider to return multiple errors. In which case you can use the Errors property to get a collection of SqlError objects.
You can find more information on MSDN: SqlException[^]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
In T-SQL (e.g. in a trigger) , if you use RAISERROR with an error severity of 16 or higher it gets returned to the client application.
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Aloha to all,
I have thrown together some code that generates a file with a lot of data (something like 200,000 rows). I would like to load all this data in a SQL Server table, but my attempt to execute BCP from C# doesn't want to behave.
I do as follows (more of less stealing everything from http://dotnetjunkies.com/WebLog/stefandemetz/archive/2004/08/19/22566.aspx ):
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.EnableRaisingEvents = false;
proc.StartInfo = new System.Diagnostics.ProcessStartInfo();
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.FileName = "bcp";
proc.StartInfo.Arguments = @"DATABASE_NAME.dbo.TABLE_NAME in c:\inetpub\wwwroot\project\bcpData.txt -c -Uusername -Ppassword -t',' -Sservername";
proc.EnableRaisingEvents = true;
proc.Start();
string s1 = proc.StandardOutput.ReadLine();
string s2 = proc.StandardOutput.ReadLine();
All I get from this code is:
s1 = "SQLState = 28000, NativeError = 18456";
s2 = "Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'username'."
I have off course checked the username/password a thousand times and it is correct. If I copy my Arguments string to the command prompt, I can execute BCP without any problem
Thank you and roger over, Mads
phi
phimix.com
|
|
|
|
|
It works now. Some 'Rob Farley' from microsoft.public.sqlserver.programming group got the idea to put everything in a .bat file and that helped!
Thanks to Rob Farley.
phi
phimix.com
|
|
|
|
|
I want to do a project in Sql Server. Iam new to Sql server and actually i dont know Oracle also.But The Ray of Hope is I know the Database concepts.
1.Is it Oracle Really necessary to learn Sql Server ?
2.What are the books I need to read for this ?
krishna
|
|
|
|
|
Krishnatv wrote: 1.Is it Oracle Really necessary to learn Sql Server ?
No. Oracle is just another database system - It has nothing to do with SQL Server.
Krishnatv wrote: 2.What are the books I need to read for this ?
There are many books and I couldn't really recommend one because all the books I have are reference books rather than teaching books. Also, everyone learns differently: Go to your local bookshop and see what they have, flick through the pages and see if it is the type of thing you would feel comfortable with.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
Hi
I have a report for printing order delivery labels. Each order can have multiple delivery addresses, so this is a nested report with the order report running first and then the delivery address report running as a sub-report.
The problem is that I want to set the labels so that up to 4 different delivery addresses for the same order could be printed on a single page.
I am not sure how to set the layout of the sub report, currently it prints each delivery address 4 times on a page as opposed to 4 different delivery addresses.
If the order had 5 different delivery addresses, I would need the first page to show the first 4 and the second page to just show 1 with 3 blank labels.
Has anyone any experience of this or does anyone know how to set this up?
|
|
|
|
|
Go to report layout and set it to break the page (Page Break) after every "parent" record.
Its much possible with reporting services. I dont have it installed rite now, but i know its possible. Try to search in properties box for the layout items.
Best Regards,
Darpan Gogia
|
|
|
|
|
Hi.
I want to retrieve the list of indexes against a table, and the fields in the index. Also if posible the ordinal of that index.
I know that sp_helpindex, will retrieve that information, but I want to do it through TSQL. Does anyone know the query or queries to retrieve this information, please post it here. Also performance is an issue, so this info must be retrieved fast.
:->:->:->
.NET Rules
|
|
|
|
|
Sk8tzz wrote: I know that sp_helpindex, will retrieve that information, but I want to do it through TSQL.
sp_helpindex is using TSQL. I suspect you mean you don't want to use a stored procedure?
You may want to take a look at the sysindexes[^] table. This will be the fastest way to retrieve the information you want - however, you'll have to join it to some of the other system tables to get the names of the columns and so on.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
I know that, I looked at that sp and I'm still fuzzy about what exactly its doing. Is there a simpler way of quering this info.
.NET Rules
|
|
|
|
|
Why not use sp_helpindex?
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
CREATE TABLE #TempIndexes (table_name varchar(255),index_name varchar(255),index_description varchar(255),index_keys nvarchar(2126))
CREATE TABLE #TempHoldIndexes (index_name varchar(255),index_description varchar(255),index_keys nvarchar(2126))
DECLARE @tablename sysname
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @tablename = RTRIM(@tablename)
INSERT #TempHoldIndexes EXEC sp_helpindex @tablename
INSERT #TempIndexes SELECT @tablename, index_name,index_description,index_keys FROM #TempHoldIndexes
DELETE FROM #TempHoldIndexes
END
FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
SELECT * FROM #TempIndexes
DROP TABLE #TempIndexes
DROP TABLE #TempHoldIndexes
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Or, here is a modified version of sp_helpindex that you can run from the query analyzer:
-- create temp table
create table #spindtab
(
table_name sysname,
index_name sysname collate database_default NOT NULL,
stats int,
groupname sysname collate database_default NOT NULL,
index_keys nvarchar(2126) collate database_default NOT NULL -- see @keys above for length descr
)
DECLARE tnames_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
declare @objname nvarchar(776) -- the table to check for indexes
FETCH NEXT FROM tnames_cursor INTO @objname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @objname = RTRIM(@objname)
-- PRELIM
set nocount on
declare @objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid smallint, -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@dbname sysname
-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
--return (1)
end
-- Check to see the the table exists and initialize @objid.
select @objid = object_id(@objname)
if @objid is NULL
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
--return (1)
end
-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
declare ms_crs_ind cursor local static for
select indid, groupid, name, status from sysindexes
where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @status
-- IF NO INDEX, QUIT
if @@fetch_status < 0
begin
deallocate ms_crs_ind
--raiserror(15472,-1,-1) --'Object does not have any indexes.'
insert into #spindtab values (@objname, '', '', '','')
CONTINUE
--return (0)
end
-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @i int, @thiskey nvarchar(131) -- 128+3
select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end
select @groupname = groupname from sysfilegroups where groupid = @groupid
-- INSERT ROW FOR INDEX
insert into #spindtab values (@objname,@indname, @status, @groupname, @keys)
-- Next index
fetch ms_crs_ind into @indid, @groupid, @indname, @status
end
deallocate ms_crs_ind
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1) select @empty = ''
declare @des1 varchar(35), -- 35 matches spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216
end
FETCH NEXT FROM tnames_cursor INTO @objname
end
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
-- DISPLAY THE RESULTS
select
'table_name' = table_name,
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname),
'index_keys' = index_keys
from #spindtab
order by index_name
--return (0) -- sp_helpindex
DROP TABLE #spindtab
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|