Adding a new row to SQL table

Hi all,

I have a database setup with a few rows and i would like to allow a user to create a new row of data. After some googling, I've deduced that I need to somehow use an onclick command to perform an insert command. here is my code as of right now:

protectedvoid ItemAdd_Click(object sender,EventArgs e)

{

int newnum = 4;int POnum = newnum ++;

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

string Description = textbox6.Text.ToString();

string Price = textbox7.Text.ToString();string UOM = textbox8.Text.ToString();

SqlDataSource2.InsertParameters.Clear();

SqlDataSource2.InsertParameters.Add("@.Item", Item);

SqlDataSource2.InsertParameters.Add("@.Quantity", Quantity);

SqlDataSource2.InsertParameters.Add("@.Part", Part);

SqlDataSource2.InsertParameters.Add("@.Description", Description);

SqlDataSource2.InsertParameters.Add("@.Price", Price);

SqlDataSource2.InsertParameters.Add("@.UOM", UOM);

SqlDataSource1.InsertCommand ="insert into ItemMaster "+

"values (12, @.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM)";

SqlDataSource2.Insert();

}

here's the aspx:

<%@.PageLanguage="C#"MasterPageFile="~/MasterPage.master"AutoEventWireup="true"CodeFile="createpo.aspx.cs"Inherits="Subpgs_Purchasing_createpo"Title="Create A PO" %>

<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">

<tablestyle="width: 600px; position: relative; top: 30px; height: 253px"cellpadding="10px">

<tr>

<tdstyle="width: 180px; text-align: left;"rowspan="2">

Select Vendor<br/>- or -<br/><asp:LinkButtonID="Linkbutton1"runat="server"OnClick="Linkbutton1_Click">Create Vendor</asp:LinkButton></td>

<tdstyle="width: 100px"rowspan="2">

<asp:ListBoxID="ListBox2"runat="server"DataSourceID="SqlDataSource1"DataTextField="Name"

DataValueField="ID"OnSelectedIndexChanged="ListBox2_SelectedIndexChanged"></asp:ListBox><asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"SelectCommand="SELECT * FROM [Vendor_info]"></asp:SqlDataSource>

</td>

<tdstyle="width: 180px; text-align: left;">

Job/Req.S.O. No.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Job"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

Terms</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Terms"runat="server"></asp:TextBox></td>

</tr>

<tr>

<tdstyle="width: 180px; text-align: left;">

F.O.B.</td>

<tdstyle="width: 100px">

<asp:TextBoxID="FOB"runat="server"></asp:TextBox></td>

<tdstyle="width: 180px; text-align: left;">

Ship To:<br/>

Address:<br/>

City / State / Zip:</td>

<tdstyle="width: 100px">

<asp:TextBoxID="Ship1"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship2"runat="server"></asp:TextBox>

<asp:TextBoxID="Ship3"runat="server"></asp:TextBox></td>

</tr>

</table>

<br/>

<br/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Purchase_Orders.mdf;Integrated Security=True;User Instance=True;Context Connection=False"

ProviderName="System.Data.SqlClient"SelectCommand="SELECT [Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber] FROM [ItemMaster]"DeleteCommand="DELETE FROM [ItemMaster] WHERE [PONumber] = @.PONumber"InsertCommand="INSERT INTO [ItemMaster] ([Item], [Quantity], [Part], [Description], [Price], [UOM], [Ammount], [PONumber]) VALUES (@.Item, @.Quantity, @.Part, @.Description, @.Price, @.UOM, @.Ammount, @.PONumber)"UpdateCommand="UPDATE [ItemMaster] SET [Item] = @.Item, [Quantity] = @.Quantity, [Part] = @.Part, [Description] = @.Description, [Price] = @.Price, [UOM] = @.UOM, [Ammount] = @.Ammount WHERE [PONumber] = @.PONumber">

<DeleteParameters>

<asp:ParameterName="PONumber"Type="Decimal"/>

</DeleteParameters>

<UpdateParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</UpdateParameters>

<InsertParameters>

<asp:ParameterName="Item"Type="String"/>

<asp:ParameterName="Quantity"Type="Decimal"/>

<asp:ParameterName="Part"Type="String"/>

<asp:ParameterName="Description"Type="String"/>

<asp:ParameterName="Price"Type="Decimal"/>

<asp:ParameterName="UOM"Type="String"/>

<asp:ParameterName="Ammount"Type="Decimal"/>

<asp:ParameterName="PONumber"Type="Decimal"/>

</InsertParameters>

</asp:SqlDataSource>

<br/>

<table>

<tr>

<td>Item #</td>

<td><asp:TextBoxID="textbox3"runat=server></asp:TextBox></td>

<td>Quantity</td>

<td><asp:TextBoxID="textbox4"runat=server></asp:TextBox></td>

<td>Part Number</td>

<td><asp:TextBoxID="textbox5"runat=server></asp:TextBox></td>

</tr>

<tr>

<td>Description</td>

<td><asp:TextBoxID="textbox6"runat=server></asp:TextBox></td>

<td>Unit Price</td>

<td><asp:TextBoxID="textbox7"runat=server></asp:TextBox></td>

<td>Unit of Measure</td>

<td><asp:TextBoxID="textbox8"runat=server></asp:TextBox></td>

</tr>

<tr>

<tdcolspan="6"align="center"><asp:ButtonID="ItemAdd"runat=servertext="Add Item"OnClick="ItemAdd_Click"/></td>

</tr>

</table>

<br/>

<br/>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="False"DataKeyNames="PONumber"

DataSourceID="SqlDataSource2">

<Columns>

<asp:CommandFieldShowEditButton="True"/>

<asp:BoundFieldDataField="Item"HeaderText="Item"SortExpression="Item"/>

<asp:BoundFieldDataField="Quantity"HeaderText="Quantity"SortExpression="Quantity"/>

<asp:BoundFieldDataField="Part"HeaderText="Part"SortExpression="Part"/>

<asp:BoundFieldDataField="Description"HeaderText="Description"SortExpression="Description"/>

<asp:BoundFieldDataField="Price"HeaderText="Price"SortExpression="Price"/>

<asp:BoundFieldDataField="UOM"HeaderText="UOM"SortExpression="UOM"/>

<asp:BoundFieldDataField="Ammount"HeaderText="Ammount"SortExpression="Ammount"/>

<asp:BoundFieldDataField="PONumber"HeaderText="PONumber"ReadOnly="True"SortExpression="PONumber"

Visible="False"/>

</Columns>

</asp:GridView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button1"runat="server"Text="Submit Data"OnClick="Button1_Click"/> <br/>

<br/>

<asp:LabelID="Label1"runat="server"Text="Please Verify Information below!"Visible="False"></asp:Label><br/>

<br/>

<tablealign="left">

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label4"runat="server"Text="Job/REQ.S.O. No."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label5"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label7"runat="server"Text="Terms"Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label8"runat="server"></asp:Label></td>

</tr>

<tr>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label10"runat="server"Text="F.O.B."Visible="false"></asp:Label></td>

<tdstyle="width: 200px; text-align: left;">

<asp:LabelID="Label11"runat="server"></asp:Label></td>

</tr>

</table>

<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataSourceID="SqlDataSource1"

Height="50px"Visible="False"Width="260px"BackColor="#CCCCCC"BorderColor="#999999"BorderStyle="Solid"BorderWidth="3px"CellPadding="4"CellSpacing="2"DataKeyNames="ID"ForeColor="Black">

<FooterStyleBackColor="#CCCCCC"/>

<EditRowStyleBackColor="#000099"Font-Bold="True"ForeColor="White"/>

<RowStyleBackColor="White"/>

<PagerStyleBackColor="#CCCCCC"ForeColor="Black"HorizontalAlign="Left"/>

<Fields>

<asp:BoundFieldDataField="ID"HeaderText="ID"ReadOnly="True"SortExpression="ID"/>

<asp:BoundFieldDataField="Name"HeaderText="Name"SortExpression="Name"/>

<asp:BoundFieldDataField="Street"HeaderText="Street"SortExpression="Street"/>

<asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/>

<asp:BoundFieldDataField="State"HeaderText="State"SortExpression="State"/>

<asp:BoundFieldDataField="Zip"HeaderText="Zip"SortExpression="Zip"/>

<asp:BoundFieldDataField="Telephone Number"HeaderText="Telephone Number"SortExpression="Telephone Number"/>

<asp:BoundFieldDataField="Contact Information"HeaderText="Contact Information"

SortExpression="Contact Information"/>

<asp:BoundFieldDataField="E-mail address"HeaderText="E-mail address"SortExpression="E-mail address"/>

<asp:BoundFieldDataField="Fax Number"HeaderText="Fax Number"SortExpression="Fax Number"/>

<asp:BoundFieldDataField="Comments"HeaderText="Comments"SortExpression="Comments"/>

</Fields>

<HeaderStyleBackColor="Black"Font-Bold="True"ForeColor="White"/>

</asp:DetailsView>

<br/>

<br/>

<br/>

<asp:ButtonID="Button2"runat="server"Text="E-Mail PO"Visible="False"OnClick="Button2_Click"/>

</asp:Content>

whenever I run this, I receive the following error and am not sure how to correct it:

System.Data.SqlClient.SqlException: Must declare the scalar variable "@.Item".

If someone could please explain to me the process here of what is going ion it would be greatly appreciated. My understanding is the @.item is just a variable if you will so i'm not sure why its stating i need to declare it.

Thank you all!

Welcome to the community. I would suggest you use the InsertCode option to insert code in your posts from next time. Lets start from the starting and then we will remove errors as we go.

Gizm0:

string Item = textbox3.Text.ToString();

string Quantity = textbox4.ToString();

string Part = textbox5.ToString();

I am assuming these are your textboxes. You should use Text property of textboxes like you used for others. Also, you don't have to use ToString() method. Text property returns the textbox value as a string.

|||

Hi Bullpit.

Sorry about that, I didn't see the code Icon there. Anyhow, I made the changes and continue to receive the same error. At first glance, does it at least look like i'm on the right path? I've always been more hardware/network inclined so a lot of the google searches and forums I used to piece this together are still Greek to me.

Thanks for the response!

|||

I don't use SqlDataSource as such. Since you are writing some code, you can instead use this code to insert values to your database. Extend this example to add more parameters.

protected void ItemAdd_Click(object sender, EventArgs e) {// connection string to your database SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");// open connection mySqlConnection.Open();// create command object to execute query SqlCommand mySqlCommand = mySqlConnection.CreateCommand();// set command text to the INSERT sql query mySqlCommand.CommandText = @."INSERT INTO Employee (Item, Quantity, Part...) VALUES( @.Item, @.Quantity, @.Part...)";// Add parameters mySqlCommand.Parameters.Add("@.Item", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Quantity", SqlDbType.VarChar); mySqlCommand.Parameters.Add("@.Part", SqlDbType.VarChar);// set parameter values mySqlCommand.Parameters["@.Item"].Value = textbox3.Text; mySqlCommand.Parameters["@.Quantity"].Value = textbox4.Text; mySqlCommand.Parameters["@.Part"].Value = textbox5.Text;// execute query mySqlCommand.ExecuteNonQuery();// close connection mySqlConnection.Close();}

You will need these namespaces.

using System.Data;
using System.Data.SqlClient;

|||

Hey BullPit,

I really appreciate the help! I was able to get this working using your code above. Now it looks like I just need to validate the input boxes to make sure the correct data is being entered in the fields.

Again, thanks!

sql

Adding a check box to a report

I need to create a nice fomatted report in RS and part of that is checking a box if the data = Yes. Is there a way to add controlsOk try this, I spent a little time but was able to find a way to make it work with a list control,
Add the list,
Drag and drop the Image control, bring in a checked and unchecked image and have them embedded in the report. You can even have the images stacked ontop of eachother, so that they will have the same alignment in the report row for row,
Go to the properties area for the image, go to the visibilty area, and choose Expression,
I find that the expression box works a little counter intuitive, so for the unchecked/other than "yes" box do this
=Fields!YourField.Value = "Yes"
Then If the field value is = "Yes" (make sure its cased correctly) then the unchecked box will appear,
=Fields!YourField.Value <> "Yes"
Conversley have it check for if it is not equal to "Yes" and It will have the checked box appear. It seems backwards but i guess the visibility expression takes the negative or something funny, Heres a lil screen shot of what i got,...


I dont see why this couldnt work in a table or matrix control if it came to that
Happy Reporting!!

adding 2 records

Hello,
I've been converting/normalizing data and inserting it to other tables.
What i would like to do is to create 2 records for a particular value if
it occurs in the denormalized table.
Any idea how i should do this?I assume you mean something like:
CREATE TABLE DenormTable
(
DenormID int PRIMARY KEY
, MainVal int
, Year2005Value int
, Year2004Value int
)
If so, you merely have to create an insert like so:
CREATE TABLE NormTable1
(
Norm1ID int PRIMARY KEY
, MainVal int
)
CREATE TABLE NormTable2
(
Norm2ID int IDENTITY(1,1) PRIMARY KEY
, Norm1ID int NOT NULL
, Val int
, Year int
)
INSERT INTO NormTable1 (Norm1ID, MainVal)
SELECT DenormID, MainVal FROM DenormTable
INSERT INTO NormTable2 (Norm1ID, Val, Year)
SELECT DenormID, Year2005Val, 2005 FROM DenormTable
INSERT INTO NormTable2 (Norm1ID, Val, Year)
SELECT DenormID, Year2004Val, 2002 FROM DenormTable
You can combine the two in a single statement, if necessary, with some CASE
statements or creating a temporary VIEW with a UNION.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think Outside the Box!
****************************************
*******
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u$%23PCme6FHA.564@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been converting/normalizing data and inserting it to other tables.
> What i would like to do is to create 2 records for a particular value if
> it occurs in the denormalized table.
> Any idea how i should do this?|||In my Delphi app connecting to Sql Server using ODBC.. When 3 or more users
updating the same table at the same time (Not the same record) there is a
delay for approx 10 sec, I tried to use Enterprise manager from another
machine to update the same table and found the same problem so the problem
is not in my program code..
Finally after trying to solve this i noticed that 1 pc is the reason of the
delay, and after changing this Client IP.. it work now great and if i change
the IP back to it's old value the problem raise again!!.
Could i know what is the relation between Sql server or ODBC and client
machine IPs'
any information will appreciated
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u$%23PCme6FHA.564@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been converting/normalizing data and inserting it to other tables.
> What i would like to do is to create 2 records for a particular value if
> it occurs in the denormalized table.
> Any idea how i should do this?

Add static calculated column AFTER dynamic columns in a matrix?

Is it possible to add a calculated static column to a matrix, after the
dynamic ones?
I have the following matrix to create:
Indicator Units 2001 2002 2003 2004 %Change
----
Volume M3 23 33 44 55 25%
Energy KJ 33 34 35 36 3%
etc.
The year values come from a dataset and may vary (sometimes only 1 year,
sometimes 5 or 6 years). The last column is not depending on the dataset and
takes the values of the last 2 dynamic columns to calculate the % change.
1) is it possible to add a static column after the dynamic ones?
2) can I refer to the last dynamic column (and the one before) in an
expression?
Thanks,
VincentI am not really sure if you can filter the totals at the
end of the dynamic columns or not , but you can place a
textbox after the matrix and specify the expression as
follows :
=First(Fields!FieldName1.Value) OR =Last(Fields!
FieldName2.Value)
OR you can have a table with just the footer and only one
column and specify the filter expression in this one place
holder in the table.
>--Original Message--
>Is it possible to add a calculated static column to a
matrix, after the
>dynamic ones?
>I have the following matrix to create:
>Indicator Units 2001 2002 2003 2004 %Change
>----
>Volume M3 23 33 44 55 25%
>Energy KJ 33 34 35 36 3%
>etc.
>The year values come from a dataset and may vary
(sometimes only 1 year,
>sometimes 5 or 6 years). The last column is not depending
on the dataset and
>takes the values of the last 2 dynamic columns to
calculate the % change.
>1) is it possible to add a static column after the
dynamic ones?
>2) can I refer to the last dynamic column (and the one
before) in an
>expression?
>Thanks,
>Vincent
>
>.
>

Add similar fields to many tables

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @.name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @.name
IF @.@.fetch_status = 0
BEGIN
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_date [DATETIME]
GO
ALTER TABLE @.name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur

I also want that if one column for a table exists; the other columns should be created rather than it quits.

Howdy!Folks, please help!
Or do i have to add the columns manually! :confused:

Howdy!|||Maybe if you are concerned about whether a column already exists it would be easier to run the four different ALTER statements separately rather than coding around possibilities...y'know, four scripts that you run against all the tables instead of one.

Just a suggestion.

But for a one-time modification like this, you have to be careful that you don't spend more time trying to devise the most optimal and elegant solution than it would take you to just knuckle down and do the dirty work.|||declare @.tablename varchar(50)
declare @.add_field varchar(255)

declare cur_tables cursor for
select name from sysobjects where type='u' and status not like '-%'

open cur_tables
fetch next from cur_tables into @.tablename
while @.@.fetch_status = 0
begin
--print 'table name is ' + @.tablename

set @.add_field = 'alter table ' + @.tablename + ' add created_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add created_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

fetch next from cur_tables into @.tablename
end
close cur_tables
deallocate cur_tables|||-- Run this and cut results to query window & run it.
-- Tim S

SET NOCOUNT ON;
SELECT
CASE WHEN col1.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_BY VARCHAR(25);' + CHAR(10) ELSE '' END +
CASE WHEN col2.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_DATE [DATETIME];'+ CHAR(10) ELSE '' END +
CASE WHEN col3.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_BY VARCHAR(25);'+ CHAR(10) ELSE '' END +
CASE WHEN col4.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_DATE [DATETIME];'+ CHAR(10) ELSE '' END
FROM INFORMATION_SCHEMA.TABLES tab
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col1
ON tab.TABLE_NAME = col1.TABLE_NAME AND tab.TABLE_SCHEMA = col1.TABLE_SCHEMA AND 'CREATED_BY' = col1.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col2
ON tab.TABLE_NAME = col2.TABLE_NAME AND tab.TABLE_SCHEMA = col2.TABLE_SCHEMA AND 'CREATED_DATE' = col2.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col3
ON tab.TABLE_NAME = col3.TABLE_NAME AND tab.TABLE_SCHEMA = col3.TABLE_SCHEMA AND 'MODIFIED_BY' = col3.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col4
ON tab.TABLE_NAME = col4.TABLE_NAME AND tab.TABLE_SCHEMA = col4.TABLE_SCHEMA AND 'MODIFIED_DATE' = col4.COLUMN_NAME
WHERE tab.TABLE_TYPE = 'BASE TABLE' AND -- tab.TABLE_NAME = 'test2' AND
( col1.COLUMN_NAME IS NULL OR col2.COLUMN_NAME IS NULL OR col3.COLUMN_NAME IS NULL OR col4.COLUMN_NAME IS NULL)

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?
Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen
|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:

> Mikus wrote:
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>
|||Mikus wrote:[vbcol=seagreen]
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server with remote source

How do i add linked server whoose source file is not located on server itself?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?Mikus wrote:
> How do i add linked server whoose source file is not located on server itself?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:
> Mikus wrote:
> > How do i add linked server whoose source file is not located on server itself?
> >
> > I want to create linked server which uses excel file as it's source, but
> > this file is located on my pc(named "mix")
> >
> > I want to do something like this...
> >
> > EXEC sp_addlinkedserver 'MikusExcelSource',
> > 'Jet 4.0',
> > 'Microsoft.Jet.OLEDB.4.0',
> > '\\mix\silo\2005 9 Sep_new.xls',
> > NULL,
> > 'Excel 5.0'
> > GO
> >
> > any ideas ?
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>|||Mikus wrote:
> This syntax work's and server is added, but when i try to see at the tables
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
>> Mikus wrote:
>> How do i add linked server whoose source file is not located on server itself?
>> I want to create linked server which uses excel file as it's source, but
>> this file is located on my pc(named "mix")
>> I want to do something like this...
>> EXEC sp_addlinkedserver 'MikusExcelSource',
>> 'Jet 4.0',
>> 'Microsoft.Jet.OLEDB.4.0',
>> '\\mix\silo\2005 9 Sep_new.xls',
>> NULL,
>> 'Excel 5.0'
>> GO
>> any ideas ?
>> Have you tried to add the linked server with the syntax you suggest?
>> There're no problems as such to add a linked server using an UNC path.
>> The only limitation that I've seen, is that if the files for the linked
>> server isn't placed on the server it self, you'll have to use this
>> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
>> add a linked server on Server A having the files placed on Server B,
>> I'll have to connect to Server A with Remote Desktop and open up QA and
>> run my query. If I open QA on my own desktop and connect to server A,
>> I'll get an error if I try to use the linked server.
>> There might be ways around this issue, but I've never been able to find
>> a way to get around it.
>> Regards
>> Steen
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add linked server with remote source

How do i add linked server whoose source file is not located on server itsel
f?
I want to create linked server which uses excel file as it's source, but
this file is located on my pc(named "mix")
I want to do something like this...
EXEC sp_addlinkedserver 'MikusExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\mix\silo\2005 9 Sep_new.xls',
NULL,
'Excel 5.0'
GO
any ideas ?Mikus wrote:
> How do i add linked server whoose source file is not located on server its
elf?
> I want to create linked server which uses excel file as it's source, but
> this file is located on my pc(named "mix")
> I want to do something like this...
> EXEC sp_addlinkedserver 'MikusExcelSource',
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> '\\mix\silo\2005 9 Sep_new.xls',
> NULL,
> 'Excel 5.0'
> GO
> any ideas ?
Have you tried to add the linked server with the syntax you suggest?
There're no problems as such to add a linked server using an UNC path.
The only limitation that I've seen, is that if the files for the linked
server isn't placed on the server it self, you'll have to use this
linked server using e.g. QA via Remote Desktop to the server. E.g. if I
add a linked server on Server A having the files placed on Server B,
I'll have to connect to Server A with Remote Desktop and open up QA and
run my query. If I open QA on my own desktop and connect to server A,
I'll get an error if I try to use the linked server.
There might be ways around this issue, but I've never been able to find
a way to get around it.
Regards
Steen|||This syntax work's and server is added, but when i try to see at the tables
(through SQL manager on my pc - mix) i get error - please look at the
screenshot:
http://www.svara-kontrole.lv/sql_srv_error.jpg
...
And i do not have permission to acces this server through RemDsktop
"Steen Persson (DK)" wrote:

> Mikus wrote:
> Have you tried to add the linked server with the syntax you suggest?
> There're no problems as such to add a linked server using an UNC path.
> The only limitation that I've seen, is that if the files for the linked
> server isn't placed on the server it self, you'll have to use this
> linked server using e.g. QA via Remote Desktop to the server. E.g. if I
> add a linked server on Server A having the files placed on Server B,
> I'll have to connect to Server A with Remote Desktop and open up QA and
> run my query. If I open QA on my own desktop and connect to server A,
> I'll get an error if I try to use the linked server.
> There might be ways around this issue, but I've never been able to find
> a way to get around it.
> Regards
> Steen
>|||Mikus wrote:[vbcol=seagreen]
> This syntax work's and server is added, but when i try to see at the table
s
> (through SQL manager on my pc - mix) i get error - please look at the
> screenshot:
> http://www.svara-kontrole.lv/sql_srv_error.jpg
> ...
> And i do not have permission to acces this server through RemDsktop
> "Steen Persson (DK)" wrote:
>
I think the error you get is due to the issue I descried. I've just
tried it myself, and if I look at the tables on my own desktop -
connected to the server where the linked server has been created- I
can't see the files and I get almost the same error message as you do.
If I log on to the server with RemoteDesktop, I can see the tables with
out any problems.
Unless somebody else know the solution to this issue, I think your only
option is to connect to the server in some way and then run your query
from there.
Regards
Steen

Add leading zero to field value via SP

Hi All,

I want to add a leading zero to a field based on a param that I create on the fly in my stored proc. I have a @.month which is created from my datetime param @.date.

@.Month needs to be char(2) but if the month is inputted as '04' I get '4 ' in the table (note the space after 4)

How can I add a leading zero to this field?
Set @.Year = right('0',1)year(@.Date) is spitting it's toys out.

Thanks,
Brett

prepend a '0' char to the front of it and take RIGHT('0'+yourString,2)

Add Foreign Key Constraint in filegroup

Hi,
I'd like to create a foreign key constraint and store it in a different
filegroup than "PRIMARY"
For this, I've created a new filegroup named "INDEX"
This is my script :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
(
[FIL_IDFOLDER]
) REFERENCES [dbo].[T_FOLDER] (
[FOL_IDFOLDER]
) ON DELETE CASCADE
ON [INDEX]
GO
And it doesn't work !
But what is very strange, is that I can create a primary key constraint
correctly on this filegroup :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
(
[FIL_IDFILE]
) ON [INDEX]
GO
And this works !
It would be great if someone can telle me where my error is !
ThanxFilegroups are containers for storing data. Data are either data pages or in
dex pages. The reason
you can specify a filegroup when you define a primary key is that it automat
ically creates an index
for you,. so the file group you specify defines where that index will be sto
red. A foreign key
doesn't come with an index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jerome.avoustin@.gmail.com> wrote in message
news:1137404237.380973.84080@.g43g2000cwa.googlegroups.com...
> Hi,
> I'd like to create a foreign key constraint and store it in a different
> filegroup than "PRIMARY"
> For this, I've created a new filegroup named "INDEX"
> This is my script :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
> (
> [FIL_IDFOLDER]
> ) REFERENCES [dbo].[T_FOLDER] (
> [FOL_IDFOLDER]
> ) ON DELETE CASCADE
> ON [INDEX]
> GO
> And it doesn't work !
> But what is very strange, is that I can create a primary key constraint
> correctly on this filegroup :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
> (
> [FIL_IDFILE]
> ) ON [INDEX]
> GO
> And this works !
> It would be great if someone can telle me where my error is !
> Thanx
>

add date range filter - brand newbie to reporting services

I've used report wizard to create a report - one of the fields in the report is a date field. I simply want a From Date: and To Date: filter which enables a user to select a range of dates to filter results. I know how to do this in .Net, but am not sure how to achieve this in RS. If anyone could shed some light, or point me to an thread which describes a similar process, I'd appreciate it.

thx

If you are using Visual Studio then for the data source your query should be just like a stored procedure with a parm...

Select col1, col2

From tbl1

Where col1 = @.parm1

When you open the report in Report Manager it should have a space for you to type in the value.

Now if you are using Report Builder then I don't know as I'm here looking for that answer myself.

|||

If you are using the report builder you just put the following the filter column for the date field.

Between @.SDate and @.EDate

Then go to your report parameters (right click on the upper left corner of the layout)

Click on each parameter SDate and EDate and set the type to Date/Time. It defaults as a string.

Hope this helps.

Jen

add column to exiting table

I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeIDYou cannot add a column in the middle of a table. When you do an ALTER
TABLE to add a column, it is added at the and of the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeID|||Big D wrote:

> I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
that
> is between FIRSTNAME and TypeCodeID named Active? I am hoping not having
to
> drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
Hi,
Yes, you use ALTER TABLE to add a column to an existing table, but columns
do not have any order, just as you have no control over the order of rows.
You can specify any order you desire for columns when you retrieve rows with
a SELECT statement.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--|||Short answer, you shouldn't care. This:
> ID SSN LastName FirstName Active TypeCodeID
is equivalent to:
> ID SSN LastName FirstName TypeCodeID Active
It has the same data, and the same properties. When you query the data, you
order the columns how you want, as you should never do:
select *
from table
Other than when you are testing.
That having been said, I know what you mean and sometimes you want to
reorder the columns for "ease of use." For this, you have to drop and
recreate the table in the order you want the columns.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I did many times previously.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||However, Big didn't want to drop and re-create the table, which is exactly w
hat EM does...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Right" <serdar@.senkronyazilim.com> wrote in message
news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
> An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I
> did many times previously.
> "Big D" <BigDaddy@.newsgroup.nospam> wrote in message news:eXsabilYFHA.1868
@.TK2MSFTNGP14.phx.gbl...
>|||Thanks everyone who helped.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vF52XpYFHA.3616@.TK2MSFTNGP15.phx.gbl...
> However, Big didn't want to drop and re-create the table, which is exactly
> what EM does...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Right" <serdar@.senkronyazilim.com> wrote in message
> news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
>

add column disaster

Hi all, got a disaster on my hands,
ive added a field to a merge replication through the enterprise manager, in
the create manage publications, and its propogate ok to one server, but to
another server, when the merge agent runs, the agents timesout trying to add
the field, and its locking the users at the subscriber out, this table has
about 3 million rows in it. ive tried deleteing the contents, and even
truncating the contents of the table at both publisher and subscriber, but
this also locks out the users.
so the problem is that i cant run the merge agent as the field wont add at
the subscriber, times out and stops, so non of the data changes in the other
tables are getting fed accross.
Any help would be great cheers Andrew
use profiler to determine what process is locking with the merge agent
running on the subscriber and kill it. Try to keep all users off this
problem subscriber until it has completed.
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:u7qot480FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all, got a disaster on my hands,
> ive added a field to a merge replication through the enterprise manager,
> in the create manage publications, and its propogate ok to one server, but
> to another server, when the merge agent runs, the agents timesout trying
> to add the field, and its locking the users at the subscriber out, this
> table has about 3 million rows in it. ive tried deleteing the contents,
> and even truncating the contents of the table at both publisher and
> subscriber, but this also locks out the users.
> so the problem is that i cant run the merge agent as the field wont add at
> the subscriber, times out and stops, so non of the data changes in the
> other tables are getting fed accross.
> Any help would be great cheers Andrew
>

Add blank rows to table

Hello,
I need to create a report with a fixed number of table rows. For example:
the table is fixed at 20 rows per page regardless of data retrieved. If say
10 rows are filled with data then the rest are blanks. If 25 rows of data is
retrieved then the first page is filled and next page has 5 rows filled with
the rest blank. I also need a running row number in the first column. I've
managed that with "RowNumber(Nothing)" but I am not sure if it will work with
blank rows too. Is there any way to accomplish this? Through report
designer or custom code?
Thanks,
RodneyI would add rows to the source dataset. If the data is coming from a stored
procedure, append a dummy column to the end of the dataset and put a 1 in it
if the row contained data and a 2 if it did not (this way you have something
to sort on so that the blank rows are at the end of the report. Use a
count() in the stored procedure to determine how many rows to append to the
end of the dataset.
"Rodney" wrote:
> Hello,
> I need to create a report with a fixed number of table rows. For example:
> the table is fixed at 20 rows per page regardless of data retrieved. If say
> 10 rows are filled with data then the rest are blanks. If 25 rows of data is
> retrieved then the first page is filled and next page has 5 rows filled with
> the rest blank. I also need a running row number in the first column. I've
> managed that with "RowNumber(Nothing)" but I am not sure if it will work with
> blank rows too. Is there any way to accomplish this? Through report
> designer or custom code?
> Thanks,
> Rodney|||How would I append blank rows to a select query? Are you suggesting a
temporary table where the data and blank rows are dumped into? Would this be
a problem in a multiuser app?
"Kebler" wrote:
> I would add rows to the source dataset. If the data is coming from a stored
> procedure, append a dummy column to the end of the dataset and put a 1 in it
> if the row contained data and a 2 if it did not (this way you have something
> to sort on so that the blank rows are at the end of the report. Use a
> count() in the stored procedure to determine how many rows to append to the
> end of the dataset.
> "Rodney" wrote:
> > Hello,
> >
> > I need to create a report with a fixed number of table rows. For example:
> > the table is fixed at 20 rows per page regardless of data retrieved. If say
> > 10 rows are filled with data then the rest are blanks. If 25 rows of data is
> > retrieved then the first page is filled and next page has 5 rows filled with
> > the rest blank. I also need a running row number in the first column. I've
> > managed that with "RowNumber(Nothing)" but I am not sure if it will work with
> > blank rows too. Is there any way to accomplish this? Through report
> > designer or custom code?
> >
> > Thanks,
> >
> > Rodney

add an attribute to an existing dimension

Hello experts,

Is there a possibility to add an attribute to an existing dimension? (AS2005)

Right now I solve this problem with delete and create this dimension, but it’s a bad solution because a delete all properties too.

Best regards

Alex

If the dimension is based off a table or view reference in the DSV, right-click the background of the DSV and select Refresh. If you are using a named query, you will need to add the field to the query.

Then, go to the dimension editor. The subset of the DSV displayed in the editor should now show the field. Drag that field to the attribute hierarchy list on the left. You now have a new attribute hierarchy.

Make sure its relationships to other attribute hierarchies are appropriately set.

B.

|||

Hello Bryan

Thank you very much. It’s very easy if you know where you have to click ^^

Have a nice day

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
Woody
Chris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>
|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)[vbcol=seagreen]
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name='''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> >
> > Hello All, I've got a script that will Create a Database and install the
> > tables onto a SQL server.
> > what code is used to add a user/username and password to allow the user
to
> > login?
> > I am aware that I can do this from the SQL Server, However, I've been
> > asked
> > to do it in the code.
> > I don't want to add a user to the network - just into 1 database. I
> > think
> > this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> >
> > I've looked at CreateUser. However when I try to run this I get an error
> > saying, "Could not find stored procedure 'createuser'"
> >
> >
> > Please help
> > TIA
> > Woody
> >
> >
>

add a Primary Key (or something like that) to a View

Hi,
I have a View like this in my SQL Server 2000:
CREATE VIEW vw_oas_linkhead
AS
SELECT *
FROM oas_linkhead
WHERE (cmpcode = SUSER_SNAME())
WITH CHECK OPTION
The problem is: When, inside an Access-applciation, I put a link to this
View, I can't delete records from the View unless I have a Primary Key
defined on the View.
I can define that Primary Key in Access, but when I refresh the Linked Table
(View) the Primary Key disspaears. This doesn't happen when the Table itself
has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
possiblity to put a Primary Key on a View in Sql Server (I thought this
isn't possible?) or to kind of simulate this on another way (with a check
constraint, ... ?)?
Does anybody has any idea?
Thanks a lot!
PieterI would assume that Access would pick up the underlying table's primary key definition. Do you have
such?
You can't define a PK on a view as the view doesn't store any data in itself. You can, in some
cases, make the view with SCHEMABINING and create a unique index on the view. The question is, of
course, whether Access would pick up on that. But that is a question for the Access experts.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a View like this in my SQL Server 2000:
> CREATE VIEW vw_oas_linkhead
> AS
> SELECT *
> FROM oas_linkhead
> WHERE (cmpcode = SUSER_SNAME())
> WITH CHECK OPTION
> The problem is: When, inside an Access-applciation, I put a link to this
> View, I can't delete records from the View unless I have a Primary Key
> defined on the View.
> I can define that Primary Key in Access, but when I refresh the Linked Table
> (View) the Primary Key disspaears. This doesn't happen when the Table itself
> has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
> possiblity to put a Primary Key on a View in Sql Server (I thought this
> isn't possible?) or to kind of simulate this on another way (with a check
> constraint, ... ?)?
> Does anybody has any idea?
> Thanks a lot!
> Pieter
>|||Well, the problem is that the underlying table doesn't have a Primary Key,
but in access it should.
I'm not allowed to change the udnerlying table, so I should put something on
theView...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> I would assume that Access would pick up the underlying table's primary
key definition. Do you have
> such?
> You can't define a PK on a view as the view doesn't store any data in
itself. You can, in some
> cases, make the view with SCHEMABINING and create a unique index on the
view. The question is, of
> course, whether Access would pick up on that. But that is a question for
the Access experts.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a View like this in my SQL Server 2000:
> > CREATE VIEW vw_oas_linkhead
> > AS
> > SELECT *
> > FROM oas_linkhead
> > WHERE (cmpcode = SUSER_SNAME())
> > WITH CHECK OPTION
> >
> > The problem is: When, inside an Access-applciation, I put a link to this
> > View, I can't delete records from the View unless I have a Primary Key
> > defined on the View.
> >
> > I can define that Primary Key in Access, but when I refresh the Linked
Table
> > (View) the Primary Key disspaears. This doesn't happen when the Table
itself
> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
a
> > possiblity to put a Primary Key on a View in Sql Server (I thought this
> > isn't possible?) or to kind of simulate this on another way (with a
check
> > constraint, ... ?)?
> >
> > Does anybody has any idea?
> >
> > Thanks a lot!
> >
> > Pieter
> >
> >
>|||The option for "putting something on the view" you find in my prior post. But the bigger question is
why the table doesn't have a PK...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Well, the problem is that the underlying table doesn't have a Primary Key,
> but in access it should.
> I'm not allowed to change the udnerlying table, so I should put something on
> theView...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> I would assume that Access would pick up the underlying table's primary
> key definition. Do you have
>> such?
>> You can't define a PK on a view as the view doesn't store any data in
> itself. You can, in some
>> cases, make the view with SCHEMABINING and create a unique index on the
> view. The question is, of
>> course, whether Access would pick up on that. But that is a question for
> the Access experts.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a View like this in my SQL Server 2000:
>> > CREATE VIEW vw_oas_linkhead
>> > AS
>> > SELECT *
>> > FROM oas_linkhead
>> > WHERE (cmpcode = SUSER_SNAME())
>> > WITH CHECK OPTION
>> >
>> > The problem is: When, inside an Access-applciation, I put a link to this
>> > View, I can't delete records from the View unless I have a Primary Key
>> > defined on the View.
>> >
>> > I can define that Primary Key in Access, but when I refresh the Linked
> Table
>> > (View) the Primary Key disspaears. This doesn't happen when the Table
> itself
>> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
> a
>> > possiblity to put a Primary Key on a View in Sql Server (I thought this
>> > isn't possible?) or to kind of simulate this on another way (with a
> check
>> > constraint, ... ?)?
>> >
>> > Does anybody has any idea?
>> >
>> > Thanks a lot!
>> >
>> > Pieter
>> >
>> >
>>
>|||I don't know why it doesn't have a Primary Key: It's a table of a big
Accountant Software, so I can't change anything to the table... :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> The option for "putting something on the view" you find in my prior post.
But the bigger question is
> why the table doesn't have a PK...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Well, the problem is that the underlying table doesn't have a Primary
Key,
> > but in access it should.
> > I'm not allowed to change the udnerlying table, so I should put
something on
> > theView...
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> I would assume that Access would pick up the underlying table's primary
> > key definition. Do you have
> >> such?
> >> You can't define a PK on a view as the view doesn't store any data in
> > itself. You can, in some
> >> cases, make the view with SCHEMABINING and create a unique index on the
> > view. The question is, of
> >> course, whether Access would pick up on that. But that is a question
for
> > the Access experts.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> >
> >> > I have a View like this in my SQL Server 2000:
> >> > CREATE VIEW vw_oas_linkhead
> >> > AS
> >> > SELECT *
> >> > FROM oas_linkhead
> >> > WHERE (cmpcode = SUSER_SNAME())
> >> > WITH CHECK OPTION
> >> >
> >> > The problem is: When, inside an Access-applciation, I put a link to
this
> >> > View, I can't delete records from the View unless I have a Primary
Key
> >> > defined on the View.
> >> >
> >> > I can define that Primary Key in Access, but when I refresh the
Linked
> > Table
> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
> > itself
> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
is
> > a
> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
this
> >> > isn't possible?) or to kind of simulate this on another way (with a
> > check
> >> > constraint, ... ?)?
> >> >
> >> > Does anybody has any idea?
> >> >
> >> > Thanks a lot!
> >> >
> >> > Pieter
> >> >
> >> >
> >>
> >>
> >
> >
>|||I see :-(. I suggest you post this to an Access forum to see whether you can define in Access what
column define uniqueness.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
>I don't know why it doesn't have a Primary Key: It's a table of a big
> Accountant Software, so I can't change anything to the table... :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
>> The option for "putting something on the view" you find in my prior post.
> But the bigger question is
>> why the table doesn't have a PK...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
>> > Well, the problem is that the underlying table doesn't have a Primary
> Key,
>> > but in access it should.
>> > I'm not allowed to change the udnerlying table, so I should put
> something on
>> > theView...
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> >> I would assume that Access would pick up the underlying table's primary
>> > key definition. Do you have
>> >> such?
>> >> You can't define a PK on a view as the view doesn't store any data in
>> > itself. You can, in some
>> >> cases, make the view with SCHEMABINING and create a unique index on the
>> > view. The question is, of
>> >> course, whether Access would pick up on that. But that is a question
> for
>> > the Access experts.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > I have a View like this in my SQL Server 2000:
>> >> > CREATE VIEW vw_oas_linkhead
>> >> > AS
>> >> > SELECT *
>> >> > FROM oas_linkhead
>> >> > WHERE (cmpcode = SUSER_SNAME())
>> >> > WITH CHECK OPTION
>> >> >
>> >> > The problem is: When, inside an Access-applciation, I put a link to
> this
>> >> > View, I can't delete records from the View unless I have a Primary
> Key
>> >> > defined on the View.
>> >> >
>> >> > I can define that Primary Key in Access, but when I refresh the
> Linked
>> > Table
>> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
>> > itself
>> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
> is
>> > a
>> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> this
>> >> > isn't possible?) or to kind of simulate this on another way (with a
>> > check
>> >> > constraint, ... ?)?
>> >> >
>> >> > Does anybody has any idea?
>> >> >
>> >> > Thanks a lot!
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Well I did, and I just got the answer!
It shoudl have been the nicest oslution if I could implement it on the view,
but now I have some VBA that puts the index on the linked view after I
refreshed everything... It seemsto work fine.
Thanks a lot for the effort!
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOSsf5A6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> I see :-(. I suggest you post this to an Access forum to see whether you
can define in Access what
> column define uniqueness.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >I don't know why it doesn't have a Primary Key: It's a table of a big
> > Accountant Software, so I can't change anything to the table... :-)
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> >> The option for "putting something on the view" you find in my prior
post.
> > But the bigger question is
> >> why the table doesn't have a PK...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> >> > Well, the problem is that the underlying table doesn't have a Primary
> > Key,
> >> > but in access it should.
> >> > I'm not allowed to change the udnerlying table, so I should put
> > something on
> >> > theView...
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> >> I would assume that Access would pick up the underlying table's
primary
> >> > key definition. Do you have
> >> >> such?
> >> >> You can't define a PK on a view as the view doesn't store any data
in
> >> > itself. You can, in some
> >> >> cases, make the view with SCHEMABINING and create a unique index on
the
> >> > view. The question is, of
> >> >> course, whether Access would pick up on that. But that is a question
> > for
> >> > the Access experts.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > I have a View like this in my SQL Server 2000:
> >> >> > CREATE VIEW vw_oas_linkhead
> >> >> > AS
> >> >> > SELECT *
> >> >> > FROM oas_linkhead
> >> >> > WHERE (cmpcode = SUSER_SNAME())
> >> >> > WITH CHECK OPTION
> >> >> >
> >> >> > The problem is: When, inside an Access-applciation, I put a link
to
> > this
> >> >> > View, I can't delete records from the View unless I have a Primary
> > Key
> >> >> > defined on the View.
> >> >> >
> >> >> > I can define that Primary Key in Access, but when I refresh the
> > Linked
> >> > Table
> >> >> > (View) the Primary Key disspaears. This doesn't happen when the
Table
> >> > itself
> >> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if
there
> > is
> >> > a
> >> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> > this
> >> >> > isn't possible?) or to kind of simulate this on another way (with
a
> >> > check
> >> >> > constraint, ... ?)?
> >> >> >
> >> >> > Does anybody has any idea?
> >> >> >
> >> >> > Thanks a lot!
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Add a coulm in a table, after a specific column

I don't think there is a quick shortcut. The way I have normally done this
is to create a new table, move the data, drop the original table and rename
the new table to the original name.

You can use SSMS to generate a script for this (that is if you make the
change via the graphical interface in SSMS). This option is available
through Generate Change Script in the Table Designer menu or if you
right-click the table editor.

HTH,

Plamen Ratchev
http://www.SQLStudio.comYou can use SSMS to generate a script for this (that is if you make the

Quote:

Originally Posted by

change via the graphical interface in SSMS). This option is available
through Generate Change Script in the Table Designer menu or if you
right-click the table editor.
>

Thanks!!!|||On Wed, 31 Oct 2007 16:56:29 +0100, D. wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>You can use SSMS to generate a script for this (that is if you make the
>change via the graphical interface in SSMS). This option is available
>through Generate Change Script in the Table Designer menu or if you
>right-click the table editor.
>>

>
>Thanks!!!
>

But please do check, double-check, and triple-check the script before
executing it on a live server. There have historically been some serious
flaws in the scripted code used, that might result in losing all your
data if you are unlucky. Maybe things have changed since the last time I
looked, but I'd never run a script generated by Enterprise Manager or
SQL Server Management Studio without prior minute inspection!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||>

Quote:

Originally Posted by

But please do check, double-check, and triple-check the script before
executing it on a live server. There have historically been some serious
flaws in the scripted code used, that might result in losing all your
data if you are unlucky. Maybe things have changed since the last time I
looked, but I'd never run a script generated by Enterprise Manager or
SQL Server Management Studio without prior minute inspection!
>

Ok,
could you please me tell me which part do I have to check?

I though it is the same script that Enterprise Manager launch when I save
the modification in the data structure...

D.|||D. (d@.d.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>But please do check, double-check, and triple-check the script before
>executing it on a live server. There have historically been some serious
>flaws in the scripted code used, that might result in losing all your
>data if you are unlucky. Maybe things have changed since the last time I
>looked, but I'd never run a script generated by Enterprise Manager or
>SQL Server Management Studio without prior minute inspection!
>>

>
Ok,
could you please me tell me which part do I have to check?
>
I though it is the same script that Enterprise Manager launch when I save
the modification in the data structure...

EM and Mgmt Studio generate the same crap.

There are many things to watch out for:

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @.@.transcount 0 BEGIN END.

o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>

Quote:

Originally Posted by

o Transsaction scope. The script has many small transactions, but there
should be one big transaction. That or just restore a backup if there
is any error.

Ok, I have to execute the DDL statements via jdbc so Ill put them inside a
unique transaction.

Quote:

Originally Posted by

>
o Constraints are restored with NOCHECK, that should be WITH CHECK.
That takes longer time, but the flip side is that the optimiser then
can trust the constraints. This can matter a lot in some cases.

Ok

Quote:

Originally Posted by

>
o Remove all "go" in the script, and wrap most statements in EXEC.
The way script lookas as generated, if there is a batch-aborting
error, the transaction is rolled back, and the rest of the statements
will be committed. An alternative is to wrap all batches in
IF @.@.transcount 0 BEGIN END.

I must remove all "go" statements because I'll execute the code via jdbc
driver.
I've not understood what do you mean with the phrase "wrap most statements
in EXEC" (sorry but I'm fairly new to SQLServer)

Quote:

Originally Posted by

>
o Rewiew that the script only includes the changes you intend. There
are situations where EM/SSMS may include a change that you have
abandoned.

Ok, I'll do.

I' also noticed that SSMS, before the ALTER TABLE statement put always some
instructions (in the automated generated SQL) that I don't unserstand
well...

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE xxxxxxx

Do you think that I have to execute them from my java program or can I start
directly with ALTER TABLE instruction?

Thanks,
D.|||D. (d@.d.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>o Remove all "go" in the script, and wrap most statements in EXEC.
> The way script lookas as generated, if there is a batch-aborting
> error, the transaction is rolled back, and the rest of the statements
> will be committed. An alternative is to wrap all batches in
> IF @.@.transcount 0 BEGIN END.

>
I must remove all "go" statements because I'll execute the code via jdbc
driver.
I've not understood what do you mean with the phrase "wrap most statements
in EXEC" (sorry but I'm fairly new to SQLServer)

If you from a client, it's actually easier. Don't remove the "go" in
the sense that you send the entire script at once, but execute each
batch separately, and abort the whole affair if there is an error.
Don't forget to add a IF @.@.trancount 0 ROLLBACK TRANSACTION in this case.

The full story is this: when there is an error in an SQL command, several
things can happen, depending on the error, and many of there errors
when you work with DDL abort the batch and rollback the transaction.
Batches is separated by the "go". This means that if one batch fails
and aborts the transaction, and you run the script from a query window,
the remaining statements will still be executed - but without a transaction.

The stuff about EXEC is that instead of "ALTER TABLE ...", you need

EXEC('ALTER TABLE ...')

This is because if you put all in one batch, the script may not compile,
because it may refer to columns that has not been created yet etc.

But since you run from JDBC, you should not need this.

Quote:

Originally Posted by

I' also noticed that SSMS, before the ALTER TABLE statement put always
some instructions (in the automated generated SQL) that I don't
unserstand well...
>
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE xxxxxxx
>
Do you think that I have to execute them from my java program or can I
start directly with ALTER TABLE instruction?

Include them. Some of these settings are save with the table and per
columns. Many of these settings are already in effect, but they do
not make any harm.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx