Link SQL Server 2005 Tables to external database through ODBC

Hi

I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005.

Thanks for any help

Using a linked server, you will need to have either a OLEDB driver or a support for the OLEDB for ODBC provider. Using OPENDATASOURCE you will need to have the init string (connectionstring) for the database. You will find more information in the BOL under the topic OPENDATASOURCE.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Link SQL Server 2005 Tables to external database through ODBC

Hi

I'm a newbie at SQL 2005 and I'm trying to create linked tables to our ERP system through ODBC. I can do this in MS Access or vb.net by using the ERP system's ODBC driver, but I am lost when it comes to SQL Server 2005.

Thanks for any help

Using a linked server, you will need to have either a OLEDB driver or a support for the OLEDB for ODBC provider. Using OPENDATASOURCE you will need to have the init string (connectionstring) for the database. You will find more information in the BOL under the topic OPENDATASOURCE.

Jens K. Suessmeyer

http://www.sqlserver2005.de

link .NET assemblies into .rdl ?

Does anyone know of a way to link .NET assemblies into an .rdl and call
methods so provided ?On Aug 23, 4:19 pm, "John Grandy" <johnagrandy-at-gmail-dot-com>
wrote:
> Does anyone know of a way to link .NET assemblies into an .rdl and call
> methods so provided ?
This article should be helpful.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/bfa42df4a833016b/6f3fe1f1b28a54b6?lnk=st&q=add+assembly+reference+sql+reporting+services&rnum=1#6f3fe1f1b28a54b6
Regards,
Enrique Martinez
Sr. Software Consultantsql

link

can anybody tell me how to connect asp.net with sql server? any coding can do?Here is a link to a tutorial on data access from ASP.NET.

Line Number

Hello,
I am getting an error in my .NET app and the error comes from a SQL
Server sproc. I am able to capture the line number. My question is how
to find that line? Do I start counting from CREATE PROCEDURE or from
the AS statement? Or elsewhere?
Frank,
Execute the sproc in Query Analyzer. When the error message appears in the
result pane, double-click it - should highlight the line or general location
of the error.
HTH
Jerry
"Frank Rizzo" <none@.none.com> wrote in message
news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am getting an error in my .NET app and the error comes from a SQL Server
> sproc. I am able to capture the line number. My question is how to find
> that line? Do I start counting from CREATE PROCEDURE or from the AS
> statement? Or elsewhere?
>
|||Jerry Spivey wrote:

>Frank,
>Execute the sproc in Query Analyzer. When the error message appears in the
>result pane, double-click it - should highlight the line or general location
>of the error.
>
Unfortunately, this is not something that happens all the time. It
happens very rarely. When it does happen, the application captures and
writes out to a log all the info it can. This is where the line number
is found as well. Thus I am trying to troubleshoot a sproc based on a
line number.
So, that said, do I start counting from CREATE PROCEDURE or from the AS
statement?

>HTH
>Jerry
>"Frank Rizzo" <none@.none.com> wrote in message
>news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
>
>
>

Line Number

Hello,
I am getting an error in my .NET app and the error comes from a SQL
Server sproc. I am able to capture the line number. My question is how
to find that line? Do I start counting from CREATE PROCEDURE or from
the AS statement? Or elsewhere?Frank,
Execute the sproc in Query Analyzer. When the error message appears in the
result pane, double-click it - should highlight the line or general location
of the error.
HTH
Jerry
"Frank Rizzo" <none@.none.com> wrote in message
news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am getting an error in my .NET app and the error comes from a SQL Server
> sproc. I am able to capture the line number. My question is how to find
> that line? Do I start counting from CREATE PROCEDURE or from the AS
> statement? Or elsewhere?
>|||Jerry Spivey wrote:
>Frank,
>Execute the sproc in Query Analyzer. When the error message appears in the
>result pane, double-click it - should highlight the line or general location
>of the error.
>
Unfortunately, this is not something that happens all the time. It
happens very rarely. When it does happen, the application captures and
writes out to a log all the info it can. This is where the line number
is found as well. Thus I am trying to troubleshoot a sproc based on a
line number.
So, that said, do I start counting from CREATE PROCEDURE or from the AS
statement?
>HTH
>Jerry
>"Frank Rizzo" <none@.none.com> wrote in message
>news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
>
>>Hello,
>>I am getting an error in my .NET app and the error comes from a SQL Server
>>sproc. I am able to capture the line number. My question is how to find
>>that line? Do I start counting from CREATE PROCEDURE or from the AS
>>statement? Or elsewhere?
>>
>
>

Line Number

Hello,
I am getting an error in my .NET app and the error comes from a SQL
Server sproc. I am able to capture the line number. My question is how
to find that line? Do I start counting from CREATE PROCEDURE or from
the AS statement? Or elsewhere?Frank,
Execute the sproc in Query Analyzer. When the error message appears in the
result pane, double-click it - should highlight the line or general location
of the error.
HTH
Jerry
"Frank Rizzo" <none@.none.com> wrote in message
news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am getting an error in my .NET app and the error comes from a SQL Server
> sproc. I am able to capture the line number. My question is how to find
> that line? Do I start counting from CREATE PROCEDURE or from the AS
> statement? Or elsewhere?
>|||Jerry Spivey wrote:

>Frank,
>Execute the sproc in Query Analyzer. When the error message appears in the
>result pane, double-click it - should highlight the line or general locatio
n
>of the error.
>
Unfortunately, this is not something that happens all the time. It
happens very rarely. When it does happen, the application captures and
writes out to a log all the info it can. This is where the line number
is found as well. Thus I am trying to troubleshoot a sproc based on a
line number.
So, that said, do I start counting from CREATE PROCEDURE or from the AS
statement?

>HTH
>Jerry
>"Frank Rizzo" <none@.none.com> wrote in message
>news:O5ChPem1FHA.3188@.TK2MSFTNGP14.phx.gbl...
>
>
>

Line Graph One Data Point

Re: SQL Svr 2000

VS.NET 2003, with SQL Reporting Services (RS)

I have a graph that is requested to be a line graph, with data starting in Jan (hence one data point).

The graph lines are not showing, but markers do show.

Can a line graph, in RS, produce lines when there is only one month's data.

If so, pray tell, how?

Y axis is a count of projects by Complexity levels.

X axis are the months, which will range from Jan to Dec, when all the data is in.

I need to show Jan results.

A line chart "connects" data points. If there is only one data point at all, there will be no line.

-- Robert

sql

Line 1: Incorrect Syntax Near.......(is this a bug?)

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Line 1: Incorrect Syntax Near.......

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Limited user

I'd like to create a limited user on SQL Server 2005 to minimize any possible problems with my ASP.NET 2.0 website. My question is how limited can I make that user? What exactly does it need to be able to do?

(In case it makes a difference, my site utilizes membership, profiles, and roles, as well as some custom tables and stored procedures that I created.)

Thanks in advance for any advice! :-)

I usually create a SQL Server login that only has Execute permission on the stored procedures that are needed to run the site. That way, they have no other permissions as far as SQL Server is concerned, which means that you are largely protected against SQL Injection through the stored procedures, and also by the fact that the user has no permissions to do anything but fire the stored procedures you allow them to execute.

There is no SQL in the app itself. It's restricted purely to stored procs.

|||

Thanks for the reply! Are there specific stored procedures that a typical user must have execute permission for or do they need to have permission to execute all of them? I'm really concerned about security so I'd like to keep things as limited as possible without removing functionality or causing errors.

|||

Since you are only likely to create stored procs in response to the needs of the site, the user will need execute permission on all of them. If you create a stored proc that returns data to the page but don't give the user permssions on it, your site will fail.

DO NOT grant the user blanket EXECUTE. Grant execute on each proc individually eg

CREATE PROCEDURE MyProc

AS
BEGIN
SELECT field 1 FROM table1
END
GO
Grant Execute On MyProc to MyUser
GO

You can almost think of it as the Page is the user. The page needs to be able to call certain procs in order to display/insert/edit data. So you give it permission to run the procs.