Problem in inner join ?

Hai ,

select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

The output of above query is

emp_id count1

EMP10041 8
EMP10058 8
EMP10059 6
EMP10008 6
EMP10012 3
EMP10018 3
EMP10039 3
EMP10001 2

I have another table as user_table

user_id user_name

EMP10001 Raja
EMP10039 Ram
EMP10018 Ravi

etc

now i am writing a inner join as follows

select y.user_name, x.count1 from [select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc] x inner join [user_table] y on x.emp_id = y.user_id

I want to get the below format

Jambu 8
Elangovan 8
Ravi 6
Anitha 6
Ram 3

but i am getting the below error :

Invalid object name 'select emp_id,count(emp_id) as count1 from poll_data group by emp_id order by count1 desc

how to solve this

remove the [ ] & order by clasue on the sub query. Sub query should be enclosed with ( ).

Code Snippet

select

y.user_name

, x.count1

from

(select emp_id,count(emp_id) as count1 from poll_data group by emp_id) x

inner join [user_table] y on x.emp_id = y.user_id

|||

You need to enclose the derived table in parentheses -NOT square brackets.

Code Snippet

SELECT
u.User_Name,
p.Count1
FROM (SELECT
Emp_ID,
Count1 = count(Emp_ID)
FROM Poll_Data
GROUP BY Emp_ID
) p
INNER JOIN User_Table u
ON p.Emp_ID = u.User_ID

problem in Function ?

Create FUNCTION FUNCTION_NAT

(

@.F_BRANCH_CODE CHAR,

@.F_COUNTRY CHAR,

@.F_CLIENT_VALUE CHAR

)

RETURNS TABLE

AS

RETURN

(

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = @.F_BRANCH_CODE

AND

COUNTRY = @.F_COUNTRY

AND

CLIENT_VALUE = @.F_CLIENT_VALUE

)

This is my function . when i run it as follows

select * from FUNCTION_NAT('450','British','BRI')

i did not get any value it is empty .

But when i run this query

SELECT NLGIC_VALUE AS VALUE

FROM RAGHU.NAT

WHERE BRANCH_CODE = '450'

AND

COUNTRY = 'British'

AND

CLIENT_VALUE = 'BRI'

it works fine . What is the problem in my function .

My knee-jerk reaction is that this might be a problem with the type definitions of the function parameters; hold on and I will try to verify. Look at this:

Code Snippet

alter FUNCTION FUNCTION_NAT
(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)
RETURNS TABLE
AS
RETURN
( select @.f_branch_code as branch_code,
@.f_country as country,
@.f_client_value as client_value
)

go

select * from function_nat('450','British','BRI')

/*
branch_code country client_value
-- -
4 B B
*/

You need to alter your type definitions from this

Code Snippet

(
@.F_BRANCH_CODE CHAR,
@.F_COUNTRY CHAR,
@.F_CLIENT_VALUE CHAR
)

to something like this:

Code Snippet

(
@.F_BRANCH_CODE CHAR(xx),

@.F_COUNTRY CHAR(yy),
@.F_CLIENT_VALUE CHAR(zz)
)

where xx, yy, and zz are the maximum number of characters that will be passed through each argument.

|||

Kent - i think you've hit the nail on the head.

From BOL: When n is not specified in a data definition or variable declaration statement, the default length is 1

SQL will not throw an error but merely cut the string off at the length. From the look of at least one of your variables, you may be better off with the VARCHAR datatype.

HTH!

sql

problem in expressions

Hello,

When i select jan month i should display december and previous year using expressions. plz advice me.

You can do that pretty easily using the DateAdd function. The trick is to subtract a month from an appropriate date.

You can do this whether you start with a real date or just a part of a date, it just takes a few preliminary steps if you don't have a real date.

In your case, let's assume that you are "selecting jan month" using a drop down of some sort, so you need the preliminary steps. You can put together a value representing the month in your drop down together with a day value of 1 plus a string value of the current year, to represent a dummy date to be subtracted from.

Like this (where MySelection would be 1 for January):

Code Snippet

=CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW))

... you will need to adjust that string concatenation for your locale -- this is m-d-y format.

Now you can cast that string to a real date

Code Snippet

=CDATE(CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW))))

... and you can subtract a month from that date, using DateAdd

Code Snippet

=DateAdd(DateInterval.Month,-1, CDATE(CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW)))))

... and finally you can format the result to appear however you want (in this case, if MySelection is 1, you will see December 2006, but if it is 2 you will see January 2007).

Code Snippet

=Format(DateAdd(DateInterval.Month,-1, CDATE(CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW))))),"MMMM yyyy")

HTH,

>L<

problem in export of csv file into sql server

I am trying to export a CSV file into sql server. I wrote a query to perform select fields from csv file using OPENROWSET. I am getting all the values in a single column, but I need to get in three different columns. Any can body can help me



The query is as follows

Select * from OpenRowset ('MSDASQL','Driver= {Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=” Directory name” \;Extended properties=''ColNameHeader=True; Format=Delimited;''','select * from Sample.csv')



Sample.csv

col1, col2, col3 ---- all the values are in single cell of excel csv file
1, a, abc
2, sfasf, sdgagas



Output of query is

col1,col2,col3 --- all the values are coming in one column and with comma between them
1,a,abc
2,sfasf,sdgagas

can any body help me where i am going wrong?

Thanks in advance
suryamight be easier to just import the entire file to a staging table and the work with it from there.|||would u like to give me some sample code|||Doh - something wrong with my pc, please ignore 2 duplicate answers and refer the top one.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.
See this http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx fyi.

Problem in DB

Trying to run the following query
SELECT COUNT(*) AS Expr1
FROM TableA INNER JOIN
TableB ON TableA.Id = TableB.Id
WHERE (TableB.EmployeeId = 15)
I get the error
"Attempt to fetch logical page 1:0 in database XXXXXXX belongs to object
'ALLOCATION', not to object 'TableA'
Also when I run the query
SELECT COUNT(*) AS Expr1
FROM TableA INNER JOIN
TableB ON TableA.Id = TableB.Id
I get the error
"Could not open FCB for invalid file ID 0 in database XXXXXXX"
It seems that there is something wrong with the specific DB
Any suggestions ?
Thanks
YannisYannis
Run DBCC CHECKDB to fix some errors in the database
"Yannis Makarounis" <Yannis.Makarounis@.ace-hellas.gr> wrote in message
news:OlBD9QMZEHA.1480@.TK2MSFTNGP10.phx.gbl...
> Trying to run the following query
> SELECT COUNT(*) AS Expr1
> FROM TableA INNER JOIN
> TableB ON TableA.Id = TableB.Id
> WHERE (TableB.EmployeeId = 15)
> I get the error
> "Attempt to fetch logical page 1:0 in database XXXXXXX belongs to object
> 'ALLOCATION', not to object 'TableA'
> Also when I run the query
> SELECT COUNT(*) AS Expr1
> FROM TableA INNER JOIN
> TableB ON TableA.Id = TableB.Id
> I get the error
> "Could not open FCB for invalid file ID 0 in database XXXXXXX"
> It seems that there is something wrong with the specific DB
> Any suggestions ?
> Thanks
> Yannis
>

Problem in creating nullable columns using SELECT INTO in SQL Serv

Hi Everyone!
I have a problem that is basically a database issue in SQL Server 2000
compared to Sybase 12. I am working on a application that should support bot
h
SYbase and SQL Server as backend. My code works fine in Sybase but not in SQ
L
Server. Following is the query that will give a an idea of the issue.
Query:
--
SELECT Null as c1,
isnull( NULL,0) as c2
INTO #temp_tbl
Above query creates both c1 and c2 as nullable columns in Sybase. The same
query in SQL Server creates C1 as nullable and C2 as not null. There are
situation that result in null data and cause inserting NULL into not columns
in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the required
results. I am looking for a solution which would create c1 and c2 as nullabl
e
columns in SQL Server.
Please help in finding me a solution.
Thanks in advance.
SomeshSRV wrote:
> Hi Everyone!
> I have a problem that is basically a database issue in SQL Server 2000
> compared to Sybase 12. I am working on a application that should support b
oth
> SYbase and SQL Server as backend. My code works fine in Sybase but not in
SQL
> Server. Following is the query that will give a an idea of the issue.
> Query:
> --
> SELECT Null as c1,
> isnull( NULL,0) as c2
> INTO #temp_tbl
> Above query creates both c1 and c2 as nullable columns in Sybase. The same
> query in SQL Server creates C1 as nullable and C2 as not null. There are
> situation that result in null data and cause inserting NULL into not colum
ns
> in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the requir
ed
> results. I am looking for a solution which would create c1 and c2 as nulla
ble
> columns in SQL Server.
> Please help in finding me a solution.
> Thanks in advance.
> Somesh
Like this:
CREATE TABLE #temp_tbl (c1 INTEGER NULL, c2 INTEGER NULL /* !!! NO
PRIMARY KEY !!! */)
INSERT INTO #temp_tbl (c1, c2)
SELECT ...
Curiously enough, the following seems to give your desired result in
SQL Server 2000 but not in 2005. Which just goes to show the folly of
such proprietary "tricks" as this.
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT NULL, NULL) AS T(c1,c2);
David Portas
SQL Server MVP
--|||David Portas wrote:
> Curiously enough, the following seems to give your desired result in
> SQL Server 2000 but not in 2005. Which just goes to show the folly of
> such proprietary "tricks" as this.
> SELECT c1, COALESCE(c2,0) AS c2
> INTO T1
> FROM (SELECT NULL, NULL) AS T(c1,c2);
>
That was just a type coercian issue. The following tests OK on both
2000 and 2005 (8.00.760 and 9.00.1399.06)
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT CAST(NULL AS INTEGER),
CAST(NULL AS INTEGER)) AS T(c1,c2);
David Portas
SQL Server MVP
--

problem in creating database and table in one shot

I need to create a database and then create tables in it..

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]

-- create a new database
CREATE DATABASE [DB1] ON PRIMARY
( NAME = N'DB1', FILENAME = N'D:\DB1.mdf' ,
SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30720KB )
LOG ON
( NAME = N'DB1_log', FILENAME = N'D:\DB1_log.ldf' , SIZE = 2048KB , MAXSIZE = UNLIMITED , FILEGROWTH = 30720KB )
COLLATE Latin1_General_CI_AS

CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

If i run the above statement one after one, they work; however, if I run all of them together (or in sp), the following error raised:

Msg 2702, Level 16, State 2, Line 43
Database 'DB1' does not exist.

May I know how can i create a database and then immediately the tables in sp!

Thanks

use master;

go

drop database mydatabase;

go

create database mydatabase;

go

use mydatabase;

go

create table mytable(i int );

|||Thx, but it doesn't work in stored procedure.|||

HI;

EXEC('CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thx

problem in copying a table into another one

I am having problems to copy a table into another one

SELECT * INTO UserCopy FROM User WHERE User.ID IN (SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser)

with SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser I get : 1,2,3,10

now if I look the result in my table Usercopy I get the values for : 1,2,3,9,10

what can be the problem ? where does the 9 comes from

thank youwas the table empty when you started|||userCopy of course is empty because it is just created

user is full and category too
I want to extract datas from user depending on an ID list in category

thank you for helping|||I created table to test data and as I first though first
your logic is correct

If you return 1,2,3,10
that is what should be used to create your table.

Try to hardcode 1,2,3,10 and see what that returns.
or
just run the select instead of the into and what is returned.

Could you be looking at usercopy created by another owner.
Could you be selecting from a user a category table from another owner.|||ok I try ! and i come back :-)

thank you|||read the sticky at the top of the forum and posts what it asks you to give...

Problem importing from Excel

When I try to import data from Excel with the dts wizard I get the following eror message when I select Excel as a data source:

TITLE: SQL Server Import and Export Wizard

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})

The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})

BUTTONS:

OK

That is the complete error message as I get it. I used the copy message text feature and pasted it directly here.

I really hope someone can help with this one as it has me completely stumped and also unable to finish an assignment.

If this is a college assignment, consider consulting with your professor or other students. I find in my classes that many students doing an identical assignment will encounter the same software problems.

Additionally you may want to try exporting the data from Excel to a CSV and then importing the CSV file and see if you have better luck there.

Hopefully someone else can chime in some better help specific to your error.

Problem getting select data set for XML Auto, Elements into a table variable -

I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.If I put it like

select @.l_variable = (select my sql statement for Auto, Elements)
it gives me syntax error.

I can't do a select into a temp table from my select statement for XML Auto.

I can't use select into as well.

I can't enclose my entire select statment in paranthesis to put it in a table variable.

How do I proceed?|||Here is the actual query. I am trying to get the result of the select statement into the table variable:

declare @.i_Customer_Id int
,@.i_Role_ID int
,@.i_Base_URL varchar(255)

declare @.l_XML_Table table (XML_String varchar (8000))

select @.i_Customer_Id = 10
,@.i_Role_ID = 1
,@.i_Base_URL = 'http://www.NewWebSite.com'

select
MM.Module_Description Topic_Type
,PM.Page_Description Title
,@.i_Base_URL + PM.Page_URL URL
from
Role_Page_Map RPM
,Module_Master MM
,Page_Master PM

where RPM.Role_ID = @.i_Role_ID
and RPM.Customer_Id = @.i_Customer_Id

and RPM.Page_ID = PM.Page_ID
and PM.Module_ID = MM.Module_ID

for XML Auto, Elements|||You can't select FOR XML into a table. See BOL for more information.|||That's true. However can't the resultant xml string be taken in a varchar variable as well?

problem getting result set through a stored procedure call using VB.

Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.See if this helps: http://www.sqlxml.org/faqs.aspx?faq=104
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"abc" <er.nehasinghal@.gmail.com> wrote in message
news:1121936278.733465.80930@.g47g2000cwa.googlegroups.com...
Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.sql

Problem getting Recoreds from Store Proedure Between Dates...

i have a gridView and i want to get the recoreds between specific dates.. so i put two calendars to select the dates...

and they will filter the recoreds..(i seleced dates and it returns non recors and theres recored betwenn those dates and in a SQL View it works )

What can i do? Thanks

Store Proceure Function:

CREATE PROCEDUREsp_FindTnoaGrid@.SearchTnoanvarchar(14),@.beginDateas nvarchar(50),@.endDateas nvarchar(50)AS-- order by dbo.V_tnuot.t_erech desc--if (@.beginDate='%')or (@.endDate='%')SELECT dbo.V_tnuot.kod_lakoha, dbo.V_tnuot.t_erech, dbo.V_tnuot.t_peula, dbo.V_tnuot.scum_peulaassum, dbo.V_tnuot.strHpoalimSugKodTnua, dbo.V_tnuot.DescSugPeula,cast(year(cast(@.beginDateas datetime))as nvarchar)+'-'+cast(month(cast(@.beginDateas datetime))as nvarchar)+'-'+cast(day(cast(@.beginDateas datetime))as nvarchar)as try, dbo.V_tnuot.Hpoalim_HodeshSaharFROM dbo.V_tnuotLEFTOUTER JOIN dbo.tblCategTnuot_KodPeulaON dbo.V_tnuot.strHpoalimSugKodTnua = dbo.tblCategTnuot_KodPeula.idKodTnuaWHERE ( kod_lakoha = @.SearchTnoa)if (@.beginDate<>'%')and (@.endDate<>'%')SELECT dbo.V_tnuot.kod_lakoha, dbo.V_tnuot.t_erech, dbo.V_tnuot.t_peula, dbo.V_tnuot.scum_peulaassum, dbo.V_tnuot.strHpoalimSugKodTnua, dbo.V_tnuot.DescSugPeula, dbo.V_tnuot.Hpoalim_HodeshSaharFROM dbo.V_tnuotLEFTOUTER JOIN dbo.tblCategTnuot_KodPeulaON dbo.V_tnuot.strHpoalimSugKodTnua = dbo.tblCategTnuot_KodPeula.idKodTnuaWHERE ( kod_lakoha = @.SearchTnoa)and (dbo.V_tnuot.t_peulaBETWEENcast(@.beginDateas datetime)andcast(@.endDateas datetime) )GO

What happens if you call the stored procedure directly e.g. from a query window in SQL Server Management Studio, passing in the appropriate parameters? Do you get the results you expect?

Are you tripping up over the classic "time" issue - DateTime fields don't just have a date, they have a time component so "2007-08-21" is not the same as "2007-08-21 14:23:08" etc.

Problem function year in sql mobile

Dear , all

I have a problem in fucntion year in sql mobile , Use select Date_in from Test Where Year(Date_in) = 8

Brg

Tingnong

The YEAR function does not exist in SQL Compact, you can use:

Code Snippet

DATEPART(yy, date)

like so:

Code Snippet

select [Order Date] from Orders Where DATEPART(yy, [Order date]) = 1991

(Using Northwind.sdf inculded with SQL Compact 3.1 SDK)

Problem executing procedure and how can I use their results in a select statement

I have this Stored Procedure that walk through a table that stores hierarchical data and reorganize the output, so the resultset will be ordered by this hierarchy.

The table structure is (fieldnames in english between parentheses for better comprehension):

CD_CATEGORIA (CD_CATEGORY)
DS_CATEGORIA (DS_CATEGORY)
CD_CATEGORIAMAE (CD_MOTHERCATEGORY)

Here is the Stored Procedure code:

CREATE PROCEDURE [dbo].[sp_RetornaCategorias]

-- Add the parameters for the stored procedure here

@.ID int = 0

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @.TabelaSaida table(

cd_categoria int,

ds_categoria varchar(70),

nr_nivel int);

declare @.i int

select @.i = 0

-- keep going until no more rows added

while @.@.rowcount > 0

begin

select @.i = @.i + 1

insert @.TabelaSaida

-- Get all children of previous level

select Categorias.cd_categoria, Categorias.ds_categoria, @.i + 1

from Categorias, @.TabelaSaida AS TblSaida

where nr_nivel = @.i

and Categorias.cd_categoriamae = TblSaida.cd_categoria

end

-- Saída de dados

-- output with hierarchy formatted

select space((nr_nivel-1)*4) + ds_categoria

from @.TabelaSaida

order by nr_nivel

END

But when I try to execute this Stored Procedure, it runs but nothing is returned.

I'm using this code to execute it:

EXEC [dbo].[sp_RetornaCategorias]

@.ID = 1

Are there anything wrong with it? How can I fix this?

And how can I call a Stored Procedure and get its resultset from a SELECT statement?

Hi Juliano,

Your SP wont actually return anything unless you declare a Variable as OUTPUT.

Your SP's resultset is from the select statement.

There is great MSDN documentation on SP's here; http://msdn2.microsoft.com/en-us/netframework/aa479373.aspx

With regards to fixing it, im not entirely sure its broken yet.

|||

It looks like your insert into the tablevariable is based in a join against that same tablevariable, but when you start out, it's newly created and thus empty..
So, the insert would then yield 0 rows, and the loop will break.

Try to run the SQL statements in a query window, then you can see what happens in each step.

/Kenneth

|||

ur procedure and calling seems to be alright...just check the data in the tyables ur refering...and is there actually nething to be returned.......basically run the select query seperately and check..

this 1...does this gives ne values ?

select Categorias.cd_categoria, Categorias.ds_categoria

from Categorias, @.TabelaSaida AS TblSaida

and Categorias.cd_categoriamae = TblSaida.cd_categoria

|||

I don't see how this could possibly return any rows, since @.TableSaida that is used in the join is newly declared and created, and thus is also empty.

/Kenneth

Problem Doing SQL 2005 DB Restore - Media Families

I did a full DB backup that I am trying now to restore via the SQL Server Management Studio.

I select "Restore" and then "From Device" and point to the bak file that I want to restore from.

I then go into the Options page and check "Overwrite the existing database".

Below that, shows the Restore the database files as

RB_Data_Services_MSCRM

RB_Data_Services_MSCRM_Log

sysft_ftcat_documentindex

When I then click OK I get the following error. The Media Set has 3 Media Families but only 1 are provided. All members must be provided.

Any ideas as to what I did to do to be able to complete this restore?

Thank

Rick Bellefond

Have you treid using RESTORE statement from query analyzer?|||Check that Database Name of the Database your are restoring matches that of the database your are resoring too.|||

refer this it has the solution

http://forums.microsoft.com/technet/showpost.aspx?postid=259647&siteid=17&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

Madhu

Problem Doing SQL 2005 DB Restore - Media Families

I did a full DB backup that I am trying now to restore via the SQL Server Management Studio.

I select "Restore" and then "From Device" and point to the bak file that I want to restore from.

I then go into the Options page and check "Overwrite the existing database".

Below that, shows the Restore the database files as

RB_Data_Services_MSCRM

RB_Data_Services_MSCRM_Log

sysft_ftcat_documentindex

When I then click OK I get the following error. The Media Set has 3 Media Families but only 1 are provided. All members must be provided.

Any ideas as to what I did to do to be able to complete this restore?

Thank

Rick Bellefond

Have you treid using RESTORE statement from query analyzer?|||Check that Database Name of the Database your are restoring matches that of the database your are resoring too.|||

refer this it has the solution

http://forums.microsoft.com/technet/showpost.aspx?postid=259647&siteid=17&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

Madhu

Problem Doing SQL 2005 DB Restore

I did a full DB backup that I am trying now to restore via the SQL Server Management Studio.

I select "Restore" and then "From Device" and point to the bak file that I want to restore from.

I then go into the Options page and check "Overwrite the existing database".

Below that, shows the Restore the database files as

RB_Data_Services_MSCRM

RB_Data_Services_MSCRM_Log

sysft_ftcat_documentindex

When I then click OK I get the following error. The Media Set has 3 Media Families but only 1 are provided. All members must be provided.

Any ideas as to what I did to do to be able to complete this restore?

Thank

Rick Bellefond

Looks like you created a backup that spanned multiple media families. You need to add in all of the pieces of the backup that you took for this to work.|||

Michael,

I did not intend to have my backup span multiple media families but I guess that is what I did.

I was not able to use that backup but found one that I could use.

Thanks.

Rick

Problem Deleting Rows

I have a table that was imported from an Excel 2003 worksheet. It has no
primary key. If I select one or more rows and try to delete them I get the
error:
Key column information is insufficient or incorrect. Too many rows were
affected by the update.
If I use QA I can delete the rows?
What is it trying to tell me here?
WaynePlease post the full DDL for your table, as well as the DELETE statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:e6dSRMHPGHA.3984@.TK2MSFTNGP14.phx.gbl...
I have a table that was imported from an Excel 2003 worksheet. It has no
primary key. If I select one or more rows and try to delete them I get the
error:
Key column information is insufficient or incorrect. Too many rows were
affected by the update.
If I use QA I can delete the rows?
What is it trying to tell me here?
Wayne|||> What is it trying to tell me here?
Two things. First, EM is not a good editing tool. Second, a table without
a primary key is, by definition, not a table.|||Interesting. Since I am importing from Excel I'll need to come up with some
autonumber key I guess?
Wayne
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e2uUW4HPGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Two things. First, EM is not a good editing tool. Second, a table
> without a primary key is, by definition, not a table.
>|||You can add an identity column to the target table and add a primary key
constraint on it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:ORjDEQIPGHA.812@.TK2MSFTNGP10.phx.gbl...
Interesting. Since I am importing from Excel I'll need to come up with some
autonumber key I guess?
Wayne
"Scott Morris" <bogus@.bogus.com> wrote in message
news:e2uUW4HPGHA.3732@.TK2MSFTNGP10.phx.gbl...
> Two things. First, EM is not a good editing tool. Second, a table
> without a primary key is, by definition, not a table.
>

problem creation, script with create view

I created a script like this :
use tk_main
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08701')
drop view V_08701
GO
CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
D_ATE,NO_ENVOI,ARRIVEE
GO
grant all on V_08701 to public
GO
if exists (select table_name from information_schema.views where table_name
= 'V95001') drop view V_95001
GO
CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
NO_FAC,NO_ENVOI_TK,ARRIVEE
GO
if exists (select table_name from information_schema.views where table_name
= 'V_08601') drop view V_08601
GO
CREATE VIEW V_08601
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO
IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
GO
but the analyser doesn't like this script.
Can someboady help me out.
Thanks in advance
RalfWhat error messages do you get?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message
news:40618606$0$7376$626a14ce@.news.free.fr...
> I created a script like this :
> use tk_main
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08701')
> drop view V_08701
> GO
> CREATE VIEW V_08701 (D_ATE,NO_ENVOI,ARRIVEE,TOT_COLIS) AS SELECT
> D_ATE,NO_ENVOI,ARRIVEE,SUM(NB_COLIS) FROM POSBAR_L GROUP BY
> D_ATE,NO_ENVOI,ARRIVEE
> GO
> grant all on V_08701 to public
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V95001') drop view V_95001
> GO
> CREATE VIEW V_95001 (NO_FAC,NO_ENVOI,ARRIVEE,NOMBRE) AS SELECT
> NO_FAC,NO_ENVOI_TK,ARRIVEE,COUNT(NO_ENVO
I_TK) FROM FACTURE_IMP_D GROUP BY
> NO_FAC,NO_ENVOI_TK,ARRIVEE
> GO
> if exists (select table_name from information_schema.views where
table_name
> = 'V_08601') drop view V_08601
> GO
> CREATE VIEW V_08601
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
> IDS,TYP_SCANNAGE) AS SELECT ARRIVEE.DTE_ARR_DEP, ARRIVEE.TRANSPORTEUR,
> ARRIVEE.ARRIVEE,POSBAR_E.NO_ENVOI,
> ARRIVEE.OPERATION_C_D,ARRIVEE.ETAT_ARRIVEE, POSBAR_E.ETAT,
> POSBAR_E.POIDS,POSBAR_E.TYP_SCANNAGE FROM ARRIVEE, POSBAR_E WHERE
> ARRIVEE.ARRIVEE = POSBAR_E.ARRIVEE
> GO
> --
> but the analyser doesn't like this script.
> Can someboady help me out.
> Thanks in advance
> Ralf
>
>[/color]|||Sorry I forgot to sedn the error :
Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
Ligne 2 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
Ligne 1 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
Ligne 3 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
Ligne 5 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
Ligne 7 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
Ligne 9 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
Ligne 11 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
Ligne 13 : syntaxe incorrecte vers 'GO'.
Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
Ralf
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> What error messages do you get?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Ralf Meuser" <rmeuser@.free.fr> wrote in message
> news:40618606$0$7376$626a14ce@.news.free.fr...
> table_name
> table_name
BY
> table_name
>
(DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,PO[col
or=darkred]
>|||Since this is an English speaking newsgroup, it would be helpful if you woul
d translate the French messages
instead of letting us do that.
I don't see a problem with this, unless you actually have a line-break in th
e middle of a column name in your
code as well (I assume it is inserted by your newsreader).
Perhaps someone has changed the batch separator (from GO to something else)
in Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ralf Meuser" <rmeuser@.free.fr> wrote in message news:406197b3$0$309$626a14ce@.news.free.fr.
.
> Sorry I forgot to sedn the error :
> Serveur : Msg 170, Niveau 15, tat 1, Procdure V_08701, Ligne 2
> Ligne 2 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 1
> Ligne 1 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 2
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 3
> Ligne 3 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 5
> Ligne 5 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 6
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 7
> Ligne 7 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 9
> Ligne 9 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 10
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 11
> Ligne 11 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 170, Niveau 15, tat 1, Ligne 13
> Ligne 13 : syntaxe incorrecte vers 'GO'.
> Serveur : Msg 111, Niveau 15, tat 1, Ligne 14
> 'CREATE VIEW' doit tre la premire instruction d'un lot de requtes.
>
> Ralf
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> a crit
> dans le message de news:em9NhSaEEHA.3980@.TK2MSFTNGP09.phx.gbl...
> BY
> (DTE,TRANSPORTEUR,ARRIVEE,NO_ENVOI,OPERA
TION_C_D,ETAT_ARRIVEE,ETAT_POSBAR,
PO
>

problem creating view on table from linked server DB using IP addr

Hello,
I can create a view on a table from a named linked server database.
select * from server1.RemoteDB.dbo.Table1
But I am having a problem creating a view on a table from a non-named linked
server that is just using the IP address of the server. Example:
Select * from [56.19.175.167].RemoteDB.dbo.Table1
When I run the view (in design mode) the square brackets get moved around
like this:
Select * from [56].[19.175.167.RemoteDB].dbo.Table1
The error message says it cannot find the server [56] and to re-run
sp_addlinkedserver. Could someone share the correct syntax for using the I
P
address as the server name?
Thanks,
Rich> When I run the view (in design mode)
STOP DOING THAT!
Create your view in Query Analyzer, and run the view in Query Analyzer.
Enterprise Mangler's tool for this is quite crippled and this is not the
only problem you'll encounter. Try using a CASE expression in your query,
for one.
A|||Just a few more details:
I am already aliasing the remote table
Select * from [56.19.175.167].RemoteDB.dbo.Table1 tblx
and
for the linked server that I can create a view on - that server resides on
the same server computer as the server I am working from.
The server I am having a problem with is a remote server which resides 3000
miles away from my local server. Does this make a difference?
"Rich" wrote:

> Hello,
> I can create a view on a table from a named linked server database.
> select * from server1.RemoteDB.dbo.Table1
> But I am having a problem creating a view on a table from a non-named link
ed
> server that is just using the IP address of the server. Example:
> Select * from [56.19.175.167].RemoteDB.dbo.Table1
> When I run the view (in design mode) the square brackets get moved around
> like this:
> Select * from [56].[19.175.167.RemoteDB].dbo.Table1
> The error message says it cannot find the server [56] and to re-run
> sp_addlinkedserver. Could someone share the correct syntax for using the
IP
> address as the server name?
> Thanks,
> Rich
>|||> Select * from [56.19.175.167].RemoteDB.dbo.Table1 tblx
Another thing to reduce the complexity here, of having IP addresses
hard-coded into your query, is to create a simply-named alias using Client
Network Utility, and then refer to the alias instead of the IP address. Not
that this makes it okay to use the view designer, but I think it is a better
approach overall. In addition to alleviating problems with 4-dot naming, it
also makes it much easier to update the system should that IP address
change - you just change the alias definition instead of all the places you
manually referred to it in code.