MS SQL Server :: Help: Why Excute A Stored Procedure Need To More 30 Seconds, But Direct Excute The Query Of This Procedure In Microsoft SQL Server Management Studio Under 1 Second

Question About Excute Store Procedure
Hi, all

i create a function("changefilepermission") to execute a procedure ("grant_file_access") to change the file permission. i click the permission checkbox to change new permission, after that i will click the submit button to update change to the database. but it does not change to the database. this is my part of code. is anybody can give me a help?

thanks in advanced!!!!!!!!!

Private Sub btnsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click

Dim FileTitle As New String("")
Dim FileName As New String("")

FileName = DGPermission.Items.Item(0).Cells(0).Text 'retrieve filename from datagrid
FileTitle = DatabaseCommand(userid, "fa_title", filename) ' retrieve the filetitle from table

Dim permission As Char ' set the permission value
If (CkRead.Checked) Then
Permission = "r"
ElseIf (CkWrite.Checked) Then
Permission = "w"
ElseIf (CkExecute.Checked) Then
Permission = "o"
End If

Try
' call the store procedure function by passing 4 value
ChangeFileAccess(userid, FileName, FileTitle, Permission) '
Catch ex As Exception
lblErrorMsg.Text = ex.ToString
End Try
End Sub

' execute store procedure function
Public Sub ChangeFileAccess(ByVal userid As String, ByVal DiskFilename As String, ByVal Title As String, ByVal Access As Char)
Dim UpdateCommand As SqlCommand
UpdateCommand = New SqlCommand

With UpdateCommand
.Connection = SqlConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "Grant_File_Access"
.Parameters.Add("@vu_id", SqlDbType.VarChar, 20).Value = userid
.Parameters.Add("@DiskFilename", SqlDbType.VarChar, 64).Value = DiskFilename
.Parameters.Add("@Title", SqlDbType.VarChar, 50).Value = Title
.Parameters.Add("@Access", SqlDbType.Char, 1).Value = Access
End With

Try
UpdateCommand.Connection.Open()
UpdateCommand.ExecuteReader() ' call the store procedure
UpdateCommand.Connection.Close()
Catch ex As Exception
lblErrorMsg.Text = ex.ToString
End Try
End Sub

View Replies !

Why Query Takes 0 Seconds And Stored Procedure Takes 16 Seconds Sql Server 2000

I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.

It doesn't recompile when i run the stored procedure, I checked that.

View Replies !

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack

View Replies !

How To Excute A Query In CLR Function? Thanks
Since it doesn't allow run dynamic query in a sql function, so I am thinking to create a CLR function to run dynamic query and call the CLR function in my sql funciton, is that a good idea? and how should I call the dynamic sql query?

thanks

View Replies !

Rename A Stored Procedure In Server Management Studio
How do you rename a stored procedure in SQL Server Management Studio? I tried right clicking on the stored procedure and I don't see a Rename option.

View Replies !

How Do I Create A Stored Procedure In SQL Server Management Studio Express?
I have wrriten many stored procedures in the past without issue, but this is my first time using SQL Server Management Studio Express. I am having trouble creating a new stored procedure. Here is what's happening:
I am opening my database, right clicking on "Stored Procedures" and selecting "New Stored Procedure". A new window opens with a template for creating a stored procedure. The window is called: "SQLEXPRESS.DBName - sqlquery1.sql". I then type up my stored procedure without an issue.
However, when I go to save the stored procedure it wants to save it as a separate file in a projects folder. It does not become part of the DB (as far as I can tell).
When I used to use Enterprise Manager (not an option anymore) this never happened.
I'm sure I'm doing something dumb. Can someone enlighten me.
Thanks,Chris

View Replies !

Attaching A Stored Procedure To A Database In MS SQL Server Management Studio
Hello all!

Quick question... I've created my DB in MSSQLSMS, then attempted to created a stored procedure for it. The code itself is fine, I just need to know how to actually attach it so that it appears in the "Stored Procedures" section of my Database.

I have Right Clicked on Stored Procedures > New Stored Procedure... > Edited as required > Save

When I attempt to save it, it prompts me for a file. That's fine, did that - but I can't see ANY way to actually attach this to the DB.

Any help is appreciated!

Daniel Davies

View Replies !

How To Empty A Stored Procedure In Ms Sql Server Management Studio Express
hi everyone,

I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id´s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?

Thanks in advance, best regards

bg

View Replies !

How To Save A Stored Procedure With Management Studio?
Hi,
i can make and save a stored procedure in Visual Web Developer (via Database Explorer). It appears then in the list op stored procedure in Management Sudio.
But how to do the same in Management Studio? When i make a sp and i want to save it, Management Studio asks me a name, but put the file in a Projects directory in 'My documents'. It never appears in the list of sp.
Thanks
tartuffe

View Replies !

Query Abt Microsoft SQL Server Management Studio

Hi all,
I am Vaibhav.I am using Microsoft SQL Server Management Studio 9.00.1399.00 on my machine , which have Windows 2000 Professional OS installed. Now I am upgrading My ystem to Windows Vista OS.
And I am not getting whether Microsoft SQL Server Management Studio 9.00.1399.00 will support Windows Vista? or I have to go for Higher version available for Microsoft SQL Server Management studio ?
Please asist me on same.

Thanks,

Vaibhav Tare

View Replies !

How To Test Stored Procedure With Output Parameters In Management Studio
I have this SP:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetSessionInformation]
@CustomerID int,
@Success bit OUTPUT,
@Email VarChar(55) OUTPUT,
@FirstName VarChar(55) OUTPUT,
@LastName VarChar(50) OUTPUT,
@PhoneNumber VarChar(50) OUTPUT,
@CompanyName VarChar(50) OUTPUT
AS

SET NOCOUNT ON

DECLARE @UserKey AS int

SELECT @CustomerID = CustomerID
FROM Customers
WHERE CustomerID = @CustomerID

IF @CustomerID IS NULL
BEGIN
SET @Success = 0
END
ELSE
BEGIN
SET @Success = 1
END

BEGIN

SELECT customerID, Email, FirstName, LastName, PhoneNumber, CompanyName
FROM Customers
WHERE CustomerID = @UserKey

How do I test it in management studio?

When I run a EXECUTE GetSessionInformation 56

I get this error:
Procedure 'GetSessionInformation' expects parameter '@Success', which was not supplied.

Thanks for any help!

View Replies !

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png

Any ideas for this issue?

Thanks a lot

View Replies !

Excute Xp_cmdshell Run BCP In SQL Scheduler
Hi,

I have a schedule task with the following command:
xp_cmdshell 'bcp DBName.TBLOwner.TBLName in h:datafile.txt /fh:formatfile.fmt /Sservername /Usa /Ppassword'
The error message that I recieved is "Unable to open Host file. I can run the BCP command in Dos C prompt successfully, but
cannot run this in the SQL Server Scheduler. H drive is net use to the Server and it is where my data file and format file located.
Does anyone has any idea? I do appreciate for your help.

View Replies !

Can't Excute SSIS Package
So everything was working great. Now I have a few problems. The little play button is no longer available for me to puch and when I try a right click execute package a get a "Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)". Does anyone know how to fix this? Also when I try and copy tasks from within my package I get a "An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)"
followed by "Could not copy object 'package name' to the clipboard.
(Microsoft.DataTransformationServices.Design)" and then "Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)"

Does anyone what might be wrong

Thanks in advance

View Replies !

How To Excute Package In Sequence
How to excute package in sequence . i have an requirement like this the package should execute in a sequence

1.PKG1

2.PKG2

3.PKG3

and in some case PKG1,.PKG2,.PKG3 shoule run parallel.

Thanks & Regards

Jegan.T

View Replies !

Variable Problem In Excute SQL Task
Hi there

I have a bit confusing problem,

In Execute SQL Task I am having a stmt like : (Select * from Emp where EmpNm=?) also I m passing variable value through Parameter Mapping.

Problem I am facing is that if I remove brakets from both end task is executed successfull but if I but those braket as they are task execution fails. and i need those brakets as it is inside a if statement.

Problem is with parameter passing ,even if i put some value as EmpNm="ABC " with brakets, it runs successfully

Any Solution to above problem

Thanks and Regards

Rahul Kumar, Software Engineer

View Replies !

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio Express (Part 2)
Hi Jonathan Kehayias, Thanks for your valuable response.

I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.

Here is my response to the last code/instruction you gave me:

I corrected a small mistake (on Integrated Security-SSPI and  executed the last code you gave me.

I got the following debug error message:

1) A Box appeared and said:   String or binary data would be truncated.

                                             The statement has been terminated.

                                                            |OK|

2) After I clicked on the |OK| button, the following message appeared:

                                   This "SqlException was unhandled

                                             String or binary data would be truncated.

                                             The statement has been terminated."

                                    is pointing to the "Throw" code statement in the middle of

                                                                                 .......................................

                                                                                 Catch ex As Exception

                                                                                       MessageBox.Show(ex.Message)

                                                                                       Throw

                                                                                  Finally

                                                                                  ..........

Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.

Thanks,
Scott Chang

The code of my Form1.vb is listed below:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7

command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"

command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"

command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"

command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"

command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"

command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"

command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"

Dim resulting As String = command.ExecuteNonQuery

MessageBox.Show("Row inserted: " + resulting)

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

InsertNewFriend()

End Sub

End Class

View Replies !

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC && Sp?
Hi all,

In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--

USE shcDB

GO

CREATE PROC sp_insertNewRecord @procPersonID int,

                                                       @procFirstName nvarchar(20),

                                                       @procLastName nvarchar(20),

                                                       @procAddress nvarchar(50),

                                                       @procCity nvarchar(20),

                                                       @procState nvarchar(20),

                                                       @procZipCode nvarchar(20),

                                                       @procEmail nvarchar(50)

AS INSERT INTO MyFriends

VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,

@procCity, @procState, @procZipCode, @procEmail)

GO

EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',

'Michigon', '67899', 'PeterWang@yahoo.com'

GO

=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"

Dim connection As SqlConnection = New SqlConnection(connectionString)

connection.Open()

Try

Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure

EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'

Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

End Class

===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.

Please help and advise me how to correct these problems.

Thanks in advance,
Scott Chang

View Replies !

How Do I Excute DBCC DBreindex From Public Group
Hi All
Could you guys help me i have small problem in running dbcc dbreindex.I could run this command from dbo,sysadmin and
Object owner logins to rebuild the index but what i want to do is i want to run this command in a stored procedure.
this is also fine if i run the stored procedure any one of above logins it executes when i grant exec permission on this procedure
to public it will not execute in public group since public group is not a owner of this object..
How do i excute DBCC DBreindex from public group.Could you please help me.....................

View Replies !

Shortcuts, Direct Keys In Management Studio ?
Hello. Does anybody knows if the are shortcuts to differrent menu items in Sql 2005 Management Studio ?For example i will to use the "Execute " button without need to use the mouse, as this case as of others.Any knows where can i find a map or a page info with the shortcuts of SQL2005 Management Studio ? Thanks

View Replies !

Management Studio Express && SQLcmd: Syntax Errors In Executing A MS-DOS Batch File && A Microsoft SQL Server Query File?
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.

In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
    sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
     "Shippers" -i c:prosseappschapter02ListListColumnVales.sql   -o
     c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
     USE $(Northwind)
     GO
     SELECT $(CompanyName) FROM $(Shippers)
     GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:

'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.

I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.

Thanks in advance,
Scott Chang

View Replies !

Microsoft SQL Server Management Studio
I was looking for Microsoft SQL Server Management Studio but downladed Microsoft SQL Server Management Studio EXPRESS.
the express version doesn't have the SQL server agent. I read that the express doesnt support SQL server agent. Is there any add-on or service pack available for this.
I am trying to find "SQL server Management studio". Any idea where I can download it?

View Replies !

Microsoft SQL Server Management Studio
i try to open database table using Microsoft SQL Server Management Studio i got the follwing error msg i don't know what the problem

===================================
Object reference not set to an instance of an object. (SQLEditors)
------------------------------Program Location:
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.OpenTableNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection) at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner() at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open() at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con) at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con) at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

View Replies !

Trigger - Microsoft SQL Server Management Studio
Hi,
How to create Trigger in "SQL SERVER 2005 - Microsoft SQL Server Management Studio"? This is my 1st interaction with SQL SERVER 2005.

Thanks,
Rahul Jha

View Replies !

Microsoft SQL Server Management Studio Express

Is Microsoft SQL Server Management Studio Express (sql 2005) compatible with Sql Server 2000 databases?
I'm using Windows Vista and the sql 2000 Enterprise Manager is not fully compatible with Vista...long not responding messages.
Can i use the client version of Slq 2005?

Regards

View Replies !

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Hi all,

I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.

Whenever I tried to right click stored procedure and select step into store procedure> i get following error

"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"

I am not sure what needs to be done on sql server side

We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?

Please advise..

Thank You

View Replies !

Microsoft SQL Server Management Studio Express Problem
Microsoft SQL Server Management Studio ExpressI have restored the database into it with 100tables and stored procedures.I have also created the new database in it.But,i don't know how to add the existing database in to Visual Studio.Net 2005 .I m using ASP.Net.Where are the database stored ?? I don't want to create the new database,.Can anyone pls tell me ....

View Replies !

Is It Possible To Create A DTS Package Using Microsoft Sql Server Management Studio
Is it possible to create a DTS package using microsoft sql server management studio. if yes? How can i do it.

Any help is appreciated.

Regards,

Karen

View Replies !

Reinstalling Microsoft Server Management Studio Express

I was having some trouble with the DTS Wizard and so I thought I'd just reinstall the whole MSSM. I used remove programs, rebooted my Vista computer and am trying to reinstall the Server Express Toolkit. It tells me that MSSM already exists on my computer (which it doesn't).

The actual screen reads:

Existing components
The following components that you chose to install are already installed on the machine.
Grayed out -- Workstation components and development tools 9.2.3042.00

How do I reinstall MSSM now? It's gone from my computer.

Thanks!

View Replies !

How To Get Started On Microsoft SQL Server Management Studio Express
Hi,

Was trying to open AdventureWorks when i got this error: "There is no editor available "C: Program Flies.....AdventureWorksDW_Log.LDF'
Make sure the application for the file type(.LDF) is installed.
How can this error be corrected? I also have some error on mdf not installed.May i know how should be the initally steps to be done inorder for me to view this database.
And if i do have a database from microsoft access, what are the steps that i need to do inorder to get it export to Microsoft SQL Server Management Studio Express. Please advise as i really new to it. Thanks!

View Replies !

Can't View .mdf Files In Microsoft SQL Server Management Studio Express
I am unable to view any .mdf files in Microsoft SQL Server Management Studio Express, even though it is looking for an .mdf extension. Why is this? Is this a limitation of the Express version?More specifically i want to view the aspnetdb.mdf file that is created with each web application.Any thoughts? Thanks.Jason

View Replies !

Can't Connect To Reporting Services Using Microsoft SQL Server Management Studio
When I try to connect to the Reporting Services using Microsoft SQL Server Management Studio, the following error message propmpts up:

_____________________________________________________________
ADDITIONAL INFORMATION:
The request failed with HTTP status 401: Unauthorized. (Microsoft.SqlServer.Management.UI.RSClient)
_____________________________________________________________

Is there any settings that I need to do in the "Reporting Services Configuration Manager" ?

Thanks.

Best regards,

Michael Wu

View Replies !

Microsoft SQL Server Management Studio Express Version To Use For SQL Compact
Hello,

i don't know if it was mentioned before, but i solved the issue that Microsoft SQL Server Management Studio Express won't show the option to create/modify a sql compact database by installing not this Version:

Microsoft SQL Server Management Studio Express
Date: 19.04.2006
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en

but this one:

SQL Server Management Studio Express Service Pack 2 €“ CTP-Version
Date: 19.12.2006
http://www.microsoft.com/downloads/details.aspx?FamilyID=ccbda432-8ecf-4c82-bdef-b575eaf07f55&DisplayLang=en

hope this helps.

Nihat

P.S. i'm curious if anybody else had the same issue, caused of using the wrong Version of SSMSe?

View Replies !

Importing Tables To Microsoft SQL Server Management Studio Express
Hello,

I'm trying to import some tables from another sql server to my laptop. I just downloaded Microsoft SQL Server Management Studio Express and i'm not sure how to import things. Could anyone help me with that?

Thanks a bunch

j

View Replies !

Microsoft SQL Server Management Studio Express (SSMSE) Upgrade
Hi

Apologies for this question

I have just found out i/we need to upgrade to the full version of this management tool to make use of SSRS for a new CRM we are putting in place, The CRM company tells me we can just install the tool from our SQL 2005 disk.

Im wondering what the best way to go about this is? i would guess we just un-install the express version install the full version of the management tool and reconnect to the database. unless im missing anything?

thanks

View Replies !

Problem Copying Database With Microsoft SQl Server Management Studio
I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).

I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:

Server user 'username' is not a valid user in database '123foo'

(123foo is the first database of all those on the server)

I need have it look only at my database which I have permissions for to copy it. How do I do this?

I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?

Thanks for any help!

View Replies !

Problems Downloading Microsoft SQL Server Management Studio Express
I everybody,

I'm trying to download Microsoft SQL Server Management Studio Express from

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796

However, the download never finishs.

If I try to download using a download manager it stops after 98% (the segment that stops is the first segment).
If I try to download without a download manager it stops after 1% (456Kb).

What can I do?

Is there an alternate place where I can download the file?

Thanks,

Nadav

View Replies !

Calling An SQL Server 2005 Stored Procedure Within Microsoft Access And Reading Values
Goodday.

I have finally been able to create a connection from Access to the SQL 2005 Server and was able to call a stored proc (in Server) in the following way

Code Block
    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset

    cnn.ConnectionString = "Provider='sqloledb'; Data Source='Private';" & _
        "Initial Catalog='DBName';Integrated Security='SSPI';"

    cnn.Open

    cmd.ActiveConnection = cnn
    cmd.CommandText = "sp_DefaultEntityData"
    cmd.CommandType = adCmdStoredProc

    Set rst = cmd.Execute


    rst.MoveFirst
    Do While Not rst.EOF
        lstEntity.AddItem (rst.Fields(0)), 0
        'lstEntity.AddItem (rst.Fields(1)), 1
        rst.MoveNext
    Loop

The Stored Proc is as follow:

Code Block
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_DefaultEntityData]
AS
BEGIN

SET NOCOUNT ON;

SELECT tblEntities.[Name], tblEntities.PrimaryKey
FROM tblEntities
ORDER BY tblEntities.PrimaryKey;

END

The table contains 24 entries

As you can see in the VB code, I am trying to read the returned "table" into an Access ListBox. The listbox should display the entities Name but not the Primary key, but the primary key should still be "stored" in the to so that it can be used to access other data.

I have moved the tables from Access to SQL Server 2005, and would also like to port all the sql queries to sp's in SQL Server. The old way for populating the listbox was a direct SQL query in the RowSource property field. I have tried to set the lstEntity.RowSource = rst but it did not work.

Here are my Q's:
1) As what does the SP return when it is called and is there a better way to catch it than I am doing at the moment?
2) How do I read the values into the listbox, without displaying the primary key in die box?

Thank you in advance!
Any help is very much appreciated.

View Replies !

Different Results When Running Procedure From Management Studio Vs Application Code
I'm updating a process that recreates a large table every night. The table is the result of a bunch of nightly batch processes and holds a couple million records. In the past, each night at the end of the batch jobs the table would be dropped and re-created with the new data. This process was embodied in dynamic sql statements from an MFC C++ program, and my task is to move it to a SQL Server 2000 stored procedure that will be called from a .Net app. Here's the relevant code from my procedure:
sql Code:

Original
- sql Code

-- recreate new empty BatchTable table
print 'Dropping old BatchTable table...'
exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table

-- validate drop
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'Old BatchTable dropped.'
End

print 'Creating new BatchTable...'
SELECT TOP 0 *, cast('' as char(3)) as Client, cast('' as char(12)) as ClientDB
INTO dbo.BatchTable
FROM differentDB.dbo.BatchArchives

--validate create
If Not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BatchTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT
End Else Begin
print 'New BatchTable Created.'
End

-- recreate new empty BatchTable table print 'Dropping old BatchTable table...' exec DropBatchTable --stored procedure called from old code that does a little extra work when dropping the table -- validate drop IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to drop old BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'Old BatchTable dropped.' END print 'Creating new BatchTable...' SELECT TOP 0 *, CAST('' AS CHAR(3)) AS Client, CAST('' AS CHAR(12)) AS ClientDB INTO dbo.BatchTable FROM differentDB.dbo.BatchArchives --validate create IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BatchTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN RAISERROR( 'Unable to create new BatchTable!',0,1) WITH NOWAIT END ELSE BEGIN print 'New BatchTable Created.' END

The print statements are there because the .net app will read them in and then write them to a log file. Some of the other mechanics are there to mimic the old process. The idea is to duplicate the old process first and then work on other improvements.

This works in Management studio. The .Net App reports that the old table was dropped, but when it tries to create the new table it complains that "There is already an object named 'BatchTable' in the database." I have verified that the old table is removed.

Any ideas on how to fix this?

View Replies !

Bug In Management Studio : Cannot Add Procedure Article For Heterogeneous Transactional Replication
Hi,
I'm setting up a heterogeneous transactional push replication with Sybase ASE 12.5.3 as subscriber. With management studio I try to create an procedure article with following properties

Copy extended properties       : false
Destination object name     : pGS_RefuseRequest
Destination object ownere       : dbo
Action if name is in use          : keep existing object unchanged
Replicate                           : Execution of the stored procedure
Create schemas at Subscriber : false

When I save the article and then the publication I got following error message:

Can not add artice 'pGS_RefuseRequest'.
Object was not found on server. Check if this object exists on the server. (Microsoft.SqlServer.Rmo)

That's realy strange because the wizard offered the procedure pGS_RefuseRequest in the list of possible articles.

Fortunatly I can create the article with following TSQL statement :

exec sp_addarticle @publication = N'RIGHTS_EDV_4T_pub'
, @article = N'pGS_RefuseRequest'
, @source_owner = N'dbo'
, @source_object = N'pGS_RefuseRequest'
, @type = N'proc exec'
, @description = N''
, @creation_script = N''
, @pre_creation_cmd = N'none'
, @schema_option = 0x00
, @destination_table = N'pGS_RefuseRequest'
, @destination_owner = N'dbo'
, @status = 0
go

Did anybody seen this bug before ?
It seems to be specific for heterogeneuous replication. In a pure MS environement the Wizard works fine!

Wolfgang Kunk

View Replies !

SQL Server 2005 Managment Studio Newbie: Can't See Stored Procedure
I just created a stored procedure in SQL Server 2005 management studio. I went to my db and expanded the programmability folder then right clicked the stored procedures folder and created a stored procedure. I saved the procedure as sp_getDistance in the default folder SQL managment studio picked. Now when I went back to that stored procedure folder I did not see my stored procedure sp_getDistance, all that was there was the system stored procedure folder. Did I save the stored procedure in the wrong place, or what did I do wrong. I can't find the procedure anywhere, its just sitting in my My DocumentsSQL Server Management StudioProjectssp_getDistance.sql folder. Thanks,Kyle Spitzer

View Replies !

Error In Microsoft Sql Server Management Studio Trying To Modify Tables Or Columns
I get the error below when trying to modify a table or colum with MSSMS. I can expad out the tree and look at the columns, but I can't change them, view the data in them, or create new ones.
__________________________________________________________________________________________________________________________________
TITLE: Microsoft SQL Server Management Studio
------------------------------
Class does not support aggregation (or class object is remote) (Exception from HRESULT: 0x80040110 (CLASS_E_NOAGGREGATION)) (Microsoft.SqlServer.SqlTools.VSIntegration)
I resintallelled .NET Framework, recommended from another post. I am using .NET 3.5 BETA Frame work, ans SQL Server 2005 V 9.00.139.00

View Replies !

Using Microsoft SQL Server Management Studio Express To Export/copy Database?
How does one export/copy a database using Microsoft SQL Server
Management Studio Express? At this time, I have an existing
database that I can access and run queries against. However, I
cannot connect to it via Visual Wed Developer 2005 Express. At
this time, I'm getting the following error message with the existing
database:

Login failed for user 'bigide2_gims2'. The user is not associated with a trusted SQL Server Connection.

Next, I'm running everything locally. If anyone can assist, it would be greatly appreciated and thanks in advance.

-Conrad

View Replies !

How Can I Create A New Login In Microsoft SQL Server Management Studio Express 9.00.1399.00?
How can I create a new login in Microsoft SQL Server Management Studio Express 9.00.1399.00?
I hope to create a new login in Microsoft SQL Server Management Studio Express for SQL 2005 database,I select SQL Server authentication item in New Login window, then I click Ok button, but I get the error information:
An exception occurred while executing a Transact-SQL statement or batch.The Must_CHANGE option is not supported by this version of microsoft Window, Error: 15195
Does it mean that I can not create new login in Microsoft SQL Server Management Studio Express 9.00.1399.00?

View Replies !

How Can I Remove Expiration Date From Microsoft SQL Server Management Studio (Standard Ed)
Recently we upgrade the SQL Server 2005 from Evaluation Ed to Standard Ed.
It was like a full installation since we already had installe the SP1, we had to uninstall it completely.
If we enter the following query:
SELECT SERVERPROPERTY('productversion') as ProductVersion,
        SERVERPROPERTY ('productlevel') as ProductLevel,
        SERVERPROPERTY ('edition') as ProductEdition
it shows that we have the Std Ed (not the Evaliation Ed.):

9.00.2047.00    SP1    Standard Edition

However, since a few days ago the Management Studio showed the following message: Microsoft SQL Server Management Studio (expires in 4 days).

Any help would be appreciated

View Replies !

REPORTING SERVICE CONFIGURATION FOR MICROSOFT SQL SERVER MANAGEMENT STUDIO EXPRESS
i'm a new beginner in microsoft sql server management studio. Can anybody guide me how to use it and give me tutorial about developing database using mcrosoft sql and how to make reporting services

View Replies !

Installing Microsoft SQL Server Management Studio Express Broke My Asp.net Databases
Morning,

I installed the managerment stuid on my machine and now none of my database connections seem to work any more. I keep getting the attached error. Before I installed the studio the system worked fine, I could connect to the database and everything was fine. Does the studio change any permission stuff? I cannot seem to get it to work again at all now, I added in aspnet as a login inside the studio and it still won't let me connect to the database. I cannot find *any* logs anywhere that might be helpful in what it is doing or see any way to debug the connection to find out what the problem is. When I look at the permissions inside of the studio it shows me that the connections should all be fine.

I checked and remote and local connections are setup, although it is only doing local connections anyway since this is a asp.net web server page.

Any idea what the problem is here? This is very frustrating and annoying :( Since I didn't change anything and now nothing I will do gets it to work. I even tried to uninstall the management tool and it still doesn't work.
Cannot open user default database. Login failed.
Login failed for user 'GREENANDFLUFFYASPNET'.

Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'GREENANDFLUFFYASPNET'.

View Replies !