|
mghiassi wrote: it executes these with error
What error?
|
|
|
|
|
Hi
The statement below return another code and not zero
SELECT ErrorProcedure AS ERRORPROCEDURE
Thanks for your help
|
|
|
|
|
mghiassi wrote: The statement below return another code and not zero
That is hardly helpful information. What code? If you remove the TRY/CATCH, what error is given (That should give you a textual description of the error)
|
|
|
|
|
Hi guys ! I have installed windows vista and i also installed microsoft sql sever 2005 express edition, and also I have downloaded the requierd update ! but when i want to create a database i get this message :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
The server principal "MRK\Mr.K" is not able to access the database "model" under the current security context. (Microsoft SQL Server, Error: 916)
|
|
|
|
|
hi,
i need a help in an automation of data fetch from db to excel.
I am having 2 tables in sql server db. at present i will type hundreds of number in excel sheet then i will import that to sql server as a table then executing that table and the primary table using some sql statement i used to fetch the required information. now the problem is when we are doing it for multiple records its too difficult to repeat the process repeatedly.so i want to automate this process.
for example:
primary table:
slno eno,ename, eaddress, ecity
exporting data through excel is eno
comparing this eno in excel with the primary table i can fetch the remining details to the specified set of eno.
how to automate if anyone have idea please share it. so that i can begin my process as soon as possible.
Tech_spidy
|
|
|
|
|
I would used DTS. It comes with SQL Server.
If you have sql server 2000 go to where you start enterprise manager. There is an icon that says import export. If you are using sql 2005 you need to go to the database you want to export the table. Right click the database go to tasks then export.
Pick your sql server name and database to export from.
Pick Excel from the drop down and set the output file name.
Then pick the table / table names or do a query.
I think that could work pretty nice for you process.
I hope that helps.
Ben
|
|
|
|
|
This is what i am already doing. I need a solution like by clicking a single button it has to export the data by executing the query in db.
Tech_spidy
|
|
|
|
|
i want copy of sql table on lan computre can i copy paste
how???
|
|
|
|
|
You can do this job by this solution
Generate the script code for the table by right click on the table and select script table as and
select Create To File and then run this file on the another computer
|
|
|
|
|
You can do this with "Copy" option of right clicking that particular table and paste in Query Analyzer - simple!!
|
|
|
|
|
Is this way a good solution in SQL Server 2005
|
|
|
|
|
hi all
can any body guide me please the exzact difference between a stored procedure,view and a simple aql query what is the major difference based on functionality between these three ,,,
thanks in advance
hello
|
|
|
|
|
A stored procedure is a cached pre compiled sql statement that exists on the database. It can have parameters or not, it can return result sets or not.
A Sql Query is a sql statement that you pass to the database.
A view is a subset or whole table or joined tables that you want to expose. Often if you don't want to do the same join over and over again you can create a view that does the join and only has the columns you are interested in. Some views are updateable, normally when they are only of a single table. Some people use views to control what other users have access to. So the user does not have access to the table, but the do have access to the view.
You can always do some searching on the net to get more info:
http://msdn2.microsoft.com/en-us/library/ms189826.aspx[^]
Hope that helps.
Ben
|
|
|
|
|
Hi, I am working on this master data repeater and got almost everything working, i have 3 levels of categories that show correct
i believe the problem is on the data that i provide through stored ptocedures
then i have the items under the first level almost fine, they are showing fine under their first level category, but the second and third go all under the first instead of group nicelly under their second like
<table><br />
<tr><br />
<td>correct:</td><td>how it is:</td><br />
<td>1.</td><td>1.</td><br />
<td>1.1</td><td>1.1</td><br />
<td>2.</td><td> 2.1 </td><br />
<td>2.1</td><td>3.2</td><br />
<td>3.</td><td>2.</td><br />
<td>3.1 </td><td>3.</td><br />
</tr><br />
</table>
here it is how the relation goes:
DataRelation relation1 = new DataRelation("STOCK_CAT_1", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Items"].Columns["CAT_LEVEL_1"]);<br />
ds.Relations.Add(relation1);<br />
<br />
DataRelation relation2 = new DataRelation("STOCK_CAT_2", ds.Tables["Cat1"].Columns["LEVEL1"], ds.Tables["Items"].Columns["CAT_LEVEL_2"], false);<br />
ds.Relations.Add(relation2);<br />
<br />
DataRelation relation3 = new DataRelation("STOCK_CAT_3", ds.Tables["Cat1"].Columns["LEVEL2"], ds.Tables["Items"].Columns["CAT_LEVEL_3"], false);<br />
ds.Relations.Add(relation3);<br />
<br />
<br />
DataRelation relation4 = new DataRelation("CAT_1toCAT2", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Cat1"].Columns["LEVEL1"]);<br />
ds.Relations.Add(relation4);<br />
<br />
DataRelation relation5 = new DataRelation("CAT_2toCAT3", ds.Tables["Cat1"].Columns["STOCK_CAT_ID"], ds.Tables["Cat1"].Columns["LEVEL2"]);<br />
ds.Relations.Add(relation5);
then when i remove the false from ["CAT_LEVEL_3"], false); to create a relation, ig throws a "These columns don't currently have unique values"
here is the data, can anyone spot what is wrong?:
Items | Items | Items | | Cat1 | Cat1 | Cat1 | CAT_LEVEL_1 | CAT_LEVEL_2 | CAT_LEVEL_3 | | STOCK_CAT_ID | Leve1 | Level2 | 62 | 0 | 0 | | 174 | 0 | 0 | 62 | 0 | 0 | | 65 | 0 | 0 | 62 | 0 | 0 | | 176 | 0 | 0 | 62 | 0 | 0 | | 177 | 0 | 0 | 62 | 0 | 0 | | 178 | 0 | 0 | 62 | 0 | 0 | | 175 | 0 | 0 | 62 | 0 | 0 | | 179 | 0 | 0 | 62 | 0 | 0 | | 180 | 0 | 0 | 62 | 0 | 0 | | 181 | 0 | 0 | 62 | 0 | 0 | | 182 | 0 | 0 | 62 | 0 | 0 | | 213 | 182 | 0 | 62 | 0 | 0 | | 214 | 182 | 0 | 62 | 0 | 0 | | 215 | 182 | 0 | 62 | 0 | 0 | | 216 | 182 | 213 | 62 | 0 | 0 | | 217 | 182 | 213 | 62 | 0 | 0 | | 218 | 182 | 213 | 65 | 0 | 0 | | 219 | 182 | 213 | 65 | 0 | 0 | | 220 | 182 | 213 | 176 | 0 | 0 | | 221 | 182 | 213 | 176 | 0 | 0 | | 222 | 182 | 213 | 176 | 0 | 0 | | 223 | 182 | 213 | 176 | 0 | 0 | | 224 | 182 | 213 | 176 | 0 | 0 | | 225 | 182 | 213 | 176 | 0 | 0 | | 226 | 182 | 214 | 176 | 0 | 0 | | 227 | 182 | 214 | 176 | 0 | 0 | | 228 | 182 | 214 | 176 | 0 | 0 | | 229 | 182 | 214 | 176 | 0 | 0 | | 230 | 182 | 214 | 176 | 0 | 0 | | 231 | 182 | 214 | 176 | 0 | 0 | | 232 | 182 | 214 | 176 | 0 | 0 | | 233 | 182 | 214 | 176 | 0 | 0 | | 234 | 182 | 215 | 176 | 0 | 0 | | 235 | 182 | 215 | 176 | 0 | 0 | | 236 | 182 | 215 | 176 | 0 | 0 | | 237 | 182 | 215 | 176 | 0 | 0 | | 238 | 182 | 215 | 176 | 0 | 0 | | 239 | 182 | 215 | 176 | 0 | 0 | | 240 | 182 | 215 | 176 | 0 | 0 | | 241 | 182 | 215 | 176 | 0 | 0 | | 242 | 182 | 215 | 176 | 0 | 0 | | 243 | 182 | 0 | 176 | 0 | 0 | | 244 | 182 | 243 | 176 | 0 | 0 | | 62 | 0 | 0 | 176 | 0 | 0 | | 63 | 0 | 0 | 176 | 0 | 0 | | 246 | 182 | 243 | 176 | 0 | 0 | | 247 | 182 | 243 | 177 | 0 | 0 | | 248 | 182 | 243 | 177 | 0 | 0 | | 245 | 182 | 243 | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 177 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 178 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 179 | 0 | 0 | | 181 | 0 | 0 | | 181 | 0 | 0 | | 182 | 213 | 216 | | 182 | 213 | 216 | | 182 | 213 | 216 | | 182 | 213 | 217 | | 182 | 213 | 217 | | 182 | 213 | 217 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 218 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 220 | | 182 | 213 | 221 | | 182 | 213 | 221 | | 182 | 213 | 221 | | 182 | 213 | 221 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 222 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 223 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 224 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 213 | 225 | | 182 | 214 | 222 | | 182 | 214 | 222 | | 182 | 214 | 222 | | 182 | 214 | 223 | | 182 | 214 | 232 | | 182 | 214 | 232 | | 182 | 214 | 232 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 214 | 233 | | 182 | 243 | 244 | | 182 | 243 | 245 | | 182 | 243 | 245 | | 182 | 243 | 246 | | 182 | 243 | 247 | | 182 | 243 | 248 | | | | | | | | |
|
|
|
|
|
Did you read the posting guidelines?
Did you take note of rule 4: Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
Did you even notice the warning before you posted that your message was very long? You must have, because you would have had to acknowledge it. I don't supposed you really cared. That shows a complete disregard and disrespect for other people here.
|
|
|
|
|
I have a stored proc which takes as input a user name and a password, and checks against a table if the user name and password exist. If it is a valid username-password,there is a return code of 0 followed by the customer name,address1,city,state etc(these are the fields in the table), if it is an invalid combination of username and password i get a return code of 1....how do i get the values from the db. here is the code i have written...i manage to get only the return code.
imports Microsoft.applicationblocks.data
'-----------below is the code in my function
Dim oPar() As SqlParameter = New SqlParameter(2) {}
'Set Parameters Up
oPar(0) = New SqlParameter("@UserID", Data.SqlDbType.VarChar, 40)
oPar(0).Value = userid
oPar(1) = New SqlParameter("@PW", Data.SqlDbType.VarChar, 20)
oPar(1).Value = password
oPar(2) = New SqlParameter("@Return", Data.SqlDbType.Int)
oPar(2).Direction = Data.ParameterDirection.Output
SqlHelper.ExecuteNonQuery(SetupProperties.ApplicationsDBConnection, Data.CommandType.StoredProcedure, _
"WebUserProc", oPar)
If oPar(2).Value Is DBNull.Value Then
Return ""
Else
Return (oPar(2).Value)
End If
Please help !!!!
RH
|
|
|
|
|
Because you are using ExecuteNonQuery the stored procedure select statements will not be returned. You will need to executereader or something like that to return data.
Hope that helps.
Ben
|
|
|
|
|
I am running SQL Server 2005 and I'm using Sql Server Management Studio Express. Is there an easy way that I can quickly generate creation scripts for my database (including table!)? If not with what I have is there a tool I can grab to do this? All I'd like to do is just right click the database and select the 'Generate Creation Scripts' and get all I need to build a new database with my tables from scratch.
Thanks,
Michael
-- modified at 13:14 Friday 16th March, 2007
|
|
|
|
|
Have you tried the following from SSMS:
1) Right-click database
2) Tasks - Generate Scripts...
3) Select Database and check the box at the bottom "Script all objects in the selected database"
The rest of the steps in the wizard allow you to set options for the output (location and script settings). Does this meet your needs? Or do you need additional functionality not offered by the Generate Scripts wizard?
|
|
|
|
|
Thanks much.
You know, I've been looking at that context menu but never 'saw' the Generate Scripts option at the bottom. Just stopped looking once I saw the Create Script... option. Thanks, that worked just great!
Michael
|
|
|
|
|
Hello all
How can I rename an instance of sql server not in sql.
Clint
|
|
|
|
|
I don't believe it's possible to rename a SQL Server instance. I think the only way to achieve that is to uninstall it and install a new instance with a different name. Be sure to back up all your databases before doing it so that you can restore them. Alternatively you might be able to detach and reattach the databases to the new instance (but I'd still take a backup first).
|
|
|
|
|
thanks
|
|
|
|
|
I have a question about stored procedures Vs dynamic SQL.
I'm a noob but from the googling I've done this seems to be a hot topic so I dont want to start an arguement but I'm stuck.
The website I am working on has a data access layer that takes in a SP name & parameters. It runs the SP and returns the resulting data to the page that requested it.
This is easy & quick to maintain for me.
However, my users want to be able to search the customer using search criteria parameters that can be different each time. By that I mean they want to be able to search for customers using whatever data they have. Sometimes they will have a last name, other times a last name & birth date, sometimes a phone number and a first name.
This doesnt seem to lend itself to a SP - the parameters change each time so the solutions seem to be:
1. A SP that has every criteria in it and use LIKE '%%' when the customer doesnt use one of them
2. Have the data access layer dynamically build the required SQL and then insert it into the SP somehow. (Which sorta kinda sounds like SQL injection)
3. Build an SP for each group of search criteria (Name, DOB, Phone numbers, TIN) and then only allow the users to use one search criteria group at a time.
Neither solution sounds that great to me. (There may be others but like I say I'm a noob)
Which leads me to think that maybe in this case I should use Dynamic SQL - e.g. have the data acccess layer create the SQL statement and then execute it against the database without using an SP.
But then that breaks my rule of only using SP (which I originally created cos I thought SP ran quicker but then I read that according to some guys at MS that dynamic SQL runs as quickly as SP in the .Net environment) so now I'm confused.
|
|
|
|
|
If you want to continue using SPs, you could always check the value of the parameter to see if it is null or not in the where clause.
For instance:
SELECT ....
FROM table1
WHERE
(Col1 = @Col1 OR @Col1 IS NULL) AND
(Col2 = @Col2 OR @Col2 IS NULL)
Deja View - the feeling that you've seen this post before.
|
|
|
|