Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts

Friday, March 23, 2012

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type =
ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.
Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type =
> ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>
sql

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type =
ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote
3;"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I
got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type =
> ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote &#
123;"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN"
I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type = ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type => ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>

Wednesday, March 21, 2012

Problem with ID update after deleting rows

Hi,

I hope some of you can help me with thatSmile

I made an application that insert some data to MS SQL 2005 DB Express Edition. One of columns in my database store text. The content of that field must, beyond the existing text, append the current id to its text string. Practically, it means that if on row nr 15 I store text value "15text", on the next row i will store "16text". I figured out that I can get value of max ID by creating following stored procedure.

USE [tracking_db]

GO

/****** Object: StoredProcedure [dbo].[spMaxId] Script Date: 07/18/2007 09:31:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spMaxId]

@.maxId integer output

as

SELECT @.maxId= MAX(id) FROM FILES_TABLE

Once i get the maxID value i simply concat max id and string like:

string.Concat(max_id.ToString(), file_name);

where "max_id is integer return value from stored procedure and "file_name" is a string to rename like "max_id+file_name"

Two problems occur!!!

Problem nr 1.

Since I use to insert 10 new rows each time, the values from 0-9 are appended to text like "(0-9)text"

Problem nr 2.

If I delete some rows, ID does not get update. It means that after deleting all rows from table, next inserted item gets last existed ID before delting +1. New inserted item should get value 1 since table is empty after deleting all rows from it!!!

Hope some of you has any idea what to doSmile

Muris

i think this table ID coulumn is Identity column. In that case identity value has to be reset using DBCC Checkident command to reset the value. Otherwise remove the Identity column and change the insert logic.

select COLUMNPROPERTY (object_id('yourtablename'),'yourcolumnname' , 'IsIdentity')

if the above statement returns 1 it means its a identity column. So you should use DBCC Checkident command to reset the identity.

Madhu

|||

Hi Madhu,

You are right. My ID column is an identity column and it is also autoincremental. I wil now try to solve problem using your idea.

Muris

|||

Hi Madhu,

I find your answer logical and helpful, but I have difficulties to implement it.

Where should I put this DBCC Checkident to reset the identity. I use a stored procedure to find the max value of my identity column.

Simply, where to add the statement your suggested?

Muris

|||

Execute Mahdu's suggestion one time only -in a empty query window.

DBCC CHECKIDENT ( 'YourTable', RESEED )

|||

Suppose u have 1000 rows in the table and you are deleting rows where ID>900 . ie 900 to 1000 - 100 rows. After deletion when you insert what i understand is you should have the ID as 900 but you will get it as 1001. If this is the case. After deletion you need to reset the indentity. in this particular scenario

Delete from yourtablename where id>900 -- It will delete 100 rows

DBCC CHECKIDENT ( 'YourTable', 900) -- REset the value to 900 , if you have not run this

statement your next id will be 1001. You can

do this in sp also. read about this BOL

Insert the new value

Madhu

|||

HI,

I was away from my computer for several days so my reply is a bit late. Anyway I solved the problem with ID update using your suggestions with small modification.

Typing DBCC CHECKIDENT('YourTable', new value of ID) does not help unless you do not specify command RESEED so following statement is fully functional:

DBCC CHECKIDENT('YourTable', RESEED,new value of ID) .

In terms of deleting all rows from your table you should write

DBCC CHECKIDENT('YourTable',RESEED,1) .

The number 1 will be the ID of the newly inserted row in your previously cleaned table.

Thank you all for your help!

Regards

Muris

Problem with Group Totals and Counts

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

Hi Maria,

Nice to see a well articulated problem for a change Smile

What you describe sounds a bit strange so let me tell you what I would expect to see happening and please correct me if I'm wrong.

You say that you have 234 row coming back, in which there are 23 unique accountID's. By adding a list and grouping it by accountID I would expect to see one of 2 results depending on where the expression resides:

In a textbox inside the list I would expect to see 1. This is because you are groupping by this field

In a textbox outside the list I'd expect to see 23

|||

Adam - thank you so much for responding.

I tried what you suggested and unfortunately it did not work. Let me elaborate on a couple of things - maybe it will help.

-If I remove all of the filters in the group section, then I get all 234 rows displayed. And the total is correct as 234.

-When I add the filter in I get the only 23 rows displayed but the count STAYS as 234. So it's almost as though it does not take the filter into consideration when doing the distinct count.

It is very very strange as it defies all logic for me!

|||

Can you elaborate further please. Can you please post the following:

the Group expression for your list control

the filter applied to the list

the textbox expression that displays the incorrect result

|||

Hi Adam,

(1) The actual group expression for my list control is =Fields!ACCT_UNIT.Value

(2) The filter applied to the list is the following:

=sum(Fields!CMCM.Value) <= =-3.0

(3) The textbox expression for the field that displays the incorrect result:

=CountDistinct(Fields!ACCT_UNIT.Value,"list4")

Thanks!

Maria

|||

Ok I figured something out...

If I add filter to the list - the total changes. But the totals don't change if I add a filter on the group level. I HAVE to add my filter on the group level, because one of them is an aggregate and you cannot have aggregates in your list filters unless it's a group filter.

|||Why not define it at both levels?|||

I thought of that...but I can't because one of my filters must be an aggregate and you cannot have aggregates in data region or data set filters.

I realize there is no other way around this within SRS but is there any way around this programmatically? Code in the report?

Problem with Group Totals and Counts

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

Hi Maria,

Nice to see a well articulated problem for a change Smile

What you describe sounds a bit strange so let me tell you what I would expect to see happening and please correct me if I'm wrong.

You say that you have 234 row coming back, in which there are 23 unique accountID's. By adding a list and grouping it by accountID I would expect to see one of 2 results depending on where the expression resides:

In a textbox inside the list I would expect to see 1. This is because you are groupping by this field

In a textbox outside the list I'd expect to see 23

|||

Adam - thank you so much for responding.

I tried what you suggested and unfortunately it did not work. Let me elaborate on a couple of things - maybe it will help.

-If I remove all of the filters in the group section, then I get all 234 rows displayed. And the total is correct as 234.

-When I add the filter in I get the only 23 rows displayed but the count STAYS as 234. So it's almost as though it does not take the filter into consideration when doing the distinct count.

It is very very strange as it defies all logic for me!

|||

Can you elaborate further please. Can you please post the following:

the Group expression for your list control

the filter applied to the list

the textbox expression that displays the incorrect result

|||

Hi Adam,

(1) The actual group expression for my list control is =Fields!ACCT_UNIT.Value

(2) The filter applied to the list is the following:

=sum(Fields!CMCM.Value) <= =-3.0

(3) The textbox expression for the field that displays the incorrect result:

=CountDistinct(Fields!ACCT_UNIT.Value,"list4")

Thanks!

Maria

|||

Ok I figured something out...

If I add filter to the list - the total changes. But the totals don't change if I add a filter on the group level. I HAVE to add my filter on the group level, because one of them is an aggregate and you cannot have aggregates in your list filters unless it's a group filter.

|||Why not define it at both levels?|||

I thought of that...but I can't because one of my filters must be an aggregate and you cannot have aggregates in data region or data set filters.

I realize there is no other way around this within SRS but is there any way around this programmatically? Code in the report?

Problem with Grid View

i seem to have problems with my grid view not displaying anything when if i test my SQLDataSource, it shows me rows of data.

i seem to have this problem only with my Stored procedure, or when i change my Database structure, like add fields

Here is my procedure: can i do this? select a row and also return a varchar?

ALTER PROCEDURE dbo.SelectUpload(@.FileNamevarchar(50),@.Return varchar(MAX) =NULLOUTPUT)ASSELECT *FROM UploadsWHERE FileName = @.FileNameRETURNSELECT WebPathFROM UploadsWHERE FileName = @.FileName

Assuming that Webpath is a column in Uploads, why do you need the two select statements? When you do Select * from Uploads, Webpath will part of the dataset that the sqldatasource returns. Why can't you do:

ALTER PROCEDURE dbo.SelectUpload

@.FileNamevarchar(50)

AS
SELECT *FROM Uploads
WHERE FileName = @.FileName
RETURN

|||

i was thinking of returning a table to display in a Grid View, and also a Web Path (eg. "~/Images/image.jpg") to be displayed as a image, something like that. so that i don't have to retrieve it from the Data Source or Grid View.

|||

I still think you can grab all the data you need using one select statement and then do what you need to do with the data:

For the data you want displayed in the gridview:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="value1" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="value1" HeaderText="value1" ReadOnly="True" SortExpression="Value1" />
<asp:BoundField DataField="value2" HeaderText="value2" SortExpression="value2" />
<asp:BoundField DataField="value3" HeaderText="value3" SortExpression="value3" />
</Columns>
</asp:GridView>

For the webpath (lets say you want to display in a label):

Dim dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), DataView)
Dim dr As DataRow
dr = dv.Table.Rows(0)
lblWebPath.Text = dr("WebPath").ToString()

Tuesday, March 20, 2012

Problem with generating stored procedures from script

Hi,
I am generating sored procedures from my script. It contains about 90 of
them, but for few I get a warning like:
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'STP_Set'. The stored procedure will still be
created."
What does it mean and can it cause some problems?
How to eliminate it?
Thank you.
PrzemoThis warning message is issued when a stored procedure is compiled but the
compiler cannot find all of the dependent stored procedures it references.
For example, if SPa calls SPb but SPb is compiled before SPa, you will
receive the warning. To eliminate the message, you need to compile all of
your procedures in dependency order.
--Brian
(Please reply to the newsgroups only.)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still
> be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||The stored procedure refers to another stored procedure that doesn't exist.
It is only a warning. If
you don't want that warning, you need to create the procedures in the right
order.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still b
e
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||You referenced an object named 'STP_Set' which doesn′t exists at the
execution of the create procedure script. If the table is created later in
the script you can ignore this message, otherwise you have to create this
objects (Manually or via script).
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still b
e
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||you may like to try a database build and see if you have anything broken in
your database - try DB Ghost (http://www.dbghost.com) - it has a build
component that takes a set of scripts, figures out the order and builds a
database which will quickly show you if you have any breakages.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still b
e
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo

Problem with generating stored procedures from script

Hi,
I am generating sored procedures from my script. It contains about 90 of
them, but for few I get a warning like:
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'STP_Set'. The stored procedure will still be
created."
What does it mean and can it cause some problems?
How to eliminate it?
Thank you.
PrzemoThis warning message is issued when a stored procedure is compiled but the
compiler cannot find all of the dependent stored procedures it references.
For example, if SPa calls SPb but SPb is compiled before SPa, you will
receive the warning. To eliminate the message, you need to compile all of
your procedures in dependency order.
--
--Brian
(Please reply to the newsgroups only.)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still
> be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||The stored procedure refers to another stored procedure that doesn't exist. It is only a warning. If
you don't want that warning, you need to create the procedures in the right order.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||You referenced an object named 'STP_Set' which doesn´t exists at the
execution of the create procedure script. If the table is created later in
the script you can ignore this message, otherwise you have to create this
objects (Manually or via script).
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Przemo" wrote:
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo|||you may like to try a database build and see if you have anything broken in
your database - try DB Ghost (http://www.dbghost.com) - it has a build
component that takes a set of scripts, figures out the order and builds a
database which will quickly show you if you have any breakages.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Przemo" wrote:
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo

Problem with generating stored procedures from script

Hi,
I am generating sored procedures from my script. It contains about 90 of
them, but for few I get a warning like:
"Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'STP_Set'. The stored procedure will still be
created."
What does it mean and can it cause some problems?
How to eliminate it?
Thank you.
Przemo
This warning message is issued when a stored procedure is compiled but the
compiler cannot find all of the dependent stored procedures it references.
For example, if SPa calls SPb but SPb is compiled before SPa, you will
receive the warning. To eliminate the message, you need to compile all of
your procedures in dependency order.
--Brian
(Please reply to the newsgroups only.)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still
> be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo
|||The stored procedure refers to another stored procedure that doesn't exist. It is only a warning. If
you don't want that warning, you need to create the procedures in the right order.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:3D491C7D-3CEF-4161-8DCA-188BE1D37A83@.microsoft.com...
> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo
|||You referenced an object named 'STP_Set' which doesn′t exists at the
execution of the create procedure script. If the table is created later in
the script you can ignore this message, otherwise you have to create this
objects (Manually or via script).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo
|||you may like to try a database build and see if you have anything broken in
your database - try DB Ghost (http://www.dbghost.com) - it has a build
component that takes a set of scripts, figures out the order and builds a
database which will quickly show you if you have any breakages.
regards,
Mark Baekdal
MSN m_baekdal@.hotmail.com
+44 (0)141 416 1490
+44 (0)208 241 1762
http://www.dbghost.com
http://www.innovartis.co.uk
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Przemo" wrote:

> Hi,
> I am generating sored procedures from my script. It contains about 90 of
> them, but for few I get a warning like:
> "Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object 'STP_Set'. The stored procedure will still be
> created."
> What does it mean and can it cause some problems?
> How to eliminate it?
> Thank you.
> Przemo

Friday, March 9, 2012

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.
Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.
|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:
> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
> > When in em, I try to open a table and return all rows, I get a weird error:
> > "The query cannot be executed because some o fthe files are missing or not
> > registered."
> > Also, I cannot connect to the database with a dataadapter in vs (2003)
> > I can, however, execute an SQL in Query Analyzer.
> > I have reinstalled several times. Some of the Google articles suggest that
> > it might be MDAC. I cannot install MDAC either. It says that the operation
> > is not supported by the OS (xp Pro) The components are already installed.
> > Any input would be greatly appreciated.

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error
:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest tha
t
> it might be MDAC. I cannot install MDAC either. It says that the operatio
n
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installatio
n
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
>

Problem with dynamic sql statement

Can someone tell me why this is not executing properly. No error
messages, just no rows returned. I need to have an output parameter
and a return value.
Thanks in advance
Julie Barnet
CREATE PROCEDURE dbo.sel_LookupChar
(
@.Lookup_Value NVarChar(30),
@.Lookup_Field NVarChar(30),
@.Lookup_Table NVarChar(30),
@.MyOutput nVarChar(100) OUTPUT
)
AS
Declare @.SqlStr VarChar(1000)
Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
From " + @.Lookup_Table + " Where " + @.Lookup_Field
Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
Exec(@.SqlStr)
return @.@.rowcount
GOUse ' not " for string delimiters.
Also, try PRINT @.SqlStr instead of EXEC, and show us the result.
"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0308270858.4563cc29@.posting.google.com...
> Can someone tell me why this is not executing properly. No error
> messages, just no rows returned. I need to have an output parameter
> and a return value.
> Thanks in advance
> Julie Barnet
> CREATE PROCEDURE dbo.sel_LookupChar
> (
> @.Lookup_Value NVarChar(30),
> @.Lookup_Field NVarChar(30),
> @.Lookup_Table NVarChar(30),
> @.MyOutput nVarChar(100) OUTPUT
> )
> AS
> Declare @.SqlStr VarChar(1000)
> Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
> From " + @.Lookup_Table + " Where " + @.Lookup_Field
> Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
>
> Exec(@.SqlStr)
> return @.@.rowcount
> GO

Wednesday, March 7, 2012

Problem with Distinct query

Hi,
I have a table(claims) with 10-15 clumns...like
claimno,serviceno,memno,serviceaff,ymdeff,ymdend....
I want to select distinct rows only for the claimno,serviceno,memno....non
distinct for rest of the clumns....I have a inner join on another
table(Members with unique memno) on memno.
If I say ( Select distinct
claimno,serviceno,memno,serviceaff,ymdeff,ymdend )...It will select
duplicate rows with claimno,serviceno,memno...I want unique
claimno,serviceno,memno and only corresponding columns for the rest ( like
serviceaff,ymdeff,ymdend.)
Select Distinct
CL.memno,Cl.serviceno,CL.memno,CL.serviceaff,CL.ymdend,CL.ymdeff.........
...
from claims CL inner join members MM
on CL.memno = MM.memno
Thanks for the Help !!!Veena,
I don't think your goal is achievable.
For example, you have the following data:
claimno serviceno memno serviceaff ymdeff ymdend
1 1 1 a b
c
1 1 1 a b
b
1 1 2 a b
c
you have the distinct set of claimno,serviceno,memno:
1,1,1
1,1,2
however, which serviceaff,ymdeff,ymdend set is the ONE CORRESPONDING set for
the distinct claimno,serviceno,memno set of 1,1,1? There is no ONE defined
corresponding set. Both a,b,c and a,b,b qualify as corresponding set.
In case you want to have ANY ONE corresponding set of additional columns
selected together with the distinct sets of the first few columns, you can
use the following technique which is for eliminating duplicates:
delete from emp
where rowid in (select rowid
from emp e
where e.rowid > (select min(x.rowid)
from emp x
where x.emp_no = e.emp_no)
)
hope this helps.
Quentin
"veena" <vgs@.yahoo.com> wrote in message
news:#76WtipSDHA.3768@.tk2msftngp13.phx.gbl...
> Hi,
> I have a table(claims) with 10-15 clumns...like
> claimno,serviceno,memno,serviceaff,ymdeff,ymdend....
> I want to select distinct rows only for the
claimno,serviceno,memno....non
> distinct for rest of the clumns....I have a inner join on another
> table(Members with unique memno) on memno.
> If I say ( Select distinct
> claimno,serviceno,memno,serviceaff,ymdeff,ymdend )...It will select
> duplicate rows with claimno,serviceno,memno...I want unique
> claimno,serviceno,memno and only corresponding columns for the rest ( like
> serviceaff,ymdeff,ymdend.)
> Select Distinct
>
CL.memno,Cl.serviceno,CL.memno,CL.serviceaff,CL.ymdend,CL.ymdeff.........
> ...
> from claims CL inner join members MM
> on CL.memno = MM.memno
> Thanks for the Help !!!
>|||Hi,
I will modify little bit the table
claimno serviceno memno serviceaff ymdeff ymdend
1 1 1 a b
c
1 1 1 a d
c
1 1 2 a b
c
2 1 2 a b
c
Here I want to select claimno, serviceno,memno 111,112,212 and
corresponding set of columns abc ,abc,abc .. Since,In my destination table,
I have to define claomno,serviceno as primary key, It says Key
violation...so, I want to reject 111 with adc row.
I hope this is possible....
Thanks|||No this is NOT possible since SQL Server does not have an idea whether you
want to have 1,1,1,a,b,c or 1,1,1,a,d,c. You have to specify it. You can
generate a row counter value by inserting the data into a new table which
has an identity key, and then modify the logic I attached in the original
response to filter out the duplicates (of the "key" columns).
"veena" <vgs@.yahoo.com> wrote in message
news:OJpoJkvSDHA.1912@.tk2msftngp13.phx.gbl...
> Hi,
> I will modify little bit the table
> claimno serviceno memno serviceaff ymdeff ymdend
> 1 1 1 a b
> c
> 1 1 1 a d
> c
> 1 1 2 a b
> c
> 2 1 2 a b
> c
> Here I want to select claimno, serviceno,memno 111,112,212 and
> corresponding set of columns abc ,abc,abc .. Since,In my destination
table,
> I have to define claomno,serviceno as primary key, It says Key
> violation...so, I want to reject 111 with adc row.
> I hope this is possible....
> Thanks
>
>
>