datatype map - Native ODBC driver

Hi,

Our software uses native ODBC driver and Visual C++ 2003 to communicate with SQL Server 2005.

SELECT xml_schema_namespace(SCHEMA_NAME(schema_id) ,name) FROM sys.xml_schema_collections.

SQLDescribeColW returns -152 as the datatype for the column in the above select statement. However, I’m unable to find a preprocessor definition for -152. The datatype looks like an nvarchar. Is it an nvarchar? Where can I find the definition for this datatype?

I have discovered similar problems while bringing back SQL_VARIANT types. The returned subtype of a SQLVariant comes up with -16 and -25. I cannot find definitions for these sub types either.

Any help is appreciated.

Regards,

Venkat

-152 is the XML data type and is defined in SQLNCLI.H as SQL_SS_XML. SQLNCLI.H is the header file for the SQL Native Client ODBC driver and OLEDB provider for SQL Server 2005.

I would advice you use the SQL Native Client ODBC driver for the best performance and functionality with SQL Server 2005. You can download the redistributable from http://www.microsoft.com/downloads/details.aspx?FamilyId=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&DisplayLang=en

Regards,

Uwa.

|||

Thank you very much!

Do you know where i can find information about SQL variant sub types -16 & -25? Subtype -16 looks like an integer (4bytes) while subtype -25 looks like a bigint (8Bytes)

Regards,

|||

-16 = "SQL_INTEGER + SQL_SIGNED_OFFSET" ( = 4 + (-20) )

-25 = "SQL_BIGINT + SQL_SIGNED_OFFSET" ( = -5 + (-20) )

So, basically they're SIGNED LONG and SIGNED BIGINT respectively, which explains their sizes of 4bytes and 8bytes.

You can find these defined in the header file SQLEXT.H:

#define SQL_BIGINT (-5)
...

#define SQL_SIGNED_OFFSET (-20)
#define SQL_UNSIGNED_OFFSET (-22)

....

#define SQL_C_SBIGINT (SQL_BIGINT+SQL_SIGNED_OFFSET) /* SIGNED BIGINT */
...

#define SQL_C_SLONG (SQL_C_LONG+SQL_SIGNED_OFFSET) /* SIGNED INTEGER */

As Uwa mentioned above, you'll need to search SQLNCLI.H for the SQL Server 2005 types. Here you can find (-152) defined:

#define SQL_SS_VARIANT (-150)
#define SQL_SS_UDT (-151)
#define SQL_SS_XML (-152)

|||Thanks a lot!sql

datatime error

I have some problem with datatime.

SELECT * FROM stat WHERE data > '2005-05-24 14:07:28' ORDER BY id Asc

I got the error:

Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Why? Format of date is the same in database?

Regards,
Hi
just try it this way:

SELECT *
FROM stat
WHERE
data > convert(varchar(10),'2005-05-24 14:07:28',101) ORDER BY id Asc

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

>I have some problem with datatime.
> SELECT * FROM stat WHERE data > '2005-05-24 14:07:28' ORDER BY id Asc
>I got the error:
>Microsoft OLE DB Provider for SQL Server error '80040e07'
>The conversion of a char data type to a datetime data type resulted in an
>out-of-range datetime value.
>Why? Format of date is the same in database?

Hi Zibi,

Assuming that "data" is declared as a [small]datetime column, then it
has no format in the database. The internal representation of datetime
is, in fact, a set of two integers (but the internal representation is
in fact not relevant).

For your query, the date/time constant is first converted to the
internal representation of either datetime or smalldatetime (to match
that of the "data" column), then the comparison is made. Obviously, the
first part (the conversion of '2005-05-24 14:07:28' to [small]datetime)
is the cause of your error. Obviously, some locale settings on your SQL
Server make it think that you use a yyyy-dd-mm hh:mm:ss format.

To prevent this kind of errors, use only the guaranteed safe formats for
date and date/time constants:

* yyyymmdd (date only - note: no dashes, slashes, dots or whatever)
* yyyy-mm-ddThh:mm:ss (date plus time - note: dashes between the
components of the date; colons between the components of the time and an
uppercase T to seperate date from time)
* yyyy-mm-ddThh:mm:ss.ttt (as above, but with milliseconds, seperated
from the time by a dot).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 31 May 2005 15:01:55 GMT, Chandra wrote:

>Hi
>just try it this way:
>SELECT *
>FROM stat
>WHERE
>data > convert(varchar(10),'2005-05-24 14:07:28',101) ORDER BY id Asc
>best Regards,
>Chandra

Hi Chandra,

This won't work, for two reasons.

First: if data is a datetime column (which I hope it is - otherwises,
the OP has a bag of other problems), then converting the constant to
varchar won't do any good. It is just an extra conversion to slow down
the process; in the end, it'll be converted to datetime in order to make
the comparison.

Second: the expression
convert(varchar(10),'2005-05-24 14:07:28',101)
returns the string constant '2005-05-24'. Since you're converting a
varchar constant to varchar, the stylle parameter is not used; you
simply get the first 10 characters. As a result, the time portion in
stripped and the query will return too many rows.

Third: since the format yyyy-mm-dd is not guaranteed safe either, this
version might result in the same error as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> For your query, the date/time constant is first converted to the
> internal representation of either datetime or smalldatetime (to match
> that of the "data" column), then the comparison is made. Obviously, the
> first part (the conversion of '2005-05-24 14:07:28' to [small]datetime)
> is the cause of your error. Obviously, some locale settings on your SQL
> Server make it think that you use a yyyy-dd-mm hh:mm:ss format.

Actually, this happens if you have a SET DATEFORMAT dmy somewhere,
explicitly or implicitly. While ymd is possible to set, it's rarely
used in practice. dmy, on the other hand is common with many
language settings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Uzytkownik "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppbovern3umptsgr3kimkai7ie1@.4ax.com...
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

> * yyyymmdd (date only - note: no dashes, slashes, dots or whatever)
> * yyyy-mm-ddThh:mm:ss (date plus time - note: dashes between the
> components of the date; colons between the components of the time and an
> uppercase T to seperate date from time)
> * yyyy-mm-ddThh:mm:ss.ttt (as above, but with milliseconds, seperated
> from the time by a dot).
Hi,

Thanks all.
I use exctly - SELECT COUNT(id) AS [stat_ile] FROM stat WHERE (data >
CONVERT(DATETIME, '2005-05-24 14:07:28',101)) and it works. I don't need to
use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt when I
use query analyzer but when I use simple SQL manager I see only format
yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Regard,|||Uzytkownik "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> napisal w
wiadomosci news:rhfp91pppbovern3umptsgr3kimkai7ie1@.4ax.com...
> On Tue, 31 May 2005 14:31:19 +0200, Zibi wrote:

> * yyyymmdd (date only - note: no dashes, slashes, dots or whatever)
> * yyyy-mm-ddThh:mm:ss (date plus time - note: dashes between the
> components of the date; colons between the components of the time and an
> uppercase T to seperate date from time)
> * yyyy-mm-ddThh:mm:ss.ttt (as above, but with milliseconds, seperated
> from the time by a dot).
Hi,

Thanks all.
I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIME,
'2005-05-24 14:07:28', 102))ORDER BY id Asc and it works. I don't need to
use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt when I
use query analyzer but when I use simple SQL manager I see only format
yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Regards,

Zibi|||Zibi (zibi@.nospam.com) writes:
> I use exctly - SELECT * FROM stat WHERE (data > CONVERT(DATETIME,
> '2005-05-24 14:07:28', 102))ORDER BY id Asc and it works. I don't need
> to use hours. I found that format in database is yyyy-mm-ddThh:mm:ss.ttt
> when I use query analyzer but when I use simple SQL manager I see only
> format yyyy-mm-ddThh:mm:ss. That's a litlle strange and mistaken.

Format in the database is binary. Then it is up to the tool to perform
a textual presentation.

This link may be helpful you:
http://www.karaszi.com/SQLServer/info_datetime.asp.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.se> napisal w wiadomosci
> Format in the database is binary. Then it is up to the tool to perform
> a textual presentation.
> This link may be helpful you:
> http://www.karaszi.com/SQLServer/info_datetime.asp.
>
Thnks - good site!

DataTable.select method

helloo

Can I use "like" in datatable.select method?

meaning:

Dim exp As String = "c.cDesc like " & txtSearch.Text & " + N'%' "
Dim rows() As DataRow = dtCenters.Select(exp)

knowing that txtSearch.text has unicode characters

Yes. However, your expression does not include quotes so you are probably not sending what you think you are sending.

Dim exp As String = "c.cDesc like '" & txtSearch.Text & "' + N'%' "

The better option is to use a parameter and set the value of the parameter to your textbox.

sql

Datasource Reader - Name for output column is blank.

Hi,

I have a problem using the odbc datasource reader to execute a sql command on a progress database. My query is something like:-

select max(id), sum(amount) from my_table

OR

select a, b, c, recid(my_table) from my_table

which produces external columns and output columns with no name. The progress sql doesn't support using aliases on column names and setting validateexternalmetadata to false and manually naming the input and output parameters in the 'Advanced Editor' doesn't seem to work either. I either get the error 'The name for output column "" is blank and columns can not be blank' or if I add my own column names in the input and output parameters it fails in the pre-execute phase saying it can't find a column in the datasource with name 'myalias'

I can get around the aggregate functions by transfering all the data and doing the aggregate on the local server but I also need to call functions such as recid() which I can't work around. SQL2000 DTS ignored these things and matched as best it could where SQL 2005 IS seems overly strict.

Has anyone encountered similar problems and does anyone have any ideas? I'm currently at a loss :(

Hi,

I don't know about 'Progress'. But just in a curiosity I am asking this. If you can't have a column alias how will you create a view using the statements that you have mentioned above. If there is any workaround there, u better try the same thing.

|||

At a stretch, it should be possible to create a script source which would execute this query and assign the results to columns in the data flow.

another possibility may be to use an Execute SQL task - if you're not returning too many rows.

However, are you sure column aliases are not supported? The Progress online documentation appears to suggest that it may be. there may be a way in progress to make this happen.

Donald

DataSet2 is using an internal DataSet1!

Hi,
I have a report with a dataset_1 (select * from SQL_ServeTable // Name:
"dsDATA"). Is it possible to use this dataset "dsDATA" in the same report
from an other dataset_2 for example "select sum(AMOUNT) from dsDATA"?
Thank you for your consideration,
RalphNo, you can not do as you envision. However you can put in expressions that
do aggragates. Look in the index of the help for aggregate functions.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ralph Hüttenmoser" <spam_no_spam@.bluewin.ch> wrote in message
news:evnFqsNzEHA.260@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a report with a dataset_1 (select * from SQL_ServeTable // Name:
> "dsDATA"). Is it possible to use this dataset "dsDATA" in the same report
> from an other dataset_2 for example "select sum(AMOUNT) from dsDATA"?
> Thank you for your consideration,
> Ralph
>|||Thanks Bruce

dataset with parameterized top query

I have a report based on a dataset that has a "top()" statement in it.

SELECT TOP (10) name, val FROM tab ORDER BY val DESC

I wanted wanted to return a report with 10 rows as the default.

But allow the user to change the default and regenerate the report with more rows.

I tried the following;

SELECT TOP (@.N) name, val FROM tab ORDER BY val DESC

Normally in VS2005, the paraemeters work fine for things like the WHERE clause. But when i do the TOP(@.N) the query/report parameter synchronization messes up. If i manual go to the parameter tab of the dataset and configure the matching between query and report params it works. But doing anything to the dataset resets this. Its getting very tiring.

Any advice for a newbie would be appreciated.

John

Hi,
you cannot do that without building dynamic SQL which is not best practice leaving you exposed to risks.
What you could do if your TOP parameter list is a constrained list like top = 10 or 20 or 50 or 100 or ALL would be to build your query with a series of If or Case statements where you evaluate the value of the TOP parameter and then build the select top query accordingly.
Another related question I have posted in this forum was how can I get the "Other" lumped into an extra result row. No easy answer so far, I wait for the silver bullet.
Philippe|||

Lets say i opt for the constrained list of top values as you suggest and write a use if/case as you suggest.

Would this not be also be dynamic SQL?

I saw another thread where someone was looking for a column that with a rank of the sorted rows.

rank name val

1 sam 9.4

2 ted 5.8

3 bob 2.4

if i could do this in the dataset sql, i could return all rows, then i could filter the rows in the report table with rank <= @.N

|||

I tried your if suggestion. Works in the dataset designer wizard in VS2005 just after entery sql code. But then testing the report it doesnt.

entering the dataset wizard again get me this error, "The Compound statement SQL construct or statement is not supported."

code:

if (@.N = 10)

begin

SELECT TOP (10) name, val FROM tab ORDER BY val DESC

end

if (@.N = 20)

begin

SELECT TOP (20) name, val FROM tab ORDER BY val DESC

end

if (@.N = 999)

begin

SELECT name, val FROM tab ORDER BY val DESC

end

|||I guess there are many ways to do it, here is a working example:

USE AdventureWorks
DECLARE @.TOP INT
SET @.TOP = 10

SELECT TOP (CASE @.TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

Philippe|||

That is much simpler. Thx

I have one question however;

this works in a report i configured

SELECT TOP (CASE @.TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

why doesnt this form work

SELECT TOP (@.TOP)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

it works in the dataset wizard. when i test the query the wizard prompts for @.top and the results work

however i run the run the report and the i get error. You mentioned dynamic queries previously. is you form not dynamic? The @.top param has to be evaluated at runtime either way.

john

|||

If you're using SQL 2005, I suggest looking at ranking functions. It's quite handy, with ROW_NUMBER()

http://www.aspfaq.com/sql2005/show.asp?id=11

http://www.sql-server-performance.com/ak_ranking_functions.asp

http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

I don't think the previous code is dynamic query, as TOP was a declared variable

not something like

EXEC 'SELECT TOP ' + @.TOP + ' * FROM table'

where you may get unwanted input (injection attack?)

dataset using stored proc with multi select params

I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.

In the data tab I'm currently using "text" for command type and :

Code Snippet

declare @.sql nvarchar(2000)

set @.sql = '
EXEC [Monitor] '' + @.p_OfferStatus + '''

exec sp_executesql @.sql, N'@.p_OfferStatus VARCHAR(100)', @.p_OfferStatus = @.p_OfferStatus

when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?

That depends on your code within the proc. You will normally have to split the values up to a table variable to make them searchable as they will be passed as 01,2,3,4. COuld you send your code over ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hey, thanks for the reply. I can send the code, but you probobly dont need it, I'm just using
WHERE field IN(@.parameter)
|||

Code Snippet

SET @.v_sql = '
SELECT
a.clientname AS Client,
s.Suptype_ID,
s.[name] AS [The Name],
convert(int, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) AS [Days Overdue],
CASE
WHEN ol. success_flag = 0 THEN ''FAILED''
WHEN ol. success_flag = 1 THEN ''SUCCESS''
ELSE ''''
END AS Processed,
s.datelastprocessed AS [Date Processed],
o.offerstatus_id AS [Offer Status],
CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END AS Auto
--ft.[File Name],
FROM #temp_suppressiontype s
LEFT OUTER JOIN #temp_1 ods ON ods.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_2 ol ON ol.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_3 p ON p.subtype_id = s.suptype_id
LEFT OUTER JOIN #temp_4 a ON a.clientkey_id = p.outsideadvertiser_clientkey_id
LEFT OUTER JOIN #temp_5 o ON o.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_6 os ON os.offerstatus_id = o.offerstatus_id
WHERE 1 = 1 ' +
CASE WHEN @.p_DaysOverdue IS NULL THEN
''
ELSE '
AND CONVERT(INT, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) >= ' + CONVERT(VARCHAR, @.p_DaysOverdue)
END +
CASE WHEN @.p_OfferStatus IS NULL THEN
''
ELSE '
AND o.offerstatus_id IN (' + CONVERT(VARCHAR, @.p_OfferStatus) + ')'
END +
CASE WHEN @.p_Auto IS NULL THEN
''
ELSE '
AND CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END IN (' + CONVERT(VARCHAR, @.p_Auto) + ')'
END + '
ORDER BY 8 DESC, 1, 3 ASC'
PRINT @.v_sql
EXEC sp_executesql @.v_sql

|||

You cannot do this in your procedure, the parameters will be passed as a delimited string, this works if you are using directly a query in Reporting Service, but won′t work using the sttored procedure interface. See my Split function which you can use inside your SQL Server to chop the values and join them to the original query.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1331765&SiteID=1

Jens K. Suessmeyer

http://www.sqlserver2005.de

dataset question

hi,
i am trying to run the following query in the dataset which works fine when
i have a number in the branch_code
SELECT tbl_calendar.date, tbl_day_type.day_type
FROM tbl_calendar INNER JOIN
tbl_day_type ON tbl_calendar.day_type = tbl_day_type.type_id
WHERE (tbl_calendar.date BETWEEN
(SELECT opened_date
FROM
HOBISINT.account_statistics.dbo.t_branches
WHERE branch_code = 56) AND GETDATE())
Problem occurs when i add a branch parameter (int) & make the following change
WHERE branch_code = @.branch)
i get the following error message
Application uses a value of the wrong type for the current operation.
thanks in advanceIs branch_code actually defined as an int in your table?
Can you run (SELECT opened_date FROM
HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
itself without an error?
"Tango" wrote:
> hi,
> i am trying to run the following query in the dataset which works fine when
> i have a number in the branch_code
> SELECT tbl_calendar.date, tbl_day_type.day_type
> FROM tbl_calendar INNER JOIN
> tbl_day_type ON tbl_calendar.day_type => tbl_day_type.type_id
> WHERE (tbl_calendar.date BETWEEN
> (SELECT opened_date
> FROM
> HOBISINT.account_statistics.dbo.t_branches
> WHERE branch_code = 56) AND GETDATE())
> Problem occurs when i add a branch parameter (int) & make the following change
> WHERE branch_code = @.branch)
> i get the following error message
> Application uses a value of the wrong type for the current operation.
> thanks in advance|||Yes it is int
yes i can run below without any errors
thanks for your interest
"daw" wrote:
> Is branch_code actually defined as an int in your table?
> Can you run (SELECT opened_date FROM
> HOBISINT.account_statistics.dbo.t_branches WHERE branch_code = @.branch) by
> itself without an error?
> "Tango" wrote:
> > hi,
> > i am trying to run the following query in the dataset which works fine when
> > i have a number in the branch_code
> >
> > SELECT tbl_calendar.date, tbl_day_type.day_type
> > FROM tbl_calendar INNER JOIN
> > tbl_day_type ON tbl_calendar.day_type => > tbl_day_type.type_id
> > WHERE (tbl_calendar.date BETWEEN
> > (SELECT opened_date
> > FROM
> > HOBISINT.account_statistics.dbo.t_branches
> > WHERE branch_code = 56) AND GETDATE())
> >
> > Problem occurs when i add a branch parameter (int) & make the following change
> > WHERE branch_code = @.branch)
> > i get the following error message
> > Application uses a value of the wrong type for the current operation.
> >
> > thanks in advance

DataSet Filter

I have one dataset that contains thousands of records i want to filter the dataset by one of the name

select col1,col2,col3 from tablename

this col1 contains various names i want to check wheather an name exists in the col1 or not

please guide me

Hi, Febin

For your problem there is two possible solutions, First one is RowFilter and second one is Select

Solution 1 (RowFilter)

Dim ds As New Data.DataSet

Dim CN As New SqlClient.SqlConnection(MyClass1.ConnStr)

Dim Stmt As String

Stmt = "select * from USER_MASTER"

Dim DA As New SqlClient.SqlDataAdapter(stmt, CN)

DA.Fill(ds, "USER_MASTER")

ds.Tables("USER_MASTER").DefaultView.RowFilter = "DEPT ='ADMIN'"

MsgBox(ds.Tables("USER_MASTER").DefaultView.Count)

Solution 2 (Select)

Dim ds As New Data.DataSet

Dim CN As New SqlClient.SqlConnection(MyClass1.ConnStr)

Dim Stmt As String

Stmt = "select * from USER_MASTER"

Dim DA As New SqlClient.SqlDataAdapter(stmt, CN)

DA.Fill(ds, "USER_MASTER")

Dim R() As DataRow

R = ds.Tables("USER_MASTER").Select("DEPT ='ADMIN'", "EMP_NAME")

If R.Length > 0 Then

MsgBox(R.Length & " Record(s) Found")

End If

In the sample code it shows the number of records found for dept called "ADMIN",

Happy Coding

sql

Dataset Distinct Query

I am trying to build a report with a prompt to select building. It all works fine except I in the prompt building is list as may times as there is data for it. I want it to only display once, but not effect the other data is there a way I can query this?

I have my 2 datasets and my parameter set up to display building to select from. My issue is I get multiple values in the Select Value Prompt. It will pull the correct data I just want to clean up the Select Value Prompt.

Example: of prompt list

Building 1

Building 1

Building 1

Building 2

Building 2

Dataset 1:

Select Building, First, Last

From Table

Dataset 2:

SELECT Building, First, Last

From Table

WHERE (Table.Building = @.Building)

Hi Ray,

You can use the DISTINCT clause:

Select DISTINCT Building, First, Last

From Table

Cheers,

Rob

|||That doesn't work|||What had to do is make a 3rd dataset in the report, w/ just the select distinct statement in it and then, change the available values dataset to the one that is just the select distinct.sql

DataSet Accessing more than one database

Is there a way we can create a dataset that extracts data from more than one
database?
Example:
SELECT d.field1, u.field1
FROM Databse1.dbo.tbl_Test d, Databse2.dbo.tbl_User u
WHERE d.ID= u.IDyou could create a VIEW in database1 that does a select * from
database2.dbo.tbl_user
that way from the report server point of view they both come from the
same database
NJ wrote:
> Is there a way we can create a dataset that extracts data from more than one
> database?
> Example:
> SELECT d.field1, u.field1
> FROM Databse1.dbo.tbl_Test d, Databse2.dbo.tbl_User u
> WHERE d.ID= u.ID

DataSet . . .?

For the my report in dataset i am using union. For example:
select * from tblOne
Union All
select * from tblTwo
Union All
select * from tblThree
Union All
select * from tblFour
But when i view the report it shows in different group order
select * from tblOne
Union All
select * from tblTwo
Union All
select * from tblFour
Union All
select * from tblThree
What do i need to do, so the way i wrote the code that i would like to see
groups?
Each select statement has column with group name.
Thanks.something like: (?)
select 'one' as GroupName, * from tblOne
Union All
select 'two' as GroupName, * from tblTwo
Union All
select 'four' as GroupName, * from tblFour
Union All
select 'three' as GroupName, * from tblThree
"GGill" <GGill@.discussions.microsoft.com> wrote in message
news:822E4C84-A22B-456C-AA23-3A14C122197D@.microsoft.com...
> For the my report in dataset i am using union. For example:
> select * from tblOne
> Union All
> select * from tblTwo
> Union All
> select * from tblThree
> Union All
> select * from tblFour
> But when i view the report it shows in different group order
> select * from tblOne
> Union All
> select * from tblTwo
> Union All
> select * from tblFour
> Union All
> select * from tblThree
> What do i need to do, so the way i wrote the code that i would like to see
> groups?
> Each select statement has column with group name.
> Thanks.|||I do not have GroupName column with one, two . . .
What i am looking for is show data the way i wrote the code. Each select, in
this case i have four has product name. So i would like to show way is in
dataset.
"Jeje" wrote:
> something like: (?)
> select 'one' as GroupName, * from tblOne
> Union All
> select 'two' as GroupName, * from tblTwo
> Union All
> select 'four' as GroupName, * from tblFour
> Union All
> select 'three' as GroupName, * from tblThree
>
> "GGill" <GGill@.discussions.microsoft.com> wrote in message
> news:822E4C84-A22B-456C-AA23-3A14C122197D@.microsoft.com...
> > For the my report in dataset i am using union. For example:
> >
> > select * from tblOne
> > Union All
> > select * from tblTwo
> > Union All
> > select * from tblThree
> > Union All
> > select * from tblFour
> >
> > But when i view the report it shows in different group order
> >
> > select * from tblOne
> > Union All
> > select * from tblTwo
> > Union All
> > select * from tblFour
> > Union All
> > select * from tblThree
> >
> > What do i need to do, so the way i wrote the code that i would like to see
> > groups?
> > Each select statement has column with group name.
> >
> > Thanks.
>|||GGill,
I think it is obvious that 'the way you wrote the code' is not going to
give you the answer you want. What JeJe was suggesting was to simply add a
user defined expression to each SELECT statement to make it sort the way you
want. Such as :
SELECT '1' as SortOrder, table1.* FROM table1
UNION ALL
SELECT '2' as SortOrder, table2.* FROM table2
UNION ALL
SELECT '3' as SortOrder, table3.* FROM table3
ORDER BY SortOrder
the field "SortOrder" doesn't need to be in your table..it is an expression.
Michael C
"GGill" wrote:
> I do not have GroupName column with one, two . . .
> What i am looking for is show data the way i wrote the code. Each select, in
> this case i have four has product name. So i would like to show way is in
> dataset.
> "Jeje" wrote:
> > something like: (?)
> > select 'one' as GroupName, * from tblOne
> > Union All
> > select 'two' as GroupName, * from tblTwo
> > Union All
> > select 'four' as GroupName, * from tblFour
> > Union All
> > select 'three' as GroupName, * from tblThree
> >
> >
> > "GGill" <GGill@.discussions.microsoft.com> wrote in message
> > news:822E4C84-A22B-456C-AA23-3A14C122197D@.microsoft.com...
> > > For the my report in dataset i am using union. For example:
> > >
> > > select * from tblOne
> > > Union All
> > > select * from tblTwo
> > > Union All
> > > select * from tblThree
> > > Union All
> > > select * from tblFour
> > >
> > > But when i view the report it shows in different group order
> > >
> > > select * from tblOne
> > > Union All
> > > select * from tblTwo
> > > Union All
> > > select * from tblFour
> > > Union All
> > > select * from tblThree
> > >
> > > What do i need to do, so the way i wrote the code that i would like to see
> > > groups?
> > > Each select statement has column with group name.
> > >
> > > Thanks.
> >

DataSet - Inserted row ID

I have a dataset that uses generated stored procedures to do its select, insert, update, delete.

I am inserting a row to that dataset, and after the update, using the ID of newly created row.

This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0)

Any idea what happened / what I have to do to fix this?

Thnx!

Hi,

The SQL Server uses SELECT SCOPE_IDENTITY() to get the last ID of the table. I'd like to know if you're using this to update your data.

Also, could you let me know what is the newly added trigger doing. It might be preventing the scope identity from returning.

Dataset

When I make a dataset as parameter how can I make an ALL value which
doesn't
select the single values but all values instead.
Like
Month: all, jan, feb, etc.
when I choose all it's supposed to ignore it in the where clause, I tried
with case but it didn't work out, any clues on this one ?
Jack
--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 45077 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dkgive it a null value and adapt your query so it says : "SELECT ... WHERE
month LIKE '@.parameterMonth'"
"Jack Nielsen" wrote:
> When I make a dataset as parameter how can I make an ALL value which
> doesn't
> select the single values but all values instead.
> Like
> Month: all, jan, feb, etc.
> when I choose all it's supposed to ignore it in the where clause, I tried
> with case but it didn't work out, any clues on this one ?
> Jack
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at fÃ?Â¥ 45077 spam-mails.
> Betalende brugere fÃ?Â¥r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>
>|||Similarly...
"Select * from mytable where (col1 = @.parm or @.parm is null)"
This can also be used w/ multiple parms
"Select * from mytable where (col1 = @.parm or @.parm is null) and (col2 =@.parm2 or @.parm2 is null)"
I've used this method in many queries/procs to avoid using dynamic sql...
Cheers!
--
Ben Sullins
"Koen" wrote:
> give it a null value and adapt your query so it says : "SELECT ... WHERE
> month LIKE '@.parameterMonth'"
> "Jack Nielsen" wrote:
> > When I make a dataset as parameter how can I make an ALL value which
> > doesn't
> > select the single values but all values instead.
> >
> > Like
> >
> > Month: all, jan, feb, etc.
> >
> > when I choose all it's supposed to ignore it in the where clause, I tried
> > with case but it didn't work out, any clues on this one ?
> >
> > Jack
> >
> >
> > --
> > Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> > Den har indtil videre sparet mig for at fÃ?Â¥ 45077 spam-mails.
> > Betalende brugere fÃ?Â¥r ikke denne besked i deres e-mails.
> > Hent gratis SPAMfighter her: www.spamfighter.dk
> >
> >
> >

DataRow syntax

command.CommandText = "SELECT UserName from Users WHERE UserID = " = userID

Executing this command returns one table with one column with one row. What is the syntax for getting that value into a variable? I can get the information into a dataSet but I can't get it out. Should I be using a dataSet for this operation?

The rest of the code so far:

SqlDataAdapter dataAdapter =newSqlDataAdapter();

dataAdapter.SelectCommand = command;

dataAdapter.TableMappings.Add("Table","Users");

dataSet =newDataSet();

dataAdapter.Fill(dataSet);

Using that code, your data would be in dataSet.tables[0].rows[0][0].

If that's always just returning one value, you might want to look into using ExecuteScalar instead of the adapter and dataset.

|||

You can just use executeScalar method of SQl command below is example from VB.Net help for scalar modified a little:

Public Function AddProductCategory( _ ByVal UserID As Integer, ByVal connString As String) As Integer Dim Username As string = "" Dim sql As String = "SELECT UserName from Users WHERE UserID = @.USERID" Using conn As New SqlConnection(connString) Dim cmd As New SqlCommand(sql, conn) cmd.Parameters.Add("@.USERID", SqlDbType.Int) cmd.Parameters("@.USERID").Value = newName Try conn.Open() userName = Convert.ToInt32(cmd.ExecuteScalar()) Catch ex As Exception Console.WriteLine(ex.Message) End Try End Using Return newProdIDEnd Function

Thanks

|||

Got it working ... thank you for your help!

DataReader source output help

I have configured my DataReader to use an ADO.net (ODBC) connectivity (entered Select * from AMPFM) in Sqlcommand and can see my database columns listed in the Advanced Editor / Column mappings window. My process needs to perform a straight column to column population from AMPFM table into my dbo.visitfinancials table. How do I point the output to the above table?

Add an OLE DB destination. Select the DataReader source, connect its green arrow to the OLE DB destination. Double click on the OLE DB destination and configure it for the appropriate connection manager (will have to click "New..." to create a new one) and table name. Be sure to use "Table or view - fast load" in the Data access mode drop down list.

Then select mappings on the left. Map your columns by dragging one to the other. When done, click OK. Run it. If your data types match, this is as easy as it gets.|||

That worked great, thanks Phil. Now of course, you knew my data types would not all match. I am getting the error "Column "ID" cannot convert between unicode and non-unicode string data types. I believe I came across one of your posts that stated to use a derived column to cast the field. My field (ID) is varchar (254) not null. Where would I enter this derived casting procecure?

|||In a derived column, add a new column.

This expression should do the trick.

(DT_STR,254,1252)[your_input_field]|||

Looks like I have ran into a snag. I navigated to DataReader Source / show advanced editor / Input and Output properties / Datareader Output / Output columns / clicked on the add column radio button and received the following error message:

Error at Data Flow task[DataReader Source[1]]: The component does not allow adding columns to this input or output. Additional information: Pipeline component has returned HRESULT error code 0xC0208019 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

Any ideas?

|||You need to use Derived Column transformation for that...it it an item in the toolbox in the data flow.|||

OK, I have added a derived column object from the toolbox, connected it to the datareader and then tried to connect it to my OLEDB data source but get an error saying there are no available inputs. I deleted the mapping for "ID" field between DataReader source and destination OLEDB and then tried to connect "ID" from derived column to the OLE DB data source adn get the same message. I feel like I'm getting close but not quite there yet. I need additiona assistance please.

|||You have the wrong OLE DB component. You have a source on there and you need the OLE DB DESTINATION component instead. It's toward the bottom of the list in the toolbox.|||

I believe I used the wrong terminology in my previous posts and confused the issue. I do have a DataReader source, Derived column control and OLEDB destination. Thanks.

|||The data reader source should be connected to the Derived Column, which should be connected to the OLEDB destination. Three boxes, two lines.

Datareader not referencing connection object

Please see following code :

SqlConnection conn=new SqlConnection(@."something...;");
SqlCommand comm=new SqlCommand("Select TOP 10 * FROM TableReaderTest WITH (HOLDLOCK) ",conn);

conn.Open();
SqlDataReader rd;
conn=null;

try
{
rd = comm.ExecuteReader();
rd.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

This Code works fine. I have set conn=null, still datareader is able to read the data. Why?

Thank you.

You set conn to null, you are setting thereference to null. Since your SqlCommand has it's own reference to the connection, the connection is still around. You should use a Finally block for the .Close() to ensure it is called.|||

Thank you for answering.

Yes it is bad code that I have set it null.

Please look following code:

rd=comm.ExecuteReader(<<behaviour close connection>>);
do someting.. donot close rd
comm.CommandText="another query"
rd=comm.ExecuteReader(<<behaviour close connection>>);
rd.close()

Is there First SQL Connection is still open?
Thank you.

|||

The only provider that will even allow such a thing is SQL Server 2005, otherwise you will get an error when you attempt to execute the second ExecuteReader.

Regardless of what it actually does, I wouldn't recommend assuming anything. It's ambiguous on what it SHOULD do, so don't rely on what it really does, and don't code that.

|||

Hi Motley,
Yes, we do not code like this way. But this is already done by other developers, and we are reviewing it.

Please have a look to following code (Assume try/catch is properly placed) :

privatevoid Form1_Load(object sender, System.EventArgs e)
{
SqlDataReader dr1=GetDataReader("Select 1"); //Line1
SqlDataReader dr2=GetDataReader("Select 2"); //Line2
dr1=GetDataReader("Select 3"); //Line3
dr1.Close(); //Line4
dr2.Close(); //Line5
}

private SqlDataReader GetDataReader(string qry)
{
SqlConnection conn=new SqlConnection(@."Server=localhost;uid=user;pwd=password;database=northwind;;");
SqlCommand comm=new SqlCommand(qry,conn);
conn.Open();
SqlDataReader rd;
rd = comm.ExecuteReader(CommandBehavior.CloseConnection);
comm=null;
conn=null;
return rd;
}

This works withSQL2000 also. Each time new instance of sqlDatareader is created and reference is returned.
At Line3, we are assigning new datareader to dr1. Will it close the dr1 previously opened (for "Select 1") automatically?
If 2 datareaders are not allowed open at the same time, how it is working?
At Line4 we are closing the dr1, but I think there are 2 different connections opened for dr1 and this will lose the last one. Right?

Thank you,

|||In this example, you are using a new connection for each data reader, and so you can get an additional datareader and assign it to dr1. However, in this case, dr1 will be closed (and in this example, the underlying connection) whenever the data reader object is garbage collected, which is a terrible idea.|||What doug said is correct.

datagrid

hey all

i have 2 tables
1. Location (locid, name)
2. Product (pid, locid, productname, ...etc)

right now i do a select * from product where locid ='locid', and a datagrid with colums
ProductId | ProductName | AvailableIn

how do i do a select * from Location where locid ='locid' and show Location Name in the datagrid?

ProductId | ProductName | AvailableIn
123456 | testing | Somewhere (instead of the locid)

thankstry this. I haven't tested this, but should give you a good indication of what is needed...

SELECT *, L.Name AS "AvailableIn'
FROM Product P
INNER JOIN Location L
ON P.locid=L.locid
WHERE P.locid='locid'

Hope that helps!
KJ
(The INNER JOIN will list all products that have a corresponding entry on the Location table. If that column was optional, you can use a LEFT JOIN)|||It should probably be

SELECTP.*, L.Name AS 'AvailableIn'......

KJ

DataFile

Select * into MedicalHistory_2001 from MedicalHistory where
year(Creationdate) = 2001
Select * into MedicalHistory_2002 from MedicalHistory where
year(Creationdate) = 2002
Select * into MedicalHistory_2003 from MedicalHistory where
year(Creationdate) = 2003
Before using the above three commands my 'Health_Data' DataFile size is 3GB
but now its 8.5GB, can I shrink the datafile ?
I have used the following command but doesn't effect.
backup log Health with truncate_only
dbcc shrinkfile('Health_Log',EmptyFile)
dbcc shrinkfile('Health_Data')
Any guidance?
Thanks in advance.
Joh wrote:
> Select * into MedicalHistory_2001 from MedicalHistory where
> year(Creationdate) = 2001
> Select * into MedicalHistory_2002 from MedicalHistory where
> year(Creationdate) = 2002
> Select * into MedicalHistory_2003 from MedicalHistory where
> year(Creationdate) = 2003
> Before using the above three commands my 'Health_Data' DataFile size
> is 3GB but now its 8.5GB, can I shrink the datafile ?
> I have used the following command but doesn't effect.
> backup log Health with truncate_only
> dbcc shrinkfile('Health_Log',EmptyFile)
> dbcc shrinkfile('Health_Data')
> Any guidance?
> Thanks in advance.
Did you drop those tables? How large is each table? Run the following
and report back:
exec sp_spaceused -- what's the "unused" portion?
exec sp_spaceused MedicalHistory_2001 -- what's the "unused" portion?
exec sp_spaceused MedicalHistory_2002 -- what's the "unused" portion?
exec sp_spaceused MedicalHistory_2003 -- what's the "unused" portion?
David Gugick
Imceda Software
www.imceda.com
|||Year Space Unused
2001 - 190432 KB
2002 - 2592 KB
2003 - 2800 KB
2004 - 1160 KB
2005 - 984 KB
what I do now ?
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:ecmhPgdSFHA.2384@.tk2msftngp13.phx.gbl...
> Joh wrote:
> Did you drop those tables? How large is each table? Run the following
> and report back:
> exec sp_spaceused -- what's the "unused" portion?
> exec sp_spaceused MedicalHistory_2001 -- what's the "unused" portion?
> exec sp_spaceused MedicalHistory_2002 -- what's the "unused" portion?
> exec sp_spaceused MedicalHistory_2003 -- what's the "unused" portion?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> Year Space Unused
> 2001 - 190432 KB
> 2002 - 2592 KB
> 2003 - 2800 KB
> 2004 - 1160 KB
> 2005 - 984 KB
>
You only have about 200MB of unused space in those tables. You didn't
run the first query which gives you the stats for the entire database.
I'm assuming, though, that the results will not be much different.
If you want to recover the space, it sounds like you'll need to identify
and move or delete objects from the data file. I'm just assuming those
tables are large and responsible for the increase in data file size.
Look at the allocated portion to see how much space each table occupies
on disk.
David Gugick
Imceda Software
www.imceda.com
|||Here is the overall result.. need to do any thing ?
reserved data index_size
unused
-- -- -- --
7653328 KB 3488344 KB 3879640 KB 285344 KB
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#9oZw3eSFHA.4028@.tk2msftngp13.phx.gbl...
> Joh wrote:
> You only have about 200MB of unused space in those tables. You didn't
> run the first query which gives you the stats for the entire database.
> I'm assuming, though, that the results will not be much different.
> If you want to recover the space, it sounds like you'll need to identify
> and move or delete objects from the data file. I'm just assuming those
> tables are large and responsible for the increase in data file size.
> Look at the allocated portion to see how much space each table occupies
> on disk.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||John wrote:
> Here is the overall result.. need to do any thing ?
>
> reserved data index_size
> unused
> -- -- --
> -- 7653328 KB 3488344 KB 3879640 KB
> 285344 KB
>
You only have 285MB of free space. There is no way to recover the 5.5GB
of used space because if is being used by objects.
As you can see you have 7.6GB reserves and 3.4GB of data and 3.8GB
indexes.
David Gugick
Imceda Software
www.imceda.com

databse properties causes mmc to close

Please help,
I am running SQL Server 2000 on a Windows 2000 server.
Whenever I open the Enterprise Manager and I right-click on any database and
select properties, the entire console just closes. No error or alert is cre
ated.
What should I doHave a look at Event log and see if there's anything there. Also, be sure to
update your sqlserver with latest service pack (sp3a).
-oj
http://www.rac4sql.net
"john" <anonymous@.discussions.microsoft.com> wrote in message
news:4BEAD47A-C47B-47ED-83EA-6AC9E00BE098@.microsoft.com...
> Please help,
> I am running SQL Server 2000 on a Windows 2000 server.
> Whenever I open the Enterprise Manager and I right-click on any database
and select properties, the entire console just closes. No error or alert is
created.
> What should I do|||I have SP3 loaded and I've checked the event logs but there is nothing regar
ding SQL.